使用 SQLServer Management Objects 和 SqlBulkCopy 将 DataTable 数据复制到 SQLServer 数据库

引言

本文将重点介绍将内存中的 DataTable 传输到 SQL Server 数据库。ADO.NET 提供了许多文档完善的方法来创建 DataTable 并将其持久化回源数据库。但在某些情况下，源数据库仅仅是一个源。问题仍然是：“如果您无意更新数据到源数据库而是更新到另一个数据库，如何将内存中的 DataTable 持久化到数据库？如何创建这个新数据库及其表来反映内存中的 DataTable，并且一旦创建完成，使用什么方法可以将数据移动到该表中？”

在本文中，我将演示一种处理将 DataTable 持久化到不相关数据库的方法。我将在本文中展示，通过实现 SqlServer Management Objects 和 SQLBulkCopy，可以找到一种可能的解决方案。

概述

ADO.NET DataTable 提供了一种断开连接的方式来处理数据库信息，而无需承担打开连接的成本。当 DataTable 加载了源数据后，ADO.NET 库提供了大量处理这些数据的功能。它还允许您通过提供添加列、计算值和重新格式化表布局等方法来转换 DataTable。

然而，当 DataTable 需要保存到新数据库中，并且其架构仅在运行时确定时，问题就出现了。虽然 DataTable 表现得像数据库中的一个表，但它们之间没有直接的关联。因此，DataTable 无法轻松地直接放置或传输到具有完整架构和数据的数据库中。这导致了两个基本问题：

1. 如何在运行时创建反映 DataTable 架构（即列和数据类型）的表或数据库？
2. 创建表后，如何最好地将数据复制到新创建的表中？

注意：此解决方案仅适用于 .NET 2.0 和 Microsoft SQL Server 2005（不确定 2000）作为目标数据库。源数据可以来自任何地方，只要它已被放入 datatable 中。

设置

为此工作所需的基本设置是将源数据检索到一个 dataset 中。源数据可以来自任何可以被 DataReader 或 DataAdapter 读取的源，所有重要的是有一个要传输到数据库的源 DataTable。在此示例中，dataset 包含一个三列的 DataTable，其类型分别为 int、string 和 decimal。虽然我在本文中没有展示任何特定的 DataTable 数据，但数据类型确实带来了一个有趣的问题，但稍后会详细介绍。此外，我还为我的目标数据库设置了一个 SqlServer Express 2005 服务器。

使用 SMO

创建动态数据库和表，其中运行时需要更改架构，这带来了最初没有清晰解决方案的有趣问题。处理此问题的传统方法将涉及某种形式的 SQL 表达式和大量代码。幸运的是，目标数据库是 SqlServer 2005 数据库，.NET 提供了用于通过 SqlServer Management Objects (SMO) 动态管理该数据库的实现库。我承认我仍然对 SMO 了解不多，但它们非常适合解决创建动态变化的运行时数据库环境的问题。我知道 SMO 可用于创建存储过程和处理其他 SQL Server 管理问题，并且它们取代了 DTO。对于本文的目的，它们最大的优点是它们不需要任何 SQL 代码实现。

开始之前，基本导入如下：

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer;

我在此包含它们，因为它们不在 System 命名空间中，并且可能需要一些搜索才能找到。使用 SMO 创建数据库是一个相当直接的过程：

//Set destination connection string
string connectionString = YourConnectionString;
SqlConnection Connection = new SqlConnection(connectionString);

//SMO Server object setup with SQLConnection.
Server server = new Server(new ServerConnection(Connection));

//Create a new SMO Database giving server object and database name
Database db = new Database(server, "TestSMODatabase");
db.Create();

这将创建一个包含服务器信息的连接字符串。Server 代表新数据库“TestSMODatabase”将被创建的 SMO 服务器对象。create 命令在服务器上创建物理数据库。现在 SQL Server 包含一个数据库，其中需要添加一个表：

//Set Database to the newly created database
db = server.Databases["TestSMODatabase"];

//Create a new SMO table
Table TestTable = new Table(db, "TestTable");

此时，数据库服务器仅包含数据库，因为需要调用 create 方法来启动创建过程。此外，该表目前不包含任何架构信息。如上所述，列名和类型需要从 dataset 中派生。这首先涉及创建一个 SMO 表，然后获取列名和数据类型（“TestTable”用作内存中的 DataTable）。

//SMO Column object referring to destination table.
Column tempC = new Column();

//Add the column names and types from the datatable into the new table
//Using the columns name and type property
foreach (DataColumn dc in SourceTable.Columns)
{
	//Create columns from datatable column schema
	tempC = new Column(TestTable, dc.ColumnName);
	tempC.DataType = GetDataType(dc.DataType.ToString());

	TestTable.Columns.Add(tempC);
}
//Create the Destination Table
TestTable.Create();

Column 有两个参数：SMO 表对象和从 dataset 派生的列名。这里的难点在于获取适当的数据类型，因为 DataTable 的类型属于 System 命名空间，而目标类型是 SMO。GetDataType 方法将转换 SMO Column 所需的类型。目前这只包含一些转换，但应该能很好地展示必要的转换过程。

public DataType GetDataType(string dataType)
{
DataType DTTemp = null;

switch (dataType)
{
	case ("System.Decimal"):
		DTTemp = DataType.Decimal(2, 18);
		break;
	case ("System.String"):
		DTTemp = DataType.VarChar(50);
		break;
	case ("System.Int32"):
		DTTemp = DataType.Int;
		break;
}
return DTTemp;
}

创建过程的最后一步是设置一个 PrimaryKey 列。我发现此步骤是可选的，并且仅在执行表上的关系函数时才真正需要。即使我不需要它，代码看起来也像这样，并且放在上述代码的 create() 方法之前：

//Create a primary key index
Index index = new Index(TestTable, "ID");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;

index.IndexedColumns.Add(new IndexedColumn(index,"ID"));

TestTable.Indexes.Add(index);

在这种情况下，我从 DataTable 获取的 ID 列提供了主键。

现在 SQL Server 上有一个新的数据库和表，其列和数据类型与源 DataTable 匹配。

使用 SQL Bulk Copy 填充新表

最后一步是将数据复制到新数据库表中。最简单的方法是使用 foreach 或 loop。而不是单独处理每个 DataRow，而是可以使用 SqlBulkCopy。SqlBulkCopy 涉及一次性传递整个数据集，并且可以更有效地利用更少的资源。该过程非常快速且简单，需要与数据库的连接以及之前创建的表名。此方法的一个警告是，它似乎不识别 using 语句作为打开的连接，因此您必须手动打开连接。

//First create a connection string to destination database
string connectionString;
connectionString = YourConnectionStringand
	Initial Catalog=TestSMODatabase";

//Open a connection with destination database;
using (SqlConnection connection = 
       new SqlConnection(connectionString))
{
   connection.Open();

   //Open bulkcopy connection.
   using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
   {
   	//Set destination table name
	//to table previously created.
	bulkcopy.DestinationTableName = "dbo.TestTable";

	try
	{
	   bulkcopy.WriteToServer(SourceTable);
	}
	catch (Exception ex)
	{
	   Console.WriteLine(ex.Message);
	}
	
	connection.Close();
   }
}

最后，我们拥有一个根据内存中的 DataTable 在运行时生成并填充的数据库和表。

结论

我发现与此相关的研究非常有趣且有益。当我开始为这篇文章进行研究时，我最初认为这会相当直接且相对简单，但现在我意识到，如果没有 SMO，从运行时数据创建数据库和表会更加困难。本文展示了一种从内存中的 datatable 信息动态创建 SQL Server 数据库和表的方法。我相信这会非常有用，并且可以通过关系表进一步扩展。

历史

• 2007 年 1 月 16 日：更改了格式以更好地利用页面布局，并修改了文本，删除了不必要的代码片段。