大家好,又见面了,我是你们的朋友全栈君。
在机房重构的过程中,几乎所有调用D层的过程中,都需要连接数据库,如果每个功能都写一段连接的代码,这不就重复了好多吗?所以我们就可以把这些重复的代码抽象出来写到一个类中,这样就可以实现代码的复用。
我们的功能和数据库的连接大体上可以分为四种情况:
如何写SQL Helper类:
1.有参数的查询:
方法一:
'执行有参的查询操作
Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
Using conn As New SqlConnection(strConnection)
Dim cmd As SqlCommand = conn.CreateCommand()
Dim adp As SqlDataAdapter
Dim ds As New DataSet
cmd.CommandText = cmdText
cmd.CommandType = cmdType
cmd.Parameters.AddRange(paras)
adp = New SqlDataAdapter(cmd)
Try
conn.Open()
adp.Fill(ds)
Return ds.Tables(0)
Catch ex As Exception
Return Nothing
Throw ex
End Try
End Using
End Function
方法二:
'执行有参的查询操作
Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
Dim sqlAdapter As SqlDataAdapter
Dim dt As New DataTable
Dim ds As New DataSet
cmd.CommandText = cmdText
cmd.CommandType = cmdType
cmd.Parameters.AddRange(paras)
sqlAdapter = New SqlDataAdapter(cmd)
cmd.Connection = conn
Try
conn.Open()
sqlAdapter.Fill(ds)
dt = ds.Tables(0)
cmd.Parameters.Clear()
Catch ex As Exception
MsgBox(ex.Message, , "数据库操作")
Finally
Call CloseConn(conn)
Call CloseCmd(cmd)
End Try
Return dt
End Function
'关闭连接
Public Shared Sub CloseConn(ByVal conn As SqlConnection)
If (conn.State <> ConnectionState.Closed) Then
conn.Close()
conn = Nothing
End If
End Sub
'释放命令
Public Shared Sub CloseCmd(ByVal cmd As SqlCommand)
If Not IsNothing(cmd) Then
cmd.Dispose()
cmd = Nothing
End If
End Sub
对比上面两种方法,都可以实现对数据库的查询操作,方法一使用using连接池,在使用完成后就会自动关闭数据库连接,然而第二种方法定义了关闭数据库的方法,每次都需要调用CloseConn()方法才能关闭数据库连接,这样在程序运行时,又是就会出现数据库未关闭的错误,所以个人认为使用using连接池比较好。
2.无参数的查询:
'执行无参的查询操作
Public Function ExecSelectNo(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
Using conn As New SqlConnection(strConnection)
Dim cmd As SqlCommand = conn.CreateCommand()
Dim adp As SqlDataAdapter
Dim ds As New DataSet
cmd.CommandText = cmdText
cmd.CommandType = cmdType
adp = New SqlDataAdapter(cmd)
Try
conn.Open()
adp.Fill(ds)
Return ds.Tables(0)
Catch ex As Exception
Return Nothing
Throw ex
End Try
End Using
End Function
3.有参数的增删改:
Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As Integer
Using conn As New SqlConnection(strConnection)
Dim cmd As SqlCommand = conn.CreateCommand
cmd.CommandText = cmdText
cmd.CommandType = cmdType
cmd.Parameters.AddRange(paras)
Try
conn.Open()
Return cmd.ExecuteNonQuery
Catch ex As Exception
Return 0
Throw ex
End Try
End Using
End Function
4.无参数的增删改:
'无参的增删改
Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
Using conn As New SqlConnection(strConnection)
Dim cmd As New SqlCommand
cmd.CommandText = cmdText
cmd.CommandType = cmdType
Try
conn.Open()
Return cmd.ExecuteNonQuery
Catch ex As Exception
Return 0
Throw ex
End Try
End Using
End Function
以登录时判断用户是否存在为例,在D层调用SQLHelper
Public Function SelectUser(enUser As Entity.UsersEntity) As DataTable Implements IDAL.IUser.SelectUser
Dim helper As New Helper.SqlHelper
Dim dt As New DataTable
Dim cmdText As String = "select userPassword,level from [T-Users] where userId=@userId and isOn='是'"
Dim sqlParams As SqlParameter() = {New SqlParameter("@userId", enUser.UserId)}
dt = helper.ExecSelect(cmdText, CommandType.Text, sqlParams)
Return dt
End Function
代码很容易就可以实现,重要是一开始自己的思考。总结上面四种情况,也可以简单的分为查询和增删改两种方法,关于参数如何处理,正在研究中……
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/105761.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...