Simple Thoughts on Everything

多說無益..做就對啦..衝衝衝..

2008-04-25

(簡單範例)用SqlBulkCopy大量寫入資料庫結合交易

  • 16442
  • 0

摘要:(簡單範例)用SqlBulkCopy大量寫入資料庫結合交易

這幾天在案子在寫這東西..發現..一點都不難..一點點程式就寫完了..Hot
大家看圖說故事..這樣比較快..^^..

名字dbo.test資料庫

ghj

.aspx頁面..就放兩個button..有各自的事件

<body>
  <form id="form1" runat="server">
  <div>
      <asp:Button ID="Button1" runat="server" Text="大量寫入" onclick="Button1_Click" />
      &nbsp;<asp:Button ID="Button2" runat="server" Text="刪除" onclick="Button2_Click" />
  </div>
  </form>
</body>
 
.cs簡單的說就是先產生一個datatable..把你要寫入資料庫的東西..先寫在datatable
然後在一次大量寫入資料庫..原理很簡單..
 
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Transactions;

public partial class test_sqlBulkCopy : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {

  }

  protected void Button1_Click(object sender, EventArgs e)
  {
      //產生source table
      DataTable sourceDt = new DataTable();
      sourceDt.Columns.Add("id", typeof(int));
      sourceDt.Columns.Add("a", typeof(string));
      sourceDt.Columns.Add("b", typeof(string));
      sourceDt.Columns.Add("c", typeof(string));

      //看你要產生幾筆row先寫在datatable
      for (int i = 0; i < 10; i++)
      {
          DataRow dr = sourceDt.NewRow();
          dr["a"] = "a" + i.ToString();
          dr["b"] = "b" + i.ToString();
          dr["c"] = "c" + i.ToString();
          sourceDt.Rows.Add(dr);
      }

      //加入2.0以後的交易,記得匯入System.Transactions.dll
      using (TransactionScope myScope = new TransactionScope())
      {
          //大量寫入
          using (SqlConnection myConn = new SqlConnection(ConfigurationManager.
              ConnectionStrings["NORTHWNDConnectionString"].ConnectionString))
          {
              myConn.Open();

              using (SqlBulkCopy mySbc = new SqlBulkCopy(myConn))
              {
                  //設定
                  mySbc.BatchSize = 1000;
                  mySbc.BulkCopyTimeout = 60;

                  //處理完後丟出一個事件,或是說處理幾筆後就丟出事件
                  mySbc.NotifyAfter = sourceDt.Rows.Count;
                  mySbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(mySbc_SqlRowsCopied);

                  //更新哪個資料庫
                  mySbc.DestinationTableName = "dbo.test";

                  //column對應
                  mySbc.ColumnMappings.Add("id", "id");
                  mySbc.ColumnMappings.Add("a", "a");
                  mySbc.ColumnMappings.Add("b", "b");
                  mySbc.ColumnMappings.Add("c", "c");

                  //寫入
                  mySbc.WriteToServer(sourceDt);
                  //throw new Exception("error");

                  //完成交易
                  myScope.Complete();
              }
          }
      }
  }

  void mySbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
  {
      ClientScript.RegisterStartupScript(GetType(), "test", "alert('完成');", true);
  }

  protected void Button2_Click(object sender, EventArgs e)
  {
      //刪除測試資料
      linqToSqlDataContext db = new linqToSqlDataContext();
      db.ExecuteCommand("DELETE FROM test");
      ClientScript.RegisterStartupScript(GetType(), "test", "alert('完成');", true);
  }
}
 
這樣就完成了..順便說一下..2.0的交易真的超簡單的..包住..就給可以跑..哈哈..
大家參考一下..有問題說ㄅ..
 
參考: