.net 使用SqlBulkCopy极速插入数据到 SQL Server----柳永法http://www.yongfa365.com/Item/SqlBulkCopy.html

于 2011-09-08 17:55:09 发布
阅读量771 收藏 1
点赞数
分类专栏： asp.net 文章标签： .net insert exception string table sql
下载: UploadFiles//SqlBulkCopy.rar 
using System;   
using System.Diagnostics;   
using System.Data;   
using System.Data.SqlClient;   
using Microsoft.ApplicationBlocks.Data;   
using System.Text;   
  
namespace ConsoleAppInsertTest   
{   
    class Program   
    {   
        static int count = 100000;           //插入的条数   
        static void Main(string[] args)   
        {   
            long runTime = 0;   
  
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");   
            runTime = SqlBulkCopyInsert();   
            Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}条数据所用的时间是{0}毫秒", runTime, count));   
  
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");   
            runTime = TransactionInsert2();   
            Console.WriteLine(string.Format("       使用事务插入{1}条数据所用的时间是{0}毫秒  --使用事务插入数据，分组执行", runTime, count));   
  
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");   
            runTime = TransactionInsert1();   
            Console.WriteLine(string.Format("       使用事务插入{1}条数据所用的时间是{0}毫秒  --先存到string再一次性执行", runTime, count));   
  
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");   
            runTime = TransactionInsert();   
            Console.WriteLine(string.Format("       使用事务插入{1}条数据所用的时间是{0}毫秒  --在事务里一条一条执行", runTime, count));   
  
  
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");   
            runTime = CommonInsert1();   
            Console.WriteLine(string.Format("       普通方式插入{1}条数据所用的时间是{0}毫秒 --保持SqlConnection", runTime, count));   
  
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");   
            runTime = CommonInsert();   
            Console.WriteLine(string.Format("       普通方式插入{1}条数据所用的时间是{0}毫秒  --一条一条插入", runTime, count));   
  
            //使用SqlBulkCopy插入100000条数据所用的时间是1301毫秒   
            //       使用事务插入100000条数据所用的时间是5811毫秒  --使用事务插入数据，分组执行   
            //       使用事务插入100000条数据所用的时间是23315毫秒  --先存到string再一次性执行   
            //       使用事务插入100000条数据所用的时间是15756毫秒  --在事务里一条一条执行   
            //       普通方式插入100000条数据所用的时间是50287毫秒 --保持SqlConnection   
            //       普通方式插入100000条数据所用的时间是49693毫秒  --一条一条插入   
  
            //使用SqlBulkCopy插入100000条数据所用的时间是1257毫秒   
            //       使用事务插入100000条数据所用的时间是5870毫秒  --使用事务插入数据，分组执行   
            //       使用事务插入100000条数据所用的时间是25062毫秒  --先存到string再一次性执行   
            //       使用事务插入100000条数据所用的时间是16943毫秒  --在事务里一条一条执行   
            //       普通方式插入100000条数据所用的时间是55764毫秒 --保持SqlConnection   
            //       普通方式插入100000条数据所用的时间是58620毫秒  --一条一条插入   
  
            //使用SqlBulkCopy插入100000条数据所用的时间是1409毫秒   
            //       使用事务插入100000条数据所用的时间是6041毫秒  --使用事务插入数据，分组执行   
            //       使用事务插入100000条数据所用的时间是23330毫秒  --先存到string再一次性执行   
            //       使用事务插入100000条数据所用的时间是15313毫秒  --在事务里一条一条执行   
            //       普通方式插入100000条数据所用的时间是50665毫秒 --保持SqlConnection   
            //       普通方式插入100000条数据所用的时间是50775毫秒  --一条一条插入   
  
            //通过三次测试分析发现，SqlBulkCopy速度是最快的，其次是SQL语句组合分组后执行，然后是SQL事务一条一条执行，然后是其它的   
            //速度比是1:5:15:50,推荐前两种方法   
  
            Console.ReadLine();   
  
        }   
  
