Last week I discussed using ADO.NET in enterprise applications. A good part of the article was explaining the negatives of using the DataSet class. Although I stand by that conclusion, I also want to say that I like ADO.net overall. Some of the tools it provides are good for enterprise applications, while others are better for smaller applications.
ADO.NET provides common interfaces and base classes that can be used with different database providers. While some companies use everything Microsoft, we do not. In all honesty, I would choose MySQL over SQL Server for almost any project but that is an argument for another day. Like I was saying, ADO.NET is good, but can have some very repetitive code. Managing connections, creating commands, adding parameters, and executing readers is pretty annoying. But, the worst thing about ADO.NET is having to create database provider specific code. To create a connection to an Odbc database, you have to actually load the OdbcConnection class.
Luckily, Microsoft realized that ADO.NET had these short comings and created Data Access Application blocks, which are part of their Enterprise Library. I have been using them for a couple weeks and am definitely a fan. The greatest thing about it is the ability to completely abstract out the database provider. For example (Copied from the Microsoft website):
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("GetProductsByCategory");
// Retrieve products from category 7.
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, 7);
DataSet productDataSet = db.ExecuteDataSet(dbCommand);
If you didn’t notice, there was no mention about an Ole, SQL, Oracle, or Odbc provider. Also, I think it is a really clean and logical way to abstract the database. I definitely recommend checking out Data Access Application blocks, as well as the entire Enterprise library.