Meta-Blog

SEARCH QandO

Email:
Jon Henke
Bruce "McQ" McQuain
Dale Franks
Bryan Pick
Billy Hollis
Lance Paddock
MichaelW

BLOGROLL QandO

 
 
Recent Posts
The Ayers Resurrection Tour
Special Friends Get Special Breaks
One Hour
The Hope and Change Express - stalled in the slow lane
Michael Steele New RNC Chairman
Things that make you go "hmmmm"...
Oh yeah, that "rule of law" thing ...
Putting Dollar Signs in Front Of The AGW Hoax
Moving toward a 60 vote majority?
Do As I Say ....
 
 
QandO Newsroom

Newsroom Home Page

US News

US National News
Politics
Business
Science
Technology
Health
Entertainment
Sports
Opinion/Editorial

International News

Top World New
Iraq News
Mideast Conflict

Blogging

Blogpulse Daily Highlights
Daypop Top 40 Links

Regional

Regional News

Publications

News Publications

 
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:

PROPERTIES:

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.

METHODS:

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.
 
TrackBacks
Return to Main Blog Page
 
 

Previous Comments to this Post 

Comments
Opening and closing a connection for every transaction, as a path to scalability? Good one, Dale. I like to start the day off with some humor.
 
Written By: Platypus
URL: http://pl.atyp.us
Hey, Linux-Boy, yes, it is.

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.
 
Written By: Dale Franks
URL: http://www.qando.net
Thanks Dale, this will be a huge time saver for me.

And of course, you’re correct about leaving a connection open while a user is updating data.
 
Written By: Davebo
URL: http://
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:

WebsiteLogin login = session.load(WebsiteLogin.class, primaryKeyLongObj);

or you could do

Criteria c = session.createCriteria(WebsiteLogin.class);
c.add(Restrictions.eq("username", userFromServlet));
WebsiteLogin login = (WebsiteLogin)c.uniqueResult();

and it has its own basic, easily customized cross-platform SQL called HQL:

String query = "DELETE FROM WebsiteLogin login WHERE login.userName = :username";
Query q = session.createQuery(query);
q.setString("username", "guest");
q.executeUpdate();
 
Written By: MikeT
URL: http://www.codemonkeyramblings.com
Thanks man. I’ll have to bookmark this post.
 
Written By: glasnost
URL: http://
Thanks for the geek stuff. I don’t understand it all, but I do learn a little.
 
Written By: timactual
URL: http://

 
Add Your Comment
  NOTICE: While we don't wish to censor your thoughts, we do blacklist certain terms of profanity or obscenity. This is not to muzzle you, but to ensure that the blog remains work-safe for our readers. If you wish to use profanity, simply insert asterisks (*) where the vowels usually go. Your meaning will still be clear, but our readers will be able to view the blog without worrying that content monitoring will get them in trouble when reading it.
Comments for this entry are closed.
Name:
Email:
URL:
HTML Tools:
Bold Italic Blockquote Hyperlink
Comment:
   
 
Vicious Capitalism

Divider

Buy Dale's Book!
Slackernomics by Dale Franks

Divider

Divider