最近写的几条线的代码,发现很多重复和数据库打交道的代码连接的打开或关闭,例如:
'定义一个连接字符串
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