SqlBulkCopy加载其他源数据到SqlServer

原出處

protected void btnBulkCopy_Click(object sender, EventArgs e)
{
String ShajarConString =ConfigurationManager.ConnectionStrings["DSN_Shajar"].ConnectionString;
String NorthWindConString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
SqlConnection ShajarCon = new SqlConnection(ShajarConString);
SqlConnection NorthwindCon = new SqlConnection(NorthWindConString);
string sql1 = " SELECT ID, First_Name, Last_Name, " +
" 'Shajar' as Source FROM MailingList_Temp ";
SqlCommand ShajarCom = new SqlCommand(sql1,ShajarCon);
SqlDataReader ShajarReader;
ShajarCon.Open();

SqlBulkCopy NorthWindBulkOp= new SqlBulkCopy(NorthWindConString, SqlBulkCopyOptions.UseInternalTransaction);

NorthWindBulkOp.DestinationTableName = "Employees";

NorthWindBulkOp.ColumnMappings.Add("Id", "EmployeeID");
NorthWindBulkOp.ColumnMappings.Add("First_Name", "FirstName");
NorthWindBulkOp.ColumnMappings.Add("Last_Name", "LastName");

SqlBulkCopyColumnMapping JobTitleColMap= new SqlBulkCopyColumnMapping("Source1", "Title");
NorthWindBulkOp.ColumnMappings.Add(JobTitleColMap);
NorthWindBulkOp.BulkCopyTimeout = 500000000;

NorthWindBulkOp.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(OnRowsCopied);

NorthWindBulkOp.NotifyAfter = 1000;

ShajarReader = ShajarCom.ExecuteReader();

try
{
NorthWindBulkOp.WriteToServer(ShajarReader);
}
catch (Exception ex)
{
lblResult.Text = ex.Message;
}
finally
{
ShajarReader.Close();
}
}

private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args)
{
lblCounter.Text += args.RowsCopied.ToString() + " rows are copied
";
}

-----------
大批量文本数据导入:
private string[] currentRow;
private int myRowCount = 1;
private int myBatchCount = 1;
private long myCopiedRows = 0;
private long countStart;

private DataTable myTable = new DataTable();

private void btnGoBulkCopy_Click(object sender, EventArgs e)
{
//Create myTable DataColumn...

using(TextFieldParser myReader = new TextFieldParser(@"data.txt"))
{
// 表示文件内容是字符分隔。
myReader.TextFieldType = FieldType.Delimited;
// 定义文字文件的字符分隔符。
myReader.Delimiters = new string[] {","};
// 循环处理文字文件中所有数据列的所有字段。
while(!myReader.EndOfData)
{
try
{
currentRow = myReader.ReadFields();
// 略过标题列
if(myRowCount > 1)
{
myTable.Rows.Add(currentRow);
}
}
catch(MalformedLineException ex)
{
MessageBox.Show(ex.Message);
return;
}

myRowCount += 1;
this.lblBeingCopyedTextRows.Text = myTable.Rows.Count.ToString();
this.lblBeingCopyedTextRows.Refresh();

if(myTable.Rows.Count == 50000)
{
try
{
GoBulkCopy();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
return;
}

myTable.Rows.Clear();
myBatchCount += 1;
}
}

if(myTable.Rows.Count > 0)
{
GoBulkCopy();
}
}
}

private void GoBulkCopy()
{
SqlConnectionStringBuilder sqlconStringBuilder = new SqlConnectionStringBuilder();
sqlconStringBuilder.DataSource = @"(local)\SQLExpress";
sqlconStringBuilder.InitialCatalog = "Northwind";
sqlconStringBuilder.IntegratedSecurity = true;
using(SqlConnection con_bulkcopy =
new SqlConnection(sqlconStringBuilder.ConnectionString))
{
con_bulkcopy.Open();
SqlCommand cmdRowCount = new SqlCommand(
"SELECT COUNT(*) FROM dbo.Bulk_Target",
con_bulkcopy);

if(myBatchCount == 1)
{
countStart =
System.Convert.ToInt32(cmdRowCount.ExecuteScalar());
this.lblRowsCountBeforeBulkCopy.Refresh();
}

using(SqlBulkCopy bcp = new SqlBulkCopy(con_bulkcopy))
{
bcp.DestinationTableName = "dbo.Bulk_Target";
bcp.WriteToServer(myTable);
}

long countEnd =
System.Convert.ToInt32(cmdRowCount.ExecuteScalar());

myCopiedRows = countEnd - countStart;

this.DataGridView1.Rows.Add(
new string[] {
Convert.ToString(myBatchCount), Convert.ToString(myCopiedRows)});
this.DataGridView1.Refresh();
}
}
}