SQlHelper强大用处
最近写的几条线的代码,发现很多重复和数据库打交道的代码连接的打开或关闭,例如:
Imports System.Data.SqlClientImports System.ConfigurationImports System.DataPublic 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 FunctionEnd Class