My Basic ADO.NET Helper Functions

I use a couple of very simply functions that I build into a common class for a lot of my projects. I’ve always tended to build onto my base classes functions that help speed up development without adding too much weight to any project.

There may be much better ways of doing this and I would love for anyone reading to feel free and comment additional methods/information or drawbacks to the information displayed here.

To begin, go ahead and create a class file (for your common functions) if you don’t already have one.

Enumeration(s)

I am a big fan of enumerations, simply because of their strongly typed usage. I’ve added the following enumeration to help with the ADO Helper functions.

Enum SQLExecuteMode
    save rollback
End Enum

The ADO.NET Wrapper Object

This wrapper object takes the place of declaring the SqlCommand Object, SQL string, SQLConnection, and parameters.

Public Class objSQLDB
    Private _SQL As String
    Private _OverrideConnectionString As String
    Private _cmd As New SqlCommand

    Public Property SQL() As String
        Get
            Return _SQL
        End Get
        Set(ByVal value As String)
            _SQL = value
        End Set
    End Property

    Public Property OverrideConnectionString() As String
        Get
            Return _OverrideConnectionString
        End Get
        Set(ByVal value As String)
            _OverrideConnectionString = value
        End Set
    End Property

    Public Property SQLCommand() As SqlCommand
        Get
            Return _cmd
        End Get
        Set(ByVal value As SqlCommand)
            _cmd = value
        End Set
    End Property

    Public Function RetrieveDataTable() As DataTable
        'NOTE: Get proper ConnectionString
        Dim strConnectionString As String = My.Settings.CHMS_DB
        If String.IsNullOrEmpty(_OverrideConnectionString) = False Then strConnectionString = _OverrideConnectionString

        'NOTE: Create connection
        Dim MyConnection As New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = _cmd

        cmd.Connection = MyConnection
        cmd.CommandText = _SQL

        'NOTE: Fill DataTable with sql results
        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable
        da.Fill(dt)

        'NOTE: Clear all objects not being returned from memory
        MyConnection = Nothing
        cmd = Nothing
        da = Nothing

        Return dt
    End Function

    Public Function ExecuteNonQuery(Optional ByVal executeMode As SQLExecuteMode = SQLExecuteMode.save) As Boolean
        'NOTE: Get proper ConnectionString
        Dim strConnectionString As String = My.Settings.CHMS_DB
        If String.IsNullOrEmpty(_OverrideConnectionString) = False Then strConnectionString = _OverrideConnectionString

        'NOTE: Create connection
        Dim MyConnection As New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = _cmd

        MyConnection.Open() 'Connection must be open prior to creation of transaction

        Dim myTransaction As SqlTransaction = MyConnection.BeginTransaction()

        cmd.Transaction = myTransaction
        cmd.Connection = MyConnection
        cmd.CommandText = _SQL

        'NOTE: Execute SQL
        Try
            cmd.ExecuteNonQuery()
            Select Case executeMode
                Case SQLExecuteMode.save
                    myTransaction.Commit()
                Case SQLExecuteMode.rollback
                    myTransaction.Rollback()
            End Select
        Catch ex As Exception
            myTransaction.Rollback()
            Throw New Exception("Error while attempting to write to database")
            Return False
        Finally
            MyConnection.Close()
            MyConnection = Nothing
            cmd = Nothing
        End Try

        Return True
    End Function

    Public Function ExecuteScalar() As Object
        'NOTE: Get proper ConnectionString
        Dim strConnectionString As String = My.Settings.CHMS_DB
        If String.IsNullOrEmpty(_OverrideConnectionString) = False Then strConnectionString = _OverrideConnectionString

        'NOTE: Create connection
        Dim MyConnection As New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = _cmd

        cmd.Connection = MyConnection
        cmd.CommandText = _SQL

        'NOTE: Retrieve Single Value
        Dim objSQLReturn As Object
        Try
            MyConnection.Open()
            objSQLReturn = cmd.ExecuteScalar()
            MyConnection.Close()
        Catch ex As Exception
            Throw New Exception("Error while attempting to retrieve value from database")
            Return False
        End Try

        'NOTE: Clear all objects not being returned from memory
        MyConnection = Nothing
        cmd = Nothing

        Return objSQLReturn
    End Function
End Class

How I Use It

I use this any time I am interacting with the database for such actions as retrieving datatables, executing stored procedures, insert/updating/deleting SQL data, whatever.

Retrieving a DataTable from a Stored Procedure

Here is a brief example of how I could retrieve a Datatable from a SPROC I’ve already created:

Public Function getEmployeeReport(ByVal lngCompanyID as Integer, _
    ByVal dtmFilterBeginDate As DateTime, _
    ByVal dtmFilterEndDate As DateTime, _
    ByVal lngUserID As Integer) As DataTable

    objSQLDB.SQL = "dbo.report_amt_employee"
    objSQLDB.SQLCommand.CommandType = CommandType.StoredProcedure
    objSQLDB.SQLCommand.Parameters.AddWithValue("@lngCompanyID", lngCompanyID)
    objSQLDB.SQLCommand.Parameters.AddWithValue("@dtmFilterBeginDate", dtmFilterBeginDate)
    objSQLDB.SQLCommand.Parameters.AddWithValue("@dtmFilterEndDate", dtmFilterEndDate)
    objSQLDB.SQLCommand.Parameters.AddWithValue("@lngUserID", lngUserID)

    Return objSQLDB.RetrieveDataTable
End Function

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s