本文通过具体实例，展示了如何通过封装数据库连接、统一关闭资源操作及参数化SQL语句等方式，提升代码的整洁度与可维护性。
开篇先看一张图片：
一个会打扮的人，是绝对不能让自己变成图片上方的那副模样的，又丑又脏，又没有一个好的表情。
显然，图片下方的女人已经掌握了装扮之道，又干净又美丽，而且非常令人喜欢。
装扮，不仅仅是在女人身上，我们的代码也是一样的。
已经迫不及待了，我们也要装扮一下我们的代码，我们也要让她们变得很美丽。
准备好了吗？开始吧！！
我们给DAL层化化妆，让她更加美丽！！！
====================华丽分割线========================
一：数据库连接字符串：
写死了的字符串拼接：
1: Private connStr As String = "server=.;database=chargeMIS;UID=sa;PWD=123"
丑吗？我给你化化妆：
从配置文件读取：
配置文件如下：
1: <;?xml version="1.0" encoding="utf-8" ?>
2: configuration>;
3:
4: add key="connStr" value="server=.;database=chargeMIS;UID=sa;PWD=123"/>;
5: /appSettings>
6: connectionStrings/>;
7: system.diagnostics>
8: sources>;
9: !-- This section defines the logging configuration for My.Application.Log -->
10: source name="DefaultSource" switchName="DefaultSwitch">;
11: listeners>
12: add name="FileLog"/>;
13: !-- Uncomment the below section to write to the Application Event Log -->
14: !--<;add name="EventLog"/>-->
15: /listeners>;
16: /source>
17: /sources>;
18: switches>
19: add name="DefaultSwitch" value="Information" />;
20: /switches>
21: sharedListeners>;
22: add name="FileLog"
23: ype="Microsoft.VisualBasic.Logging.FileLogTraceListener, Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL"
24: nitializeData="FileLogWriter"/>;
25: !-- Uncomment the below section and replace APPLICATION_NAME with the name of your application to write to the Application Event Log -->
26: !--<;add name="EventLog" type="System.Diagnostics.EventLogTraceListener" initializeData="APPLICATION_NAME"/> -->
27: /sharedListeners>;
28: /system.diagnostics>
29: /configuration>调用：
1: Private connStr As String = System.Configuration.ConfigurationSettings.AppSettings("connStr")
二：数据库连接：
DAL层中的每个类库都会出现的一句数据库连接：
1: Dim conn As SqlConnection = New SqlConnection(connStr)
装扮：从一个基类中读取：
1: Public Class daBase
2: '''
3: ''' 创建一个数据库连接
4: '''
5: ''' 数据库连接字符串
6: '''
7: ''' 2011-4-2 14：45 by cjq
8: Public Function CreateConn(ByVal connStr As String) As SqlConnection
9: Return New SqlConnection(connStr)
10: End Function
11: End Class
调用：
1: Public Class sqlUserInfo
2: Inherits daBase
3: Implements Interfaces.IUserInfo
4: 5: Dim conn As SqlConnection = CreateConn(connStr)
6: 7: End Class
三：关闭数据库连接、关闭cmd等
在DAL层中只要有一个方法就会出现关闭数据库连接和关闭cmd等：
1: If Not IsNothing(conn) Then
2: conn.Close() 3: conn = Nothing
4: End If
5: If Not IsNothing(cmd) Then
6: cmd.Dispose() 7: cmd = Nothing
8: End If
9: 10: If Not IsNothing(dap) Then
11: dap.Dispose() 12: dap = Nothing
13: End If
让它从基类中编写重载方法调用，好，我们就这样装扮它：
1: Public Class daBase
2: '''
3: ''' 关闭相关对象
4: '''
5: '''
6: ''' 2011-4-2 14：45 by cjq
7: Public Sub Close(ByVal conn As SqlConnection)
8: If Not IsNothing(conn) Then
9: conn.Close() 10: conn = Nothing
11: End If
12: End Sub
13: 14: '''
15: ''' 关闭相关对象
16: '''
17: '''
18: ''' 2011-4-2 14：45 by cjq
19: Public Sub Close(ByVal cmd As SqlCommand)
20: If Not IsNothing(cmd) Then
21: cmd.Dispose() 22: cmd = Nothing
23: End If
24: End Sub
25: 26: '''
27: ''' 关闭相关对象
28: '''
29: '''
30: ''' 2011-4-2 14：45 by cjq
31: Public Sub Close(ByVal sdr As SqlDataReader)
32: If Not IsNothing(sdr) Then
33: sdr.Dispose() 34: sdr = Nothing
35: End If
36: End Sub
37: 38: '''
39: ''' 关闭相关对象
40: '''
41: '''
42: ''' 2011-4-2 14：45 by cjq
43: Public Sub Close(ByVal sdp As SqlDataAdapter)
44: If Not IsNothing(sdp) Then
45: sdp.Dispose() 46: sdp = Nothing
47: End If
48: End Sub
49: nd Class四：给sql语句传参
DAL层中每个方法都会有sql语句，但是如果使用参数传递，那么传参语句会很多！
1: Dim sql As String = "insert into UserInfo(UserName,[Password],StatusType,ComputerLabNo,Teachername) values (@UserName,@Password,@StatusType,@ComputerLabNo,@Teachername)"
1: Dim sqlparam As SqlParameter
2: sqlparam = New SqlParameter("@password", SqlDbType.VarChar)
3: sqlparam.Value = entityUserInfo.Password 4: cmd.Parameters.Add(sqlparam) 5: 6: sqlparam = New SqlParameter("@userName", SqlDbType.VarChar)
7: sqlparam.Value = entityUserInfo.UserName 8: cmd.Parameters.Add(sqlparam) 9: 10: sqlparam = New SqlParameter("@StatusType", SqlDbType.Char)
11: sqlparam.Value = entityUserInfo.StatusType 12: cmd.Parameters.Add(sqlparam) 13: 14: 15: 16: sqlparam = New SqlParameter("@ComputerLabNo", SqlDbType.Int)
17: sqlparam.Value = entityUserInfo.ComputerLabNo 18: cmd.Parameters.Add(sqlparam) 19: 20: sqlparam = New SqlParameter("@Teachername", SqlDbType.Char)
21: sqlparam.Value = entityUserInfo.TeacherName 22: cmd.Parameters.Add(sqlparam)装扮以后：
在基类写一个方法
1: Public Class daBase
2: '''
3: ''' 为sql变量赋值并添加到sqlcommand中
4: '''
5: '''
6: '''
7: '''
8: '''
9: ''' 2011-4-2 15：24
10: Public Sub AddSqlParameter(ByRef cmd As SqlCommand, ByVal dbParam As String, ByVal dbType As SqlDbType, ByVal value As Object)
11: Dim sqlParam As SqlParameter = New SqlParameter(dbParam, dbType)
12: sqlParam.Value = value 13: cmd.Parameters.Add(sqlParam)14: End Sub
15: 16: End Class
开始化妆了：
1: AddSqlParameter(cmd, "@userName", SqlDbType.Char, entityUserInfo.UserName)
2: AddSqlParameter(cmd, "@password", SqlDbType.Char, entityUserInfo.Password)
3: AddSqlParameter(cmd, "@StatusType", SqlDbType.Char, entityUserInfo.StatusType)
4: AddSqlParameter(cmd, "@ComputerLabNo", SqlDbType.Int, entityUserInfo.ComputerLabNo)
5: AddSqlParameter(cmd, "@Teachername", SqlDbType.Char, entityUserInfo.TeacherName)
美了吗？不够美！！！
继续化：
再实体层中对应的实体类中添加一个共有属性，利用参数传递。
1: Public Class UserInfo
2: Public Const DBPARAM_USERNAME = "@UserName"
3: Public Const DBPARAM_PASSWORD = "@Password"
4: Public Const DBPARAM_STATUSTYPE = "@StatusType"
5: Public Const DBPARAM_COMPUTERLABNO = "@ComputerLabNo"
6: Public Const DBPARAM_TEACHERNAME = "@Teachername"
7: End Class
之后调用为：
1: AddSqlParameter(cmd, UserInfo.DBPARAM_USERNAME, SqlDbType.Char, entityUserInfo.UserName)
2: AddSqlParameter(cmd, UserInfo.DBPARAM_PASSWORD, SqlDbType.Char, entityUserInfo.Password)
3: AddSqlParameter(cmd, UserInfo.DBPARAM_STATUSTYPE, SqlDbType.Char, entityUserInfo.StatusType)
4: AddSqlParameter(cmd, UserInfo.DBPARAM_COMPUTERLABNO, SqlDbType.Int, entityUserInfo.ComputerLabNo)5: AddSqlParameter(cmd, UserInfo.DBPARAM_TEACHERNAME, SqlDbType.Char, entityUserInfo.TeacherName)
相应的sql语句改为：
1: Dim sql As String = String.Format("insert into UserInfo(UserName,[Password],StatusType,ComputerLabNo,Teachername) values ({0},{1},{2},{3},{4})" _
2: , UserInfo.DBPARAM_USERNAME, UserInfo.DBPARAM_PASSWORD, UserInfo.DBPARAM_STATUSTYPE, UserInfo.DBPARAM_COMPUTERLABNO, UserInfo.DBPARAM_TEACHERNAME)
这样化妆之后，如果数据库字段改了或者是参数变了只要把基类中属性做相应的改变即可。
再这里笔者就不再往下化了，如果有兴趣的朋友可以继续化，就是用存储过程哦！！！
====================华丽分割线=========================
如果读者没有看明白这些装扮的话，可以详细看下面的代码！！！
我把我的一个用户表的dal层和用户表对应的实体层还有基类的代码放到下面！！！
希望读者能够从这篇文章里面读出一些东西，相信你自己的手，其实出现在你手下的精灵都非常美丽的！
用户表DAL层代码：
1: Imports System.Data.SqlClient
2: Imports Entity
3: Public Class sqlUserInfo
4: Inherits daBase
5: Implements Interfaces.IUserInfo
6: 7: Private connStr As String = System.Configuration.ConfigurationSettings.AppSettings("connStr")
8: 9: 'Private connStr As String = "server=.;database=chargeMIS;UID=sa;PWD=123"
10: 11: '''
12: ''' 插入一条用户信息
13: '''
14: ''' 一个用户信息表对象
15: ''' 是否添加成功
16: ''' 2011-3-14 15：38 by cjq
17: Public Function insertRecord(ByVal entityUserInfo As Entity.UserInfo) As Boolean Implements Interfaces.IUserInfo.insertRecord
18: 19: 'Dim sql As String = "insert into UserInfo(UserName,[Password],StatusType,ComputerLabNo,Teachername) values (@UserName,@Password,@StatusType,@ComputerLabNo,@Teachername)"
20: Dim sql As String = String.Format("insert into UserInfo(UserName,[Password],StatusType,ComputerLabNo,Teachername) values ({0},{1},{2},{3},{4})" _
21: , UserInfo.DBPARAM_USERNAME, UserInfo.DBPARAM_PASSWORD, UserInfo.DBPARAM_STATUSTYPE, UserInfo.DBPARAM_COMPUTERLABNO, UserInfo.DBPARAM_TEACHERNAME)22: Dim conn As SqlConnection = CreateConn(connStr)
23: Dim cmd As SqlCommand = New SqlCommand(sql, conn)
24: 25: 'AddSqlParameter(cmd, "@UserName", SqlDbType.Char, entityUserInfo.UserName)
26: 'AddSqlParameter(cmd, "@Password", SqlDbType.Char, entityUserInfo.Password)
27: 'AddSqlParameter(cmd, "@StatusType", SqlDbType.Char, entityUserInfo.StatusType)
28: 'AddSqlParameter(cmd, "@ComputerLabNo", SqlDbType.Int, entityUserInfo.ComputerLabNo)
29: 'AddSqlParameter(cmd, "@Teachername", SqlDbType.Char, entityUserInfo.TeacherName)
30: 31: AddSqlParameter(cmd, UserInfo.DBPARAM_USERNAME, SqlDbType.Char, entityUserInfo.UserName)
32: AddSqlParameter(cmd, UserInfo.DBPARAM_PASSWORD, SqlDbType.Char, entityUserInfo.Password)
33: AddSqlParameter(cmd, UserInfo.DBPARAM_STATUSTYPE, SqlDbType.Char, entityUserInfo.StatusType)
34: AddSqlParameter(cmd, UserInfo.DBPARAM_COMPUTERLABNO, SqlDbType.Int, entityUserInfo.ComputerLabNo)35: AddSqlParameter(cmd, UserInfo.DBPARAM_TEACHERNAME, SqlDbType.Char, entityUserInfo.TeacherName)
36: 37: 'Dim sqlParam As SqlParameter
38: 'sqlParam = New SqlParameter("@UserName", SqlDbType.Char)
39: 'sqlParam.Value = entityUserInfo.UserName
40: 'cmd.Parameters.Add(sqlParam)
41: 42: 'sqlParam = New SqlParameter("@Password", SqlDbType.Char)
43: 'sqlParam.Value = entityUserInfo.Password
44: 'cmd.Parameters.Add(sqlParam)
45: 46: 'sqlParam = New SqlParameter("@StatusType", SqlDbType.Char)
47: 'sqlParam.Value = entityUserInfo.StatusType
48: 'cmd.Parameters.Add(sqlParam)
49: 50: 51: 52: 'sqlParam = New SqlParameter("@ComputerLabNo", SqlDbType.Int)
53: 'sqlParam.Value = entityUserInfo.ComputerLabNo
54: 'cmd.Parameters.Add(sqlParam)
55: 56: 'sqlParam = New SqlParameter("@Teachername", SqlDbType.Char)
57: 'sqlParam.Value = entityUserInfo.TeacherName
58: 'cmd.Parameters.Add(sqlParam)
59: 60: Try
61: conn.Open()62: Return cmd.ExecuteNonQuery() > 0
63: Catch ex As Exception
64: Return False
65: Finally
66: 'If Not IsNothing(conn) Then
67: ' conn.Close()
68: ' conn = Nothing
69: 'End If
70: 'If Not IsNothing(cmd) Then
71: ' cmd.Dispose()
72: ' cmd = Nothing
73: 'End If
74: Close(conn) 75: Close(cmd) 76: 77: End Try
78: End Function
79: '''
80: ''' 更新一条记录
81: '''
82: ''' 一个用户信息表
83: ''' 是否更新成功
84: ''' 2011-3-15 23：24 by cjq
85: Public Function UpdateRecord(ByVal entityUserInfo As Entity.UserInfo) As Boolean Implements Interfaces.IUserInfo.UpdateRecord
86: 87: 'Dim sql As String = "update UserInfo set StatusType=@StatusType,ComputerLabNo=@ComputerLabNo,Teachername=@Teachername,[Password]=@password where UserName=@userName"
88: Dim sql As String = String.Format("update UserInfo set StatusType={0},ComputerLabNo={1},Teachername={2},[Password]={3} where UserName={4}" _
89: , UserInfo.DBPARAM_STATUSTYPE, UserInfo.DBPARAM_COMPUTERLABNO, UserInfo.DBPARAM_TEACHERNAME, UserInfo.DBPARAM_PASSWORD, UserInfo.DBPARAM_USERNAME) 90: 91: Dim conn As SqlConnection = CreateConn(connStr)
92: Dim cmd As SqlCommand = New SqlCommand(sql, conn)
93: 94: 'AddSqlParameter(cmd, "@userName", SqlDbType.Char, entityUserInfo.UserName)
95: 'AddSqlParameter(cmd, "@password", SqlDbType.Char, entityUserInfo.Password)
96: 'AddSqlParameter(cmd, "@StatusType", SqlDbType.Char, entityUserInfo.StatusType)
97: 'AddSqlParameter(cmd, "@ComputerLabNo", SqlDbType.Int, entityUserInfo.ComputerLabNo)
98: 'AddSqlParameter(cmd, "@Teachername", SqlDbType.Char, entityUserInfo.TeacherName)
99: 100: AddSqlParameter(cmd, UserInfo.DBPARAM_USERNAME, SqlDbType.Char, entityUserInfo.UserName)
101: AddSqlParameter(cmd, UserInfo.DBPARAM_PASSWORD, SqlDbType.Char, entityUserInfo.Password)
102: AddSqlParameter(cmd, UserInfo.DBPARAM_STATUSTYPE, SqlDbType.Char, entityUserInfo.StatusType)
103: AddSqlParameter(cmd, UserInfo.DBPARAM_COMPUTERLABNO, SqlDbType.Int, entityUserInfo.ComputerLabNo)104: AddSqlParameter(cmd, UserInfo.DBPARAM_TEACHERNAME, SqlDbType.Char, entityUserInfo.TeacherName)
105: 106: 'Dim sqlparam As SqlParameter
107: 108: 'sqlparam = New SqlParameter("@password", SqlDbType.VarChar)
109: 'sqlparam.Value = entityUserInfo.Password
110: 'cmd.Parameters.Add(sqlparam)
111: 112: 'sqlparam = New SqlParameter("@userName", SqlDbType.VarChar)
113: 'sqlparam.Value = entityUserInfo.UserName
114: 'cmd.Parameters.Add(sqlparam)
115: 116: 'sqlparam = New SqlParameter("@StatusType", SqlDbType.Char)
117: 'sqlparam.Value = entityUserInfo.StatusType
118: 'cmd.Parameters.Add(sqlparam)
119: 120: 121: 122: 'sqlparam = New SqlParameter("@ComputerLabNo", SqlDbType.Int)
123: 'sqlparam.Value = entityUserInfo.ComputerLabNo
124: 'cmd.Parameters.Add(sqlparam)
125: 126: 'sqlparam = New SqlParameter("@Teachername", SqlDbType.Char)
127: 'sqlparam.Value = entityUserInfo.TeacherName
128: 'cmd.Parameters.Add(sqlparam)
129: 130: Try
131: conn.Open()132: Return cmd.ExecuteNonQuery() > 0
133: 134: Catch ex As Exception
135: Return False
136: Finally
137: 'If Not IsNothing(conn) Then
138: ' conn.Close()
139: ' conn = Nothing
140: 'End If
141: 'If Not IsNothing(cmd) Then
142: ' cmd.Dispose()
143: ' cmd = Nothing
144: 'End If
145: Close(conn) 146: Close(cmd) 147: 148: End Try
149: End Function
150: '''
151: ''' 删除一条记录
152: '''
153: ''' 一条用户表信息
154: ''' 是否删除成功
155: ''' 2011-3-16 9：41 by cjq
156: Public Function deleteRecord(ByVal entityUserInfo As Entity.UserInfo) As Boolean Implements Interfaces.IUserInfo.deleteRecord
157: 'Dim sql As String = "delete from userInfo where UserName=@userName"
158: Dim sql As String = String.Format("delete from userInfo where UserName={0}", UserInfo.DBPARAM_USERNAME)
159: 160: Dim conn As SqlConnection = CreateConn(connStr)
161: Dim cmd As New SqlCommand(sql, conn)
162: 163: AddSqlParameter(cmd, UserInfo.DBPARAM_USERNAME, SqlDbType.Char, entityUserInfo.UserName)
164: 'AddSqlParameter(cmd, "@UserName", SqlDbType.Char, entityUserInfo.UserName)
165: 166: 167: 'Dim sqlParam As New SqlParameter("@userName", SqlDbType.VarChar)
168: 169: 'sqlParam.Value = entityUserInfo.UserName
170: 'cmd.Parameters.Add(sqlParam)
171: 172: 173: Try
174: conn.Open()175: Return cmd.ExecuteNonQuery() > 0
176: 177: Catch ex As Exception
178: Return False
179: Finally
180: 'If Not IsNothing(conn) Then
181: ' conn.Close()
182: ' conn = Nothing
183: 'End If
184: 'If Not IsNothing(cmd) Then
185: ' cmd.Dispose()
186: ' cmd = Nothing
187: 'End If
188: Close(conn) 189: Close(cmd) 190: 191: End Try
192: End Function
193: 194: '''
195: ''' 检查一条记录
196: '''
197: ''' 一记录
198: ''' 是否存在这样一条记录
199: ''' 2011-3-19 20：32 by cjq
200: Public Function checkRecord(ByVal entityUserInfo As Entity.UserInfo) As Boolean Implements Interfaces.IUserInfo.checkRecord
201: 'Dim sql As String = "select * from UserInfo where userName=@userName"
202: Dim sql As String = String.Format("select * from UserInfo where userName={0}", UserInfo.DBPARAM_USERNAME)
203: Dim conn As SqlConnection = CreateConn(connStr)
204: Dim cmd As SqlCommand = New SqlCommand(sql, conn)
205: 206: 207: 'AddSqlParameter(cmd, "@userName", SqlDbType.Char, entityUserInfo.UserName)
208: AddSqlParameter(cmd, UserInfo.DBPARAM_USERNAME, SqlDbType.Char, entityUserInfo.UserName)
209: 210: 'Dim sqlParam As SqlParameter
211: 'sqlParam = New SqlParameter("@userName", SqlDbType.VarChar)
212: 'sqlParam.Value = entityUserInfo.UserName
213: 'cmd.Parameters.Add(sqlParam)
214: 215: Dim sdr As SqlDataReader = Nothing
216: Try
217: conn.Open() 218: sdr = cmd.ExecuteReader() 219: sdr.Read()220: If (sdr.HasRows = True) Then
221: Return True
222: Else
223: Return False
224: End If
225: 226: 227: 'Return cmd.ExecuteNonQuery() > 0
228: 229: Catch ex As Exception
230: Return False
231: Finally
232: 233: 'If Not IsNothing(conn) Then
234: ' conn.Close()
235: ' conn = Nothing
236: 'End If
237: 'If Not IsNothing(cmd) Then
238: ' cmd.Dispose()
239: ' cmd = Nothing
240: 'End If
241: Close(conn) 242: Close(cmd) 243: Close(sdr)244: End Try
245: 246: End Function
247: '''
248: ''' 获得多个用户信息
249: '''
250: ''' 一条用户信息
251: ''' 所有用户的dataset集合
252: ''' 2011-3-16 10：11 by cjq
253: Public Function GetObject(ByVal entityUserInfo As Entity.UserInfo) As DataSet Implements Interfaces.IUserInfo.GetObject
254: 'Dim sql As String = "select * from UserInfo where StatusType=@strstatustype"
255: 256: Dim sql As String = String.Format("select UserName 用户名,[Password] 密码,StatusType 身份,ComputerLabNo 机房号,Teachername 教师姓名 from UserInfo where StatusType={0}", UserInfo.DBPARAM_STATUSTYPE)
257: Dim conn As SqlConnection = CreateConn(connStr)
258: Dim cmd As SqlCommand = New SqlCommand(sql, conn)
259: 260: AddSqlParameter(cmd, UserInfo.DBPARAM_STATUSTYPE, SqlDbType.Char, entityUserInfo.StatusType)
261: 262: 'AddSqlParameter(cmd, "@statusType", SqlDbType.Char, entityUserInfo.StatusType)
263: 264: 'Dim sqlParam As New SqlParameter("@strstatustype", SqlDbType.VarChar)
265: 'sqlParam.Value = entityUserInfo.StatusType
266: 'cmd.Parameters.Add(sqlParam)
267: 268: 269: Dim dap As SqlDataAdapter = New SqlDataAdapter(cmd)
270: Dim ds As New DataSet
271: 272: Try
273: conn.Open() 274: dap.Fill(ds)275: Return ds
276: 277: Catch ex As Exception
278: Return Nothing
279: Finally
280: 'If Not IsNothing(conn) Then
281: ' conn.Close()
282: ' conn = Nothing
283: 'End If
284: 'If Not IsNothing(cmd) Then
285: ' cmd.Dispose()
286: ' cmd = Nothing
287: 'End If
288: 'If Not IsNothing(dap) Then
289: ' dap.Dispose()
290: ' dap = Nothing
291: 'End If
292: Close(conn) 293: Close(cmd) 294: Close(dap)295: End Try
296: 297: 298: End Function
299: '''
300: ''' 返回一条用户信息记录
301: '''
302: ''' 一条用户信息
303: ''' 一条用户信息记录
304: ''' 2011-3-19 16：06 by cjq
305: Public Function ReturnRecord(ByVal entityUserInfo As Entity.UserInfo) As Entity.UserInfo Implements Interfaces.IUserInfo.ReturnRecord
306: Dim sql As String = String.Format("select UserName 用户名,[Password] 密码,StatusType 身份,ComputerLabNo 机房号,Teachername 教师姓名 from UserInfo where userName={0}", UserInfo.DBPARAM_USERNAME)
307: 308: Dim conn As SqlConnection = CreateConn(connStr)
309: Dim cmd As SqlCommand = New SqlCommand(sql, conn)
310: 311: AddSqlParameter(cmd, UserInfo.DBPARAM_USERNAME, SqlDbType.Char, entityUserInfo.UserName)
312: 313: 'AddSqlParameter(cmd, "@userName", SqlDbType.Char, entityUserInfo.UserName)
314: 315: 'Dim sqlParam As SqlParameter
316: 'sqlParam = New SqlParameter("@userName", SqlDbType.VarChar)
317: 'sqlParam.Value = entityUserInfo.UserName
318: 'cmd.Parameters.Add(sqlParam)
319: 320: 'Dim sdr As SqlDataReader = Nothing
321: Dim ds As New DataSet
322: Dim dap As SqlDataAdapter = New SqlDataAdapter(cmd)
323: 324: 325: Try
326: conn.Open() 327: 328: dap.Fill(ds, "UserInfo")
329: Dim dr As DataRow = ds.Tables("UserInfo").Rows(0)
330: entityUserInfo.LoadFromDataRow(dr) 331: 332: 333: 'sdr = cmd.ExecuteReader()
334: 'While sdr.Read
335: ' entityUserInfo.UserName = Trim(sdr.GetString(0))
336: ' entityUserInfo.Password = Trim(sdr.GetString(1))
337: ' entityUserInfo.StatusType = Trim(sdr.GetString(2))
338: ' entityUserInfo.ComputerLabNo = Trim(sdr.GetInt32(3))
339: ' entityUserInfo.TeacherName = Trim(sdr.GetString(4))
340: 'End While
341: 342: Return entityUserInfo
343: 344: Catch ex As Exception
345: Return Nothing
346: Finally
347: 'If Not IsNothing(conn) Then
348: ' conn.Close()
349: ' conn = Nothing
350: 'End If
351: 'If Not IsNothing(cmd) Then
352: ' cmd.Dispose()
353: ' cmd = Nothing
354: 'End If
355: Close(conn) 356: Close(cmd) 357: Close(dap)358: End Try
359: End Function
360: 361: '''
362: ''' 获得多个用户信息
363: '''
364: ''' 一条用户信息
365: ''' 所有用户的dataset集合
366: ''' 2011-4-1 11：11 by cjq
367: Public Function GetObjectAccount(ByVal entityUserInfo As Entity.UserInfo) As DataSet Implements Interfaces.IUserInfo.GetObjectAccount
368: Dim sql As String = String.Format("select UserName 用户名,[Password] 密码,StatusType 身份,ComputerLabNo 机房号,Teachername 教师姓名 from UserInfo where StatusType<>{0}", UserInfo.DBPARAM_STATUSTYPE)
369: Dim conn As SqlConnection = CreateConn(connStr)
370: Dim cmd As SqlCommand = New SqlCommand(sql, conn)
371: 372: 'AddSqlParameter(cmd, "@statusType", SqlDbType.Char, entityUserInfo.StatusType)
373: AddSqlParameter(cmd, UserInfo.DBPARAM_STATUSTYPE, SqlDbType.Char, entityUserInfo.StatusType)
374: 'Dim sqlParam As New SqlParameter("@strstatustype", SqlDbType.VarChar)
375: 'sqlParam.Value = entityUserInfo.StatusType
376: 'cmd.Parameters.Add(sqlParam)
377: 378: 379: Dim dap As SqlDataAdapter = New SqlDataAdapter(cmd)
380: Dim ds As New DataSet
381: 382: Try
383: conn.Open() 384: dap.Fill(ds)385: Return ds
386: 387: Catch ex As Exception
388: Return Nothing
389: Finally
390: 'If Not IsNothing(conn) Then
391: ' conn.Close()
392: ' conn = Nothing
393: 'End If
394: 'If Not IsNothing(cmd) Then
395: ' cmd.Dispose()
396: ' cmd = Nothing
397: 'End If
398: 'If Not IsNothing(dap) Then
399: ' dap.Dispose()
400: ' dap = Nothing
401: 'End If
402: Close(conn) 403: Close(cmd) 404: Close(dap)405: End Try
406: 407: 408: End Function
409: 410: End Class
用户表实体类代码：
1: Public Class UserInfo
2: 3: Private _intComputerLabNo As Integer
4: Private _strPassword As String
5: 6: Private _strStatusType As String
7: Private _strTeacherName As String
8: Private _strUserName As String
9: 10: Private Const DBFEILD_USERNAME = "UserName"
11: Private Const DBFEILD_PASSWORD = "Password"
12: Private Const DBFEILD_COMPUTERLABNO = "ComputerLabNo"
13: Private Const DBFEILD_STATUSTYPE = "StatusType"
14: Private Const DBFEILD_TEACHERNAME = "Teachername"
15: 16: Public Const DBPARAM_USERNAME = "@UserName"
17: Public Const DBPARAM_PASSWORD = "@Password"
18: Public Const DBPARAM_STATUSTYPE = "@StatusType"
19: Public Const DBPARAM_COMPUTERLABNO = "@ComputerLabNo"
20: Public Const DBPARAM_TEACHERNAME = "@Teachername"
21: 22: 23: 24: '''
25: ''' 从数据库为实体填充数据
26: '''
27: '''
28: ''' 2011-4-4 14：11
29: Public Sub LoadFromDataRow(ByVal dr As DataRow)
30: UserName = dr(DBFEILD_USERNAME) 31: Password = dr(DBFEILD_PASSWORD) 32: ComputerLabNo = dr(DBFEILD_COMPUTERLABNO) 33: StatusType = dr(DBFEILD_STATUSTYPE) 34: TeacherName = dr(DBFEILD_TEACHERNAME) 35: 36: End Sub
37: 38: 39: '''
40: ''' 机房号
41: '''
42: '''
43: '''
44: ''' 2011-3-14 15：26 by cjq
45: Public Property ComputerLabNo() As Integer
46: 47: Get
48: Return _intComputerLabNo
49: End Get
50: Set(ByVal value As Integer)
51: _intComputerLabNo = value52: End Set
53: 54: End Property
55: '''
56: ''' 用户名
57: '''
58: '''
59: '''
60: ''' 2011-3-14 15：27 by cjq
61: Public Property UserName() As String
62: 63: Get
64: Return _strUserName
65: End Get
66: Set(ByVal value As String)
67: _strUserName = value68: End Set
69: End Property
70: 71: '''
72: ''' 口令密码
73: '''
74: '''
75: '''
76: ''' 2011-3-14 15：28 by cjq
77: Public Property Password() As String
78: 79: Get
80: Return _strPassword
81: End Get
82: Set(ByVal value As String)
83: _strPassword = value84: End Set
85: 86: End Property
87: '''
88: ''' 身份类型
89: '''
90: '''
91: '''
92: ''' 2011-3-14 15：29 by cjq
93: Public Property StatusType() As String
94: 95: Get
96: Return _strStatusType
97: End Get
98: Set(ByVal value As String)
99: _strStatusType = value100: End Set
101: 102: End Property
103: 104: '''
105: ''' 教师姓名
106: '''
107: '''
108: '''
109: ''' 2011-3-14 15：30 by cjq
110: Public Property TeacherName() As String
111: 112: Get
113: Return _strTeacherName
114: End Get
115: Set(ByVal value As String)
116: _strTeacherName = value117: End Set
118: End Property
119: 120: End Class
基类代码：
1: Imports System.Data.SqlClient
2: 3: Public Class daBase
4: '''
5: ''' 创建一个数据库连接
6: '''
7: ''' 数据库连接字符串
8: '''
9: ''' 2011-4-2 14：45 by cjq
10: Public Function CreateConn(ByVal connStr As String) As SqlConnection
11: Return New SqlConnection(connStr)
12: End Function
13: 14: '''
15: ''' 关闭相关对象
16: '''
17: '''
18: ''' 2011-4-2 14：45 by cjq
19: Public Sub Close(ByVal conn As SqlConnection)
20: If Not IsNothing(conn) Then
21: conn.Close()22: conn = Nothing
23: End If
24: End Sub
25: 26: '''
27: ''' 关闭相关对象
28: '''
29: '''
30: ''' 2011-4-2 14：45 by cjq
31: Public Sub Close(ByVal cmd As SqlCommand)
32: If Not IsNothing(cmd) Then
33: cmd.Dispose()34: cmd = Nothing
35: End If
36: End Sub
37: 38: '''
39: ''' 关闭相关对象
40: '''
41: '''
42: ''' 2011-4-2 14：45 by cjq
43: Public Sub Close(ByVal sdr As SqlDataReader)
44: If Not IsNothing(sdr) Then
45: sdr.Dispose()46: sdr = Nothing
47: End If
48: End Sub
49: 50: 51: '''
52: ''' 关闭相关对象
53: '''
54: '''
55: ''' 2011-4-2 14：45 by cjq
56: Public Sub Close(ByVal sdp As SqlDataAdapter)
57: If Not IsNothing(sdp) Then
58: sdp.Dispose()59: sdp = Nothing
60: End If
61: End Sub
62: '''
63: ''' 为sql变量赋值并添加到sqlcommand中
64: '''
65: '''
66: '''
67: '''
68: '''
69: ''' 2011-4-2 15：24
70: Public Sub AddSqlParameter(ByRef cmd As SqlCommand, ByVal dbParam As String, ByVal dbType As SqlDbType, ByVal value As Object)
71: Dim sqlParam As SqlParameter = New SqlParameter(dbParam, dbType)
72: sqlParam.Value = value 73: cmd.Parameters.Add(sqlParam)74: End Sub
75: 76: End Class
==================华丽分割线===========================
来吧，一起给她们化妆吧，她们真的很美丽。
扫一扫
1703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
