Database operation class using DbProviderFactories under asp.net
- 2020-05-10 17:58:58
- OfStack
Encapsulates database operations and provides transaction processing.
Simple 3-tier structured database transaction class
use DbProviderFactories Database operation class
Imports System.Data
Imports System.Configuration
Imports System.Data.Common
'*******************************************************************
'* Page/Class Name : XPDBHelper.vb
'* Title Use: DbProviderFactories Database operation class
'* Description Simple: 3 Hierarchical database operation class
'* Copyright :
'* Company :
'* @author : Owen,Yuan
'* Create Date : 2010/3/9 14:04:56
'* Last Modifier :
'* Last Modify Date :
'*******************************************************************
Public Class XPDBHelper
' Gets the database driver provider in the connection string
Private Shared dbProviderName As String = ConfigurationManager.ConnectionStrings("ConnectionString").ProviderName
' Gets the database connection string
Private Shared dbConnectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
' Database connection.
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
' Creating a database connection
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
' Creating a database connection
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
' Created from a stored procedure 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
' According to the SQL Statements to create 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 " Increase the parameter "
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 " perform "
' perform DbCommand To return to 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
' perform DbCommand To return to 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
' perform DbCommand To return to 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 " Perform transactions "
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
Simple 3-tier structured database transaction class
Imports System.Data
Imports System.Data.Common
'*******************************************************************
'* Page/Class Name : XPTransaction.vb
'* Title : database transaction processing class
'* Description Simple: 3 Hierarchical database transaction processing class
'* 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 ' Detect redundant calls
' IDisposable
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
conn.Close()
' TODO: Release other states ( Managed objects ) .
End If
conn = Nothing
dbTrans = Nothing
' TODO: Release your own state ( Unmanaged object ) .
' TODO: Set the large field to null .
End If
Me.disposedValue = True
End Sub
#Region " IDisposable Support "
' Visual Basic This code was added to properly implement the resolvable pattern.
Public Sub Dispose() Implements IDisposable.Dispose
' Do not change this code. Please put the cleanup code into the above Dispose(ByVal disposing As Boolean) In the.
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region
End Class