SQlHelper强大用处

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

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

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.DataPublic Class DBHelper''' 
''' 有参数的非查询的操作''' 
''' 增删改语句或者存储过程''' 命令类型文本或者存储过程''' 参数数组''' ''' 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 = cmdTypecmd.Parameters.AddRange(paras)Try'打开数据连接If conn.State = ConnectionState.Closed Thenconn.Open()End If'执行查询操作res = cmd.ExecuteNonQuery()Catch ex As ExceptionMsgBox(ex.Message, , "数据库操作")Finally'关闭数据库连接If conn.State = ConnectionState.Open Thenconn.Close()End IfEnd TryReturn res '返回受影响的行数End Function''' 
''' 执行不带参数的非查询操作''' 
''' 增删改Sql语句或者存储过程''' 命令类型文本或者存储过程''' 受影响的行数''' 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 = cmdTypeTry'打开数据库连接If conn.State = ConnectionState.Closed Thenconn.Open()End If'执行命令res = cmd.ExecuteNonQuery()Catch ex As ExceptionMsgBox(ex.Message, , "数据库操作")Finally'关闭数据库连接If conn.State = ConnectionState.Open Thenconn.Close()End IfEnd TryReturn res '返回受影响的行数End Function''' 
''' 获取一个不带参数的查询的DataTable 结果集''' 
''' 查询sql语句或者存储过程''' 命令类型(文本或者存储过程)''' 查询的结果''' 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 DataSetDim adaptor As SqlDataAdapter '定义一个适配器对象cmd = New SqlCommand(cmdtxt, conn)adaptor = New SqlDataAdapter(cmd)adataset = New DataSetcmd.CommandType = cmdTypeTry'打开数据库连接If conn.State = ConnectionState.Closed Thenconn.Open()'填充数据集adaptor.Fill(adataset)End IfCatch ex As ExceptionMsgBox(ex.Message, , "数据库操作")Finally'关闭数据库连接If conn.State = ConnectionState.Open Thenconn.Close()End IfEnd TryReturn adataset.Tables(0) '返回数据集的第一个表End Function''' 
''' 获取一个带参数的查询的DataTable结果集''' 
''' 查询SQL语句或者存储过程名称''' 命令类型（文本或者存储过程）''' 参数数组''' ''' Public Shared Function GetDataTable(ByVal cmdTxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTableDim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")'定义一个数据库连接对象Dim conn As SqlConnection = New SqlConnection(strConnStr)Dim cmd As New SqlCommand '定义一个命令对象Dim adataset As DataSetDim adaptor As SqlDataAdapter '定义一个适配器对象cmd = New SqlCommand(cmdTxt, conn)adaptor = New SqlDataAdapter(cmd)adataset = New DataSetcmd.CommandType = cmdTypecmd.Parameters.AddRange(paras)Try'打开数据库连接If conn.State = ConnectionState.Closed Thenconn.Open()End If'填充数据集adaptor.Fill(adataset)Catch ex As ExceptionMsgBox(ex.Message, , "数据库操作")Finally'关闭数据库连接If conn.State = ConnectionState.Open Thenconn.Close()End IfEnd TryReturn adataset.Tables(0)End Function''' 
''' 获取一个不带参数的查询结果阅读器''' 
''' 查询的SQL语句或者存储过程名称''' 命令类型（文本或者存储过程）''' 查询结果''' Public Shared Function GetReader(ByVal cmdTxt As String, ByVal cmdType As CommandType) As SqlDataReaderDim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")'定义一个数据库连接对象Dim conn As SqlConnection = New SqlConnection(strConnStr)Dim cmd As New SqlCommand '定义一个命令对象'Dim reader As SqlDataReadercmd = New SqlCommand(cmdTxt, conn)cmd.CommandType = cmdTypeTry'打开数据库连线  If conn.State = ConnectionState.Closed Thenconn.Open()End If'执行命令  'reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)Catch ex As ExceptionMsgBox(ex.Message, , "数据库操作")FinallyEnd Try'返回一个阅读器  Return cmd.ExecuteReader(CommandBehavior.CloseConnection)End Function''' 
''' 获取一个带参数的查询结果阅读器  ''' 
''' 查询的SQL语句或者存储过程名称''' 命令类型（文本或者存储过程）''' 参数数组''' 查询结果''' Public Shared Function GetReader(ByVal cmdTxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As SqlDataReaderDim 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 = cmdTypecmd.Parameters.AddRange(paras)'Dim reader As SqlDataReader  Try'打开数据库连线  If conn.State = ConnectionState.Closed Thenconn.Open()End If'执行命令  'reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)  Catch ex As ExceptionMsgBox(ex.Message, , "数据库操作")FinallyEnd Try'返回一个阅读器  Return cmd.ExecuteReader()End Function
End Class

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！