如何使用单一的查询中插入从数据集多条记录到SQL Server 2005？ [英] How can I use single query to insert multiple records from Dataset into SQL Server 2005?

问题描述

我在包含来自用户侧的多条记录ADO.NET数据集。我需要插入在单个查询中的所有那些行到数据库中，以避免多个查询

I have a dataset in ADO.NET containing multiple records from user side. I need to insert all those rows in single query into the database, in order to avoid multiple queries

推荐答案

也许就像一个大容量复制将是一个答案。低于code项目的例子说明了如何使用数据表来做到这一点，但你应该能够改变如约使用DataSet。

Maybe something like a Bulk copy would be an answer. The example in Code Project below show how to do it using a DataTable, but you should be able to change the example around to use a DataSet.

下面的code覆盖到conenction和exection在SQL Server（从的 $ C $的CProject ）。

Below is an small part of the code which covers to conenction and exection in SQL Server (taken from CodeProject).

要注意的关键部分是bulkcopy.WriteToServer（ SourceTable ）;是的SourceTable将是数据集的一部分，你会传递给它

The key part to notice is the bulkcopy.WriteToServer(SourceTable); were the SourceTable would be the part of the DataSet you would pass to it

//First create a connection string to destination database
string connectionString;
connectionString = <EM>YourConnectionString</EM>and
    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); // SourceTable would come from your DataSet
    }
    catch (Exception ex)
    {
       Console.WriteLine(ex.Message);
    }

    connection.Close();
   }
}

这篇关于如何使用单一的查询中插入从数据集多条记录到SQL Server 2005？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！