Database-Common-Code

Database Common Code

What is Database Common?

In programming it is often wise to create objects to perform frequently done tasks. DatabaseCommon is the class we use to do this. Included here is the full code of the class that is used by our team to perform work.

How it works

When you create a new instance of DatabaseCommon, or you create an object that inherits from DatabaseCommon, you get a number of procedures and functions that allow easy access to a database via a connection string. There is some intelligence built into the class to determine if the connection is a generic OleDB connection or a SQL Connection.

The ConnectionString must be stored in the Web.config file as the line of code that retrieves it is

            ConnectionString = ApplicationSettings.GetValue(“Connectionstring”, GetType(String))

The most commonly used function (by our team) is

    Public Function ExecuteProcedure(ByVal spName As String, ByVal spParams As Hashtable, Optional ByVal returns As Boolean = True) As System.Data.DataSet

which will execute a stored procedure which is passed to the function as the variable spName. All name value pairs of parameters to be passed to the stored procedure are passed to the function in the Hash Table spParams. The function returns a Dataset that can be manipulated in code upon return. If there is no return value –  as in the case of an insertion or update, then the optional returns variable should be set to false.

Full Code of Database Common

Imports Microsoft.VisualBasic
Imports System.data
Public Class DatabaseCommon
    ' DatabaseCommon is meant ot be an abstract class, instantiated objects
    ' representing tables or views in the database should inherit from this class
    Dim myConnectionString As String
    Dim ApplicationSettings As New AppSettingsReader
    Dim myValidationMessages As Collection
Public Property validationMessages() As Collection
        ' This class tracks a collection of messages that
        ' are set during validation of the datbase object
        Get
            Return myValidationMessages
        End Get
        Set(ByVal value As Collection)
           myValidationMessages = value
        End Set
    End Property
    Public Property ConnectionString() As String
        Get
            Return myConnectionString
        End Get
        Set(ByVal value As String)
            myConnectionString = value
        End Set
    End Property
 
   Public Function isOLEDBConnection() As Boolean
        ' Helps to handle both SQL and OleDB connections
        If ConnectionString.ToUpper.Contains("OLEDB") Then Return True
        Return False
    End Function
 
    Private Function Connection() As Object
        If isOLEDBConnection() Then
            Dim conn As New System.Data.OleDb.OleDbConnection(ConnectionString)
            Return conn
        Else
            Dim conn As New System.Data.SqlClient.SqlConnection(ConnectionString)
            Return conn
        End If
    End Function
 
    Private Function Command() As Object
        If isOLEDBConnection() Then
            Dim cmd As New System.Data.OleDb.OleDbCommand
            Return cmd
        Else
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Return cmd
        End If
    End Function
 
    Private Function Adapter(ByVal cmd As Object) As Object
        If isOLEDBConnection() Then
            Dim a As New System.Data.OleDb.OleDbDataAdapter(cmd)
            Return a
        Else
            Dim a As New System.Data.SqlClient.SqlDataAdapter(cmd)
            Return a
        End If
    End Function
 
    Public Sub New()
        ' Gets Connection from Application Settings
        ' This code may be be modified if the ConnectionString is not obtained
        ' From the Applications Settings or uses a different key.
        Try
            ConnectionString = ApplicationSettings.GetValue("Connectionstring", GetType(String))
        Catch
            ' This is the case that the ConnectionString would be entered by a user
            ' No action needs to be performed here
        End Try
    End Sub
 
    Public Sub New(ByVal aConnectionString As String)
        ' Gets Connection as passed by user
        ConnectionString = aConnectionString
    End Sub
 
    Public Sub addValidationMessage(ByVal vm As String)
        validationMessages.Add(vm)
    End Sub
 
    Public Sub clearValidationMessages()
        validationMessages.Clear()
    End Sub
 
    Public Function ExecuteProcedure(ByVal spName As String, ByVal spParams As Hashtable, ByVal order As ArrayList) As System.Data.DataSet
        ' Create the connection
        Dim conn = Connection()
        ' Create the  Command Object
        Dim sqlCommand1 = Command()
        sqlCommand1.Connection = conn
        sqlCommand1.CommandText = spName
        sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
        ' Create an adapter to work with the dataset
        Dim sqlAdapter1 = Adapter(sqlCommand1)
        ' The HashTable is not preserving the order in which items were
        ' placed within it, need to find way to fix this
        ' Fill the SQL command object with the parameters
        Dim item As String
        For Each item In order
            sqlCommand1.Parameters.AddWithValue(item, spParams.Item(item))
        Next
        conn.Open()
        sqlCommand1.ExecuteNonQuery()
        sqlCommand1.Dispose()
        conn.Close()
        Return Nothing
    End Function
 
    Public Function ExecuteProcedure(ByVal spName As String, ByVal spParams As Hashtable, Optional ByVal returns As Boolean = True) As System.Data.DataSet
        ' Create the connection
        Dim conn = Connection()
        ' Create the  Command Object
        Dim sqlCommand1 = Command()
        sqlCommand1.connection = conn
        sqlCommand1.CommandText = spName
        sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
        ' Create an adapter to work with the dataset
        Dim sqlAdapter1 = Adapter(sqlCommand1)
        ' Fill the SQL command object with the parameters
        Dim item As IDictionaryEnumerator = spParams.GetEnumerator
        While item.MoveNext
            sqlCommand1.Parameters.Add(item.Key, item.Value)
        End While
        conn.Open()
        If returns Then
            Dim ds As New System.Data.DataSet
            sqlAdapter1.Fill(ds)
            sqlCommand1.Dispose()
            conn.Close()
            Return ds
        Else
            sqlCommand1.ExecuteNonQuery()
            sqlCommand1.Dispose()
            conn.Close()
            Return Nothing
        End If
   End Function
 
    Public Function ExecuteProcedure(ByVal spName As String, Optional ByVal returns As Boolean = True) As System.Data.DataSet
        ' Create the connection
        Dim conn = Connection()
        ' Create the  Command Object
        Dim sqlCommand1 = Command()
        sqlCommand1.connection = conn
        sqlCommand1.CommandText = spName
        sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
        ' Create an adapter to work with the dataset
        Dim sqlAdapter1 = Adapter(sqlCommand1)
        conn.Open()
        If returns Then
            Dim ds As New System.Data.DataSet
            sqlAdapter1.Fill(ds)
            sqlCommand1.Dispose()
            conn.Close()
            Return ds
        Else
            sqlCommand1.ExecuteNonQuery()
            sqlCommand1.Dispose()
            conn.Close()
            Return Nothing
        End If
    End Function
 
    Public Function ExecuteSQL(ByVal sql As String) As System.Data.DataSet
        ' Create the connection
        Dim conn = Connection()
        ' Create the  Command Object
        Dim sqlCommand1 = Command()
        sqlCommand1.connection = conn
        sqlCommand1.CommandText = sql
        sqlCommand1.CommandType = System.Data.CommandType.Text
        ' Create an adapter to work with the dataset
        Dim sqlAdapter1 = Adapter(sqlCommand1)
        conn.Open()
        Dim ds As New System.Data.DataSet
        sqlAdapter1.Fill(ds)
        sqlCommand1.Dispose()
        conn.Close()
        Return ds
    End Function
 
    Public Function IsDateOK(ByVal d As Date) As Boolean
        If d = "#12:00:00 AM#" Then
            Return False
        Else
            Return IsDate(d)
        End If
    End Function
 
    Public Function returnScalarLong(ByVal ds As DataSet) As Long
        If ds Is Nothing Then Return 0
        If ds.Tables.Count = 0 Then Return 0
        If ds.Tables(0).Rows.Count = 0 Then Return 0
        Return ds.Tables(0).Rows(0).Item(0)
    End Function
 
    Public Function returnScalarString(ByVal ds As DataSet) As String
        If ds Is Nothing Then Return ""
        If ds.Tables.Count = 0 Then Return ""
        If ds.Tables(0).Rows.Count = 0 Then Return ""
        Return ds.Tables(0).Rows(0).Item(0).ToString
    End Function
 
    Function br() As String
        Return "<br/>"
    End Function
 
    Function br2() As String
        Return br() + br()
    End Function
End Class

Example of calling the executeProcedure function

Public Function CategorySave() As Boolean
        ' *** saves  to the database, returns true on success ***
        If Not CategoryValidate() Then Exit Function
        isModified = False
        Dim ht As New Hashtable
        ht.Add("@CategoryNameText", CategoryNameText)
        ht.Add("@GroupID", CategoryGroupID)
        ht.Add("@DescriptionText", DescriptionText)
        ht.Add("@ParentCategoryID", ParentCategoryID)
        ht.Add("@ParentCodeID", ParentCodeID)
        ht.Add("@DeletedBit", DeletedBit)
        ht.Add("@LastUpdUserID", LastUpdUserID)
        If IsDateOK(LastUpdDate) Then ht.Add("@LastUpdDate", LastUpdDate) Else ht.Add("@LastUpdDate", Date.Today)
        If IsDateOK(EffectiveDate) Then ht.Add("@EffectiveDate", EffectiveDate) Else ht.Add("@EffectiveDate", Date.Today)
        If IsDateOK(ExpirationDate) Then ht.Add("@ExpirationDate", ExpirationDate) Else ht.Add("@ExpirationDate", Nothing)
        ht.Add("@CategoryYear", CategoryYear)
        Dim ds As New DataSet
        ds = ExecuteProcedure("spInsertCategories", ht, InsertCategoryPropertyList())
        If Not ds Is Nothing Then
            CategoryID = ds.Tables(0).Rows(0).Item("Category")
        End If
    End Function

Web.config Usage

The following code should be under the configuration root in your project web.config file.

 <appSettings>

        <add key="Connectionstring" value="Your Database Connection String goes here"/>

any other application settings should go here  

</appSettings>