随着机房收费系统的不断深入,越来越发现每个功能都要连接数据库,通过不断查阅博客,根据常用归一的原则,把数据库连接实现一个类成为必然;SQLhelper就充当了为配置文件搭配链接文字,为D层数据的增删改查提供了归一化处理。
初始化连接配置文件:
'strConnection负责生成配置时所需要的字符串
Private ReadOnly strConnection As String = System.Configuration.ConfigurationSettings.AppSettings("ConnStr")
'conn实现数据连接
Dim conn As SqlConnection = New SqlConnection(strConnection)
'cmd控制命令
Dim cmd As New SqlCommand
带参数增删改:
'增删改函数(有参数),传进参数为sql命令字,命令类型,参数数组
Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As Integer
Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter() '数据适配器
Dim dt As New DataTable '创建数据表(虚拟表)
Dim ds As New DataSet 'dataset内存中的数据库(不依赖真是数据库)
cmd.CommandText = cmdText '获取和执行SQL命令
cmd.CommandType = cmdType 'cmd类型
cmd.Connection = conn '连接对象
cmd.Parameters.AddRange(paras) '传进去参数
Try
conn.Open() '打开连接
Return cmd.ExecuteNonQuery() '执行操作
cmd.Parameters.Clear() '清除参数
Catch ex As Exception
Return 0
Finally
Call CloseConnn(conn) '关闭连接
Call CloseCmd(cmd) '关闭命令
End Try
End Function
不带参数增删改:
Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
cmd.CommandText = cmdText
cmd.CommandType = cmdType
cmd.Connection = conn
Try
conn.Open() '连接打开
Return cmd.ExecuteNonQuery() '无条件执行
Catch ex As Exception
Return 0
Finally
Call CloseConnn(conn)
Call CloseCmd(cmd)
End Try
End Function
带参数的查询:
Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter '实例化适配器
Dim dt As New DataTable '创建网格化数据库
Dim ds As New DataSet '存在内存中的数据表
cmd.CommandText = cmdText '获取SQL命令语句
cmd.CommandType = cmdType '命令类型
cmd.Connection = conn '连接
cmd.Parameters.AddRange(paras) '添加参数
sqlAdapter = New SqlDataAdapter(cmd) '适配器生成
Try
sqlAdapter.Fill(ds) '适配器中添加内存中数据表
dt = ds.Tables(0) '生成第一个数据表
cmd.Parameters.Clear() '清空参数
Catch ex As Exception
MsgBox("查询失败", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "警告")
Finally
Call CloseCmd(cmd) '销毁cmd
End Try
Return dt '最终返回数据表(内存)
End Function
不带参数:
Public Function ExecSelectNo(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter()
Dim ds As New DataSet
cmd.CommandText = cmdText
cmd.CommandType = cmdType
cmd.Connection = conn
sqlAdapter = New SqlDataAdapter(cmd)
Try
sqlAdapter.Fill(ds)
Return ds.Tables(0)
Catch ex As Exception
Return Nothing
Finally
Call CloseCmd(cmd)
End Try
End Function
释放连接和关闭按钮连接:
Private Sub CloseConnn(ByVal conn As SqlConnection) '释放连接
If (conn.State <> ConnectionState.Closed) Then
conn.Close() '关闭连接
conn = Nothing '不指向原来对象
End If
End Sub
Private Sub CloseCmd(ByVal cmd As SqlCommand) '关闭命令
If Not IsNothing(cmd) Then
cmd.Dispose() '销毁cmd命令
cmd = Nothing
End If
End Sub
调用过程的关键是SQL语句的实现以及数组的添加,以注册为例:
Dim sqlcmd As String = "insert into Student(CardID,StuID,Password,Name,Seariers,class,cash,state,registDate,registTime,PC,Others)values(@CardID,@StuID,@Seariers,@Password,@Name,@class,@cash,@state,@registDate,@registTime,@PC,@Others)" 'SQL语句实现插入
Dim insertSQL As New SQLHelper ‘初始化SQLHelper类
'由于字段较多,容易出现问题
Dim paras As SqlParameter() = {New SqlParameter("@CardID", Dstu.cardID),
New SqlParameter("@Password", Dstu.cardID),
New SqlParameter("@StuID", Dstu.stuID),
New SqlParameter("@Name", Dstu.name),
New SqlParameter("@Seariers", Dstu.serial),
New SqlParameter("@class", Dstu.classNum),
New SqlParameter("@cash", Dstu.balance),
New SqlParameter("@state", Dstu.state),
New SqlParameter("@registDate", Date.Today),
New SqlParameter("@registTime", Date.Now),
New SqlParameter("@PC", "Whp-PC"),
New SqlParameter("@Others", Dstu.others)}
insertSQL.ExecAddDelUpdate(sqlcmd, CommandType.Text, paras) '调用helper函数来进行操作