系列文章导航：
如何将数据导入到 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 语句的主要代码：
/// 生成创建数据库表结构的 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 方法获取查询结果各列元数据，相关代码如下：
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 数据类型。这个方法的代码如下所示：
/// 从 .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));
}
}
当然，仅仅知道列的数据类型还不够，我们需要为某些列的数据类型加上长度、精度或小数位数等列大小信息。可以通过下面的方法实现：
/// 从 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 语句，生成结果如下：
[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 数据库所有用户表名的数组：
/// 从一个打开的 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 语句。
{
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();
}
示例程序运行效果如下图所示：
示例代码下载：sqlce_data_import3.rar
更新记录：
2008-2-9 修正对money数据类型的支持，修正对包含空格的表名的支持。
作者：黎波
博客：http://upto.cnblogs.com/
日期：2008年1月31日
