wdabase.vb

Beginning VB.NET DatabasesAll_Code.rar

Imports Microsoft.Win32
Imports System.Data.SqlClient

Public Class WDABase
    Implements IDisposable

#Region " Class Level Variables "
    'Class level variables that are available to classes that instantiate me
    Public SQL As String

    Public Connection As SqlConnection
    Public Command As SqlCommand
    Public DataAdapter As SqlDataAdapter
    Public DataReader As SqlDataReader

    'Key size must be either 16 or 24 bytes (128 or 192 bits)
    Public CryptoKey() As Byte = System.Text.Encoding.UTF8.GetBytes( _
        "G~v!x@Z#c$a%C^b&h*K(e)K_")
    Public CryptoIV() As Byte = System.Text.Encoding.UTF8.GetBytes( _
        "rgYBp$b%K^L&r*P?k!S_B+v`")
    Public Crypto As WDACrypto
#End Region

#Region " Constructor and Destructor "
    Public Sub New(ByVal Company As String, ByVal Application As String)
        'Declare variables
        Dim objReg As RegistryKey
        Crypto = New WDACrypto(CryptoKey, CryptoIV)

        Try
            'Open the registry key
            objReg = Registry.LocalMachine.OpenSubKey("SOFTWARE\" & _
                Company & "\" & Application & "\Database", False)

            'Build the SQL connection string from the registry values
            'and initialize the Connection object
            Connection = New SqlConnection( _
                "Data Source=" & objReg.GetValue("Server") & ";" & _
                "Database=" & objReg.GetValue("Database") & ";" & _
                "User ID=" & Crypto.Decrypt(objReg.GetValue("Login")) & ";" & _
                "Password=" & Crypto.Decrypt(objReg.GetValue("Password")) & ";")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
                ExceptionErr.InnerException)
        Finally
            'Clean up
            Crypto.Dispose()
            Crypto = Nothing
            objReg = Nothing
        End Try
    End Sub

    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
#End Region

#Region " Public Procedures & Functions "
    Public Sub OpenConnection()
        Try
            Connection.Open()
        Catch SqlExceptionErr As SqlException
            Throw New System.Exception(SqlExceptionErr.Message, _
                SqlExceptionErr.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 SqlCommand(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("UPDATE ") _
                    And Not SQL.ToUpper.StartsWith("DELETE ") Then
                    Command.CommandType = CommandType.StoredProcedure
                End If
            Catch SqlExceptionErr As SqlException
                Throw New System.Exception(SqlExceptionErr.Message, _
                    SqlExceptionErr.InnerException)
            End Try
        End If
    End Sub

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

    Public Sub InitializeDataAdapter()
        Try
            DataAdapter = New SqlDataAdapter
            DataAdapter.SelectCommand = Command
        Catch SqlExceptionErr As SqlException
            Throw New System.Exception(SqlExceptionErr.Message, _
            SqlExceptionErr.InnerException)
        End Try
    End Sub

    Public Sub FillDataSet(ByRef oDataSet As DataSet, ByVal TableName As String)
        Try
            InitializeCommand()
            InitializeDataAdapter()
            DataAdapter.Fill(oDataSet, TableName)
        Catch SqlExceptionErr As SqlException
            Throw New System.Exception(SqlExceptionErr.Message, _
                SqlExceptionErr.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)
        Finally
            CloseConnection()
        End Try
    End Function
#End Region

End Class