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

来源：互联网发布：python heatmap 编辑：程序博客网时间：2024/05/17 08:02

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

1. 表结构：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb]') AND type in (N'U'))BEGINDROP TABLE [dbo].[tb]ENDGOCREATE 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