Posted on 2008-06-29 10:08
玄铁剑 阅读(411)
评论(0) 编辑 收藏 引用 所属分类:
asp.net 、
C# Winform
MSDN摘要:Microsoft SQL Server 提供一个称为 bcp 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表既可以在同一个服务器上，也可以在不同服务器上）。SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下 SqlBulkCopy 提供明显的性能优势。
使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。
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<Br>";
}大批量文本数据导入:
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();
}
}
}