How to write a database class that supports parameterized queries

Question

I'm a former classic ASP programmer and sometimes PHP programmer writing my first ASP.NET application. I'm loving the much-improved ADO.NET functions, but I'm feeling the need to write a database class. Partly I want to consolidate the code that actually interacts with the database, and partly I want to reduce what feels like repetition.

A simple database class is not hard to do, but I'm using parameterized queries as one of my security measures. I'm struggling with how to incorporate this into a class. I wrote a function to return the datatype of a column in the database by passing in the table and column name, but I can't think of a robust way to obtain the table and column name from the SQL query.

My design for the class was to have a Query() function for selecting, and an Execute() function for insert/update/delete. (Not opposed to having more public functions, but didn't want to get ahead of myself.) Both functions take a SQL string and a SortedList for the parameters. It might be possible to get the column name by finding the parameter name in the SQL string and looking in front of the equal sign. Likewise, it should be fairly simple to get the table name when the query is insert, update, or delete, because you only work with one table at a time. The big concern is selecting, because there could be one or more joins, inner selects, etc.

Am I headed in the wrong direction? Anything I'm not thinking of that could make my life easier or more difficult? Anybody written a class for this in any language that could offer some advice?

Answer 1

Don't reinvent the wheel. Look into nHibernate or LINQToSQL (or LINQToEntities) for your ORM needs.

Answer 2

Would second the call to find a tried and tested wheel that works for you, especially if this is your first foray into aspnet... there will be plenty else to keep you busy.

Would add a suggestion for SubSonic, which is perhaps a little lighter than nHibernate, but it really depends on the nature of your project, they are both great tools, and both have saved me months of work over the last few years.

Answer 3

It sounds to me like your "simple database class" is hiding too many details from the classes that need to use it.

I've written classes that contain a SqlCeEngine and expose methods like "LookupDescription(String Code)" ... I think that kind of design is something you should be looking into. And, consider looking into LINQ. It has a lot to offer.

Answer 4

I think since this is your first experience in ASP.NET you would be well advised to look into Linq to SQL. Do some tutorials so you get a feel for how it works before you try to code any Linq queries.

The only reasons I can think of to NOT use Linq to SQL in your case would be if you are not using SQL Server (or need to support other DBs either now or in future), or you cannot use .NET 3.5 runtime for some reason.

Good luck