Introduction
After developing many applications and websites I have always felt the need to create a class that gives me the power to program in as few lines as possible.
With that in mind, I became rather frustrated with the Microsoft database access classes. These classes are sold to us as "best practices", but they fail to abide by the best practices of coding that we all learned in programming 101.
If those helper classes are so "helpfull" then why do I still have define parameters before every sp call?
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, 7)
If you're like me you must be sick of doing that over and over again. More then anything I really hate having to define all the parameter types every time I need to make a call to the database. What is even worse is that the microsoft data access classes make a call to
SqlCommandBuilder.DeriveParameters(cmd)
lol.. you must be kidding right? No I'm not! The code makes this call and yet it still requires you to define all your stored procedure parameter datatypes. I have the source code to the application block. Look for yourself if you have it. After that call the Microsoft code clones all the parameters and stuffs them into nasty array. Talk about over complicating everything and wasting memory. Clearly this is unacceptable code and should be droped from your application.
With these problems in mind, I went about creating a new class that does things my way. :)
The code I've posted here is basically an example of what an ideal database access class should look like.
Using the code
The code that I've uploaded is a simple class called DataManager. This class must be inherited from. It allows the derived class to make database calls in only three lines of code. It will handle all your connections, transactions, rollbacks, and dataset / datareader calls.
Public Class ExampleDAL
Inherits DataManager
Public Sub ExampleSet(ByVal param1 As String, ByVal param2 As String, ByVal param3 As String)
Me.Begin()
Me.Exec("example1_set", param1, param2)
Me.Exec("example2_set", param3)
Me.Commit()
End Sub
Public Function ExampleList() As Data.DataSet
Me.Begin()
Dim ds As Data.DataSet = Me.Exec("example1_list")
Me.Commit()
Return ds
End Function
End Class
Points of Interest
Usually, you will want to create classes for each of your application constructs. For example you might want to create a class called Accounts that derives from DataManager. This class will contain all your get, set, and list methods.
Please note that this is only an example of how to make a simple DataManager class. You will need to add more methods to execute datareaders, log functionality, and handle other situations for a real application.
The class only allows you execute stored procedures. This is all you should ever be doing anyway.
I've included inline debuging code that will help you see what calls are being made to the database in the debug window. I find this to be very helpfull since there are some situations where you do not have rights to use SQL Profiler on the database.
