利用DataTable一次性将数据插入到数据库中

删除、更新等更复杂的综合操作见：点击打开链接

1. 表结构：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb]') AND type in (N'U'))
BEGIN
	DROP TABLE [dbo].[tb]	
END
GO
CREATE TABLE [dbo].[tb](
	[A] [int] NULL,
	[B] [nvarchar](1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

2. 测试代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectString = "Data Source=leaf-home\\sqlserver2005;Initial Catalog=Test;Persist Security Info=True;User ID=site_dev;Password=???";
            using (SqlConnection conn = new SqlConnection(connectString))
            {
                //-------- 创建要测试的数据 begin -------------
                DataTable dtNew = new DataTable();
                dtNew.Columns.Add(new DataColumn( "A",typeof(int)) );
                dtNew.Columns.Add(new DataColumn( "B",typeof(string)) );

                DataRow dr = dtNew.NewRow();
                dr["A"] = 99;
                dr["B"] = "酒";
                dtNew.Rows.Add(dr);
                dr = dtNew.NewRow();
                dr["A"] = 100;
                dr["B"] = "百";
                dtNew.Rows.Add(dr);
                //-------- 创建要测试的数据 end -------------

                //得到原表结构
                conn.Open();
                string strSql = "SELECT A,B FROM tb WHERE 1=0";
                SqlDataAdapter adapter = new SqlDataAdapter(strSql, connectString);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                DataSet ds = new DataSet();
                adapter.Fill(ds, "flag");
                DataTable table = ds.Tables["flag"];

                //将新数据传到对应的datatable中
                foreach (DataRow drNew in dtNew.Rows) 
                {
                    DataRow row = table.NewRow();
                    foreach (DataColumn dc in table.Columns) 
                    {
                        row[dc.ColumnName]=drNew[dc.ColumnName];
                    }
                    table.Rows.Add(row);
                }

                //更新数据库
                adapter.Update(table);
            }
            Console.Read();
        }//end of Main
    }//end of class
}//end of namespace