C# 利用ADO.NET SQL SERVER数据库插入插入大量数据，三种方式的性能比较

先看结论，有图有真相：

环境条件：

首先是新建一张表，和一个配置文件的连接字符串，以及控制台程序

CREATE TABLE [dbo].[TestInsert](
	[GUID] [varchar](50) NOT NULL,
	[No] [int] NULL,
	[AddTime] [datetime] NULL,
 CONSTRAINT [PK_TestInsert] PRIMARY KEY CLUSTERED 
(
	[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 相关代码如下：

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestDBinsert
{
    public class SqlServerDbo
    {
        private string connectionstr;
        public SqlServerDbo() {
            connectionstr = ConfigurationManager.ConnectionStrings["ConnectionSetting"].ConnectionString;
        }
        public SqlServerDbo(string str)
        {
            connectionstr = str;
        }
        public virtual void Insert(DataTable dt,string tablename){
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionstr))
            {
                bulkCopy.DestinationTableName =tablename;
                bulkCopy.BatchSize = 10000;               
                bulkCopy.WriteToServer(dt);               
            }
        }
        public virtual void Insert(Hashtable ht, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionstr))
            {
                string sql = "select * from "+tableName+" where 1<>1";
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(sql, connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                connection.Open();
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, tableName);
                DataTable dt = dataSet.Tables[tableName];
                DataRow dr = dt.NewRow();
                foreach (DictionaryEntry de in ht)
                {               
                    if (string.IsNullOrEmpty(de.Value.ToString()))
                    {
                        dr[de.Key.ToString()]=DBNull.Value;
                    }
                    else {
                        dr[de.Key.ToString()] = de.Value.ToString();
                    }
                }
                dt.Rows.Add(dr);
                builder.GetUpdateCommand();
                adapter.Update(dataSet, tableName);
            }
        }
        public virtual int Update(string sql) {
            using (SqlConnection connection = new SqlConnection(
               connectionstr))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                command.Connection.Open();
                return command.ExecuteNonQuery();
            }
        }
    }
}

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

namespace TestDBinsert
{
    class Program
    {
        static SqlServerDbo conn = new SqlServerDbo();
        static void Main(string[] args)
        {
            using (new TestGC("通过bulkCopy.WriteToServer(dt)插入10万条数据"))
            {
                DataTable dt = new DataTable("TestInsert");
                dt.Columns.Add(new DataColumn("GUID", Type.GetType("System.String")));
                dt.Columns.Add(new DataColumn("No", Type.GetType("System.Int32")));
                dt.Columns.Add(new DataColumn("AddTime", Type.GetType("System.DateTime")));
                for (int i = 0; i < 100000; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["GUID"] = Guid.NewGuid().ToString();
                    dr["No"] = i;
                    dr["AddTime"] = DateTime.Now.ToString();
                    dt.Rows.Add(dr);
                }
                conn.Insert(dt, "TestInsert");
            }
            using (new TestGC("通过adapter.Update(dataSet, tableName)插入10万条数据"))
            {
                for (int i = 0; i < 100000; i++)
                {
                    Hashtable ht = new Hashtable();
                    ht.Add("GUID", Guid.NewGuid().ToString());
                    ht.Add("No", i);
                    ht.Add("AddTime", DateTime.Now.ToString());
                    conn.Insert(ht, "TestInsert");
                }
            }
            using (new TestGC("通过command.ExecuteNonQuery()插入10万条数据"))
            {
                for (int i = 0; i < 100000; i++)
                {
                    conn.Update("insert into TestInsert(GUID,No,AddTime) values ('" + Guid.NewGuid().ToString() + "'," + i + ",'" + DateTime.Now.ToString() + "')");
                }
            }
            
        }      
    }
}

 总结，SqlBulkCopy对象，实现的性能最优，大批量数据插入用途非常广泛，尤其是在将表格excel数据插入到数据库，或是导入数据包（其他数据库导出一定格式的文件）非常有用，这里记录于此，同时决定用下一篇文章，用Redis测试插入大量数据，作为对比测试。