[ASP.NET] 使用 SqlBulkCopy 批次新增大量資料

2013-01-02

12975
0
ASP.NET (C#)
2013-05-18

摘要:[ASP.NET] 使用 SqlBulkCopy 批次新增大量資料

前言

當要處理資料轉檔的時候常常會碰到會需要一次新增大量的資料到資料庫中的問題，如果來源資料量非常大的話，程式常常會跑到 Timeout，在這裡介紹使用 SqlBulkCopy 類別新增大量資料的方法，下面範例中我會使用三種方式新增大量資料:

單筆新增
串字串每10筆新增
使用SqlBulkCopy新增

在經過測試之後，就可以瞭解此三種方式效能上的差別了。

範例

1.單筆新增


protected void Button1_Click(object sender, EventArgs e)
{
    DeleteData();
    Stopwatch sw = new Stopwatch();
    sw.Start();
    for (int i = 1; i < 999; i++)
    {
        using (SqlConnection sqlConn = new SqlConnection(connectionString))
        {

            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.Connection = sqlConn;
            sqlCmd.CommandText = "INSERT INTO Products (PK, ProductName) VALUES (@PK, @ProductName)";
            sqlCmd.Parameters.AddWithValue("@PK", i);
            sqlCmd.Parameters.AddWithValue("@ProductName", "ProductName" + i);
            sqlCmd.Connection.Open();
            sqlCmd.ExecuteNonQuery();
        }
    }
    sw.Stop();
    Label1.Text = string.Format("耗時:{0}毫秒", sw.ElapsedMilliseconds.ToString());
}

單筆新增裡使用每次產生一個連線與命令去處理新增，在大量新增資料的情況下，這種方式是最耗時耗資源的。

2.串字串每10筆新增


protected void Button2_Click(object sender, EventArgs e)
{
    DeleteData();
    Stopwatch sw = new Stopwatch();
    sw.Start();
    StringBuilder sbCmd = new StringBuilder();
    List lstParams = new List();
    for (int i = 1; i < 999; i++)
    {
        if ((i % 10) == 0)
        {
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.CommandText = sbCmd.ToString();
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.Connection = sqlConn;
                sqlCmd.Parameters.AddRange(lstParams.ToArray());
                sqlCmd.Connection.Open();
                sqlCmd.ExecuteNonQuery();
                sbCmd.Clear();
                lstParams.Clear();
            }
        }
        else
        {
            sbCmd.AppendFormat(" INSERT INTO Products (PK, ProductName) VALUES (@PK{0}, @ProductName{1}); ", i, i);
            lstParams.Add(new SqlParameter("@PK" + i, i));
            lstParams.Add(new SqlParameter("@ProductName" + i, "ProductName" + i));
        }
    }
    if (sbCmd.Length > 0)
    {
        using (SqlConnection sqlConn = new SqlConnection(connectionString))
        {
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandText = sbCmd.ToString();
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.Connection = sqlConn;
            sqlCmd.Parameters.AddRange(lstParams.ToArray());
            sqlCmd.Connection.Open();
            sqlCmd.ExecuteNonQuery();
            sbCmd.Clear();
            lstParams.Clear();
        }
    }
    sw.Stop();
    Label2.Text = string.Format("耗時:{0}毫秒", sw.ElapsedMilliseconds.ToString());
}

在串字串的方法裡，這個方式已經比上一種方式好很多了，將 CommandText 累計至一定的數量後再開起連線執行命令。

3.使用 SqlBulkCopy 新增


protected void Button3_Click(object sender, EventArgs e)
{
    DeleteData();
    Stopwatch sw = new Stopwatch();
    sw.Start();
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlTransaction sqlTrans = connection.BeginTransaction();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, sqlTrans))
        {
            DataTable dt = GetTempTableData();
            bulkCopy.DestinationTableName = "dbo.Products";
            try
            {
                bulkCopy.WriteToServer(dt);
            }
            catch (Exception)
            {
                sqlTrans.Rollback();
                return;
            }
            sqlTrans.Commit();
        }
    }
    sw.Stop();
    Label3.Text = string.Format("耗時:{0}毫秒", sw.ElapsedMilliseconds.ToString());
}

最後使用 SqlBulkCopy 來新增資料，此方法可以一次將大量的來源資料寫入MSSQL，來源可以是 DataRow[] 、DataTable、DataReader，本範例的使用方式為新一次建立好要新增入資料庫的DataTable，再將DataTable使用WriteToServer方法將DataTable內的資料複製到SqlBulkCopy物件的目標資料表中，而整體執行上的效能快上已上兩種方法之多喔，最後讓我們實際測試看看所消耗的時間比較一下。