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