如何将数据导入到 SQL Server Compact Edition 数据库中(四)
系列文章导航: 如何将数据导入到 SQL Server Compact Edition 数据库中(一) 如何将数据导入到 SQL Server Compact Edition 数据库中(二) 如何将数据导入到 SQL Server Compact Edition 数据库中(三) 摘要:在本系列文章的第一篇和第二篇为了提高数据写
系列文章导航:如何将数据导入到 SQL Server Compact Edition 数据库中(一)
如何将数据导入到 SQL Server Compact Edition 数据库中(二)
如何将数据导入到 SQL Server Compact Edition 数据库中(三)
摘要:在本系列文章的第一篇和第二篇为了提高数据写入的性能,我使用了 SqlCeResultSet 基于表的数据写入方式,而不是使用常规的 Insert 语句。使用 SqlCeResultSet 写入数据确实方便又快速,但是必须保证从源数据库查询的结果集(通过 Select 查询语句)跟目标数据库(SQL Server Compact Edition)表的字段先后顺序一致。如果不一致,可能会导致数据导入出错;即便是导入成功,数据跟原来的字段位置也对不上。所以,我觉得有必要给大家介绍常规的 Insert 语句数据插入方式,解决 SqlCeResultSet 存在的问题。
在第三篇文章中,我们学习了 IDataReader.GetSchemaTable 方法,它可以返回一个描述了 DataReader 查询结果中各列的元数据的 DataTable。在前面的文章介绍的数据导入方法中,都是使用 DataReader 从源数据库读取数据。那么从这个 DataReader 获取的 SchemaTable 信息,就可以用于生成插入数据的 Insert 语句,前提是源数据库和目标数据库的表字段名称一致,字段的先后顺序可以不一样。以下是根据 SchemaTable 生成 Insert 语句的代码:
// 通过 DataReader 获取 SchemaTable 信息
srcReader = srcCommand.ExecuteReader(CommandBehavior.KeyInfo);
DataTable scheamTable = srcReader.GetSchemaTable();
// 生成 SQL Server Compact Edition 数据插入 SQL 语句
StringBuilder sbFields = new StringBuilder();
StringBuilder sbParams = new StringBuilder();
string field, param;
DataRow schemaRow;
for (int i = 0; i scheamTable.Rows.Count; i++)
{
if (i != 0)
{
sbFields.Append(", ");
sbParams.Append(", ");
}
schemaRow = scheamTable.Rows[i];
field = string.Format("[{0}]", schemaRow["ColumnName"]); //字段名称
param = "@" + ((string)schemaRow["ColumnName"]).Replace(" ", "_"); //参数名称
sbFields.Append(field);
sbParams.Append(param);
destCommand.Parameters.Add(param, null);
}
string insertSql = string.Format("INSERT INTO [{0}]({1}) VALUES({2})", destTableName, sbFields, sbParams);
destCommand.CommandText = insertSql;
生成 Insert 语句的代码很简单,这里就不再详细说明了。准备好了 Insert 语句,就可以开始从源数据库取数据并写入目标数据库了。这里我使用了 DataReader.GetValues 方法一次性读取一整行数据,再给 Insert 命令的参数赋值。代码如下所示:
// 执行数据导入
object[] values;
while (srcReader.Read())
{
values = new object[srcReader.FieldCount];
srcReader.GetValues(values);
for (int i = 0; i values.Length; i++)
{
destCommand.Parameters[i].Value = values[i];
}
destCommand.ExecuteNonQuery();
}
本文的主要内容到这里已经介绍完了,我们可以结合第三篇文章介绍的根据 SchemaTable 自动生成创建表结构的 SQL 语句的代码,在导入数据前先自动创建数据表结构。相关的代码如下:
// 通过 DataReader 获取 SchemaTable 信息
srcReader = srcCommand.ExecuteReader(CommandBehavior.KeyInfo);
DataTable scheamTable = srcReader.GetSchemaTable();
// 创建 SQL Server Compact Edition 表结构
SqlCeCommand command = destConnection.CreateCommand();
command.CommandText = GenerateTableSchemaSql(scheamTable);
command.ExecuteNonQuery();
// 生成 SQL Server Compact Edition 数据插入 SQL 语句
StringBuilder sbFields = new StringBuilder();
StringBuilder sbParams = new StringBuilder();
......
通过遍历 SQL Server 2000 的 Northwind 数据库的每个用户表,并将每个表的数据导入到一个 SQL Server Compact Edition 数据文件 Northwind.sdf 中。代码如下所示:
// 创建源 SQL Server 数据库连接对象
string srcConnString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
SqlConnection srcConnection = new SqlConnection(srcConnString);
// 创建目标 SQL Server Compact Edition 数据库连接对象
string destConnString = @"Data Source=C:/Northwind.sdf";
SqlCeConnection destConnection = new SqlCeConnection(destConnString);
// 创建 SQL Server Compact Edition 数据文件
VerifyDatabaseExists(destConnString);
srcConnection.Open();
destConnection.Open();
// 复制数据
string[] tableNames = GetTableNames(srcConnection);
string query;
for (int i = 0; i tableNames.Length; i++)
{
query = string.Format("SELECT * FROM [{0}]", tableNames[i]);
CopyTable(srcConnection, destConnection, query, tableNames[i]);
}
srcConnection.Close();
destConnection.Close();
同第二篇文章相比,本文中的 VerifyDatabaseExists 方法只创建 SQL Server Compact Edition 数据文件,不批量创建表结构,因为我们用上了“自动”的方法,不需要预先准备好创建表结构的 SQL 脚本。GetTableNames 和 GenerateTableSchemaSql 方法跟第三篇文章的一样,这里不再解释。
总结:本文介绍了一种比 SqlCeResultSet 更安全的数据写入方式,并结合了第三篇文章中介绍的自动生成创建数据库表结构的 SQL 语句的方法,向大家展示了一种比较完善的 SQL Server Compact Edition 数据导入方法。在后续的文章中我会继续深入下去,提供本方案在实际应用中面临的问题的解决方法。
示例代码下载:sqlce_data_import4.rar
作者:黎波
博客:http://upto.cnblogs.com/
日期:2008年2月9日

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics











HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).

0.What does this article do? We propose DepthFM: a versatile and fast state-of-the-art generative monocular depth estimation model. In addition to traditional depth estimation tasks, DepthFM also demonstrates state-of-the-art capabilities in downstream tasks such as depth inpainting. DepthFM is efficient and can synthesize depth maps within a few inference steps. Let’s read about this work together ~ 1. Paper information title: DepthFM: FastMonocularDepthEstimationwithFlowMatching Author: MingGui, JohannesS.Fischer, UlrichPrestel, PingchuanMa, Dmytr

The performance of JAX, promoted by Google, has surpassed that of Pytorch and TensorFlow in recent benchmark tests, ranking first in 7 indicators. And the test was not done on the TPU with the best JAX performance. Although among developers, Pytorch is still more popular than Tensorflow. But in the future, perhaps more large models will be trained and run based on the JAX platform. Models Recently, the Keras team benchmarked three backends (TensorFlow, JAX, PyTorch) with the native PyTorch implementation and Keras2 with TensorFlow. First, they select a set of mainstream

Fujifilm fans were recently very excited at the prospect of the X-T50, since it presented a relaunch of the budget-oriented Fujifilm X-T30 II that had become quite popular in the sub-$1,000 APS-C category. Unfortunately, as the Fujifilm X-T50's launc

Facing lag, slow mobile data connection on iPhone? Typically, the strength of cellular internet on your phone depends on several factors such as region, cellular network type, roaming type, etc. There are some things you can do to get a faster, more reliable cellular Internet connection. Fix 1 – Force Restart iPhone Sometimes, force restarting your device just resets a lot of things, including the cellular connection. Step 1 – Just press the volume up key once and release. Next, press the Volume Down key and release it again. Step 2 – The next part of the process is to hold the button on the right side. Let the iPhone finish restarting. Enable cellular data and check network speed. Check again Fix 2 – Change data mode While 5G offers better network speeds, it works better when the signal is weaker

The Fujifilm X-M5 has shown itself in a handful of rumours that suggested that the compact APS-C camera would launch as an affordable alternative to the X100VI sometime in late 2024. Now, a new rumour out of Fujirumours reveals Fujifilm's film simula

The latest video of Tesla's robot Optimus is released, and it can already work in the factory. At normal speed, it sorts batteries (Tesla's 4680 batteries) like this: The official also released what it looks like at 20x speed - on a small "workstation", picking and picking and picking: This time it is released One of the highlights of the video is that Optimus completes this work in the factory, completely autonomously, without human intervention throughout the process. And from the perspective of Optimus, it can also pick up and place the crooked battery, focusing on automatic error correction: Regarding Optimus's hand, NVIDIA scientist Jim Fan gave a high evaluation: Optimus's hand is the world's five-fingered robot. One of the most dexterous. Its hands are not only tactile

New SOTA for multimodal document understanding capabilities! Alibaba's mPLUG team released the latest open source work mPLUG-DocOwl1.5, which proposed a series of solutions to address the four major challenges of high-resolution image text recognition, general document structure understanding, instruction following, and introduction of external knowledge. Without further ado, let’s look at the effects first. One-click recognition and conversion of charts with complex structures into Markdown format: Charts of different styles are available: More detailed text recognition and positioning can also be easily handled: Detailed explanations of document understanding can also be given: You know, "Document Understanding" is currently An important scenario for the implementation of large language models. There are many products on the market to assist document reading. Some of them mainly use OCR systems for text recognition and cooperate with LLM for text processing.
