asp.net下使用DbProviderFactories的資料庫操作類

封裝資料庫操作，並且提供交易處理。 複製代碼 代碼如下:使用DbProviderFactories的資料庫操作類
Imports System.Data
Imports System.Configuration
Imports System.Data.Common

'*******************************************************************
'* Page/Class Name：XPDBHelper.vb
'* Title：使用DbProviderFactories的資料庫操作類
'* Description：簡單三層結構資料庫操作類
'* Copyright：
'* Company：
'* @author：Owen,Yuan
'* Create Date：2010/3/9 14:04:56
'* Last Modifier：
'* Last Modify Date：
'*******************************************************************
Public Class XPDBHelper

'取得連接字串中的資料庫驅動提供者
Private Shared dbProviderName As String = ConfigurationManager.ConnectionStrings("ConnectionString").ProviderName

'取得資料庫連接字串
Private Shared dbConnectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

'資料庫連接。
Private connection As DbConnection

Public Sub New()
Me.connection = CreateConnection(XPDBHelper.dbConnectionString)
End Sub

Public Sub New(ByVal connectionString As String)
Me.connection = CreateConnection(connectionString)
End Sub

'建立資料庫連接
Public Shared Function CreateConnection() As DbConnection
Dim dbfactory As DbProviderFactory = DbProviderFactories.GetFactory(dbProviderName)
Dim dbconn As DbConnection = dbfactory.CreateConnection()
dbconn.ConnectionString = XPDBHelper.dbConnectionString
Return dbconn
End Function

'建立資料庫連接
Public Shared Function CreateConnection(ByVal connectionString As String) As DbConnection
Dim dbfactory As DbProviderFactory = DbProviderFactories.GetFactory(dbProviderName)
Dim dbconn As DbConnection = dbfactory.CreateConnection()
dbconn.ConnectionString = connectionString
Return dbconn
End Function

'根據預存程序建立DbCommand
Public Function GetStoredProcCommond(ByVal storedProcedure As String) As DbCommand
Dim dbCommand As DbCommand = connection.CreateCommand()
dbCommand.CommandText = storedProcedure
dbCommand.CommandType = CommandType.StoredProcedure
Return dbCommand
End Function

'根據SQL語句建立DbCommand
Public Function GetSqlStringCommond(ByVal sqlQuery As String) As DbCommand
Dim dbCommand As DbCommand = connection.CreateCommand()
dbCommand.CommandText = sqlQuery
dbCommand.CommandType = CommandType.Text
Return dbCommand
End Function

#Region "增加參數"
Public Sub AddParameterCollection(ByVal cmd As DbCommand, ByVal dbParameterCollection As DbParameterCollection)
For Each dbParameter As DbParameter In dbParameterCollection
cmd.Parameters.Add(dbParameter)
Next
End Sub

Public Sub AddOutParameter(ByVal cmd As DbCommand, ByVal parameterName As String, ByVal dbType As DbType, ByVal size As Integer)
Dim dbParameter As DbParameter = cmd.CreateParameter()
dbParameter.DbType = dbType
dbParameter.ParameterName = parameterName
dbParameter.Size = size
dbParameter.Direction = ParameterDirection.Output
cmd.Parameters.Add(dbParameter)
End Sub

Public Sub AddInParameter(ByVal cmd As DbCommand, ByVal parameterName As String, ByVal dbType As DbType, ByVal value As Object)
Dim dbParameter As DbParameter = cmd.CreateParameter()
dbParameter.DbType = dbType
dbParameter.ParameterName = parameterName
dbParameter.Value = value
dbParameter.Direction = ParameterDirection.Input
cmd.Parameters.Add(dbParameter)
End Sub

Public Sub AddReturnParameter(ByVal cmd As DbCommand, ByVal parameterName As String, ByVal dbType As DbType)
Dim dbParameter As DbParameter = cmd.CreateParameter()
dbParameter.DbType = dbType
dbParameter.ParameterName = parameterName
dbParameter.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(dbParameter)
End Sub

Public Function GetParameter(ByVal cmd As DbCommand, ByVal parameterName As String) As DbParameter
Return cmd.Parameters(parameterName)
End Function
#End Region

#Region "執行"
'執行DbCommand，返回DataSet
Public Function ExecuteDataSet(ByVal cmd As DbCommand) As DataSet
Dim dbfactory As DbProviderFactory = DbProviderFactories.GetFactory(dbProviderName)
Dim dbDataAdapter As DbDataAdapter = dbfactory.CreateDataAdapter()
dbDataAdapter.SelectCommand = cmd
Dim ds As DataSet = New DataSet()
dbDataAdapter.Fill(ds)
Return ds
End Function

'執行DbCommand，返回DataTable
Public Function ExecuteDataTable(ByVal cmd As DbCommand) As DataTable
Dim dbfactory As DbProviderFactory = DbProviderFactories.GetFactory(dbProviderName)
Dim dbDataAdapter As DbDataAdapter = dbfactory.CreateDataAdapter()
dbDataAdapter.SelectCommand = cmd
Dim dataTable As DataTable = New DataTable()
dbDataAdapter.Fill(dataTable)
Return dataTable
End Function