        /// <summary>   
        /// 使用普通插入数据   
        /// </summary>   
        /// <returns></returns>   
        private static long CommonInsert()   
        {   
            Stopwatch stopwatch = new Stopwatch();   
            stopwatch.Start();   
            for (int i = 0; i < count; i++)   
            {   
                SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");   
            }   
            stopwatch.Stop();   
            return stopwatch.ElapsedMilliseconds;   
        }   
  
  
        /// <summary>   
        /// 使用普通插入数据,保持SqlConnection   
        /// </summary>   
        /// <returns></returns>   
        private static long CommonInsert1()   
        {   
            Stopwatch stopwatch = new Stopwatch();   
            stopwatch.Start();   
            SqlConnection sqlconn = new SqlConnection(SqlHelper.SqlConnection);   
            for (int i = 0; i < count; i++)   
            {   
                SqlHelper.ExecuteNonQuery(sqlconn, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");   
            }   
            stopwatch.Stop();   
            return stopwatch.ElapsedMilliseconds;   
        }   
  
  
        /// <summary>   
        /// 使用事务插入数据   
        /// </summary>   
        /// <returns></returns>   
        private static long TransactionInsert()   
        {   
            Stopwatch stopwatch = new Stopwatch();   
            stopwatch.Start();   
  
            using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))   
            {   
                ConnNow.Open();   
                using (SqlTransaction trans = ConnNow.BeginTransaction())   
                {   
                    try  
                    {   
                        for (int i = 0; i < count; i++)   
                        {   
                            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");   
                        }   
                        trans.Commit();   
                    }   
                    catch (Exception ex)   
                    {   
                        trans.Rollback();   
                    }   
                }   
            }   
  
            stopwatch.Stop();   
            return stopwatch.ElapsedMilliseconds;   
        }   
  
        /// <summary>   
        /// 使用事务插入数据,先存到string再一次性执行   
        /// </summary>   
        /// <returns></returns>   
        private static long TransactionInsert1()   
        {   
            Stopwatch stopwatch = new Stopwatch();   
            stopwatch.Start();   
            StringBuilder sb = new StringBuilder();   
            for (int i = 0; i < count; i++)   
            {   
                sb.AppendFormat("insert into passport(PassportKey) values('{0}');", Guid.NewGuid());   
            }   
            using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))   
            {   
                ConnNow.Open();   
                using (SqlTransaction trans = ConnNow.BeginTransaction())   
                {   
                    try  
                    {   
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.ToString());   
                        for (int i = 0; i < count; i++)   
                        {   
                            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");   
                        }   
                        trans.Commit();   
                    }   
                    catch (Exception ex)   
                    {   
                        trans.Rollback();   
                    }   
                }   
            }   
  
            stopwatch.Stop();   
            return stopwatch.ElapsedMilliseconds;   
        }   
  
        /// <summary>   
        /// 使用事务插入数据，分组执行-- 感谢 闫道民 提供   
        /// </summary>   
        /// <returns></returns>   
        private static long TransactionInsert2()   
        {   
            Stopwatch stopwatch = new Stopwatch();   
            stopwatch.Start();   
            using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))   
            {   
                ConnNow.Open();   
                string tmp = string.Empty; ;   
                int itmp = 0;   
                using (SqlTransaction trans = ConnNow.BeginTransaction())   
                {   
                    try  
                    {   
                        for (int i = 0; i < count; i++)   
                        {   
                            itmp++;   
                            tmp = tmp + "insert into passport(PassportKey) values('" + Guid.NewGuid() + "');";   
                            if (itmp == 180)   
                            {   
                                itmp = 0;   
                                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, tmp);   
                                tmp = string.Empty;   
                            }   
                        }   
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, tmp);   
                        trans.Commit();   
                    }   
                    catch (Exception ex)   
                    {   
                        trans.Rollback();   
                    }   
                }   
            }   
  
            stopwatch.Stop();   
            return stopwatch.ElapsedMilliseconds;   
        }   
  
        /// <summary>   
        /// 使用SqlBulkCopy方式插入数据   
        /// </summary>   
        /// <returns></returns>   
        private static long SqlBulkCopyInsert()   
        {   
            Stopwatch stopwatch = new Stopwatch();   
            stopwatch.Start();   
  
            DataTable dataTable = GetTableSchema();   
            for (int i = 0; i < count; i++)   
            {   
                DataRow dataRow = dataTable.NewRow();   
                dataRow[2] = Guid.NewGuid();   
                dataTable.Rows.Add(dataRow);   
            }   
  
            //Console.WriteLine(stopwatch.ElapsedMilliseconds);//初始化数据时间   
  
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(SqlHelper.SqlConnection);   
            sqlBulkCopy.DestinationTableName = "Passport";   
  
            if (dataTable != null && dataTable.Rows.Count != 0)   
            {   
                sqlBulkCopy.WriteToServer(dataTable);   
            }   
            sqlBulkCopy.Close();   
  
  
            stopwatch.Stop();   
            return stopwatch.ElapsedMilliseconds;   
        }   
  
  
        private static DataTable GetTableSchema()   
        {   
            return SqlHelper.ExecuteDataset(SqlHelper.SqlConnection, CommandType.Text, "select * from Passport where 1=2").Tables[0];   
        }   
  
    }   
}  
using System;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Text;

