如何将数据导入到 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 语句的主要代码:
///生成创建数据库表结构的SQL语句。
///</summary>
privatestaticstringGenerateTableSchemaSql(IDbConnectionconnection,stringqueryString)
{
StringBuildertableSql=newStringBuilder();
IDbCommandcommand=connection.CreateCommand();
command.CommandText=queryString;
try
{
/*获取查询结果各列的元数据*/
DataTableschemaTable=null;
using(IDataReaderreader=command.ExecuteReader(CommandBehavior.KeyInfo))
{
schemaTable=reader.GetSchemaTable();
}
/*生成创建表定义语句*/
stringtableName=schemaTable.Rows[0]["BaseTableName"].ToString();
tableSql.Append("CREATETABLE[").Append(tableName).AppendLine("](");
/*生成各列的定义语句*/
stringcolumnName;
stringallowDBNull;
DataRowrow;
boolhasKey=false;
StringBuildersbPKFields=newStringBuilder();
for(inti=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":"NOTNULL");
if((bool)row["IsKey"])
{
sbPKFields.AppendFormat("[{0}],",columnName);
hasKey=true;
}
tableSql.AppendFormat("[{0}]{1}{2}",columnName,GetSqlCeDataType(row),allowDBNull);
}
/*生成主键约束语句*/
if(hasKey)
{
stringpkFields=sbPKFields.ToString().TrimEnd(',');
tableSql.AppendLine(",");
tableSql.Append("CONSTRAINTPK_").Append(tableName.Replace(" ", "_")).Append("PRIMARYKEY(").Append(pkFields).AppendLine(")");
}
tableSql.AppendLine(");");
}
catch(Exceptionex)
{
Debug.WriteLine(ex);
}
returntableSql.ToString();
}
同样的,该方法也使用了 ADO.NET 的接口类,不依赖于具体的数据库类型。该方法的核心就是通过 IDataReader.GetSchemaTable 方法获取查询结果各列元数据,相关代码如下:
command.CommandText=queryString;
DataTableschemaTable=null;
using(IDataReaderreader=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数据类型获取对应的SQLServerCE类型名称。
///</summary>
privatestaticstringGetSqlCeNativeType(TypesystemType)
{
stringtypeName=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:
thrownewApplicationException(string.Format("找不到{0}类型对应的SQLServerCE数据类型。",typeName));
}
}
当然,仅仅知道列的数据类型还不够,我们需要为某些列的数据类型加上长度、精度或小数位数等列大小信息。可以通过下面的方法实现:
///从ColumnSchemaRow获取SQLServerCE数据类型。
///</summary>
privatestaticstringGetSqlCeDataType(DataRowcolumnSchemaRow)
{
Typetype=columnSchemaRow["DataType"]asType;
stringdataType=GetSqlCeNativeType(type);
switch(dataType)
{
case"numeric":
Int16precision=(Int16)columnSchemaRow["NumericPrecision"];
Int16scale=(Int16)columnSchemaRow["NumericScale"];
Int32colsize=(Int32)columnSchemaRow["ColumnSize"];
if(precision!=0&&scale!=0&&scale!=255)
{
dataType=string.Format("{0}({1},{2})",dataType,precision,scale);
}
elseif(scale==255&&colsize==8)
{
dataType="money";
}
break;
case"nvarchar":
intcolumnSize=(int)columnSchemaRow["ColumnSize"];
if(columnSize>4000)
{
dataType="ntext";
}
else
{
dataType=string.Format("{0}({1})",dataType,columnSize);
}
break;
}
returndataType;
}
关于 SQL Server 2005 Compact Edition 数据类型的描述,详细请参考联机丛书。使用上面的几段代码,对 SQL Server 2000 自带的 Northwind 数据库的 Customers 表生成创建数据库表的 SQL 语句,生成结果如下:
[CustomerID]nvarchar(5)NOTNULL,
[CompanyName]nvarchar(40)NOTNULL,
[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,
CONSTRAINTPK_CustomersPRIMARYKEY([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 数据库所有用户表名的数组:
///从一个打开的SQLServer数据库连接获取数据库的表名数组。
///</summary>
privatestaticstring[]GetTableNames(IDbConnectionconnection)
{
IDbCommandcommand=connection.CreateCommand();
//从SQLServer信息架构视图获取Northwind数据库所有表的名称
command.CommandText=@"SELECT*FROMINFORMATION_SCHEMA.TABLES
WHERETABLE_TYPE='BASETABLE'ANDTABLE_CATALOG='Northwind'";
List<string>tableNames=newList<string>();
using(IDataReaderreader=command.ExecuteReader())
{
while(reader.Read())
{
tableNames.Add(reader["TABLE_NAME"].ToString());
}
}
returntableNames.ToArray();
}
有了 GetTableNames 方法,我们就可以一次性对 Northwind 数据库的所有用户表生成相应的创建 SQL Server CE 数据库表结构的 SQL 语句。
{
stringconnectionString="DataSource=(local);InitialCatalog=Northwind;IntegratedSecurity=True";
IDbConnectionconnection=newSqlConnection(connectionString);
connection.Open();
string[]tableNames=GetTableNames(connection);
stringqueryString,createTableSql;
foreach(stringtableNameintableNames)
{
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日