SqlBulkCopy 类就像名字所描述的一样是专门用于批量复制的。相对于其他将数据加载到SQL Server表中的方式,使用SqlBulCopy具有明显的性能优势。这个类只能用于向SQL Server表中写入数据。但是,可以使用任何数据源,主要数据可以加载到DataTable或使用DataReader实例读取即可。
下面是一个演示的例子,关于SqlBulkCopy注意NotifyAfter属性的含义, 这个属性设定的是Copy多少行后触发SqlRowsCopied 事件,事件的加载就不多说了。
这个例子同样使用了专门处理分布式事务的TransactionScope的类,TransactionScope是ADO.net 2.0中提供了一个轻量级的事务容器,可以允许我们方便的创建分布式事务, 用户根本不需要考虑是简单事务还是分布式事务. TransactionScope会自动根据事务中涉及的对象资源判断使用何种事务管理器.。
以下例子来源于邹建的书,不过我用C#改写了。
using
System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Transactions;
using System.Data;
namespace ConsoleApplication2
{
class Program
{
private static string m_ConnStr = "Data Source=tantan\\SQLEXPRESS;Initial Catalog=tempdb;Integrated Security=True";
public static void InitTest(SqlConnection sConn)
{
using (SqlCommand iCmd = new SqlCommand())
{
iCmd.Connection = sConn;
iCmd.CommandText = "SELECT TOP 0 * INTO dbo.tb_bulk_test_objects FROM sys.objects";
iCmd.ExecuteNonQuery();
iCmd.CommandText = "SELECT TOP 0 * INTO dbo.tb_bulk_test_columns FROM sys.columns";
iCmd.ExecuteNonQuery();
}
}
private static void OnsqlRowsCopied(object sender, SqlRowsCopiedEventArgs args)
{
Console.WriteLine(String.Format("Copied {0} so far.", args.RowsCopied.ToString()));
}
private static void Bulk(SqlConnection sConn, string sSourceSQL, string sDestinationTable)
{
using (SqlCommand iCmd = new SqlCommand(sSourceSQL,sConn))
{
using (SqlBulkCopy iBcp = new SqlBulkCopy(sConn))
{
iBcp.NotifyAfter = 1000;
iBcp.DestinationTableName = sDestinationTable;
iBcp.SqlRowsCopied += OnsqlRowsCopied;
using (DataTable iDataTable = new DataTable())
{
iDataTable.Load(iCmd.ExecuteReader());
iBcp.WriteToServer(iDataTable);
}
iBcp.SqlRowsCopied -= OnsqlRowsCopied;
}
}
}
public static void BulkTest()
{
using (TransactionScope iTs = new TransactionScope())
{
using (SqlConnection iConn=new SqlConnection (m_ConnStr))
{
iConn.Open();
InitTest(iConn);
DateTime iDate;
string iSQL;
iDate = DateTime.Now;
Console .WriteLine (string.Format ("{0} start 1 batch processing",iDate));
iSQL = "SELECT o.* FROM sys.objects o, sys.columns c";
Bulk(iConn, iSQL, "dbo.tb_bulk_test_objects");
Console.WriteLine(string.Format("{0} finish 2 batch processing", iDate));
iDate = DateTime.Now;
Console.WriteLine(string.Format("{0} start 2 batch processing", iDate));
iSQL = "SELECT c.* FROM sys.objects o,sys.columns c";
Bulk(iConn, iSQL, "dbo.tb_bulk_test_columns");
Console.WriteLine(string.Format("{0} finish 2 batch processing", iDate));
}
iTs.Dispose();
}
}
private
static void Main(string[] args)
{
BulkTest();
Console.WriteLine("Done");
Console.ReadKey();
}
}
}
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Transactions;
using System.Data;
namespace ConsoleApplication2
{
class Program
{
private static string m_ConnStr = "Data Source=tantan\\SQLEXPRESS;Initial Catalog=tempdb;Integrated Security=True";
public static void InitTest(SqlConnection sConn)
{
using (SqlCommand iCmd = new SqlCommand())
{
iCmd.Connection = sConn;
iCmd.CommandText = "SELECT TOP 0 * INTO dbo.tb_bulk_test_objects FROM sys.objects";
iCmd.ExecuteNonQuery();
iCmd.CommandText = "SELECT TOP 0 * INTO dbo.tb_bulk_test_columns FROM sys.columns";
iCmd.ExecuteNonQuery();
}
}
private static void OnsqlRowsCopied(object sender, SqlRowsCopiedEventArgs args)
{
Console.WriteLine(String.Format("Copied {0} so far.", args.RowsCopied.ToString()));
}
private static void Bulk(SqlConnection sConn, string sSourceSQL, string sDestinationTable)
{
using (SqlCommand iCmd = new SqlCommand(sSourceSQL,sConn))
{
using (SqlBulkCopy iBcp = new SqlBulkCopy(sConn))
{
iBcp.NotifyAfter = 1000;
iBcp.DestinationTableName = sDestinationTable;
iBcp.SqlRowsCopied += OnsqlRowsCopied;
using (DataTable iDataTable = new DataTable())
{
iDataTable.Load(iCmd.ExecuteReader());
iBcp.WriteToServer(iDataTable);
}
iBcp.SqlRowsCopied -= OnsqlRowsCopied;
}
}
}
public static void BulkTest()
{
using (TransactionScope iTs = new TransactionScope())
{
using (SqlConnection iConn=new SqlConnection (m_ConnStr))
{
iConn.Open();
InitTest(iConn);
DateTime iDate;
string iSQL;
iDate = DateTime.Now;
Console .WriteLine (string.Format ("{0} start 1 batch processing",iDate));
iSQL = "SELECT o.* FROM sys.objects o, sys.columns c";
Bulk(iConn, iSQL, "dbo.tb_bulk_test_objects");
Console.WriteLine(string.Format("{0} finish 2 batch processing", iDate));
iDate = DateTime.Now;
Console.WriteLine(string.Format("{0} start 2 batch processing", iDate));
iSQL = "SELECT c.* FROM sys.objects o,sys.columns c";
Bulk(iConn, iSQL, "dbo.tb_bulk_test_columns");
Console.WriteLine(string.Format("{0} finish 2 batch processing", iDate));
}
iTs.Dispose();
}
}
private
static void Main(string[] args)
{
BulkTest();
Console.WriteLine("Done");
Console.ReadKey();
}
}
}