As a guy who started programming back in the days of DAO, I have nothing but love for ADO.NET. But let’s face it, you can still end up writing pretty lousy code with it. Not everybody’s fully aware of the best uses of the ADO.NET. One of the most common problems I keep stumbling onto in other peoples code is the poor use of ADO.NET Connection Pooling feature.
Somewhere, down the line Microsoft probably started noticing that their cool new data access model wasn’t being fully utilized. The birth of the Microsoft Enterprise Library was probably due to this.
As cool as the Enterprise Library is, I’ve found it to be an over kill for most of my projects. In fact one of my favorite projects featured on CodeProject, was because Enterprise Library’s Offline Application Block was so clunky (and very limited).
Around the same time that I worked on on the SCOAB (Smart Client Offline Application Block), I also wrote a ADO.NET wrapper similar to the Enterprise Library. It pretty much follows the public interface of the Enterprise Library, but it’s a very tiny wrapper compared to the enormous DAAB (Data Access Application Block).
You can download the .cs file here:Ā AbstractDatabase.zip (1.88 kb)
This contains a generic abstract base class called AbstractDatabase. You can extend this class to use it with any type of relational database type. If you take a look at my SQLite Membership Provider, you’ll find this class derived to be used with SQLite ADO.NET Wrapper. I have simply called that class Database, and it’s code looks something like this:
public class Database : AbstractDatabase<SQLiteConnection, SQLiteCommand, SQLiteDataAdapter>
{
protected override string GetConnectionString()
{
return string.Format(ConfigurationManager.ConnectionStrings["BlogEngine"].ConnectionString,
HttpContext.Current.Server.MapPath("~/App_Data"));
}
}
As you see, the only member you need to override is the GetConnectionString() method. This brings up the question as to why I made this abstract. Why not read the connection string value from the web.config? Well, I’ve done this because I use this class on both web and desktop projects. And in some cases, especially with WinForms applications, the database is SQLite and resides in the application directory. When that happens, I can always hard code the database file name.
Here’s a example of the the Database class’s usage (taken from the SQLiteBlogProvier class).
using (Database db = new Database())
using (DbTransaction txn = db.BeginTransaction())
{
// SQLite doesn't support TRUNCATE
DbCommand cmd = db.GetSqlStringCommand("DELETE FROM be_Settings");
db.ExecuteNonQuery(cmd, txn);
cmd = db.GetSqlStringCommand(
"INSERT INTO be_Settings (SettingName, SettingValue) VALUES (@name, @value)");
db.AddInParameter(cmd, "@name", DbType.String, "");
db.AddInParameter(cmd, "@value", DbType.String, "");
foreach (string key in settings.Keys)
{
cmd.Parameters[0].Value = key;
cmd.Parameters[1].Value = settings[key];
cmd.ExecuteNonQuery();
}
txn.Commit();
}
A quick rundown of the public interface of AbstractDatabase class:
void AddInParameter(System.Data.Common.DbCommand, string, System.Data.DbType, object)
void AddInParameter(System.Data.Common.DbCommand, string, System.Data.DbType, int, object)
DbTransaction BeginTransaction()
void Dispose()
DataSet ExecuteDataSet(System.Data.Common.DbCommand)
int ExecuteNonQuery(System.Data.Common.DbCommand)
ExecuteNonQuery(System.Data.Common.DbCommand, System.Data.Common.DbTransaction)
DbReader ExecuteReader(System.Data.Common.DbCommand)
ExecuteReader(System.Data.Common.DbCommand, System.Data.CommandBehavior)
T ExecuteScalar<T>(System.Data.Common.DbCommand, T)
abstract string GetConnectionString()
DbCommand GetSqlStringCommand(string)
DbCommand GetSqlStringCommand(string, params object[])
DbCommand GetStoredProcedureCommand(string)
Connection { get; }
Update (June 20, 2008): I have updated this class with a minor bug fix and have added support for other parmeter types including Output and InputOuput parameters (Thanks Yordan).
Update (Oct 20, 2008): This wrapper has been updated and made a part of the Codoxide Common Library
Sameera Perera 
Hi this post rocks!!!
You should put a donate paypal link to it ; )
How to get a dataset from stored procedure using the AbstractDatabase wrapper:
Obviously I am doing something wrong :
public bool Meta_Data_Get ( ref string msg, ref DataSet ds, ref string dbObjectName , ref bool visible , ref string tableName )
{
try
{
using (Database db = new Database ( ))
using (DbTransaction txn = db.BeginTransaction ( ))
{ //comm — so existing users are overwritten !!!
" + e.Message );
msg = "An error in the application occurred. Report to advanced support error_code: mm1 " + e.Message;
return false;
} //eof catch
LikeLike
Sorry , this was the way to do it … I had error in other parts of my code … So put this pay pal link anyway . Thanks !!!
LikeLike
public DbParameter AddInOutParameter ( DbCommand cmd, string paramName, DbType paramType, int size, object value )
{
DbParameter param = cmd.CreateParameter ( );
param.DbType = paramType;
param.ParameterName = paramName;
param.Size = size;
param.Value = value;
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add ( param );
return param;
}
LikeLike
Yordan,
Hey, I'm really sorry I couldn't reply to your comment. BlogEngine has apparently stopped sending me comment notifications. I need to find out why.
Yes, I was intending on including a method for adding an output parameter as well. Somehow, have missed it. I'll update as soon as my laptop comes back from repair… which should be any day now.
Thanks for the PayPal idea. I really want to post bit more code before I considered a step like that š
Don't want to seem too gready š
LikeLike
Hi,
nice post – wasn´t an applicable solution for my needs, but pointed me on the right way. Thanks.
LikeLike
I tried using the AbstractDatabase class for an Oracle connection, but I get the following error.
Error 1 The type 'Oracle.DataAccess.Client.OracleCommand' cannot be used as type parameter 'COMMAND_TYPE' in the generic type or method 'SERP.core.Providers.AbstractDatabase<CONNECTION_TYPE,COMMAND_TYPE,ADAPTER_TYPE>'. There is no implicit reference conversion from 'Oracle.DataAccess.Client.OracleCommand' to 'System.Data.Common.DbCommand'. C:Documents and SettingsdhawsMy DocumentsVisual Studio 2008ProjectsSERPSERPProvidersOracleProviderDatabase.cs 13 18 SERP.core
The code looks like this:
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using Oracle.DataAccess.Client;
namespace App.core.Providers.OracleProvider
{
/// <summary>Wrapper for Oracle Database interations</summary>
public class Database : AbstractDatabase<OracleConnection, OracleCommand, OracleDataAdapter>
{
…
}
I tried using your BlogEngine implementation as an example. I am very new to C#, but would like to implement a database access methodology that is db platform independent; however, Oracle is one of the providers I would like to use.
LikeLike
Hi dkhaws7,
I'm not sure what Oracle.DataAccess.Client.OracleCommand is. But the following code will work. I have to admit that I haven't tried this with Oracle and have almost no experience with any flavor of Oracle. But as long as the database client library conforms to ADO.NET specification, the wrapper will work. System.Data.OracleClient which is bundled with .NET framework does conform to it.
using System.Data.OracleClient;
namespace Codoxide.Data.Samples
{
class Program
{
class OracleDb : Database<OracleConnection, OracleCommand, OracleDataAdapter>
{
..
LikeLike
Thanks, that cleared the errors. I got Oracle.DataAccess.Client from another example. It worked for the example, but did not work for your implementation. Thank you again.
LikeLike
Excellent post. The AbstractDatabase class works nicely… but aren't you missing methods for 'committing' and 'rolling back' transactions?
LikeLike
The Commit and Rollback are available through the DbTransaction (returned in db.BeginTransaction()). You can see a code sample in the second snippet in the post.
LikeLike
does this class work with MS SQL server ?
i can not seem to get pasted the first line :
public class Database : AbstractDatabase<SQLiteConnection, SQLiteCommand,…. }
I have no idea what SQLite is and what parameters are required to get this working for MS SQL!!
can you provide an example using MS SQL
THANKS
LEE
LikeLike
Lee,
It sure does work with MS SQL Server.
SQLite is a embedded SQL database. All you need to do to get this working with SQL Server, is to replace SQLiteXX bits with SqlXX. Which means that you'll replace:
SQLiteConnection with SqlConnection
SQLiteCommand with SqlCommand
SQLiteDataAdapter with SqlDataAdapter
LikeLike
Thanks that worked
Lee
LikeLike
For Oracle.DataAccess.Client, BindByName was set to false by default, but your wrapper class does not allow resetting the property. So it is not working when using parameters.
LikeLike