Inheritance question - retrieving data from Access database file and SQL Express

Question

Hi,

I am developing an application which will be connected to Access database at the beginning and the plan is to switch to MS SQL or SQL Express in the near future. The datatables structures are same for both types of databases and I am trying to avoid duplicating the code and trying to find the way to minimize the code.

For example I wrote the following function for retrieving data from Access database:

public static DataTable GetActiveCalls()
    {
        string select = "SELECT call_id, call_time, msisdn, status FROM call WHERE status = 0 OR status = 1 ORDER by call_id ASC";
        OleDbCommand cmd = new OleDbCommand(select, conn);
        DataTable dt = new DataTable("Active Calls");
        OleDbDataAdapter DA = new OleDbDataAdapter(cmd);
        try
        {
            conn.Open();
            DA.Fill(dt);
        }
        catch (Exception ex)
        {
            string sDummy = ex.ToString();
        }
        finally
        {
            conn.Close();
        }
        return dt;
    }

and the following code is for SQL Express database:

public static DataTable GetActiveCalls()
    {
        string select = "SELECT call_id, call_time, msisdn, status FROM call WHERE status = 0 OR status = 1 ORDER by call_id ASC";
        SqlCommand cmd = new SqlCommand(select, conn);
        DataTable dt = new DataTable("Active Calls");
        SqlDataAdapter DA = new SqlDataAdapter(cmd);
        try
        {
            conn.Open();
            DA.Fill(dt);
        }
        catch (Exception ex)
        {
            string sDummy = ex.ToString();
        }
        finally
        {
            conn.Close();
        }
        return dt;
    }

These two methods are almost the same. The only differences are SqlCommand/OleDbCommand and SqlDataAdapter/OleDbDataAdapter. There are also some methods which take arguments for example:

public static void AddMessage(string callID, string content)
    {
        string select =
            "INSERT INTO message(key, direction, content, read, write_time) VALUES (@callId, 0, @content, 0, @insertTime)";
        OleDbCommand cmd = new OleDbCommand(select, conn);
        cmd.Parameters.AddWithValue("callId", callID.ToString());
        cmd.Parameters.AddWithValue("content", content);
        cmd.Parameters.AddWithValue("insertTime", DateTime.Now.ToString());
        try
        {
            conn.Open();
            cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            string sDummy = ex.ToString();
        }
        finally
        {
            conn.Close();
        }
    }

In this case SQL query string is also the same for both databases but there is the difference between the type of cmd (SqlCommand/OleDbCommand).

I would really appreciate if anyone could give any suggestion about how to avoid duplicating the code and optimize the given problem.

Answer 1

You can use the database independent interfaces IDbDataAdapter and IDbCommand, and then create the concrete instances using a factory. Here is an example.

However I would reccommend using an ORM solution like NHibernate if your application is not very simple, because there are little differences in the SQL language between Access and SQL Server that can make your data access code more complex.

Answer 2

You should take a look at the Enterprise Library data access bits. The specifics of the underlying database provider are abstracted away for you. For example:

string sql = @"UPDATE tblContent 
         SET Title = @Title, Content = @Content, IsPublic = @IsPublic, ItemOrder = @ItemOrder
         WHERE ContentItemID = @ContentItemID";

Database db = DatabaseFactory.CreateDatabase();
using(DbCommand cmd = db.GetSqlStringCommand(sql))
{
    db.AddInParameter(cmd, "Title", DbType.String, title);
    db.AddInParameter(cmd, "Content", DbType.String, content);
    db.AddInParameter(cmd, "IsPublic", DbType.Boolean, isPublic);
    db.AddInParameter(cmd, "ItemOrder", DbType.Int32, itemOrder);
    db.AddInParameter(cmd, "ContentItemID", DbType.Int32 , contentItemID);

    db.ExecuteNonQuery(cmd);
}

...or...

string sql = "SELECT MenuText FROM tblMenuItems WHERE MenuItemID = @MenuItemID";

Database db = DatabaseFactory.CreateDatabase();
using(DbCommand cmd = db.GetSqlStringCommand(sql))
{
    db.AddInParameter(cmd, "MenuItemID", DbType.Int32, menuItemID);

    using(IDataReader dr = db.ExecuteReader(cmd))
    {
     while(dr.Read())
     {
      return dr["MenuText"].ToString();
     }
     return null;
    }
}

Both of the examples above work with Access and MS SQL and provided you stick to SQL statements that are compatible with both Jet and MS SQL you shouldn't have any problems.

When you switch from Access to MS SQL just change the connection string, for example, from:

<connectionStrings>
    <add 
         name="ContentManager" 
         connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Content.mdb;" 
         providerName="System.Data.OleDb"/>
</connectionStrings>

...to...

<connectionStrings>
    <add
     name="ContentManager"
     connectionString="Data Source=your.sql.server;Initial Catalog=content;Persist Security Info=True;User ID=cmuser;Password=password"
     providerName="System.Data.SqlClient" />
</connectionStrings>

To achieve this you just need to reference the following DLL's in the EL distribution:

Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Data.dll

Well worth a look.

Cheers
Kev