SQlHelper强大用处

小鱼儿 2022-03-28 05:16 199阅读 0赞

最近写的几条线的代码,发现很多重复和数据库打交道的代码连接的打开或关闭,例如:

'定义一个连接字符串 Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr") '定义一个数据库连接对象 Dim conn As SqlConnection = New SqlConnection(strConnStr) Dim cmd As New SqlCommand '定义一个命令对象

很是费劲,代码重复多很是不友好,这个连接过程自己理解的差不多了,听师傅常说当代码发现重复在两遍以上就要考虑如何把它拿出来抽象成一个类,整个机房收费系统需要使用这样的方法太频繁了,再重复需要的时候直接调用它就行了,后来自己查资料和博客,自己加上了SqlHelper里面封装了方法,发现它真的很是强大,大大减少了代码量与提高了效率.

SqlHelper主要是用于简化重复的去写那些数据库连接（SqlConnection）,SqlCommand,SqlDataReader等等。SqlHelper 封装过后通常是只需要给方法传入一些参数如数据库连接字符串，SQL参数,存储过程等，就可以访问数据库了，很方便。

在 SqlHelper 类中实现的方法包括：

ExecuteNonQuery。此方法用于执行(有参数或无参数的)不返回任何行或值的命令。这些命令通常用于执行数据库(增\\删\\改)更新，但也可用于返回存储过程的输出参数。

ExecuteReader。此方法用于返回 SqlDataReader 对象，该对象包含由某一命令返回的结果集。

ExecuteDataset。此方法返回 DataSet 对象，该对象包含由某一命令返回的结果集。

下面是自己的SqlHelper,和大家共同学习

