Re-Usable Database Connection Class – A Beginner’s Tutorial Part 1

At least 95% of the web applications I have dealt with in the past years tend to read, write or read/write to a SQL database. Since this is such a big part of every application I write, I came up with a data Class that I re-use for each project needing a database connection.

This standardisation ensures that every application I work on uses exactly the same naming and code structure, ensuring that if I go back to something from 2 years ago, I’ll still be able to read and understand my code. The same goes for anyone else who ends up have to edit my work.

I got the original idea for the below code from someone else’s blog a few years ago and massaged it over time. If anyone recognises it, please give me the original URL and I’ll happily give them credit and add the link.

 

Getting Started

The way I have set this up for myself 3 fold, I’ll explain each step in detail, further in the article

1. For every new web application I build, I open my dataReader solution and change the ConnectionString to reference the new database in 4 locations (search & replace), then compile a new .DLL
2. Add the data_readers.DLL to my BIN folder and add ‘Imports data_readers’ to each page’s Namespace Declaration.
3. I reuse the exact same code to connect and interact with the Database on every page

The above has worked for me for years and is simple enough that it typically takes me less then 5minutes to compile a new data_readers.dll and have a valid database connection.

 

Step. 1 – The DataReader DLL – A Database Connection

The first step is to define the reference to the database connection in your new webApplication’s web.config.  Doing so is actually out of the scope of this exercise, so I will reference an existing connection string for display purpose only.

If you’ve seen my WHS article, you’ll know that I regularly use my WHS HomeServer to work from home, and then transfer my Solution to my work PC.  The method I am proposing is fully portable as the database connection string is maintained in the Application web.config, not the data_readers.dll.

    MyWebSiteconnection " connectionString="Data Source=Server-S13223;Initial Catalog=EveryDayProgrammer;User Id=EveryMan;Password=xyz;"/>
    

When I am at work, I use the top connection and at home I REM that out and use the bottom, otherwise I just make sure to use the same username, password and catalog as what I’ve used at work.

There are those who would say that recompiling is overkill and that I could just use an extra parameter in the function to pass the reference to the ConnectionString but I don’t like having this reference visible in my code.  This way I feel it’s more secure, while it does take me a couple of minutes more at the beginning of each project, it’s a onetime change.

 

The Project

Unzip the ReUsableDatabaseConenct.zip and open the solution in Visual Studio 2010.

Imports System.Data.SqlClient
Imports System.Configuration

Public Class data

    Public Shared Function ExecuteDataSet( _
                        ByVal storedProcedureName As String, _
                        ByVal ParamArray arrParam() As SqlParameter) As DataSet

        Dim MyCommand As SqlDataAdapter
        MyCommand = New SqlDataAdapter(storedProcedureName, ConfigurationManager.ConnectionStrings("MyWebSiteconnection").ConnectionString)
        MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure

        ' Handle the parameters 
        If arrParam IsNot Nothing Then
            For Each param As SqlParameter In arrParam
                MyCommand.SelectCommand.Parameters.Add(param)
            Next
        End If

        Dim ds As New DataSet
        MyCommand.Fill(ds, " MyWebSiteconnection")

        Return ds

    End Function

    Public Shared Function ExecuteDataReader( _
                                   ByVal storedProcedureName As String, _
                                   ByVal ParamArray arrParam() As SqlParameter) As SqlDataReader

        Dim reader As SqlDataReader

        ' Open the connection 
        Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyWebSiteconnection").ConnectionString)
        cnn.Open()

        ' Define the command 
        Dim cmd As New SqlCommand
        cmd.Connection = cnn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = storedProcedureName

        ' Handle the parameters 
        If arrParam IsNot Nothing Then
            For Each param As SqlParameter In arrParam
                cmd.Parameters.Add(param)
            Next
        End If

        ' Execute the reader 
        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        Return reader
    End Function

    Public Shared Function ExecuteInsertInto( _
                                  ByVal storedProcedureName As String, _
                                  ByVal ParamArray arrParam() As SqlParameter) As Integer

        Dim returnID As Integer
        Dim strConnString As String = ConfigurationManager.ConnectionStrings("MyWebSiteconnection").ConnectionString
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = storedProcedureName

        ' Handle the parameters 
        If arrParam IsNot Nothing Then
            For Each param As SqlParameter In arrParam
                cmd.Parameters.Add(param)
            Next
        End If

        cmd.Connection = con

        Try

            con.Open()
            Dim obj As Object = cmd.ExecuteScalar()
            If TypeOf obj Is DBNull OrElse obj Is Nothing Then
                returnID = 0
            Else
                returnID = obj.ToString()
            End If

            'Catch ex As Exception

            '    Throw ex

        Finally

            con.Close()
            con.Dispose()

        End Try

        Return returnID
    End Function

End Class

I use 3 different functions to attain all my Database work, 1 dataset, 1 DataReader and one DataInsert.

a)      The ExecuteDataReader – for use in Binding controls, Delete from SQL, Update SQL

b)      The ExecuteDataSet – for use in Caching, such as with a PagedDatasource

c)       The ExecuteInsertInto – for using an Insert statement with SQL

 

You’ll notice that in each function, I reference the database connection from my web.config and in ExecuteDataSet, I also name the dataset that’s returned the same way, this is merely for simplicity.  I do a simple Search & Replace, changing the old connectionString with the new, and simultaneously I change the DataSet name as well.

Each function receives 2 parameters from the code, what Stored Procedure to fire, as well as an Array containing the SQL Parameters.

I won’t go through the above code line by line as I think it’s self-explanatory.

At this point you can do a Build Data_readers from the Build Menu and copy the data_readers.dll from the BIN folder referenced at the path in the OutPut window.  If you’re OutPut window didn’t appear after the Build, you may open it using View –  Output.

The final step would be to copy the data_treaders.dll file into your projects BIN folder, add a Reference by doing a Right click in the root of the Solutions Explorer, and choose Add Reference.  Choose the Browse tab and find data_readers.dll in BIN folder of your current Project.

 

Conclusion – Part 1

Ok, that’s the end of part 1, in my next article, I’ll show you how to write the code behind to connect to the above Class and start reading and writing to your database!

Go To Re-Usable Database Connection Class – A Beginner’s Tutorial Part 2

Happy Coding!

Comments

comments