如何将数据导入到 SQL Server Compact Edition 数据库中

一、场景描述
在智能设备（Smart Device）应用程序和智能客户端（Smart Client）应用程序的部署阶段，我们需要对离线数据进行初始化，即将后台数据库服务器中的一些数据，导入到离线数据库中。通常采用两种方式对离线数据进行初始化，第一种是在程序第一次运行时，通过数据同步的方式，把数据从后台下载下来；第二种是将预先准备好的离线数据随应用程序一起部署。

对于 SQL Server Compact Edition (SQL CE 3.1) 数据库，第一种方式通常可以利用 Remote Data Access (RDA), Merge Replication, Sync Services for ADO.NET (SQL CE 3.5 中新增) 或者自己实现基于 Web Service 的数据同步机制来实现。RDA 和 Merge Replication 最大的缺点是只能连到 SQL Server 数据库，如果 SQL CE 需要跟 Oracle 和 DB2 等数据库进行数据同步，需要 SQL Server 做“中介”。另外，RDA 没有冲突处理机制，并且每次必须重新下载全部数据；Merge Replication 配置太繁琐了。Sync Services for ADO.NET 目前还在 beta 阶段，beta1 还不支持智能设备应用程序，只支持桌面应用程序。Orcas beta2 刚刚发布，目前还没有下载完毕，不知道有没有性能方面的提升和增加对智能设备应用程序的支持。暂时先对 Sync Services for ADO.NET 保留意见，等我用上 beta2 了再详细介绍。自己实现基于 Web Service 的数据同步机制需要考虑大数据量如何分批次传输和性能问题。总的来说，第一种方式的实现途径很多，如果初始化数据量比较大，并且客户端数量比较多的话，那么将有可能带来漫长的部署过程和一笔巨大的无线网络流量的费用。

第二种方式可以利用 SQL CE 3.1 对桌面应用程序的支持，预先将 SQL Server, Oracle, DB2, MySQL 等等各种数据库的数据导入到 SQL CE 中，然后通过 ActiveSync 批量将 SQL CE 的数据库文件(*.sdf)拷贝到设备或机器上。以后客户端再通过 Web Service 的方式下载新增/修改/删除的数据来更新本地的离线数据。这样可以节约大量的部署时间和网络通信成本。

当然并不是第二种方式一定比第一种方式好，这个看具体的实施环境。本文主要介绍的是第二种方式。

二、技术选择

既然 SQL CE 3.1 支持桌面应用程序，那么我们可以通过三种方式来准备离线数据：第一，利用 RDA 数据同步；第二，利用 Merge Replication 数据同步；第三，用 ADO.NET 直接从读写数据。第一和第二种方式需要额外的安装和配置，而且只支持 SQL Server 数据库。如果非要在第一和第二种方式中选择的话，我会选择 RDA，因为它配置工作量更少，性能更好，更加灵活。本文选择第三种方式，因为它离两个数据库的距离最近，而且支持多种数据库。

三、实现原理

数据导入程序实现起来很简单，不过需要考虑性能。从源数据库读取数据要考虑速度和内存冲击，可以采用 DataSet 或者 DataReader，毫无疑问我们选择 DataReader。将数据写入 SQL CE，通常大家会想到编写一个 SqlCeCommand，然后给 SqlCeCommand 的 CommandText 属性赋上 Insert SQL 语句“insert into Products values(@ProductID, @ProductName)”，接着一边读取数据，一边给参数赋值并写入 SQL CE 数据库中……大家冷落了一个叫 SqlCeResultSet 的对象，它是 SQL Mobile 增加的数据访问对象。SqlCeResultSet 提供了一个功能的组合：DataSet 的可更新性和可滚动性以及与 SqlCeDataReader 类似的性能。SqlCeResultSet 类继承了 SqlCeDataReader 类，因此它拥有 SqlCeDataReader 类所有的特性。利用 SqlCeResultSet 可以实现高性能的数据读取和写入。

四、代码和分析

/// <summary>
/// 将源数据库表的数据复制到 SQL Server Compact Edition 数据库的表中。
/// </summary>
/// <param name="srcConnection">源数据库连接接对象。</param>
/// <param name="destConnection">目标 SQL Server Compact Edition 数据库连接对象。</param>
/// <param name="queryString">源数据的查询语句。</param>
/// <param name="destTableName">目标数据库表名称。</param>
/// <remarks>本方法假设目标 SQL Server Compact Edition 数据库的表已经存在。</remarks>
public static void CopyTable(
    IDbConnection srcConnection, 
    SqlCeConnection destConnection, 
    string queryString, 
    string destTableName)
{
    IDbCommand srcCommand = srcConnection.CreateCommand();
    srcCommand.CommandText = queryString;

    SqlCeCommand destCommand = destConnection.CreateCommand();
    destCommand.CommandType = CommandType.TableDirect; //基于表的访问，性能更好
    destCommand.CommandText = destTableName;
    try
    {
        IDataReader srcReader = srcCommand.ExecuteReader();

        SqlCeResultSet resultSet = destCommand.ExecuteResultSet(
            ResultSetOptions.Sensitive |   //检测对数据源所做的更改
            ResultSetOptions.Scrollable |  //可以向前或向后滚动
            ResultSetOptions.Updatable); //允许更新数据

        object[] values;
        SqlCeUpdatableRecord record;
        while (srcReader.Read())
        {
            // 从源数据库表读取记录
            values = new object[srcReader.FieldCount];
            srcReader.GetValues(values);

            // 把记录写入到目标数据库表
            record = resultSet.CreateRecord();
            record.SetValues(values);
            resultSet.Insert(record);
        }

        srcReader.Close();
        resultSet.Close();
    }
    catch (Exception ex)
    {
        System.Diagnostics.Debug.WriteLine(ex.ToString());
    }
}

