c# 批量处理数据录入

c# 分批处理数据录入

//using System.Text;

//using System.Data;

//using System.Data.SqlClient;

//using System;

//namespace InsertEfficiency

//{

//    class Program

//    {

//        static void Main(string[] args)

//        {

//            //构造数据源

//            DataTable dt = new DataTable();

//            dt.Columns.Add(new DataColumn("ID", typeof(int)));

//            dt.Columns.Add(new DataColumn("CreateDateTime", typeof(DateTime)));

//            dt.Columns.Add(new DataColumn("TestMethod", typeof(string)));



//            for (int i = 1; i <= 30; i++)

//            {

//                dt.Rows.Add(new object[] { i, DateTime.Now, "ExecuteSqlBulkCopy" });

//            }



//            Test t = new Test();



//            DateTime begin1 = DateTime.Now;

//            t.ExecuteRowByRow(dt);

//            DateTime end1 = DateTime.Now;

//            Console.WriteLine("ExecuteRowByRow:{0}ms", (end1 - begin1).Milliseconds);



//            DateTime begin2 = DateTime.Now;

//            t.ExecuteOnce(dt);

//            DateTime end2 = DateTime.Now;

//            Console.WriteLine("ExecuteOnce:{0}ms", (end2 - begin2).Milliseconds);



//            DateTime begin3 = DateTime.Now;

//            t.ExecuteSqlBulkCopy(dt);

//            DateTime end3 = DateTime.Now;

//            Console.WriteLine("ExecuteSqlBulkCopy:{0}ms", (end3 - begin3).Milliseconds);



//            Console.ReadLine();

//        }

//    }



//    class Test

//    {

//        public Test()

//        {

//        }



//        public void ExecuteRowByRow(DataTable dt)

//        {

//            using (SqlConnection conn = new SqlConnection(GetConnectionString))

//            {

//                conn.Open();

//                for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)

//                {

//                    DataRow dr = dt.Rows[rowIndex];



//                    SqlCommand cmd = new SqlCommand();

//                    cmd.Connection = conn;

//                    cmd.CommandText = string.Format("insert into TestTable values ({0},'{1}','{2}')",

//                        dr[0].ToString(), dr[1].ToString(), "ExecuteRowByRow");

//                    cmd.ExecuteNonQuery();

//                }

//            }

//        }

//        public void ExecuteOnce(DataTable dt)

//        {

//            StringBuilder strSql = new StringBuilder();

//            for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)

//            {

//                DataRow dr = dt.Rows[rowIndex];

//                string sql = string.Format("insert into TestTable values ({0},'{1}','{2}')",

//                        dr[0].ToString(), dr[1].ToString(), "ExecuteOnce");

//                if (strSql.ToString().Length == 0)

//                {

//                    strSql.Append(sql);

//                }

//                else

//                {

//                    strSql.Append(";").Append(sql);

//                }



//            }

//            using (SqlConnection conn = new SqlConnection(GetConnectionString))

//            {

//                conn.Open();

//                SqlCommand cmd = new SqlCommand();

//                cmd.Connection = conn;

//                cmd.CommandText = strSql.ToString();

//                cmd.ExecuteNonQuery();

//            }

//        }



//        public void ExecuteSqlBulkCopy(DataTable dt)

//        {

//            using (SqlConnection conn = new SqlConnection(GetConnectionString))

//            {

//                SqlBulkCopy bulk = new SqlBulkCopy(conn);

//                bulk.DestinationTableName = "TestTable";

//                bulk.BatchSize = dt.Rows.Count;



//                if (dt != null && dt.Rows.Count != 0)

//                {

//                    conn.Open();

//                    bulk.WriteToServer(dt);

//                }

//                bulk.Close();

//            }

//        }



//        string GetConnectionString

//        {

//            get

//            {

//                //return @"server=.mssqlserver2005;database=test;uid=sa;pwd=123456";

//                return @"server=SA-PCSQLEXPRESS;database=test;uid=sa;pwd=123";

//            }

//        }

//    }

//}

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Threading;