namespace ConsoleAppInsertTest
{
    class Program
    {
        static int count = 100000;           //插入的条数
        static void Main(string[] args)
        {
            long runTime = 0;

            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
            runTime = SqlBulkCopyInsert();
            Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}条数据所用的时间是{0}毫秒", runTime, count));

            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
            runTime = TransactionInsert2();
            Console.WriteLine(string.Format("       使用事务插入{1}条数据所用的时间是{0}毫秒  --使用事务插入数据，分组执行", runTime, count));

            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
            runTime = TransactionInsert1();
            Console.WriteLine(string.Format("       使用事务插入{1}条数据所用的时间是{0}毫秒  --先存到string再一次性执行", runTime, count));

            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
            runTime = TransactionInsert();
            Console.WriteLine(string.Format("       使用事务插入{1}条数据所用的时间是{0}毫秒  --在事务里一条一条执行", runTime, count));


            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
            runTime = CommonInsert1();
            Console.WriteLine(string.Format("       普通方式插入{1}条数据所用的时间是{0}毫秒 --保持SqlConnection", runTime, count));

            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
            runTime = CommonInsert();
            Console.WriteLine(string.Format("       普通方式插入{1}条数据所用的时间是{0}毫秒  --一条一条插入", runTime, count));

            //使用SqlBulkCopy插入100000条数据所用的时间是1301毫秒
            //       使用事务插入100000条数据所用的时间是5811毫秒  --使用事务插入数据，分组执行
            //       使用事务插入100000条数据所用的时间是23315毫秒  --先存到string再一次性执行
            //       使用事务插入100000条数据所用的时间是15756毫秒  --在事务里一条一条执行
            //       普通方式插入100000条数据所用的时间是50287毫秒 --保持SqlConnection
            //       普通方式插入100000条数据所用的时间是49693毫秒  --一条一条插入

            //使用SqlBulkCopy插入100000条数据所用的时间是1257毫秒
            //       使用事务插入100000条数据所用的时间是5870毫秒  --使用事务插入数据，分组执行
            //       使用事务插入100000条数据所用的时间是25062毫秒  --先存到string再一次性执行
            //       使用事务插入100000条数据所用的时间是16943毫秒  --在事务里一条一条执行
            //       普通方式插入100000条数据所用的时间是55764毫秒 --保持SqlConnection
            //       普通方式插入100000条数据所用的时间是58620毫秒  --一条一条插入