由于 CopyTable 函数的源数据库连接参数采用的是 IDbConnection 接口，所以该方法可以支持多种源数据库。代码中还利用 IDataReader.GetValues(object[] values) 和 SqlCeUpdatableRecord.SetValues(object[] values) 更方便的读取和写入数据。

在使用 CopyTable 函数之前必须预先创建好 SQL CE 数据库的表结构，并且 SQL CE 数据库的表结构必须跟 queryString 参数（select SQL 语句）的查询结果的表结构对应。

通过下面的代码使用 CopyTable 函数：

// 创建源 SQL Server 数据库连接对象
string srcConnString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
SqlConnection srcConnection = new SqlConnection(srcConnString);

// 创建目标 SQL Server Compact Edition 数据库连接对象
string destConnString = @"Data Source=C:/Northwind.sdf";
SqlCeConnection destConnection = new SqlCeConnection(destConnString);

VerifyDatabaseExists(destConnString); //创建数据库结构

srcConnection.Open();
destConnection.Open();

// 复制数据
CopyTable(srcConnection, destConnection, "SELECT * FROM Products", "Products");
CopyTable(srcConnection, destConnection, "SELECT * FROM Employees", "Employees");

srcConnection.Close();
destConnection.Close();

五、创建数据库结构

上面说到在使用 CopyTable 函数之前 SQL CE 数据库必须存在并且表结构都创建好。我们现在就来编写创建数据库结构的代码。首先创建一个名为 DbSchema.sql 的文件，并编写 Northwind 数据库中的 Products 和 Employees 表的创建脚本：

CREATE TABLE Products(
    ProductID int NOT NULL CONSTRAINT PK_Products PRIMARY KEY,
    ProductName nvarchar(40) NOT NULL,
    SupplierID int NULL,
    CategoryID int NULL,
    QuantityPerUnit nvarchar(20) NULL,
    UnitPrice money NULL,
    UnitsInStock smallint NULL,
    UnitsOnOrder smallint NULL,
    ReorderLevel smallint NULL,
    Discontinued bit NOT NULL
)
GO
CREATE TABLE Employees(
    EmployeeID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY,
    LastName nvarchar(20) NOT NULL,
    FirstName nvarchar(10) NOT NULL,
    Title nvarchar(30) NULL,
    TitleOfCourtesy nvarchar(25) NULL,
    BirthDate datetime NULL,
    HireDate datetime NULL,
    Address nvarchar(60) NULL,
    City nvarchar(15) NULL,
    Region nvarchar(15) NULL,
    PostalCode nvarchar(10) NULL,
    Country nvarchar(15) NULL,
    HomePhone nvarchar(24) NULL,
    Extension nvarchar(4) NULL,
    Photo image NULL,
    Notes ntext NULL,
    ReportsTo int NULL,
    PhotoPath nvarchar(255) NULL
)
GO

这段 SQL 语句不能直接在 SQL CE 上执行的，我们需要进行一些字符串的处理。现在将该文件添加到 Visual Studio 2005 的项目资源中。





并添加执行这段 SQL 创建数据库表结构的方法：

public static void VerifyDatabaseExists(string connectionString)
{
    using (SqlCeConnection connection = new SqlCeConnection(connectionString))
    {
        if (! File.Exists(connection.Database))
        {
            using (SqlCeEngine engine = new SqlCeEngine(connection.ConnectionString))
            {
                engine.CreateDatabase();

                string[] commands = Properties.Resources.DbSchema.Split(
                    new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);

                SqlCeCommand command = new SqlCeCommand();
                command.Connection = connection;
                connection.Open();
                for (int i = 0; i < commands.Length; i++)
                {
                    command.CommandText = commands[i];
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}

六、总结

性能测试的结果会因为环境的不同而有一些出入，我想留给大家去做会更有意义。不过我相信这是当前性能比较好的向 SQL CE 导入数据的方法之一。目前需要预先创建好 SQL CE 的表结构是美中不足的地方，我会在后续文章中实现一个自动根据查询结果生成创建 SQL CE 表结构的 SQL 语句的代码，其实并不难。

参考：
ADO.NET Generic Copy Table Data Function
Creating your SQL Server Compact Edition database and schema in code

示例代码下载：sqlce_data_import.rar

原文出处：如何将数据导入到 SQL Server Compact Edition 数据库中（一）
作者：黎波
日期：2007年7月29日