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
   }


About the Author

igor igor

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

Downloads

Comments

  • 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

  • Live Event Date: May 7, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT This eSeminar will explore three popular games engines and how they empower developers to create exciting, graphically rich, and high-performance games for Android® on Intel® Architecture. Join us for a deep dive as experts describe the features, tools, and common challenges using Marmalade, App Game Kit, and Havok game engines, as well as a discussion of the pros and cons of each engine and how they fit into your development …

  • With JRebel, developers get to see their code changes immediately, fine-tune their code with incremental changes, debug, explore and deploy their code with ease (both locally and remotely), and ultimately spend more time coding instead of waiting for the dreaded application redeploy to finish. Every time a developer tests a code change it takes minutes to build and deploy the application. JRebel keeps the app server running at all times, so testing is instantaneous and interactive.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds