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


About the Author

igor igor

I am a software developer, practicing combat martial arts and managing www.handymandy.ca website



  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Anthony Christie, the Chief Marketing Officer for Level Communications, is responsible for customer experience, worldwide marketing and product management. In this informative asset, he shares his insights into why a private network connection to cloud-bases applications is the right decision for your enterprise. Download now to find out more.

  • Enterprises are increasingly looking to platform as a service (PaaS) to lower their costs and speed their time to market for new applications. Developing, deploying, and managing applications in the cloud eliminates the time and expense of managing a physical infrastructure to support them. PaaS offerings must deliver additional long-term benefits, such as a lower total cost of ownership (TCO), rapid scalability, and ease of integration, all while providing robust security and availability. This report …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date