Wednesday Sep 12th 2007 by igor igor

This class can work with any database that supports ADO.NET and saves a lot of code writing.

I have developed a small database application and was really overwhelmed by the amount of SQL code I had to generate. At some point, I decided to change databases from Access to Sqlite and had to change a huge amount of code because of this change.

I came up with three new database classes that can save you a substantial amount of work.


This is an extremely small and useful class:

SqlDatabaseAdaptor db_adaptor =
   new SqlDatabaseAdaptor(DatabaseType.SqlServer, connectionString);

After you have created a db_adaptor, you can use it to access a database in the way you would use ADO Net interfaces. Say you want to insert a new record into some Test table and you want to use a transaction:

using(DbTransaction trans = db_adaptor.Connection.BeginTransaction())
      using(DbCommand cmd = db_adaptor.Connection.CreateCommand())
         cmd.CommandText = "insert into Test ( Name,Age)
                            Values ('Joe',29)";


Say you want to open a DataSet:

using(DbCommand cmd = db_adaptor.Connection.CreateCommand())
      cmd.CommandText = "select * from Test";
      DbDataAdapter adapter =
      adapter.SelectCommand = cmd;
      DataSet ds = new DataSet();

If you are familiar with the Ado NET, there are no differences at all, but the advantage of using SqlDatabaseAdaptor is amazing; you can switch to another database in no time at all:

SqlDatabaseAdaptor db_adaptor =
   new SqlDatabaseAdaptor(SqlDatabaseAdaptorNS.DatabaseType.Access,

Another nice class that can be used instead of standard DbCommandBuilder is a new easyCommandBuilder class:

easyCommandBuilder command_builder =

This command builder doesn't have the huge overhead that a standard DbCommandBuilder has. The easyCommandBuilder class caches all tables information and uses simplified queries for better performance. Check this out:

//assuming that you filled these parameter array prior to the call
DbParameter[] parameters;
easyCommandBuilder command_builder =
   command_builder.Init("Test");    //Name of the table
   DbCommand ins_cmd = command_builder.GetInsertCommand(parameters);
   DbCommand upd_cmd = command_builder.GetUpdateCommand(parameters);
   DbCommand del_cmd = command_builder.GetDeleteCommand(parameters);
   DbCommand set_cmd = command_builder.GetSelectCommand(parameters);

And the last class is SqlProcessor that automates all mentioned above:

SqlProcessor p = new SqlProcessor(DatabaseType.SQLite,

Test t = new Test();
t.iNumber = 10;
t.dNumber = 25;
t.sNumber = "hello";
//inserts the record into the Test database ( table and class name
//should be the same
t.sNumber = "bye";
p.iNumber = 5;    // this is a key field
p.Select();       //selects a record where iNumber = 5

p.Delete()        // deletes a record where iNumber = 5
Note: All queries are parameterized and high performing.

This is it. I would be happy to have your opinion about this and ideas how to improve it.

/////////////////////// Some test class ////////////////////////

public class Test
      private int iVal;
      private double dVal;
      private string sVal;
      private bool m_Modified;

      public Test()
         iVal = 10;
         dVal = 15.45f;
         sVal = "hello_again";

       public int iNumber
         get { return iVal; }
         set { iVal = value; }

      public double dNumber
         get { return dVal; }
         set { dVal = value; }

      public string sNumber
         get { return sVal; }
         set { sVal = value; }

      #region ISqlHandle Members

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved