系列文章导航: 如何将数据导入到 SQL Server Compact Edition 数据库中(一) 如何将数据导入到 SQL Server Compact Edition 数据库中(二) 摘要:时隔近半年了,不知道大家是否还记得,我在本系列的第一篇文章的总结中提到,创建 SQL Server CE 数据库表
系列文章导航:
如何将数据导入到 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 语句。///privatestaticstringGenerateTableSchemaSql(IDbConnection connection,stringqueryString)
{
StringBuilder tableSql=newStringBuilder();
IDbCommand command=connection.CreateCommand();
command.CommandText=queryString;try{/*获取查询结果各列的元数据*/DataTable schemaTable=null;using(IDataReader reader=command.ExecuteReader(CommandBehavior.KeyInfo))
{
schemaTable=reader.GetSchemaTable();
}/*生成创建表定义语句*/stringtableName=schemaTable.Rows[0]["BaseTableName"].ToString();
tableSql.Append("CREATE TABLE [").Append(tableName).AppendLine("] (");/*生成各列的定义语句*/stringcolumnName;stringallowDBNull;
DataRow row;boolhasKey=false;
StringBuilder sbPKFields=newStringBuilder();for(inti=0; 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)
{stringpkFields=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);
}returntableSql.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 数据类型。这个方法的代码如下所示:
//从 .NET 数据类型获取对应的 SQL Server CE 类型名称。///privatestaticstringGetSqlCeNativeType(Type systemType)
{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} 类型对应的 SQL Server CE 数据类型。", typeName));
}
}
当然,仅仅知道列的数据类型还不够,我们需要为某些列的数据类型加上长度、精度或小数位数等列大小信息。可以通过下面的方法实现:
//从 ColumnSchemaRow 获取 SQL Server CE 数据类型。///privatestaticstringGetSqlCeDataType(DataRow columnSchemaRow)
{
Type type=columnSchemaRow["DataType"]asType;stringdataType=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);
}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 数据库所有用户表名的数组:
//从一个打开的 SQL Server 数据库连接获取数据库的表名数组。///privatestaticstring[] 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'";
ListtableNames=newList();using(IDataReader reader=command.ExecuteReader())
{while(reader.Read())
{
tableNames.Add(reader["TABLE_NAME"].ToString());
}
}returntableNames.ToArray();
}
有了 GetTableNames 方法,我们就可以一次性对 Northwind 数据库的所有用户表生成相应的创建 SQL Server CE 数据库表结构的 SQL 语句。
staticvoidMain(string[] args)
{stringconnectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
IDbConnection connection=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日
本文原创发布php中文网,转载请注明出处,感谢您的尊重!