2/21/2007

SQL Server: "Don't Reinvent the Wheel" Department

In your travels as a professional software developer, especially when you come into a new position and need to get used to a new enterprise and its programming - related environment - the tools, the programming style, the existing codebase, etc. you get to observe some of the repetitive coding patterns that people resort to in order to solve their problems.

One of the most common ones I've observed is where developers do not have a full understanding of how ADO.NET and connection pooling work. It seems almost like instead of seeking out and using best-practices code and techniques, that some people, either through lack of knowledge or just plain being stubborn, feel compelled to "roll their own" DAL and Database layers. Mistakes and poor design often result.

Back around 2001, I found the Microsoft Data Access Application Block ("SqlHelper") class, which quickly solved a whole bunch of data access problems for me, and in fact I still use it today - 5 years later (the v2 version, not the more recent provider-based version) for most of my data access with SQL Server. Knock-offs have been created for Oracle, etc. and I even wrote one for SQLite.

The SqlHelper class is one of the true gems of the Patterns and Practices group, IMHO. If you aren't familiar with it, you really owe yourself the favor. All this time, the only addition or change I've ever needed to make was to add a CommandTimeOut feature, which isn't present in the existing code.

What is often surprising to me is how few developers actually use this as the "talk to the database" portion of their DAL code. Yo! Don't Reinvent the Wheel! But they still do, and often they make real design boo-boos in doing so. I've actually seen a custom Connection Pool one dev created because he wasn't fully aware of the best-practices technique of "open the connection using the same connection string, do the work, and immediately close the connection and let it go back to the pool" -- somebody had told him that there was a lot of overhead in creating and tearing down connections! Wait a minute -- I thought that's why they already give you the connection pool! The coolest thing about the SQLHelper class is that you can get a DataSet from a stored procedure with parameters in ONE LINE OF CODE -- and it will even cache the SQLParameter for you! DOH! You wanna reinvent the wheel? Knock yourself out -- I wouldn't hire you!

Here is an example of the above "one liner":

DataSet ds = SqlHelper.ExecuteDataSet(connectionString, "dbo.StoredProcName", new object[] {paramvalue, paramvalue, etc.});

Here's a link to the class:
Data Access Application Block v2

VB.NET Folks, don't sweat. It comes in two flavors.

Scott Guthrie pointed out another tool that recently came out. I originally had modified the Admin assembly of their Web Data Administrator application to use it in a Windows Forms app for my Database Export Utility, but the new Database Publishing Wizard is much more sophisticated.

Here are a couple of links to additional resources courtesy of "Mr. ASP.NET":

Deploying SQL Database to Remote
Upload SQL File to Hoster and Execute

Finally, don't forget that SQL Server 2005 Service Pack 2 final is out!