More Geek Stuff Posted by: Dale Franks
on Wednesday, October 25, 2006
My day-to-work consists mainly of building software applications that add, edit, and delete data from databases. Most often, I use SQL Server or Microsoft Access as a data store. Since we switched over to a .Net shop a few years ago, I created a standard data access class to handle all of the heavy lifting of grabbing adding, or changing the data.
I know there are a lot of developers, both professional and hobbyist, who do programming who read the blog, so I thought I'd offer the class to anyone who wanted it.
The class assumes that you prefer to operate disconnected from the database, in order to increase scaleability. In the environment in which I work, there are limited number of concurrent database connections, so we always open the database connection, extract or modify the data, then close the database connection. This allows a large number of users to utilize a relatively small number of concurrent database connections. This also works well for web applications, where the number of potential users is far higher than the number of concurrent database connections you can afford to buy.
So, I've uploaded this zip file containing the source code for the class. There are two files in the zip archive, one of which contains the VB.NET version of the class, and the other contains the C# version. Alternatively, you can simply copy and paste the source code from these text files: The VB.NET version is here, and the C# version is here.
The C# version, by the way, is already commented using the appropriate XML tags for automatically creating documentation for the code in an XML file.
There's nothing really spectacular about them, I've just found that they make accessing databases easier, and they are generic enough to fit into any application.
Please feel free to use them if you think they might have some utility for you.
Each class contains the following elements for both SQL Server and OleDb databases:
SQLString: A public string to which you pass the SQL statement you wish to execute.
TargetTable: A public string containing the name of the dataset table into which you want to add data using the CreateDataset procedure.
ConnWD: Publicly accessible connection to the database.
adocWD: Publicly accessible ADO command.
dtrWD: Publicly accessible Data Reader.
daWD: Publicly accessible Data Adapter.
dsWD: Publicly accessible Dataset.
OpenBasicReader: Opens the database and creates a DataReader to transmit the data that corresponds to the SQLString property.
CloseBasicReader: Closes the DataReader and closes the database connection.
OpenParameterSet: Creates a database connection and command object so that the user can set parameters for database queries.
OpenParameterReader: Opens a DataReader using the connection and command that were created by the OpenParameterSet procedure, and the command text of the SQLString property. In general, you call the OpenParameterSet procedure to open the database connection and command, add your required parameters to the command object, then call the OpenParameterReader procedure.
CloseParameterReader: Closes the parameterized DataReader opened by the OpenParameterReader procedure.
ExecuteDataUpdater: Executes a non-query SQL statement from the SQLString Property on the command and connection created by the OpenParameterSet procedure. In general, you call the OpenParameterSet procedure to open the database connection and command, add your required parameters to the command object, then call the ExecuteDataUpdater procedure to run UPDATE, INSERT, or DELETE SWL statements.
CreateDataSet: Creates a dataset, and fills the table named in the TargetTable property with data. This is especially useful for filling a combo box or list box with data.
SQLDate: Converts a DateTime value into the standardized UTC DateTime string format used by Microsoft SQL Server. INPUT PARAMETERS: TestDate - The DateTime value that must be converted to the SQL Server UTC format. RETURNS: The String value containing the SQL UTC-format date.
1) Most of the time, the application consists of a user opening a record, making changes to it, then saving the changes a few minutes later. So, you’ve got two transactions in 3 minutes. If you’ve got 150 users and 50 database connection licenses, you can’t hold a connection open to just sit there idle for three minutes while the user futzes around with the record. Try it, and you’ll end up maxing out your database connections, while other users can’t use the application.
2) Hey, did you notice the class allows you to make a dataset? Do you know what you can do with datasets? You can build a client side model of the database, disconnect it from the main database, make a whole bunch of changes to different records, then, when you’re done, update the whole shebang all at once. So, no matter how many changes you make, you only have to do two transactions. One to grab the dataset, and the second to update it.
Oh, but wait, you aren’t a .NET developer, are you? So, maybe the problem is that you don’t have a frickin’ clue what’s going on.
Hey, do you know why I don’t come by your site and criticize how you do C++ application design on Linux? It’s because I don’t want to look like a fool by criticizing stuff I might not know much about.
I could see issues with harmonizing that stuff, though. The database stuff I do would end up being a nightmare harmonizing the data if you did that.
Have you checked out NHibernate? I’ve had the opportunity to use Hibernate and it makes raw JDBC or ADO.NET look certifiably ancient. It’s an object-relational mapper so a call to load an object from a record might look like this: