批量导入 Bulk Insert C#

private static string connectionString = SqlHelper.ConnectionStringLocalTransaction; //数据库连接字符串
private static int count = 200000; //插入的条数

主程序入口

private static void Main(string[] args)
        {
            long commonInsertRunTime = CommonInsert();
            Console.WriteLine(string.Format("普通方式插入{1}条数据所用的时间是{0}毫秒", commonInsertRunTime, count));

            long MySqlBulkRunTime = MySqlBulk();
            Console.WriteLine(string.Format("使用MySqlBulkCopy插入{1}条数据所用的时间是{0}毫秒", MySqlBulkRunTime, count));

            long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert();
            Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}条数据所用的时间是{0}毫秒", sqlBulkCopyInsertRunTime, count));

            long TVPInsertRunTime = TVPInsert();
            Console.WriteLine(string.Format("使用表值方式（TVP）插入{1}条数据所用的时间是{0}毫秒", TVPInsertRunTime, count));
        }

用Bulk Insert语句执行的方法-----MySqlBulk()

private static long MySqlBulk()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            string sql = "BULK INSERT contact_list FROM 'e:contact_list.csv' WITH (FIELDTERMINATOR = ',') ";

            SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, sql);
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

普通调用存储过程插入数据的方法-----CommonInsert()

private static long CommonInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            string passportKey;
            for (int i = 0; i < count; i++)
            {
                passportKey = Guid.NewGuid().ToString();
                SqlParameter[] sqlParameter = {new SqlParameter("@passport", passportKey)};
                SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassport", sqlParameter);
            }
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

使用SqlBulkCopy方式插入数据的方法-----SqlBulkCopyInsert()

private static long SqlBulkCopyInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            DataTable dataTable = OpenCSVFile2("e:\\contact_list.csv");

            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
            sqlBulkCopy.DestinationTableName = "contact_list";
            sqlBulkCopy.BatchSize = dataTable.Rows.Count;
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            sqlConnection.Open();
            if (dataTable != null && dataTable.Rows.Count != 0)
            {
                sqlBulkCopy.WriteToServer(dataTable);
            }
            sqlBulkCopy.Close();
            sqlConnection.Close();
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

TVPInsert()

private static long TVPInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

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

            SqlParameter[] sqlParameter = {new SqlParameter("@TVP", dataTable)};
            SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP",
                                      sqlParameter);

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

创建DataTable结构-----GetTableSchema()

private static DataTable GetTableSchema()
        {
            DataTable dataTable = new DataTable();
            dataTable.Columns.AddRange(new DataColumn[] {new DataColumn("PassportKey")});

            return dataTable;
        }

获取csv文件中的内容并赋值给DataTable-----OpenCSVFile()和OpenCSVFile2()

private static DataTable OpenCSVFile(string filepath)
        {
            DataTable mycsvdt=new DataTable();
            string strpath = filepath; //csv文件的路径
            try
            {
                int intColCount = 0;
                bool blnFlag = true;

                DataColumn mydc;
                DataRow mydr;

                string strline;
                string[] aryline;
                StreamReader mysr = new StreamReader(strpath, System.Text.Encoding.Default);

                while ((strline = mysr.ReadLine()) != null)
                {
                    if (!String.IsNullOrEmpty(strline))
                    {
                        aryline = strline.Split(new char[] {','});
                        //给datatable加上列名
                        if (blnFlag)
                        {
                            blnFlag = false;
                            intColCount = aryline.Length;
                            int col = 0;
                            for (int i = 0; i < aryline.Length; i++)
                            {
                                col = i + 1;
                                mydc = new DataColumn(col.ToString());
                                mycsvdt.Columns.Add(mydc);
                            }
                        }
                        //填充数据并加入到datatable中
                        mydr = mycsvdt.NewRow();
                        for (int i = 0; i < intColCount; i++)
                        {
                            mydr[i] = aryline[i];
                        }
                        mycsvdt.Rows.Add(mydr);
                    }
                }
                return mycsvdt;
            }
            catch (Exception e)
            {
                //throw (Stack.GetErrorStack(strpath + "读取CSV文件中的数据出错." + e.Message, "OpenCSVFile("));
                return null;
            }
        }
        private static DataTable OpenCSVFile2(string filepath)
        {
            DataTable mycsvdt = new DataTable();
            string strpath = filepath; //csv文件的路径
            try
            {
                int intColCount = 0;
                bool blnFlag = true;

                DataColumn mydc;
                DataRow mydr;

                string strline;
                string[] aryline;
                StreamReader mysr = new StreamReader(strpath, System.Text.Encoding.Default);

                while ((strline = mysr.ReadLine()) != null)
                {
                    if (!String.IsNullOrEmpty(strline))
                    {
                        strline = strline.Replace("\"\"", "'");
                        MatchCollection mCol = Regex.Matches(strline, ",\"([^\"]+)\",", RegexOptions.ExplicitCapture);
                        IEnumerator ie = mCol.GetEnumerator();
                        int key = 0;
                        string patn = "";
                        while (ie.MoveNext())
                        {
                            patn = ie.Current.ToString();
                            key = strline.Substring(0, strline.IndexOf(patn)).Split(',').Length;
                        }
                        if (key > 0)
                        {
                            strline = strline.Replace(patn, ",,");
                        }
                        aryline = strline.Split(new char[] { ',' });
                        //给datatable加上列名
                        if (blnFlag)
                        {
                            blnFlag = false;
                            intColCount = aryline.Length;
                            for (int i = 0; i < aryline.Length; i++)
                            {
                                if (i == key && key>0)
                                {
                                    mydc = new DataColumn((i + 1).ToString());
                                    mycsvdt.Columns.Add(patn.Trim(new char[] { ',', '"' }).Replace("'", "\""));
                                }
                                else
                                {
                                    mydc = new DataColumn((i + 1).ToString());
                                    mycsvdt.Columns.Add(mydc);
                                }
                            }
                        }
                        //填充数据并加入到datatable中
                        mydr = mycsvdt.NewRow();
                        for (int i = 0; i < intColCount; i++)
                        {
                            if (i == key && key>0)
                            {
                                mydr[i] = patn.Trim(new char[] { ',', '"' }).Replace("'", "\"");
                            }
                            else
                            {
                                mydr[i] = aryline[i];
                            }
                        }
                        mycsvdt.Rows.Add(mydr);
                    }
                }
                return mycsvdt;
            }
            catch (Exception e)
            {
                //throw (Stack.GetErrorStack(strpath + "读取CSV文件中的数据出错." + e.Message, "OpenCSVFile("));
                return null;
            }
        }

Passport表只有一列PassportKey，字段类型nchar(50)

CreatePassportWithTVP存储过程如下：

CREATE PROCEDURE [dbo].[CreatePassportWithTVP] 

@TVP PassportTableType readonly

AS
BEGIN
SET NOCOUNT ON;

Insert into Passport(PassportKey) select PassportKey from @TVP

END