Imports System.Data.SqlClient Imports System.Configuration Imports System.Data Public Class DBHelper ''' <summary> ''' 有参数的非查询的操作 ''' </summary> ''' <param name="cmdText">增删改语句或者存储过程</param> ''' <param name="cmdType">命令类型文本或者存储过程</param> ''' <param name="paras">参数数组</param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function ExecuteNoQuery(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As Integer '定义一个连接字符串 Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr") '定义一个数据库连接对象 Dim conn As SqlConnection = New SqlConnection(strConnStr) Dim cmd As New SqlCommand '定义一个命令对象 Dim res As Integer '定义一个变量用户存放返回结果 cmd = New SqlCommand(cmdText, conn) cmd.CommandType = cmdType cmd.Parameters.AddRange(paras) Try '打开数据连接 If conn.State = ConnectionState.Closed Then conn.Open() End If '执行查询操作 res = cmd.ExecuteNonQuery() Catch ex As Exception MsgBox(ex.Message, , "数据库操作") Finally '关闭数据库连接 If conn.State = ConnectionState.Open Then conn.Close() End If End Try Return res '返回受影响的行数 End Function ''' <summary> ''' 执行不带参数的非查询操作 ''' </summary> ''' <param name="cmdTxt">增删改Sql语句或者存储过程</param> ''' <param name="cmdType">命令类型文本或者存储过程</param> ''' <returns>受影响的行数</returns> ''' <remarks></remarks> Public Shared Function ExecuteNoQuery(ByVal cmdTxt As String, ByVal cmdType As CommandType) As Integer '定义一个连接字符串 Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr") '定义一个数据库连接对象 Dim conn As SqlConnection = New SqlConnection(strConnStr) Dim cmd As New SqlCommand '定义一个命令对象 Dim res As Integer '定义一个变量用户存放返回结果 cmd = New SqlCommand(cmdTxt, conn) cmd.CommandType = cmdType Try '打开数据库连接 If conn.State = ConnectionState.Closed Then conn.Open() End If '执行命令 res = cmd.ExecuteNonQuery() Catch ex As Exception MsgBox(ex.Message, , "数据库操作") Finally '关闭数据库连接 If conn.State = ConnectionState.Open Then conn.Close() End If End Try Return res '返回受影响的行数 End Function ''' <summary> ''' 获取一个不带参数的查询的DataTable 结果集 ''' </summary> ''' <param name="cmdtxt">查询sql语句或者存储过程</param> ''' <param name="cmdType">命令类型(文本或者存储过程)</param> ''' <returns>查询的结果</returns> ''' <remarks></remarks> Public Shared Function GetDataTable(ByVal cmdtxt As String, ByVal cmdType As CommandType) As DataTable '定义一个连接字符串 Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr") '定义一个数据库连接对象 Dim conn As SqlConnection = New SqlConnection(strConnStr) Dim cmd As New SqlCommand '定义一个命令对象 Dim adataset As DataSet Dim adaptor As SqlDataAdapter '定义一个适配器对象 cmd = New SqlCommand(cmdtxt, conn) adaptor = New SqlDataAdapter(cmd) adataset = New DataSet cmd.CommandType = cmdType Try '打开数据库连接 If conn.State = ConnectionState.Closed Then conn.Open() '填充数据集 adaptor.Fill(adataset) End If Catch ex As Exception MsgBox(ex.Message, , "数据库操作") Finally '关闭数据库连接 If conn.State = ConnectionState.Open Then conn.Close() End If End Try Return adataset.Tables(0) '返回数据集的第一个表 End Function ''' <summary> ''' 获取一个带参数的查询的DataTable结果集 ''' </summary> ''' <param name="cmdTxt">查询SQL语句或者存储过程名称</param> ''' <param name="cmdType">命令类型（文本或者存储过程）</param> ''' <param name="paras">参数数组</param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function GetDataTable(ByVal cmdTxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr") '定义一个数据库连接对象 Dim conn As SqlConnection = New SqlConnection(strConnStr) Dim cmd As New SqlCommand '定义一个命令对象 Dim adataset As DataSet Dim adaptor As SqlDataAdapter '定义一个适配器对象 cmd = New SqlCommand(cmdTxt, conn) adaptor = New SqlDataAdapter(cmd) adataset = New DataSet cmd.CommandType = cmdType cmd.Parameters.AddRange(paras) Try '打开数据库连接 If conn.State = ConnectionState.Closed Then conn.Open() End If '填充数据集 adaptor.Fill(adataset) Catch ex As Exception MsgBox(ex.Message, , "数据库操作") Finally '关闭数据库连接 If conn.State = ConnectionState.Open Then conn.Close() End If End Try Return adataset.Tables(0) End Function ''' <summary> ''' 获取一个不带参数的查询结果阅读器 ''' </summary> ''' <param name="cmdTxt">查询的SQL语句或者存储过程名称</param> ''' <param name="cmdType">命令类型（文本或者存储过程）</param> ''' <returns>查询结果</returns> ''' <remarks></remarks> Public Shared Function GetReader(ByVal cmdTxt As String, ByVal cmdType As CommandType) As SqlDataReader Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr") '定义一个数据库连接对象 Dim conn As SqlConnection = New SqlConnection(strConnStr) Dim cmd As New SqlCommand '定义一个命令对象 'Dim reader As SqlDataReader cmd = New SqlCommand(cmdTxt, conn) cmd.CommandType = cmdType Try '打开数据库连线 If conn.State = ConnectionState.Closed Then conn.Open() End If '执行命令 'reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) Catch ex As Exception MsgBox(ex.Message, , "数据库操作") Finally End Try '返回一个阅读器 Return cmd.ExecuteReader(CommandBehavior.CloseConnection) End Function ''' <summary> ''' 获取一个带参数的查询结果阅读器 ''' </summary> ''' <param name="cmdTxt">查询的SQL语句或者存储过程名称</param> ''' <param name="cmdType">命令类型（文本或者存储过程）</param> ''' <param name="paras">参数数组</param> ''' <returns>查询结果</returns> ''' <remarks></remarks> Public Shared Function GetReader(ByVal cmdTxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As SqlDataReader Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr") '定义一个数据库连接对象 Dim conn As SqlConnection = New SqlConnection(strConnStr) Dim cmd As New SqlCommand '定义一个命令对象 cmd = New SqlCommand(cmdTxt, conn) cmd.CommandType = cmdType cmd.Parameters.AddRange(paras) 'Dim reader As SqlDataReader Try '打开数据库连线 If conn.State = ConnectionState.Closed Then conn.Open() End If '执行命令 'reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) Catch ex As Exception MsgBox(ex.Message, , "数据库操作") Finally End Try '返回一个阅读器 Return cmd.ExecuteReader() End Function End Class