long_yu2
- 浏览: 313435 次
-
文章分类
最新评论
-
luokery：
你把正常的代码放到try里面不就好了, 异常的时候就catch ...
巧用while循环避免执行除了异常处理的的另一个处理机制 -
java_doom：
这个行号根据什么来的 为什么不是连续的？
别拿Hadoop map key当id使 -
mixer_b：
...
写博客的那些事儿 -
flyfx：
谢谢谢谢
java break,continue,return 使用。【借鉴与改编】 -
andilyliao：
比起青鸟 我更鄙视大学。
北大青鸟就让你们这么鄙视吗.
给DAL层化妆，让她更加美丽
开篇先看一张图片：
一个会打扮的人，是绝对不能让自己变成图片上方的那副模样的，又丑又脏，又没有一个好的表情。
显然，图片下方的女人已经掌握了装扮之道，又干净又美丽，而且非常令人喜欢。
装扮，不仅仅是在女人身上，我们的代码也是一样的。
已经迫不及待了，我们也要装扮一下我们的代码，我们也要让她们变得很美丽。
准备好了吗？开始吧！！
我们给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 = value
52: 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 = value
68: 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 = value
84: 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 = value
100: 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 = value
117: 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
==================华丽分割线===========================
来吧，一起给她们化妆吧，她们真的很美丽。
分享到：
发表评论
相关推荐
三层架构中的DAL层，连接sqlsever数据库，其他数据库也可，因资源确实比较珍惜实用（我做东西一直在用这个），网络上没见过相同的代码，所以资源分比较高，若下载后不能用，请联系我~
Code Generation Tool(C#)是专门针对SQL SERVER 2005以上数据库实现自动生成DAL层的MODEL的C#代码。在分层开发中，将数据库表实现类操作是常用的方法，但是枯燥和繁琐的代码编写占用了我们大量的时间，于是通过使用...
C#代码模板生成器; Model层;DAL层;BLL层代码自动生成; 连接数据库，查询表字段，创建数据模型; 查询表字段，构造数据逻辑; 查询表字段，构造数据库交互的代码。
java dal 封装的通用dao 数据访问层，如果你不喜欢用Hibernate、Mybaits这类ORM框架，喜欢Spring JdbcTemplate或DbUtils，那么可以试试这个封装的通用dal
CodeGen,一个很好的自动生成DAL. Model层代码的工具源码 大家可以下载下来，根据自己的项目需要进行修改..好东东下载吧
包含 OracleDBHelper SQLDBHelper IBatisNet ADO.NET、EnterpriseLibrary等多种方式生成的DAL层代码，适合新手参考
给DAL层加上Cache(张宁).pdf
能够根据建好的数据库生成Modle层与DAL层
可以生成简单三层Models DAL BLL
.net三层结构生成器，帮助你自动生成Model,Dal,Bell层代码，不在把时间浪费在，生成底层代码上
c#三层架构入门讲解三层架构入门讲解（c#）UIL、BLL、DAL三层架构入门讲解（c#）UIL、BLL、DAL
简单三层小实例，主要解释三层怎么个调用法。
将Maps内的XML文件放在DAL层，便于同一个方案下的多个网站实现共用
LinqToSql类生成工具 自动生成Model层 和DAL层的代码，并且带有注释
在ASP.NET 2.0中操作数据：在ASP_NET页面中处理BLL-DAL层的异常
c# asp.net DAL数据访问层通用库源码c# asp.net DAL数据访问层通用库源码c# asp.net DAL数据访问层通用库源码
codesmith实体类三层DAL实体类
三层架够EntityFramework应用于DAL层 + 抽象工厂模式
一些使编码更加方便的扩展方法 Lm.Common.Security.Cryptography 加密 Lm.Common.Threading 同类任务线型、不同类任务并行 Lm.Common.Web web开发中比较便捷的代码 Lm.Common.Web.Security cookie读取以及验证码...
对DAL数据访问层的封装,一个泛型接口，一个实现泛型接口的父类,泛型父类实现了对某个表的增删改查,用EF技术实现