SqlProcessor

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.

SqlDatabaseAdaptor

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)";
         cmd.ExecuteNonQuery();
      }
      trans.Commit();

   }

Say you want to open a DataSet:

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

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,
                          connectionString);

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

easyCommandBuilder command_builder =
   db_adaptor.CreateSqlCommandBuilder();

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 =
   db_adaptor.CreateSqlCommandBuilder();
   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,
                                  connectionString);
p.SetObject(p);

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
p.Insert()
t.sNumber = "bye";
p.Update();
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


      #endregion
   }