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.
The way I have set this up for myself 3 fold, I’ll explain each step in detail, further in the article
- 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
- Add the data_readers.DLL to my BIN folder and add ‘Imports data_readers’ to each page’s Namespace Declaration.
- 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.
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!
Visit my Sponsors
- Using the Ajax Modal Popup as a Login Control - A Beginner`s Guide - The Everyman Programmer The Everyman Programmer on Using the Ajax Modal Popup as a Login Control – A Beginner`s Guide – Part 2
- Using the Ajax Modal Popup as a Login Control – A Beginner`s Guide - Part 2 - The Everyman Programmer The Everyman Programmer on Passing Parameters to a QueryString – The right way to do it, Encryption
- Using the Ajax Modal Popup as a Login Control – A Beginner`s Guide - Part 2 - The Everyman Programmer The Everyman Programmer on Re-Usable Database Connection Class – A Beginner’s Tutorial Part 1
- Passing Parameters to a QueryString - The right way to do it, Encryption - The Everyman Programmer The Everyman Programmer on asp.Net 4.0 Routing, a simple Tutorial that works
- Nested Repeaters, do it clean and simple – A Beginner's Tutorial – Part 1 - The Everyman Programmer The Everyman Programmer on Re-Usable Database Connection Class – A Beginner’s Tutorial Part 1