Database operation class using DbProviderFactories under asp.net

  • 2020-05-10 17:58:58
  • OfStack

Encapsulates database operations and provides transaction processing.
 
 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 

Related articles: