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

系列文章导航：
如何将数据导入到 SQL Server Compact Edition 数据库中（一）
如何将数据导入到 SQL Server Compact Edition 数据库中（二）

摘要：时隔近半年了，不知道大家是否还记得，我在本系列的第一篇文章的总结中提到，创建 SQL Server CE 数据库表结构的 SQL 语句是可以自动生成的。那么本系列的第三篇文章就向大家介绍一种比较简单的方法。

ADO.NET 中的 IDataReader.GetSchemaTable 方法可以返回一个 DataTable，它描述了 IDataReader 查询结果中各列的元数据。列的元数据包含了列的名称、数据类型、大小、是否为主键字段、是否为自动增长字段……等等。有了这些元数据，我们就可以通过编写几段 C#/VB.NET 代码，实现创建 SQL Server CE 数据库表结构的 SQL 语句的自动生成。以下方法是生成创建表 SQL 语句的主要代码：

/// <summary>
/// 生成创建数据库表结构的 SQL 语句。
/// </summary>
private static string GenerateTableSchemaSql(IDbConnection connection, string queryString)
{
    StringBuilder tableSql = new StringBuilder(); 

    IDbCommand command = connection.CreateCommand();
    command.CommandText = queryString; 

    try
    {
        /* 获取查询结果各列的元数据 */
        DataTable schemaTable = null;
        using (IDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo))
        {
            schemaTable = reader.GetSchemaTable();
        } 

        /* 生成创建表定义语句 */
        string tableName = schemaTable.Rows[0]["BaseTableName"].ToString();
        tableSql.Append("CREATE TABLE [").Append(tableName).AppendLine("] ("); 

        /* 生成各列的定义语句 */
        string columnName;
        string allowDBNull;
        DataRow row;
        bool hasKey = false;
        StringBuilder sbPKFields = new StringBuilder();
        for (int i = 0; i < schemaTable.Rows.Count; i++)
        {
            if (i != 0) tableSql.AppendLine(","); 

            row = schemaTable.Rows[i];
            columnName = (string)row["ColumnName"];
            allowDBNull = ((bool)row["AllowDBNull"] == true ? "NULL" : "NOT NULL"); 

            if ((bool)row["IsKey"])
            {
                sbPKFields.AppendFormat("[{0}],", columnName);
                hasKey = true;
            }
            tableSql.AppendFormat("  [{0}] {1} {2}", columnName, GetSqlCeDataType(row), allowDBNull);
        } 

        /* 生成主键约束语句 */
        if (hasKey)
        {
            string pkFields = sbPKFields.ToString().TrimEnd(',');
            tableSql.AppendLine(",");
            tableSql.Append("  CONSTRAINT PK_").Append(tableName.Replace(" ", "_")).Append(" PRIMARY KEY(").Append(pkFields).AppendLine(")");
        }
        tableSql.AppendLine(");");
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex);
    } 

    return tableSql.ToString();
}

同样的，该方法也使用了 ADO.NET 的接口类，不依赖于具体的数据库类型。该方法的核心就是通过 IDataReader.GetSchemaTable 方法获取查询结果各列元数据，相关代码如下：

IDbCommand command = connection.CreateCommand();
command.CommandText = queryString;
DataTable schemaTable = null;
using (IDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo))
{
    schemaTable = reader.GetSchemaTable();
}

首先，IDbCommand 的 CommandText 属性一般是针对一个表的 SELECT 查询语句，如：SELECT * FROM Customers。其次，IDbCommand.ExecuteReader 方法必须传入 CommandBehavior.KeyInfo 参数，这样才能获取到列的主键元数据。最后，通过 IDataReader.GetSchemaTable 方法返回一个包含查询结果所有列的元数据的 DataTable。关于 IDataReader.GetSchemaTable 方法的详细使用说明，请阅读《HOW TO：使用 DataReader GetSchemaTable 方法和 Visual C# .NET 检索列架构》。

IDataReader.GetSchemaTable 返回的 SchemaTable 对列数据类型的描述是用相应的 .NET 数据类型，如 SQL Server CE 的 int 类型对应的是 .NET 的 System.Int32 类型。另外需要注意的是，由于 Windows Mobile 只支持 Unicode 编码，因此 SQL Server CE 只支持 NChar, NVarChar 和 NText 等 Unicode 字符数据类型，而不支持 Char, VarChar 和 Text 等非 Unicode 字符数据类型。所以，我们需要编写一个方法，它根据列的 .NET 数据类型找到对应的 SQL Server CE 数据类型。这个方法的代码如下所示：

/// <summary>
/// 从 .NET 数据类型获取对应的 SQL Server CE  类型名称。
/// </summary>
private static string GetSqlCeNativeType(Type systemType)
{
    string typeName = systemType.ToString();
    switch (typeName)
    {
        case "System.Boolean":
            return "bit";
        case "System.Byte":
            return "tinyint";
        case "System.Byte[]":
            return "image";
        case "System.DateTime":
            return "datetime";
        case "System.Decimal":
            return "numeric";
        case "System.Double":
            return "float";
        case "System.Guid":
            return "uniqueidentifier";
        case "System.Int16":
            return "smallint";
        case "System.Int32":
            return "integer";
        case "System.Int64":
            return "bigint";
        case "System.Single":
            return "real";
        case "System.String":
            return "nvarchar";
        default:
            throw new ApplicationException(string.Format("找不到 {0} 类型对应的 SQL Server CE 数据类型。", typeName));
    }
}

当然，仅仅知道列的数据类型还不够，我们需要为某些列的数据类型加上长度、精度或小数位数等列大小信息。可以通过下面的方法实现：

/// <summary>
/// 从 ColumnSchemaRow 获取 SQL Server CE 数据类型。
/// </summary>
private static string GetSqlCeDataType(DataRow columnSchemaRow)
{
    Type type = columnSchemaRow["DataType"] as Type;
    string dataType = GetSqlCeNativeType(type);
    switch (dataType)
    {
        case "numeric":
            Int16 precision = (Int16)columnSchemaRow["NumericPrecision"];
            Int16 scale = (Int16)columnSchemaRow["NumericScale"];
            Int32 colsize = (Int32)columnSchemaRow["ColumnSize"];
            if (precision != 0 && scale != 0 && scale != 255)
            {
                dataType = string.Format("{0}({1},{2})", dataType, precision, scale);
            }
            else if (scale == 255 && colsize == 8)
            {
                dataType = "money";
            }
            break;
        case "nvarchar":
            int columnSize = (int)columnSchemaRow["ColumnSize"];
            if (columnSize > 4000)
            {
                dataType = "ntext";
            }
            else
            {
                dataType = string.Format("{0}({1})", dataType, columnSize);
            }
            break;
    }
    return dataType;
}

关于 SQL Server 2005 Compact Edition 数据类型的描述，详细请参考联机丛书。使用上面的几段代码，对 SQL Server 2000 自带的 Northwind 数据库的 Customers 表生成创建数据库表的 SQL 语句，生成结果如下：

CREATE TABLE [Customers] (
  [CustomerID] nvarchar(5) NOT NULL,
  [CompanyName] nvarchar(40) NOT NULL,
  [ContactName] nvarchar(30) NULL,
  [ContactTitle] nvarchar(30) NULL,
  [Address] nvarchar(60) NULL,
  [City] nvarchar(15) NULL,
  [Region] nvarchar(15) NULL,
  [PostalCode] nvarchar(10) NULL,
  [Country] nvarchar(15) NULL,
  [Phone] nvarchar(24) NULL,
  [Fax] nvarchar(24) NULL,
  CONSTRAINT PK_Customers PRIMARY KEY([CustomerID])
);

对于 SQL Server 2000，我们可以从信息架构视图查询 INFORMATION_SCHEMA.TABLES 出数据库有哪些表，并一次性对所有表进行生成。以下是 INFORMATION_SCHEMA.TABLES 视图各列的说明：

列名	数据类型	说明
TABLE_CATALOG	nvarchar(128)	表限定符。
TABLE_SCHEMA	nvarchar(128)	包含该表的架构的名称。
TABLE_NAME	sysname	表名。
TABLE_TYPE	varchar(10)	表的类型。可以是 VIEW 或 BASE TABLE。

我们可以通过以下方法获得 Northwind 数据库所有用户表名的数组：

/// <summary>
/// 从一个打开的 SQL Server 数据库连接获取数据库的表名数组。
/// </summary>
private static string[] GetTableNames(IDbConnection connection)
{
    IDbCommand command = connection.CreateCommand(); 

    // 从 SQL Server 信息架构视图获取 Northwind 数据库所有表的名称
    command.CommandText = @"SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE='BASE TABLE' AND TABLE_CATALOG='Northwind'"; 

    List<string> tableNames = new List<string>();
    using (IDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            tableNames.Add(reader["TABLE_NAME"].ToString());
        }
    }
    return tableNames.ToArray();
}

有了 GetTableNames 方法，我们就可以一次性对 Northwind 数据库的所有用户表生成相应的创建 SQL Server CE 数据库表结构的 SQL 语句。

static void Main(string[] args)
{
    string connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
    IDbConnection connection = new SqlConnection(connectionString);
    connection.Open(); 

    string[] tableNames = GetTableNames(connection); 

    string queryString, createTableSql;
    foreach (string tableName in tableNames)
    {
        queryString = string.Format("select * from [{0}]", tableName);
        createTableSql = GenerateTableSchemaSql(connection, queryString); 

        Console.WriteLine(createTableSql);
        Debug.WriteLine(createTableSql);
    } 

    connection.Close();
    Console.Read();
}

示例程序运行效果如下图所示：

总结：阅读完本文，相信你已经了解了如何利用 ADO.NET 的 IDataReader.GetSchemaTable 方法获得服务器端数据库表的元数据，并用于生成对应的创建 SQL Server CE 数据库表的 SQL 语句。本系列文章可能还会有更精彩的续篇，我会将平时积累的关于 SQL Server CE 数据导入的一些经验充实到本系列中。

示例代码下载：sqlce_data_import3.rar

更新记录：
2008-2-9 修正对money数据类型的支持，修正对包含空格的表名的支持。

作者：黎波
博客：http://upto.cnblogs.com/
日期：2008年1月31日