在机房重构过程中,我们会发现,每个功能的实现都会涉及到数据库的链接访问,代码重复率非常高,耗时耗力,还会使系统出现代码冗余,正所谓费力不讨好。于是,把这些重复的代码抽象成一个类,有需求直接调用就可以了。这时候,sqlHelper就登场了。
sqlHelper是一个基于·NET Framework的数据库操作组件。组件中包含数据库操作方法。sqlHelper用于简化你重复的去写那些数据库连接(sqlConnection),sqlCommand,sqlDataReader等等。sqlHelper 封装过后通常是只需要给方法传入一些参数如数据库连接字符串,sql参数等,就可以访问数据库了。
sqlHelper中有两种方法,对数据库的查询和增删改,每种方法都有有参和无参两大类:
Imports System.Data Imports System.Data.sqlClient Imports System.Configuration '添加对配置文件的引用,在管理器中相应的类库下同时添加引用 ''' <summary> ''' sqlhelper类封装简化对数据库操作代码 ''' </summary> ''' <remarks></remarks> Public Class sqlHelper '定义变量 '获得数据库的连接字符串 Private ReadOnly strConnection As String = ConfigurationManager.AppSettings("ConnStr") '设置连接 Dim conn As sqlConnection = New sqlConnection(strConnection) '定义cmd命令 Dim cmd As New sqlCommand ''' <summary> ''' 执行增删改三个操作(有参),返回值为Boolean类型,确认是否执行成功 ''' </summary> ''' <param name=" cmdText ">需要执行的命令</param > ''' <param name=" cmdType ">所执行命令的类型(sql语句、表或存储过程)</param > ''' <param name="paras" >参数数组,无法确认有多少参数</param > ''' <returns></returns > ''' <remarks></remarks> Public Function ExecAddDelUpdate(ByVal cmdText As String,ByVal cmdType As CommandType,ByVal paras As sqlParameter()) As Integer '将传入的值分别为cmd的属性赋值 cmd.Parameters.AddRange(paras) '传入参数 cmd.CommandType = cmdType '设置一个值来解释cmdText cmd.Connection = conn '设置连接,全局变量 cmd.CommandText = cmdText '设置查询语句 '执行操作 Try conn.Open() '打开连接 Return cmd.ExecuteNonQuery() '执行增删改操作 cmd.Parameters.Clear() '清除参数 Catch ex As Exception '如有异常,返回值为0 Return 0 Finally Call CloseConn(conn) '关闭连接 Call CloseCmd(cmd) '关闭命令 End Try End Function ''' <summary> ''' 执行增删改三个操作(无参) ''' </summary> ''' <param name=" cmdText ">需要执行语句,一般是sql语句,也有存储过程</param > ''' <param name=" cmdType ">所执行命令的类型(sql语句、表或存储过程)</param > ''' <returns>Integer,受影响的行数</returns > ''' <remarks></remarks> Public Function ExecAddDelUpdateNo(ByVal cmdText As String,ByVal cmdType As CommandType) As Integer '为要执行的cmd赋值 cmd.CommandText = cmdText '设置sql语句 cmd.CommandType = cmdType '设置一个值来解释sql语句 cmd.Connection = conn '设置连接 '执行操作 Try conn.Open() '先是打开连接 Return cmd.ExecuteNonQuery '执行增删改操作 Catch ex As Exception Return 0 Finally Call CloseConn(conn) Call CloseCmd(cmd) End Try End Function ''' <summary> ''' 执行查询操作(有参) ''' </summary> ''' <param name=" cmdText ">需要执行命令</param > ''' <param name=" cmdType ">所执行命令的类型</param > ''' <param name="paras" >传入的参数</param > ''' <returns></returns > ''' <remarks></remarks> Public Function ExecSelect(ByVal cmdText As String,ByVal paras As sqlParameter()) As DataTable Dim sqlAdapter As sqlDataAdapter Dim dt As New DataTable Dim ds As New DataSet cmd.Parameters.AddRange(paras) cmd.CommandText = cmdText cmd.CommandType = cmdType cmd.Connection = conn sqlAdapter = New sqlDataAdapter(cmd) '实例化adapter Try sqlAdapter.Fill(ds) '用adapter将dataset填充 dt = ds.Tables(0) 'datatable为dataset的第一个表 cmd.Parameters.Clear() Catch ex As Exception MsgBox("查询失败",CType(vbOKOnly + MsgBoxStyle.Exclamation,MsgBoxStyle),"警告") Finally Call CloseConn(conn) Call CloseCmd(cmd) '关闭cmd End Try Return dt End Function ''' <summary> ''' 执行查询操作(无参) ''' </summary> ''' <param name=" cmdText ">需要执行命令</param > ''' <param name=" cmdType ">所执行命令的类型</param > ''' <returns>datatable,查询到的表格</returns > ''' <remarks></remarks> Public Function ExecSelectNo(ByVal cmdText As String,ByVal cmdType As CommandType) As DataTable Dim sqlAdapter As sqlDataAdapter Dim ds As New DataSet cmd.CommandText = cmdText cmd.CommandType = cmdType cmd.Connection = conn sqlAdapter = New sqlDataAdapter(cmd) '实例化adapter Try sqlAdapter.Fill(ds) '用adapter将dataset填充 Return ds.Tables(0) 'datatable为dataset的第一个表 Catch ex As Exception Return Nothing Finally Call CloseConn(conn) Call CloseCmd(cmd) '关闭cmd End Try End Function ''' <summary> ''' 关闭连接 ''' </summary> ''' <param name="conn">需要关闭的连接</param> ''' <remarks></remarks> Public Sub CloseConn(ByVal conn As sqlConnection) If (conn.State <> ConnectionState.Closed) Then '如果没有关闭 conn.Close() '关闭连接 conn = Nothing End If End Sub ''' <summary> ''' 关闭命令 ''' </summary> ''' <param name="cmd">需要关闭的命令</param> ''' <remarks></remarks> Public Sub CloseCmd(ByVal cmd As sqlCommand) If Not IsNothing(cmd) Then '如果命令存在 cmd.Dispose() cmd = Nothing End If End Sub End Class
快使用小助手为我们的代码瘦瘦身,为系统减减负吧!