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 

'**************************

上一个：asp.net下降文本格式数据导入到数据库中的代码
下一个：asp.net BasePage类+Session通用用户登录权限控制