            //使用SqlBulkCopy插入100000条数据所用的时间是1409毫秒
            //       使用事务插入100000条数据所用的时间是6041毫秒  --使用事务插入数据，分组执行
            //       使用事务插入100000条数据所用的时间是23330毫秒  --先存到string再一次性执行
            //       使用事务插入100000条数据所用的时间是15313毫秒  --在事务里一条一条执行
            //       普通方式插入100000条数据所用的时间是50665毫秒 --保持SqlConnection
            //       普通方式插入100000条数据所用的时间是50775毫秒  --一条一条插入

            //通过三次测试分析发现，SqlBulkCopy速度是最快的，其次是SQL语句组合分组后执行，然后是SQL事务一条一条执行，然后是其它的
            //速度比是1:5:15:50,推荐前两种方法

            Console.ReadLine();

        }

        /// <summary>
        /// 使用普通插入数据
        /// </summary>
        /// <returns></returns>
        private static long CommonInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            for (int i = 0; i < count; i++)
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");
            }
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }


        /// <summary>
        /// 使用普通插入数据,保持SqlConnection
        /// </summary>
        /// <returns></returns>
        private static long CommonInsert1()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            SqlConnection sqlconn = new SqlConnection(SqlHelper.SqlConnection);
            for (int i = 0; i < count; i++)
            {
                SqlHelper.ExecuteNonQuery(sqlconn, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");
            }
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }


        /// <summary>
        /// 使用事务插入数据
        /// </summary>
        /// <returns></returns>
        private static long TransactionInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))
            {
                ConnNow.Open();
                using (SqlTransaction trans = ConnNow.BeginTransaction())
                {
                    try
                    {
                        for (int i = 0; i < count; i++)
                        {
                            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");
                        }
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                    }
                }
            }

            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

        /// <summary>
        /// 使用事务插入数据,先存到string再一次性执行
        /// </summary>
        /// <returns></returns>
        private static long TransactionInsert1()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < count; i++)
            {
                sb.AppendFormat("insert into passport(PassportKey) values('{0}');", Guid.NewGuid());
            }
            using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))
            {
                ConnNow.Open();
                using (SqlTransaction trans = ConnNow.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.ToString());
                        for (int i = 0; i < count; i++)
                        {
                            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");
                        }
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                    }
                }
            }

            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

        /// <summary>
        /// 使用事务插入数据，分组执行-- 感谢 闫道民 提供
        /// </summary>
        /// <returns></returns>
        private static long TransactionInsert2()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))
            {
                ConnNow.Open();
                string tmp = string.Empty; ;
                int itmp = 0;
                using (SqlTransaction trans = ConnNow.BeginTransaction())
                {
                    try
                    {
                        for (int i = 0; i < count; i++)
                        {
                            itmp++;
                            tmp = tmp + "insert into passport(PassportKey) values('" + Guid.NewGuid() + "');";
                            if (itmp == 180)
                            {
                                itmp = 0;
                                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, tmp);
                                tmp = string.Empty;
                            }
                        }
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, tmp);
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                    }
                }
            }

            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

        /// <summary>
        /// 使用SqlBulkCopy方式插入数据
        /// </summary>
        /// <returns></returns>
        private static long SqlBulkCopyInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            DataTable dataTable = GetTableSchema();
            for (int i = 0; i < count; i++)
            {
                DataRow dataRow = dataTable.NewRow();
                dataRow[2] = Guid.NewGuid();
                dataTable.Rows.Add(dataRow);
            }

            //Console.WriteLine(stopwatch.ElapsedMilliseconds);//初始化数据时间

            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(SqlHelper.SqlConnection);
            sqlBulkCopy.DestinationTableName = "Passport";

            if (dataTable != null && dataTable.Rows.Count != 0)
            {
                sqlBulkCopy.WriteToServer(dataTable);
            }
            sqlBulkCopy.Close();


            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }


        private static DataTable GetTableSchema()
        {
            return SqlHelper.ExecuteDataset(SqlHelper.SqlConnection, CommandType.Text, "select * from Passport where 1=2").Tables[0];
        }

    }
}
喂大的程序员
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值