用SqlBulkCopy批量插入数据到SqlServer数据库表中

浏览数：18 / 时间：2015年06月12日

首先创建一个数据库连接类：SQLHelper

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

namespace insetData
{
   public  class SQLHelper
    {
       public static readonly string Strconn = "Data Source=LocalHost;database=CardID;uid=sa;pwd=123";

        /// <summary>

        /// 创建SqlConnection对象

        /// </summary>

        /// <returns></returns>

        public static SqlConnection sqlConn()
        {

            return new SqlConnection(Strconn);

        }

        /// <summary>
        /// SQLServer批量插入数据功能
        /// 这是SQLServer特有的功能，故不再上层抽象类中编写
        /// </summary>
        /// <param name="dt">数据表</param>

        /// <param name="tableName">表名称</param>

        public static void BulkInsert(SqlConnection conn, DataTable dt, string tableName)
        {

            if (string.IsNullOrEmpty(tableName))
            {

                throw new ArgumentNullException("请指定需要插入的数据表");

            }

            var count = dt.Rows.Count;

            if (count == 0)
            {

                return;

            }

            if (conn.State == ConnectionState.Closed)
            {

                conn.Open();

            }

            //SqlBulkCopy用其他源的数据有效批量加载 SQL Server 表

            using (SqlBulkCopy copy = new SqlBulkCopy(conn))
            {

                copy.BatchSize = dt.Rows.Count;//每一批次中的行数。在每一批次结束时，将该批次中的行发送到服务器。 

                copy.DestinationTableName = tableName;//指定目标表

                copy.WriteToServer(dt);//将dt中的所有行复制到SqlBulkCopy对象的DestinationTableName指定的目标表中

                conn.Close();
                conn.Dispose();

            }

        }

        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <param name="columns"></param>

        public static DataTable CreateTable(IList<string> columns)
        {
            var dt = new DataTable();
            foreach (var c in columns)
            {
                dt.Columns.Add(c);
            }

            return dt;

        }
        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="tableName">数据表</param>
        /// <param name="columns">字段</param>
        /// <param name="dataList">数据列表</param>
        /// <param name="action">具体操作</param>

        public static void CreateInner<T>(SqlConnection conn, string tableName, IList<string> columns, IList<T> dataList, Action<DataRowWaper, T, int> action)
        {
            if (string.IsNullOrEmpty(tableName))
            {
               throw new ArgumentNullException("需要指定操作的数据表");
            }

            if (columns == null || columns.Count == 0)
            {
                throw new ArgumentNullException("数据表列不能为空");
            }

            var dt = CreateTable(columns);

            if (action != null)
            {
                for (var i = 0; i < dataList.Count; i++)
                {
                    var wapper = new DataRowWaper(dt.NewRow());
                    action(wapper, dataList[i], i);
                    dt.Rows.Add(wapper.Row);

                }
            }

            BulkInsert(conn, dt, tableName);

        }
    }
}

View Code

其次创建DataRowWaper类

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

namespace insetData
{
  public  class DataRowWaper
    {
        private DataRow row = null;

        public DataRowWaper(DataRow row)
        {

            this.row = row;

        }

        public DataRow Row
        {

            get
            {

                return this.row;

            }

        }



        public object this[DataColumn column]
        {

            get
            {

                return this.row[column];

            }

            set
            {

                this.row[column] = value;

            }

        }

        public object this[int columnIndex]
        {

            get
            {

                return this.row[columnIndex];

            }

            set
            {

                this.row[columnIndex] = value;

            }

        }

        public object this[string columnName]
        {

            get
            {

                return this.row[columnName];

            }

            set
            {

                this.row[columnName] = value;

            }

        }

        public void SetValue(string key, object value)
        {

            this.row[key] = value;

        }
    }
}

View Code

映射到数据库对象表信息People

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

namespace insetData
{
   public class People
    {
       public int id { get; set;}
       public string uname { get; set; }
       public string era { get; set; }
       public string amount { get; set; }
    }
}

View Code

批量操作数据

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

namespace insetData
{
    class Program
    {
        static void Main(string[] args)
        {
           
            //构造的数据源
            List<People> list = new List<People>() 

            {
                new People(){ uname="张亮",era="中年",amount="100000"},

                new People(){ uname="天天",era="年轻",amount="233233"},

                new People(){ uname="kimi",era="年轻",amount="455631"}

             };

            //列名称
            var lists = new[] { "id", "uname", "era", "amount" };

            ////构建DataTable
        
            //DataTable dt = new DataTable();
            //foreach (var item in lists)
            //{
            //    dt.Columns.Add(item, item.GetType());
            //}

            //for (int i = 0; i < list.Count; i++)
            //{
            //    DataRow newRow = dt.NewRow();
            //   newRow["uname"] = list[i].uname;
            //    newRow["era"] = list[i].era;
            //    newRow["amount"] = list[i].amount;
            //   dt.Rows.Add(newRow);

            //}
            
            ////批量插入

            //SQLHelper.BulkInsert(SQLHelper.sqlConn(), dt, "Tb_People");
            //上面这段代码是直接构造的DataTable


            SQLHelper.CreateInner<People>(SQLHelper.sqlConn(), "Tb_People", lists, list,

               (curow, People, i) =>
               {

                   curow["id"] = People.id;

                   curow["uname"] = People.uname;

                   curow["era"] = People.era;

                   curow["amount"] = People.amount;

               }
               );

        }

    }
}