Homo Erasmus

El Homo Erasmus nace, se va de fiesta, se examina y muere.

20 diciembre 2006

DALBase Oledb

Imports System.Configuration
Imports System.Data.Oledb
Public Class DALBase
Implements IDisposable

'Class level variables that are available to classes tha instantiate me
Public SQL As String

Public Connection As OledbConnection
Public Command As OledbCommand
Public DataAdapter As OledbDataAdapter
Public DataReader As OledbDataReader

Public Sub Dispose() Implements System.IDisposable.Dispose
If Not DataReader Is Nothing Then
DataReader.Close()
DataReader = Nothing
End If
If Not DataAdapter Is Nothing Then
DataAdapter.Dispose()
DataAdapter = Nothing
End If
If Not Command Is Nothing Then
Command.Dispose()
Command = Nothing
End If
If Not Connection Is Nothing Then
Connection.Close()
Connection.Dispose()
Connection = Nothing
End If
End Sub

Public Sub OpenConnection()
Try
Connection.Open()
Catch OledbExceptionErr As OledbException
Throw New System.Exception(OledbExceptionErr.Message, _
OledbExceptionErr.InnerException)
Catch InvalidOperationExceptionErr As InvalidOperationException
Throw New System.Exception(InvalidOperationExceptionErr.Message, _
InvalidOperationExceptionErr.InnerException)
End Try
End Sub

Public Sub CloseConnection()
Connection.Close()
End Sub

Public Sub InitializeCommand()
If Command Is Nothing Then
Try
Command = New OledbCommand(SQL, Connection)
'See if this is a stored procedure
If Not SQL.ToUpper.StartsWith("SELECT ") _
And Not SQL.ToUpper.StartsWith("INSERT ") _
And Not SQL.ToUpper.StartsWith("DELETE ") Then
Command.CommandType = CommandType.StoredProcedure
End If
Catch OledbExceptionErr As OledbException
Throw (New System.Exception(OledbExceptionErr.Message, _
OledbExceptionErr.InnerException))
End Try
End If
End Sub

Public Sub New()
'Get the connection settings from the application configuration file
Dim objAppSettings As Specialized.NameValueCollection
objAppSettings = ConfigurationSettings.AppSettings
'Build the SQL connection string and initialize the Connection object
Connection = New OledbConnection("Data Source=" & objAppSettings.Item("Data Source") & ";User ID=" & _
objAppSettings.Item("User ID") & ";Password=" & objAppSettings.Item("Password"))
objAppSettings = Nothing
End Sub

Public Sub AddParameter(ByVal Name As String, ByVal Type As OledbType, _
ByVal Size As Integer, ByVal value As Object)
Try
Command.Parameters.Add(Name, Type, Size).Value = value
Catch OledbExceptionErr As OledbException
Throw New System.Exception(OledbExceptionErr.Message, _
OledbExceptionErr.InnerException)
End Try
End Sub

Public Sub InitializeDataAdapter()
Try
DataAdapter = New OledbDataAdapter
DataAdapter.SelectCommand = Command
Catch OledbExceptionErr As OledbException
Throw New System.Exception(OledbExceptionErr.Message, _
OledbExceptionErr.InnerException)
End Try
End Sub

Public Sub FillDataSet(ByRef oDataSet As DataSet, ByVal TableName As String)
Try
InitializeCommand()
InitializeDataAdapter()
DataAdapter.Fill(oDataSet, TableName)
Catch OledbExceptionErr As OledbException
Throw New System.Exception(OledbExceptionErr.Message, _
OledbExceptionErr.InnerException)
Finally
Command.Dispose()
Command = Nothing
DataAdapter.Dispose()
DataAdapter = Nothing
End Try
End Sub

Public Function ExecuteStoredProcedure() As Integer
Try
OpenConnection()
ExecuteStoredProcedure = Command.ExecuteNonQuery()
Catch ExceptionErr As Exception
Throw New System.Exception(ExceptionErr.Message, _
ExceptionErr.InnerException)
End Try
End Function

Private disposedValue As Boolean = False ' To detect redundant calls

' IDisposable
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
' TODO: free unmanaged resources when explicitly called
End If

' TODO: free shared unmanaged resources
End If
Me.disposedValue = True
End Sub
End Class