批量导入数据到SQL

主要方法有:Bcp,Bulk insert,dts,openrowset,adapter.update,insert等

基中对bulk,adapter.update,insert测试如下:

private static void TestAdapterUpdate()
        {
            DataRow newRow;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["testCon"].ConnectionString);
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.UpdatedRowSource = UpdateRowSource.None;
            com.CommandText = "select top 1 * from Auto_ValueAdmin";
            SqlDataAdapter adapter=new SqlDataAdapter(com);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DateTime bdt = DateTime.Now;
            for (int i = 0; i < 2000; i++)
            {
                newRow = ds.Tables[0].NewRow();
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    foreach (DataColumn col in ds.Tables[0].Columns)
                    {
                        if (!col.ColumnName.Equals("ID", StringComparison.CurrentCultureIgnoreCase))
                        {
                            newRow[col] = row[col];
                        }
                    }
                }
                ds.Tables[0].Rows.Add(newRow);
            }
            DateTime mdt = DateTime.Now;
            double mrs = ((TimeSpan)(mdt - bdt)).TotalMilliseconds;
            Console.WriteLine(mrs);
            
            
            adapter.UpdateBatchSize = 500;
            adapter.Update(ds);
            DateTime edt = DateTime.Now;
            double rs = ((TimeSpan)(edt - bdt)).TotalMilliseconds;
            Console.WriteLine(rs);
            double real = ((TimeSpan)(edt - mdt)).TotalMilliseconds;
            Console.WriteLine(real);
            Console.WriteLine("任意键退出");
            Console.Read();
        }

        private static void TestInsertSql()
        {
            StringBuilder sb = new StringBuilder();
            string startSql = "INSERT INTO [Auto_ValueAdmin] ([UserSiteID], [IsChecked], [TaskID], [IsExpire], [FontFile1], [FontFile2], [FontFile3], [FontFile4], [FontFile5], [FontFile6], [FontFile7], [FontFile8], [FontFile9], [FontFile10], [FontFile11], [FontFile12], [FontFile13], [FontFile14], [FontFile15], [FontFile16], [FontFile17], [FontFile18], [FontFile19], [FontFile20], [FontFile21], [FontFile22], [FontFile23], [FontFile24], [FontFile25], [FontFile26], [FontFile27], [FontFile28], [FontFile29], [FontFile30], [FontFile31], [FontFile32], [FontFile33], [FontFile34], [FontFile35], [FontFile36], [FontFile37], [FontFile38], [FontFile39], [FontFile40], [FontFile41], [FontFile42], [FontFile43], [FontFile44], [FontFile45], [FontFile46], [FontFile47], [FontFile48], [FontFile49], [FontFile50]) values(";
            List<string> sqlList = new List<string>();
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["testCon"].ConnectionString);
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "select top 1 * from Auto_ValueAdmin";
            SqlDataAdapter adapter = new SqlDataAdapter(com);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            DateTime bdt = DateTime.Now;
            for (int i = 0; i < 2000; i++)
            {
                sb = new StringBuilder();
                sb.Append(startSql);
                foreach (DataRow row in dt.Rows)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (!col.ColumnName.Equals("ID", StringComparison.CurrentCultureIgnoreCase))
                        {
                            sb.Append("'");
                            sb.Append(row[col]);
                            sb.Append("',");
                        }
                    }
                }
                sb.Remove(sb.Length - 1, 1);
                sb.Append(")");
                sqlList.Add(sb.ToString());
            }

            //SqlTransaction st = null;
            DateTime mdt = DateTime.Now;
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            com = new SqlCommand();
            com.Connection = con;
            //st = con.BeginTransaction();
            //com.Transaction = st;
            foreach (string item in sqlList)
            {
                
                com.CommandText = item;

                try
                {
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    com.ExecuteNonQuery();
                }
                catch { }
            }
            try
            {
                //st.Commit();
            }
            catch { }
            DateTime edt = DateTime.Now;
            double mrs = ((TimeSpan)(edt - mdt)).TotalMilliseconds;
            Console.WriteLine(mrs);
            double rs = ((TimeSpan)(edt - bdt)).TotalMilliseconds;
            double real = ((TimeSpan)(edt - mdt)).TotalMilliseconds;
            Console.WriteLine(real);
            Console.WriteLine(rs);

            

            
        }

        private static void BulkCopy()
        {
            DataRow newRow;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["testCon"].ConnectionString);
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.UpdatedRowSource = UpdateRowSource.None;
            com.CommandText = "select top 1 * from Auto_ValueAdmin";
            SqlDataAdapter adapter = new SqlDataAdapter(com);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            DataSet ds = new DataSet();
            adapter.Fill(ds);

            con.Open();
            SqlBulkCopy bc = new SqlBulkCopy(con);
            bc.BulkCopyTimeout = 360;
            bc.DestinationTableName = "Auto_ValueAdmin";

            foreach (DataColumn item in ds.Tables[0].Columns)
            {
                if (!item.ColumnName.Equals("ID", StringComparison.CurrentCultureIgnoreCase))
                {
                    bc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
                }
            }

            DateTime bdt = DateTime.Now;
            for (int i = 0; i < 2000; i++)
            {
                newRow = ds.Tables[0].NewRow();
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    foreach (DataColumn col in ds.Tables[0].Columns)
                    {
                        if (!col.ColumnName.Equals("ID", StringComparison.CurrentCultureIgnoreCase))
                        {
                            newRow[col] = row[col];
                        }
                    }
                }
                ds.Tables[0].Rows.Add(newRow);
            }
            DateTime mdt = DateTime.Now;
            double mrs = ((TimeSpan)(mdt - bdt)).TotalMilliseconds;
            Console.WriteLine(mrs);

            bc.WriteToServer(ds.Tables[0]);
            DateTime edt = DateTime.Now;
            double rs = ((TimeSpan)(edt - bdt)).TotalMilliseconds;
            Console.WriteLine(rs);
            double real = ((TimeSpan)(edt - mdt)).TotalMilliseconds;
            Console.WriteLine(real);
        }

经测试,bulk的速度最快