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

izuoyan

浏览: 9219460 次
性别:
来自: 上海

最近访客更多访客>>

GDGZWQZ

morelily

eternal1025

devcang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2012-06 ( 32)
2012-05 ( 145)
2012-04 ( 50)
更多存档...

SQL Server SQL 数据结构 VB.NET .net

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

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

///<summary>
///生成创建数据库表结构的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 方法获取查询结果各列元数据，相关代码如下：

IDbCommandcommand=connection.CreateCommand();
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 数据类型。这个方法的代码如下所示：

///<summary>
///从.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));
}
}

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

///<summary>
///从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 语句，生成结果如下：

CREATETABLE[Customers](
[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 数据库所有用户表名的数组：

///<summary>
///从一个打开的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 语句。

staticvoidMain(string[]args)
{
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日

分享到：