| Imports System |
| Imports System.Data |
| Imports System.Data.Common |
| |
| Public Class DbFacade |
| Implements IDisposable |
| |
| Private _factory As DbProviderFactory |
| Private _cnn As DbConnection |
| Private _cmd As DbCommand |
| Private _txn As DbTransaction |
| |
| Public Sub New() |
| Me.New("Data Source=ExampleDb;User ID=SCOTT;Password=TIGER") |
| End Sub |
| |
| Public Sub New(ByVal connectionString As String) |
| _factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client") |
| DbConnect(connectionString) |
| End Sub |
| |
| Public Function DbConnect(ByVal connectionString As String) As DbConnection |
| _cnn = _factory.CreateConnection() |
| Me.ConnectionString = connectionString |
| Return _cnn |
| End Function |
| |
| Public Property ConnectionString() As String |
| Get |
| Return _cnn.ConnectionString |
| End Get |
| Set(ByVal value As String) |
| If _cnn.ConnectionString <> value Then |
| _cnn.ConnectionString = value |
| End If |
| End Set |
| End Property |
| |
| Public Sub Open() |
| If _cnn.State = ConnectionState.Closed Then _cnn.Open() |
| End Sub |
| |
| Public Sub Close() |
| ClearParams() |
| If _cnn.State = ConnectionState.Open Then _cnn.Close() |
| End Sub |
| |
| |
| |
| |
| |
| Private Sub SetBindByName(ByVal cmd As DbCommand, ByVal isName As Boolean) |
| cmd.GetType().GetProperty("BindByName").SetValue(cmd, isName, Nothing) |
| End Sub |
| |
| Public Function CreateCommand() As DbCommand |
| Dim cmd As DbCommand = _factory.CreateCommand() |
| cmd.Connection = _cnn |
| SetBindByName(cmd, True) |
| Return cmd |
| End Function |
| |
| Public Function CreateCommand(ByVal sql As String) As DbCommand |
| Dim cmd As DbCommand = CreateCommand() |
| cmd.CommandText = sql |
| Return cmd |
| End Function |
| |
| Public Function CreateDataAdapter() As DbDataAdapter |
| Return _factory.CreateDataAdapter() |
| End Function |
| |
| |
| |
| |
| |
| |
| |
| Private Sub AddParam(ByVal cmd As DbCommand, ByVal param As DbParameter) |
| If cmd.Parameters.Contains(cmd.Parameters(param.ParameterName)) Then |
| cmd.Parameters.Remove(cmd.Parameters(param.ParameterName)) |
| End If |
| cmd.Parameters.Add(param) |
| End Sub |
| |
| |
| |
| |
| |
| |
| |
| Public Function AddParam(ByVal name As String, ByVal value As Object, ByVal type As DbType) As IDataParameter |
| If value.GetType().IsArray Then |
| Return AddParamPLSQLAssociativeArray(name, value, type) |
| End If |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| Dim param As DbParameter = _cmd.CreateParameter |
| param.ParameterName = name |
| param.DbType = type |
| param.Value = value |
| AddParam(_cmd, param) |
| Return param |
| End Function |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| Public Function AddParam(ByVal name As String, ByVal type As DbType, ByVal direction As ParameterDirection, ByVal size As Integer) As IDataParameter |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| Dim param As DbParameter = _cmd.CreateParameter() |
| param.ParameterName = name |
| param.DbType = type |
| param.Direction = direction |
| param.Size = size |
| AddParam(_cmd, param) |
| Return param |
| End Function |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| Public Function AddParam(ByVal cmd As DbCommand, ByVal name As String, ByVal sourceColumn As String, ByVal type As DbType) As IDataParameter |
| Dim param As DbParameter = cmd.CreateParameter() |
| param.ParameterName = name |
| param.SourceColumn = sourceColumn |
| param.DbType = type |
| AddParam(cmd, param) |
| Return param |
| End Function |
| |
| |
| |
| |
| |
| Public Function AddParamRefCursor(ByVal name As String) As IDataParameter |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| |
| Dim asm As System.Reflection.Assembly = _factory.GetType().Assembly |
| |
| Dim paramType As Type = asm.GetType("Oracle.DataAccess.Client.OracleParameter") |
| Dim param As Object = Activator.CreateInstance(paramType) |
| |
| paramType.GetProperty("ParameterName").SetValue(param, name, Nothing) |
| paramType.GetProperty("Direction").SetValue(param, ParameterDirection.Output, Nothing) |
| |
| Dim oraDbType As Type = asm.GetType("Oracle.DataAccess.Client.OracleDbType") |
| paramType.GetProperty("OracleDbType").SetValue(param, oraDbType.GetField("RefCursor").GetValue(Nothing), Nothing) |
| AddParam(_cmd, DirectCast(param, DbParameter)) |
| Return DirectCast(param, IDataParameter) |
| End Function |
| |
| |
| |
| |
| |
| |
| |
| Public Function AddParamPLSQLAssociativeArray(ByVal name As String, ByVal value As Object, ByVal type As DbType) As IDataParameter |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| |
| Dim asm As System.Reflection.Assembly = _factory.GetType().Assembly |
| |
| Dim oraParamType As Type = asm.GetType("Oracle.DataAccess.Client.OracleParameter") |
| Dim oraParam As Object = Activator.CreateInstance(oraParamType) |
| |
| oraParamType.GetProperty("ParameterName").SetValue(oraParam, name, Nothing) |
| oraParamType.GetProperty("Size").SetValue(oraParam, CType(value, Array).Length, Nothing) |
| |
| Dim oraCollectionType As Type = asm.GetType("Oracle.DataAccess.Client.OracleCollectionType") |
| oraParamType.GetProperty("CollectionType").SetValue(oraParam, oraCollectionType.GetField("PLSQLAssociativeArray").GetValue(Nothing), Nothing) |
| |
| Dim param As IDataParameter = DirectCast(oraParam, IDataParameter) |
| param.DbType = type |
| param.Value = value |
| |
| AddParam(_cmd, DirectCast(param, DbParameter)) |
| |
| Return param |
| End Function |
| |
| |
| |
| |
| Public Sub ClearParams() |
| If _cmd Is Nothing Then Exit Sub |
| _cmd.Parameters.Clear() |
| End Sub |
| |
| |
| |
| |
| |
| Public Function GetDataTable(ByVal sql As String) As DataTable |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| _cmd.CommandText = sql |
| Dim da As DbDataAdapter = _factory.CreateDataAdapter() |
| da.SelectCommand = _cmd |
| Dim dt As DataTable = New DataTable |
| da.Fill(dt) |
| Return dt |
| End Function |
| |
| |
| |
| |
| |
| |
| Public Sub FillRefCursor(ByRef dataSet As DataSet, ByVal procedureName As String) |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| _cmd.CommandText = procedureName |
| _cmd.CommandType = CommandType.StoredProcedure |
| Dim da As DbDataAdapter = _factory.CreateDataAdapter() |
| da.SelectCommand = _cmd |
| da.Fill(dataSet) |
| End Sub |
| |
| |
| |
| |
| |
| |
| Public Sub FillRefCursor(ByRef dataTable As DataTable, ByVal procedureName As String) |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| _cmd.CommandText = procedureName |
| _cmd.CommandType = CommandType.StoredProcedure |
| Dim da As DbDataAdapter = _factory.CreateDataAdapter() |
| da.SelectCommand = _cmd |
| da.Fill(dataTable) |
| End Sub |
| |
| |
| |
| |
| |
| Public Function ExecuteSql(ByVal sql As String) As Integer |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| _cmd.CommandText = sql |
| Open() |
| Dim result As Integer = _cmd.ExecuteNonQuery() |
| If _txn Is Nothing OrElse _txn.Connection Is Nothing Then |
| Close() |
| End If |
| Return result |
| End Function |
| |
| |
| |
| |
| |
| Public Function ExecuteStored(ByVal procedureName As String) As Integer |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| _cmd.CommandType = CommandType.StoredProcedure |
| _cmd.CommandText = procedureName |
| Open() |
| Dim result As Integer = _cmd.ExecuteNonQuery() |
| If _txn Is Nothing OrElse _txn.Connection Is Nothing Then |
| Close() |
| End If |
| Return result |
| End Function |
| |
| Public Function ExecuteReader(ByVal sql As String) As DbDataReader |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| _cmd.CommandText = sql |
| Open() |
| Dim result As DbDataReader = _cmd.ExecuteReader(CommandBehavior.CloseConnection) |
| |
| Return result |
| End Function |
| |
| Public Function ExecuteScalar(ByVal sql As String) As Object |
| If _cmd Is Nothing Then _cmd = CreateCommand() |
| _cmd.CommandText = sql |
| Open() |
| Dim result as Object =_cmd.ExecuteScalar() |
| Close() |
| Return result |
| End Function |
| |
| Public Sub BeginTransaction() |
| Open() |
| _txn = _cnn.BeginTransaction() |
| End Sub |
| |
| Public Sub Commit() |
| _txn.Commit() |
| Close() |
| End Sub |
| |
| Public Sub Rollback() |
| _txn.Rollback() |
| Close() |
| End Sub |
| |
| Private disposedValue As Boolean = False |
| |
| |
| Protected Overridable Sub Dispose(ByVal disposing As Boolean) |
| If Not Me.disposedValue Then |
| If disposing Then |
| |
| If _txn IsNot Nothing Then _txn.Dispose() |
| If _cmd IsNot Nothing Then _cmd.Dispose() |
| If _cnn IsNot Nothing Then |
| If _cnn.State = ConnectionState.Open Then |
| _cnn.Close() |
| End If |
| _cnn.Dispose() |
| End If |
| End If |
| |
| |
| End If |
| Me.disposedValue = True |
| End Sub |
| |
| #Region " IDisposable Support " |
| |
| Public Sub Dispose() Implements IDisposable.Dispose |
| |
| Dispose(True) |
| GC.SuppressFinalize(Me) |
| End Sub |
| #End Region |
| |
| End Class |
