数据写入数据库效率比较（好的方法效率提高20倍）

 

长连接

一条数据

长连接耗时：0

短连接耗时：0

DataTable耗时：16

 

10条数据

长连接耗时：0

短连接耗时：16

DataTable耗时：15

 

100条数据

长连接耗时：62

短连接耗时：63

DataTable耗时：78

 

1000条数据

 

长连接耗时：625

短连接耗时：719

DataTable耗时：734

SqlBulkCopy：16

 

[番茄花园1] 10000条数据

长连接耗时：6390

短连接耗时：8296

DataTable耗时：7265

 

100000条数据

长连接耗时：62484

短连接耗时：70157

DataTable耗时：80125

SqlBulkCopy：3203

 

长连接、短连接、以及DataTable方式这个速度太慢，不能满足需求

后来我认真查看了MSDN，发现还有一种更优的方式。

SqlBulkCopy

通过测试数据，我们知道插入100000条数据时时间减少了个3秒，这个可是效率提高了20倍。

 

测试代码：

  private void button1_Click(object sender, EventArgs e)

        {

            SqlConnection conn0 = new SqlConnection();

            conn0.ConnectionString = "server=192.168.10.104; database=DBTest; uid=sa;pwd=sa";

            conn0.Open();

            string sql0 = "delete InsertTest";

            SqlCommand cmd0 = new SqlCommand(sql0, conn0);

            //长连接

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = "server=192.168.10.104; database=DBTest; uid=sa;pwd=sa";

            conn.Open();

            cmd0.ExecuteNonQuery();

            int times = int.Parse(textBox1.Text.Trim());

            string sql = "insert into InsertTest(testColumn1,testColumn2,testColumn3,testColumn4)values('测试数据1','测试数据2','测试数据3','测试数据4')";

            label8.Text = DateTime.Now.ToString("yy-MM-dd HH:mm:ss fff");

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

            {

                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.ExecuteNonQuery();

            }

            label11.Text = DateTime.Now.ToString("yy-MM-dd HH:mm:ss fff");

            conn.Close();

            cmd0.ExecuteNonQuery();

            //短连接

         

            label9.Text = DateTime.Now.ToString("yy-MM-dd HH:mm:ss fff");

            for (int j = 0; j < times; j++)

            {

                SqlConnection conn1 = new SqlConnection();

                conn1.ConnectionString = "server=192.168.10.104; database=DBTest; uid=sa;pwd=sa";

                if (conn1.State == ConnectionState.Closed)

                    conn1.Open();

                using (conn1)

                {

                    SqlCommand cmd = new SqlCommand(sql, conn1);

                    cmd.ExecuteNonQuery();

                }

            }

            label12.Text = DateTime.Now.ToString("yy-MM-dd HH:mm:ss fff");

 

            cmd0.ExecuteNonQuery();

//DataTable

            DataTable dt = CreateDataTable(times);

            label10.Text = DateTime.Now.ToString("yy-MM-dd HH:mm:ss fff");

            string sql1 = "select * from InsertTest";

            SqlConnection conn2 = new SqlConnection();

            conn2.ConnectionString = "server=192.168.10.104; database=DBTest; uid=sa;pwd=sa";

              if (conn2.State == ConnectionState.Closed)

                    conn2.Open();

              using (conn2)

              {

                  System.Data.SqlClient.SqlDataAdapter adt = new System.Data.SqlClient.SqlDataAdapter(sql1, conn);

                  System.Data.SqlClient.SqlCommandBuilder builder = new System.Data.SqlClient.SqlCommandBuilder(adt);

                  adt.InsertCommand = builder.GetInsertCommand();

                  adt.Update(dt).ToString();

              }

            label13.Text = DateTime.Now.ToString("yy-MM-dd HH:mm:ss fff");

            cmd0.ExecuteNonQuery();

            conn0.Close();

        }

        private DataTable CreateDataTable(int count)

        {

            DataTable dt = new DataTable();

            dt.Columns.Add("testColumn1");

            dt.Columns.Add("testColumn2");

            dt.Columns.Add("testColumn3");

            dt.Columns.Add("testColumn4");

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

            {

                DataRow dr = dt.NewRow();

                dr["testColumn1"] = "测试数据1";

                dr["testColumn2"] = "测试数据2";

                dr["testColumn3"] = "测试数据3";

                dr["testColumn4"] = "测试数据4";

                dt.Rows.Add(dr);

            }

            return dt;

        }

 

要是有更好的方法请大家不吝赐教，谢谢大家

联系方式：bobui@163.com

QQ:125941562