继承问题 - 从Access数据库文件和SQL Express中检索数据

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.

我正在开发一个应用程序,它将在开始时连接到Access数据库,并且计划在不久的将来切换到MS SQL或SQL Express。两种类型的数据库的数据表结构是相同的,我试图避免重复代码并试图找到最小化代码的方法。

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

例如,我编写了以下函数来从Access数据库中检索数据:

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:

以下代码适用于SQL Express数据库:

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:

这两种方法几乎相同。唯一的区别是SqlCommand / OleDbCommand和SqlDataAdapter / OleDbDataAdapter。还有一些方法以参数为例:

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).

在这种情况下,SQL查询字符串对于两个数据库也是相同的,但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.

如果有人能就如何避免重复代码并优化给定问题提出任何建议,我将非常感激。

2 个解决方案

#1

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

您可以使用数据库独立接口IDbDataAdapter和IDbCommand,然后使用工厂创建具体实例。这是一个例子。

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.

但是,如果您的应用程序不是很简单,我会建议使用像NHibernate这样的ORM解决方案,因为Access和SQL Server之间的SQL语言几乎没有什么区别可以使您的数据访问代码更复杂。

#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:

您应该查看Enterprise Library数据访问位。底层数据库提供程序的细节将被抽象出来。例如:

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.

上面的两个示例都适用于Access和MS SQL,并且您可以坚持使用与Jet和MS SQL兼容的SQL语句,您不应该遇到任何问题。

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

当您从Access切换到MS SQL时,只需更改连接字符串,例如:

<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:

要实现这一点,您只需要在EL发行版中引用以下DLL:

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

Well worth a look.

非常值得一看。

Cheers
Kev