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

In my previous article Re-Usable Database Connection Class – A Beginner’s Tutorial Part 1, I offered a breakdown of my custom Database Connection Class which I use in almost every project I am involved with.  In this article, I’ll discuss implementing the class and its usage.

 

Taking a step back and understanding the Database Connection Class

As I discussed in my previous article, I have standardised my connection methods using this database connection class, this provides multiple pluses.

• I can literally copy and paste existing code from a previous project, change the parameters and Stored Procedure name and off I go.
• I’m not re-inventing the wheel with every project
• I can hand off the project to someone else and after a few minutes they know exactly what’s going on and what code to expect in every page

 

3 Functions are all you need

My class contains three simple functions; I’ll list them here and explain how to use them.

 

ExecuteDataSet

I use this method anytime I use a page that I need to use caching or more often, paging, its used this way.

Dim Source As DataView

        Dim param(1) As SqlParameter

        param(1) = New SqlParameter("@TextToSearch", SqlDbType.NVarChar, 25)
 param(1).Value = TextToSearch

        Using ds As DataSet = data_readers.data.ExecuteDataSet("LoadSearch", param(1)
            If ds.Tables.Count > 0 Then
                If ds.Tables(0).Rows.Count > 0 Then

                    lblErrorMsg.Text = String.Empty
                    Source = New DataView(ds.Tables("nestedRepeaters"))

                    repSearchResults.DataSource = Source
repSearchResults.DataBind()

                Else

                    repSearchResults.DataSource = Nothing
                    repSearchResults.DataBind()

                    lblErrorMsg.Visible = True

                    lblErrorMsg.Text = "No records Found for '" & txtSearch.Text & "'"

                End If
            End If
         End Using

What’s actually happening here?  Let’s walk through it, in my Page Declaration I declare the variable called Source as a Dataview.

 

Below, I am

1. Telling asp.Net to expect  1 sql Parameter
2. I declare the SQL parameter as type NVarChar, with length of 25 characters and named @TectToSearch
3. I simply assign the text from my Search Textbox as the value that will be sent to the Stored Procedure

        Dim param(1) As SqlParameter

        param(1) = New SqlParameter("@TextToSearch", SqlDbType.NVarChar, 25)
 param(1).Value = Searchbox.text

An example of multiple Parameters becomes simply

        Dim param(3) As SqlParameter

        param(1) = New SqlParameter("@TextToSearch", SqlDbType.NVarChar, 25)
 param(1).Value = Searchbox.text

        param(2) = New SqlParameter("@UserId", SqlDbType.int)
 param(2).Value = userId.toString

        param(3) = New SqlParameter("@Culture", SqlDbType.NVarChar, 6)
 param(3).Value = culture.toString

From here we assemble the call to the Class.  We call the function ExecuteDataSet with 2 parameters, the first being the Stored Procedure to fire and the second, an array of parameters.  The function will return a dataset to our newly declared dataset ds.  You now have a all your rows stored in your dataset (ds).

Using ds As DataSet = data_readers.data.ExecuteDataSet("LoadSearch", param(1), param(2), param(3))
Now, we do some errorchecking for efficiency.

If ds.Tables.Count > 0 Then
                If ds.Tables(0).Rows.Count > 0 Then

If no tables were found we move to our Else statement, if Tables were found but 0 rows were returned, we move to our Else statement.  In this case, in our Else statement, I bind the Repeaters dataSource to Nothing, and display an error Message on a label.

 

If the rows.count is above 0, I ensure the Error Message label is blank and move my dataset (ds) into the DataView I declared at my Page Declaration called Source.  Source is then bound to my Repeater.

 

                    lblErrorMsg.Text = String.Empty
                    Source = New DataView(ds.Tables("nestedRepeaters"))

                    repSearchResults.DataSource = Source
repSearchResults.DataBind()

Why am I using Source?

In the above example, I move my dataset (ds) into a Dataview called source before Binding Source to the repeater, why?  Simply, I can manipulate the content of the dataset (ds) before binding.  Also, in some cases, such as paging,  the binding will occur in another sub procedure once a correct page number has been found.  Paging is outside the scope of this example.

At this point, you should have a functioning call to the database connection and your repeater should be bound.  You will need to write the code behind to populate the content to your Repeater’s ItemTemplate.  If you are unsure how to do this, please see my Article Nested Repeaters, do it Clean and Simple, A Beginners Tutorial – Part 1.

 

ExecuteDataReader – Reading

 

The usage of ExecuteDataReader is similar to ExecuteDataSet but I use it more often, typically for display only pages, like a user profile, etc.  This method is also used for Updates and Deletes to the Database.

Dim param(1) As SqlParameter
        param(1) = New SqlParameter("@id", SqlDbType.Int)
        param(1).Value = id

We declare our number of Parameters and their value(s).

Using reader As SqlClient.SqlDataReader = data_readers.data.ExecuteDataReader("Load_UserName", param(1))
            If reader.Read Then

lblUserName.Text = reader("UserName").ToString

            Else

                lblUserName.Text = "Not Found"

            End If
        End Using

We declare a SqlDataReader as Reader and call the SubProcedure ‘LoadUser_Name) with the parameter called @Id.  Next we will determine whether rows have been found, if reader.read (=True) then we simply assigns the contents of the database column Username for this row to our Label called lblUserName.  If reader.read is not True, we simply display an error message to the user.

 