'執行DbCommand，返回DbDataReader
Public Function ExecuteReader(ByVal cmd As DbCommand) As DbDataReader
cmd.Connection.Open()
Dim reader As DbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
cmd.Connection.Close()
Return reader
End Function

Public Function ExecuteNonQuery(ByVal cmd As DbCommand) As Integer
cmd.Connection.Open()
Dim ret As Integer = cmd.ExecuteNonQuery()
cmd.Connection.Close()
Return ret
End Function

Public Function ExecuteScalar(ByVal cmd As DbCommand) As Object
cmd.Connection.Open()
Dim ret As Object = cmd.ExecuteScalar()
cmd.Connection.Close()
Return ret
End Function
#End Region

#Region "執行事務"

Public Function ExecuteDataSet(ByVal cmd As DbCommand, ByVal t As XPTransaction) As DataSet
cmd.Connection = t.DbConnection
cmd.Transaction = t.DbTransact
Dim dbfactory As DbProviderFactory = DbProviderFactories.GetFactory(XPDBHelper.dbProviderName)
Dim dbDataAdapter As DbDataAdapter = dbfactory.CreateDataAdapter()
dbDataAdapter.SelectCommand = cmd
Dim ds As DataSet = New DataSet()
dbDataAdapter.Fill(ds)
Return ds
End Function

Public Function ExecuteDataTable(ByVal cmd As DbCommand, ByVal t As XPTransaction) As DataTable
cmd.Connection = t.DbConnection
cmd.Transaction = t.DbTransact
Dim dbfactory As DbProviderFactory = DbProviderFactories.GetFactory(XPDBHelper.dbProviderName)
Dim dbDataAdapter As DbDataAdapter = dbfactory.CreateDataAdapter()
dbDataAdapter.SelectCommand = cmd
Dim dataTable As DataTable = New DataTable()
dbDataAdapter.Fill(dataTable)
Return dataTable
End Function

Public Function ExecuteReader(ByVal cmd As DbCommand, ByVal t As XPTransaction) As DbDataReader
cmd.Connection.Close()
cmd.Connection = t.DbConnection
cmd.Transaction = t.DbTransact
Dim reader As DbDataReader = cmd.ExecuteReader()
Return reader
End Function

Public Function ExecuteNonQuery(ByVal cmd As DbCommand, ByVal t As XPTransaction) As Integer
cmd.Connection.Close()
cmd.Connection = t.DbConnection
cmd.Transaction = t.DbTransact
Return cmd.ExecuteNonQuery()
End Function

Public Function ExecuteScalar(ByVal cmd As DbCommand, ByVal t As XPTransaction) As Object
cmd.Connection.Close()
cmd.Connection = t.DbConnection
cmd.Transaction = t.DbTransact
Return cmd.ExecuteScalar()
End Function
#End Region

End Class

簡單三層結構資料庫交易處理類 複製代碼 代碼如下:Imports System.Data
Imports System.Data.Common

'*******************************************************************
'* Page/Class Name：XPTransaction.vb
'* Title：資料庫交易處理類
'* Description：簡單三層結構資料庫交易處理類
'* Copyright：
'* Company：
'* @author：Owen,Yuan
'* Create Date：2010/3/22 10:44:20
'* Last Modifier：
'* Last Modify Date：
'*******************************************************************
Public Class XPTransaction
Implements IDisposable

Private conn As DbConnection
Private dbTrans As DbTransaction

Public Property DbConnection() As DbConnection
Get
Return Me.conn
End Get
Set(ByVal Value As DbConnection)
Me.conn = Value
End Set
End Property

Public Property DbTransact() As DbTransaction
Get
Return Me.dbTrans
End Get
Set(ByVal Value As DbTransaction)
Me.dbTrans = Value
End Set
End Property

Public Sub New()
conn = XPDBHelper.CreateConnection()
conn.Open()
dbTrans = conn.BeginTransaction()
End Sub

Public Sub New(ByVal connectionString As String)
conn = XPDBHelper.CreateConnection(connectionString)
conn.Open()
dbTrans = conn.BeginTransaction()
End Sub

Public Sub Commit()
dbTrans.Commit()
Me.Colse()
End Sub

Public Sub RollBack()
dbTrans.Rollback()
Me.Colse()
End Sub

Public Sub Colse()
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Sub

Private disposedValue As Boolean = False ' 檢測冗餘的調用

' IDisposable
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
conn.Close()
' TODO: 釋放其他狀態(託管對象)。
End If
conn = Nothing
dbTrans = Nothing
' TODO: 釋放您自己的狀態(非託管對象)。
' TODO: 將大型欄位設定為 null。
End If
Me.disposedValue = True
End Sub

#Region " IDisposable Support "
' Visual Basic 添加此代碼是為了正確實現可處置模式。
Public Sub Dispose() Implements IDisposable.Dispose
' 不要更改此代碼。請將清理代碼放入上面的 Dispose(ByVal disposing As Boolean) 中。
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region

End Class