Imports System.Data.SqlClient
Public Class SQLHelper
Dim ConnString As String '连接字符串
Dim Conn As SqlClient.SqlConnection '数据库连接
Dim Trans As SqlClient.SqlTransaction '事务---自动开启、出错自动回滚,但必须手动提交
Dim IfTrans As Boolean = False '是否启用事务,默认不启用
''' <summary>
''' 获取或设置用于打开SQL Server数据库的连接字符串。
''' </summary>
''' <returns></returns>
Public Property ConnectionString As String '连接字符串 属性
Get
Return ConnString
End Get
Set(value As String)
ConnString = value
End Set
End Property
''' <summary>
''' 获取或设置SQLHelper的此实例使用的SqlConnection。
''' </summary>
''' <returns></returns>
Public Property Connection As SqlClient.SqlConnection '数据库连接 属性
Get
Return Conn
End Get
Set(value As SqlClient.SqlConnection)
Conn = value
End Set
End Property
''' <summary>
''' 获取SQLHelper的此实例使用的SqlTransaction,需配合UseTransaction属性使用。
''' </summary>
''' <returns></returns>
Public ReadOnly Property Transaction As SqlClient.SqlTransaction '事务 只读属性
Get
Return Trans
End Get
End Property
''' <summary>
''' 设置SQLHelper的此实例是否使用事务,若为True,则事务自动开启、出错自动回滚,但必须手动提交事务。
''' </summary>
Public WriteOnly Property UseTransaciton As Boolean '是否启用事务 属性
Set(value As Boolean)
IfTrans = value
If value Then '如果使用事务,则添加连接状态改变事件
AddHandler Conn.StateChange, AddressOf Conn_StateChange
End If
End Set
End Property
Public Sub New()
End Sub
''' <summary>
''' 如果给定包含连接字符串的字符串,则初始化SQLHelper的新实例。使用完后请调用Dispose销毁此实例释放资源。
''' </summary>
''' <param name="sqlconnstring">连接字符串</param>
Public Sub New(sqlconnstring As String) '用连接字符串 构造函数 +1重载
ConnString = sqlconnstring
Conn = New SqlClient.SqlConnection(ConnString)
End Sub
''' <summary>
''' 初始化包含连接字符串和是否使用事务的SQLHelper的新实例。使用完后请调用Dispose销毁此实例释放资源。
''' </summary>
''' <param name="sqlconnstring">连接字符串。</param>
''' <param name="usetransaction">指示是否使用事务。</param>
Public Sub New(sqlconnstring As String, usetransaction As Boolean) '用连接字符串,是否使用事务 构造函数 +2重载
ConnString = sqlconnstring
Conn = New SqlClient.SqlConnection(ConnString)
IfTrans = usetransaction
If usetransaction Then '如果使用事务,则添加连接状态改变事件
AddHandler Conn.StateChange, AddressOf Conn_StateChange
End If
End Sub
''' <summary>
''' 使用一个SqlConnection初始化SQLHelper的新实例。使用完后请调用Dispose销毁此实例释放资源。
''' </summary>
''' <param name="sqlconn">一个SqlConnection,它表示到SQL Server实例的连接。</param>
Public Sub New(sqlconn As SqlClient.SqlConnection) '用数据库连接 构造函数 +3重载
ConnString = sqlconn.ConnectionString
Conn = sqlconn
End Sub
''' <summary>
''' 初始化具有一个SqlConnection和是否使用事务的SQLHelper的新实例。使用完后请调用Dispose销毁此实例释放资源。
''' </summary>
''' <param name="sqlconn">一个SqlConnection,它表示到SQL Server实例的连接。</param>
''' <param name="usetransaction">指示是否使用事务。</param>
Public Sub New(sqlconn As SqlClient.SqlConnection, usetransaction As Boolean) '用数据库连接,是否使用事务 构造函数 +4重载
ConnString = sqlconn.ConnectionString
Conn = sqlconn
IfTrans = usetransaction
If usetransaction Then '如果使用事务,则添加连接状态改变事件
AddHandler Conn.StateChange, AddressOf Conn_StateChange
End If
End Sub
''' <summary>
''' Insert、Delete、Update数据库。
''' </summary>
''' <param name="cmdtext">要对数据源执行的Transact-SQL语句、表名或存储过程,cmdtext中不包含参数。</param>
''' <param name="cmdtype">指示如何解释cmdtext。</param>
''' <returns>返回受影响的数据行数。</returns>
Public Function ExecAddDelUpdate(cmdtext As String, cmdtype As CommandType) As Integer '添加删除更新数据库 无参,有/无事务 +1重载
Dim CMD As New SqlClient.SqlCommand(cmdtext, Connection) With {
.CommandType = cmdtype '设置cmdtext类型:存储过程名、SQL命令、表名
} '新建查询命令
Try
If Conn.State <> ConnectionState.Open Then
Conn.Open()
End If
If IfTrans Then
CMD.Transaction = Transaction
End If
Return CMD.ExecuteNonQuery
Catch ex As Exception
If IfTrans Then
'Trans.Rollback()
'Trans.Dispose()
'If Conn.State <> ConnectionState.Closed Then
'Conn.Close()
'End If
Return 0
End If
Throw New Exception(ex.Message)
Finally
If Conn.State <> ConnectionState.Closed And Not IfTrans Then
Conn.Close()
End If
End Try
End Function
''' <summary>
''' Insert、Delete、Update数据库。
''' </summary>
''' <param name="cmdtext">要对数据源执行的Transact-SQL语句、表名或存储过程,cmdtext中包含参数。</param>
''' <param name="cmdtype">指示如何解释cmdtext。</param>
''' <param name="paras">用于cmdtext的参数。</param>
''' <returns>返回受影响的数据行数。</returns>
Public Function ExecAddDelUpdate(cmdtext As String, cmdtype As CommandType, paras As SqlClient.SqlParameter()) As Integer '添加删除更新数据库 有参,有/无事务 +2重载
Dim CMD As New SqlClient.SqlCommand(cmdtext, Connection, Trans) With {
.CommandType = cmdtype '设置cmdtext类型:存储过程名、SQL命令、表名
} '新建查询命令
CMD.Parameters.AddRange(paras) '添加参数
Try
If Conn.State <> ConnectionState.Open Then '若连接未打开,则打开
Conn.Open()
End If
If IfTrans Then
CMD.Transaction = Transaction
End If
Return CMD.ExecuteNonQuery '执行
Catch ex As Exception
If Not IfTrans Then '如果使用了事务,则回滚,销毁,关闭连接
'Trans.Rollback()
'Trans.Dispose()
'If Conn.State <> ConnectionState.Closed Then
' Conn.Close()
'End If
Return 0
End If
Throw New Exception(ex.Message)
Finally
If Conn.State <> ConnectionState.Closed And Not IfTrans Then '如果未使用事务,则关闭连接
Conn.Close()
End If
End Try
End Function
''' <summary>
''' Select数据库。
''' </summary>
''' <param name="cmdtext">要对数据源执行的Transact-SQL语句、表名或存储过程,cmdtext中不包含参数。</param>
''' <param name="cmdtype">指示如何解释cmdtext。</param>
''' <returns>返回检索到的DataTable数据表。</returns>
Public Function ExecSelect(cmdtext As String, cmdtype As CommandType) As DataTable '检索数据库 无参 +1重载
Dim dt As New DataTable
Dim ds As New DataSet
'定义cmd
Dim CMD As New SqlClient.SqlCommand(cmdtext, Connection) With {
.CommandType = cmdtype '设置cmdtext类型:存储过程名、SQL命令、表名
}
'定义adapter
Dim sqlAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(CMD)
Try
sqlAdapter.Fill(ds) '用adapter将dataSet填充
If ds.Tables.Count > 0 Then
dt = ds.Tables(0) 'datatable为dataSet的第一个表
End If
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
Return dt
End Function
''' <summary>
''' Select数据库。
''' </summary>
''' <param name="cmdtext">要对数据源执行的Transact-SQL语句、表名或存储过程,cmdtext中包含参数。</param>
''' <param name="cmdtype">指示如何解释cmdtext。</param>
''' <param name="paras">用于cmdtext的参数。</param>
''' <returns>返回检索到的DataTable数据表。</returns>
Public Function ExecSelect(cmdtext As String, cmdtype As CommandType, paras As SqlClient.SqlParameter()) As DataTable '检索数据库 有参 +1重载
Dim dt As New DataTable
Dim ds As New DataSet
'定义cmd
Dim CMD As New SqlClient.SqlCommand(cmdtext, Connection) With {
.CommandType = cmdtype '设置cmdtext类型:存储过程名、SQL命令、表名
}
CMD.Parameters.AddRange(paras) '添加参数
'定义adapter
Dim sqlAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(CMD)
Try
sqlAdapter.Fill(ds) '用adapter将dataSet填充
If ds.Tables.Count > 0 Then
dt = ds.Tables(0) 'datatable为dataSet的第一个表
End If
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
Return dt
End Function
''' <summary>
''' 当此实例的Connection打开,且UseTransaction为True时发生:自动开启事务(Transaction)。
''' </summary>
Private Sub Conn_StateChange() '打开连接自动开启事务
If Conn.State = ConnectionState.Open And IfTrans Then
Try
Trans = Conn.BeginTransaction
Catch ex As Exception
MsgBox(ex.Message, CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "开启事务失败")
If Conn.State <> ConnectionState.Closed Then
Conn.Close()
End If
End Try
End If
End Sub
''' <summary>
''' 提交此实例的Transaction,并关闭连接,若提交失败则自动回滚。
''' </summary>
Public Sub CommitTransaction() '提交事务
Try
Trans.Commit()
Catch ex As Exception
Trans.Rollback()
Throw New Exception(ex.Message)
Finally
If Conn.State <> ConnectionState.Closed Then
Conn.Close()
End If
End Try
End Sub
''' <summary>
''' 销毁SQLHelper的此实例
''' </summary>
Public Sub Dispose()
'释放数据库连接
If Conn.State <> ConnectionState.Closed Then
Conn.Close()
End If
Conn.Dispose()
Conn = Nothing
ConnString = Nothing
'释放事务
If Trans IsNot Nothing Then
Trans.Dispose()
End If
Trans = Nothing
IfTrans = Nothing
End Sub
End Class