如何将数据导入到 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 数据库中》一文后，有一位读者提出这样的疑问：示例程序是否能够在 PPC 上跑，直接从远程 PC 上的 SQL Server 数据库读取数据，导入到 PPC 上的 SQL Server CE 数据库中？

事实上是可以的！！！

.NET Compact Framework 支持智能设备应用程序直接访问远程的 SQL Server 数据库，命名空间还是原来的  System.Data.SqlClient。不过，System.Data.SqlClient 命名空间下的类并不在  System.Data.dll 程序集中，而是封装在一个独立的程序集 System.Data.SqlClient.dll 中。这个程序集和它的安装包可以在“C:/Program Files/Microsoft Visual Studio 8/SmartDevices/SDK/SQL Server/Client/v2.0”目录下找到。注意，这个程序集需要独立安装，它并不包含在 .NET Compact Framework 的安装包中。



为了证实这一点，我新建了一个叫 CopyTableMobileDemo 的 Visual C# 2005 智能设备项目，添加对 System.Data.SqlServerCe.dll 和 System.Data.SqlClient.dll 的引用，并从原来的示例程序复制了主要代码过来。



接下来有代码几个地方需要稍微修改一下。

1) SQL Server 数据库连接不能使用 Integrated Security=True，要使用 User Id 和 Password。Data Source 要设置成 SQL Server 服务器的 IP 地址或者计算机名称。设置成 IP 地址还是计算机名称是有区别的。如果你的 Windows Mobile 设备或仿真器是通过 ActiveSync 连接到 PC 的，那么请注意 ActiveSync 的连接设置那里，“这台计算机已连接到”选项如果选择的是单位网络，那么请使用计算机名访问，如果选择了 Internet 则使用 IP 地址访问。




// 创建源 SQL Server 数据库连接对象
string srcConnString = "Data Source=bjb-libo;Initial Catalog=Northwind;User Id=sa;Password=1234;";
SqlConnection srcConnection = new SqlConnection(srcConnString);
2) SQL Server CE 数据库连接要换成智能设备的文件路径，如果 Data Source 只设置文件名，那么示例程序将会在设备的根目录下创建 SQL Server CE 数据库文件。
// 创建目标 SQL Server Compact Edition 数据库连接对象
string destConnString = "Data Source=Northwind.sdf";
SqlCeConnection destConnection = new SqlCeConnection(destConnString);
3) 由于 .NET Compact Framework 只支持 string[] string.Split(params char[] separator)，那么首先 SQL Server CE 数据库创建脚本中每条命令的分隔符 GO 需要替换成分号(';')。
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
);

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
);
4) 创建 SQL Server CE 数据库的方法也需要相应改动。
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(';');

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

其他的地方都不需要修改，让我们看看运行的效果：




总结：在 Windows Mobile 上直接将远程 SQL Server 的数据导入到 SQL Server CE 中是可行的，并且利用 CopyTable 方法可以很轻松得实现多个表的数据导入。不过从智能设备直接访问 SQL Server 数据库存在一定的局限性，它比较适合局域网环境，并且需要开放 SQL Server 的端口。另外，我还没有测试过大数据量的导入，我担心会存在内存不足的问题。大家可以测试一下！

示例代码下载：sqlce_data_import2.rar