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

2021-08-24 01:19:28

首先创建一个数据库连接类：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 == )

            {

                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 == )

            {

                throw new ArgumentNullException("数据表列不能为空");

            }

            var dt = CreateTable(columns);

            if (action != null)

            {

                for (var i = ; i < dataList.Count; i++)

                {

                    var wapper = new DataRowWaper(dt.NewRow());

                    action(wapper, dataList[i], i);

                    dt.Rows.Add(wapper.Row);

                }

            }

            BulkInsert(conn, dt, tableName);

        }

    }

}

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);

        }

    }

}

其次创建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;

        }

    }

}

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;

        }

    }

}

映射到数据库对象表信息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; }

    }

}

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; }

    }

}

批量操作数据

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=""},

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

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

             };

            //列名称

            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;

               }

               );

        }

    }

}

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;

               }

               );

        }

    }

}

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;

               }

               );

        }

    }

}

码农公寓

相关文章