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!
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!
The SqlHelper class is one of the true gems of the Patterns and Practices group [...]
ReplyDeleteI will jump on this bandwagon. All of my .NET applications use the Data Access Application Block (DAAB). It's spectacular in it's simplicity. How can you not love only worrying about three methods: .ExecuteReader, .ExecuteDataSet and .ExecuteNonQuery. All overloaded for parameterized queries.
We evaluated using the newer versions of the enterprise library but the older "SqlHelper" version is just too simple to use. One DLL and no configuration necessary.
Ditto - don't re-invent the wheel! Been using the original one for a long time. The only thing that I'd like to see rolled into their framework for .NET 2.0 would be more thorough support for reading column values - especially for handling null values and mapping to nullable .NET primitives...good stuff otherwise...
ReplyDeleteObviously the wheel that you have to develop your application will look very different in a year or two so code naturally atrophies. In certain cases in some companies you run into this. They're not reinventing the wheel but instead they're rolling with the same wheels they had installed years earlier. That may or may not be an issue, it can be when they get a flat tire, but definitely a whole other topic altogether ;)