using System;

namespace InsertEfficiency

{

    class Program

    {

        static void Main(string[] args)

        {

            Test t = new Test();

            t.Testing();

            Console.ReadLine();

        }



    }



    class Test

    {

        DataTable dt = new DataTable();



        public Test()

        {

           CreateData();

        }



        private void CreateData()

        {

            //构造数据源

            dt.Columns.Add(new DataColumn("ID", typeof(int)));

            dt.Columns.Add(new DataColumn("CreateDateTime", typeof(DateTime)));

            dt.Columns.Add(new DataColumn("TestMethod", typeof(string)));



            for (int i = 1; i <= 30; i++)

            {

                dt.Rows.Add(new object[] { i, DateTime.Now, "ExecuteSqlBulkCopy" });

            }

        }



        public void Testing()

        {

            for (int i = 0; i < 20; i++)

            {

                Thread t = new Thread(new ParameterizedThreadStart(ExcuteTesting));

                t.Start(i);

            }

        }



        private void ExcuteTesting(object TreadNo)

        {

            //DateTime begin1 = DateTime.Now;

            //ExecuteRowByRow();

            //DateTime end1 = DateTime.Now;

            //Console.WriteLine("Tread-{0}-ExecuteRowByRow:{1}ms", TreadNo, (end1 - begin1).Milliseconds);



            DateTime begin2 = DateTime.Now;

            ExecuteOnce();

            DateTime end2 = DateTime.Now;

            Console.WriteLine("Tread-{0}-ExecuteOnce:{1}ms", TreadNo, (end2 - begin2).Milliseconds);



            //DateTime begin3 = DateTime.Now;

            //ExecuteSqlBulkCopy();

            //DateTime end3 = DateTime.Now;

            //Console.WriteLine("Tread-{0}-ExecuteSqlBulkCopy:{1}ms", TreadNo, (end3 - begin3).Milliseconds);



            //Console.WriteLine("Tread-{0} execute successfully
", TreadNo);



        }



        private void ExecuteRowByRow()

        {

            using (SqlConnection conn = new SqlConnection(GetConnectionString))

            {

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = conn;

                DataRow dr;

                conn.Open();

                SqlTransaction tran = conn.BeginTransaction();

                try

                {

                    for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)

                    {

                        dr = dt.Rows[rowIndex];

                        cmd.CommandText = string.Format("insert into TestTable values ({0},'{1}','{2}')",

                            dr[0].ToString(), dr[1].ToString(), "ExecuteRowByRow");

                        cmd.ExecuteNonQuery();

                    }

                    tran.Commit();

                }

                catch

                {

                    tran.Rollback();

                }

            }

        }



        private void ExecuteOnce()

        {

            StringBuilder strSql = new StringBuilder();

            for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)

            {

                DataRow dr = dt.Rows[rowIndex];

                string sql = string.Format("insert into TestTable values ({0},'{1}','{2}')",

                dr[0].ToString(), dr[1].ToString(), "ExecuteOnce");

                if (strSql.ToString().Length == 0)

                {

                    strSql.Append(sql);

                }

                else

                {

                    strSql.Append(";").Append(sql);

                }



            }

            using (SqlConnection conn = new SqlConnection(GetConnectionString))

            {

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = conn;

                cmd.CommandText = strSql.ToString();

                conn.Open();

                cmd.ExecuteNonQuery();

            }

        }



        public void ExecuteSqlBulkCopy()

        {

            using (SqlConnection conn = new SqlConnection(GetConnectionString))

            {

                SqlBulkCopy bulk = new SqlBulkCopy(conn);

                bulk.DestinationTableName = "TestTable";

                bulk.BatchSize = dt.Rows.Count;



                if (dt != null && dt.Rows.Count != 0)

                {

                    conn.Open();

                    bulk.WriteToServer(dt);

                }

                bulk.Close();

            }

        }



        private string GetConnectionString

        {

            get

            {

                return @"server=SA-PCSQLEXPRESS;database=test;uid=sa;pwd=123";

            }

        }

    }

}