ExecuteDataReader – Deleting

                Dim param(2) As SqlParameter

                param(1) = New SqlParameter("userID", SqlDbType.UniqueIdentifier)
                param(1).Value = New Guid(myGuid)

                param(2) = New SqlParameter("@beerId", SqlDbType.Int)
                param(2).Value = itemId

                Using reader As SqlClient.SqlDataReader = data_readers.data.ExecuteDataReader("Delete_FromFavorites", param(1), param(2))
                    If reader.RecordsAffected > 0 Then
                        Bind(itemId)
                    End If
                End Using

As you can see from the above example, performing a Delete is simple and clean.  Multiple parameters are sent to the function, the Delete_FromFavorites Stored Procedure is called and if the Database returns a RecordsAffected number above zero, we simply call our Bind procedure which would call all contents from the Database anew.  If you wanted, you could throw some error checking into an Else statement.

ExecuteDataReader – Update

Dim param(4) As SqlParameter

                param(1) = New SqlParameter("beerId", SqlDbType.Int)
                param(1).Value = itemId

                param(2) = New SqlParameter("@CountryID", SqlDbType.Int)
                param(2).Value = CountryID

                param(3) = New SqlParameter("userID", SqlDbType.UniqueIdentifier)
                param(3).Value = New Guid(myGuid)

                param(4) = New SqlParameter("@CountryName", SqlDbType.VarChar, 250)
                param(4).Value = CountryName.toString

                Using reader As SqlClient.SqlDataReader = data_readers.data.ExecuteDataReader("Update_Country", param(1), param(2), param(3), param(4))
                    If reader.RecordsAffected > 0 Then
                        Bind(itemId)
                        lblSuccess.Text = "** Country successfully updated. **"
                    Else

                    End If
                End Using

Again, as you can see, I’m using more and more paramters but the code remains clean and simple, easy to read and maintain.  In this case, the Update_Country Stored Procedure is called, again,if the database returns a RecordsAffected number above zero, we rebind the control with the newly updated information and in this case, we display a confirmation message to the user.

 

ExecuteInsertInto

 

The ExecuteInsertInto is used for insert statements, I made a separate function because I use it to return the ID of the newly created row.  If you do not require the Id of the new entity created, then use the ExecuteDataReader and just check for RecordsAffect > 0, just like the two previous examples.

Dim returnID As Integer

        returnID = data_readers.Read.ExecuteInsertInto("INSERT_thisProduct", param(1), param(2), param(3), param(4) _
                                                                                                , param(5), param(6), param(7), param(8), param(9), param(10) _
                                                                                                , param(11), param(12), param(13))

As you can see, the ExecuteInsertInto function calls the Insert_thisProduct Stored Procedure and returns the  SCOPE_IDENTITY()of the last created record.  Obviously, for this to work, your Stored Procedure needs to SELECT SCOPE_IDENTITY() after the Insert Statement.

At this point, you have the RecordId of the new record.

 

Bringing it all together

 

Hopefully the above article was clear enough to demonstrate the ease of which I am able recreate my Query statements in clear and concise manner.  The .dll I use is portable to I regularly copy and paste code from previous projects into my new ones, sometimes I don’t even have to change any of the parameter names or values if properly planned.

To properly test this code, you’ll need the two zip file below.

If you have any questions or comments about this article, please feel free to comment below.

Happy Coding!

 

Comments

comments