我们创建一个用于测试的数据库和表。代码示例如下：
```sql
-- 创建数据库
CREATE DATABASE BulkTestDB;
USE BulkTestDB;
-- 创建表
CREATE TABLE BulkTestTable(
Id INT PRIMARY KEY,
UserName NVARCHAR(32),
Pwd VARCHAR(16)
);
```
首先连接数据库：
```csharp
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString); // 连接数据库
sqlConn.Open(); // 打开数据库连接
```
```csharp
SqlCommand sqlComm = new SqlCommand();
sqlCommmandText = "insert into BulkTestTable(Id, UserName, Pwd) values (@p0, @p1, @p2)"; // 参数化SQL语句
sqlComm.Parameters.Add("@p0", SqlDbType.Int);
sqlComm.Parameters.Add("@p1", SqlDbType.NVarChar);
sqlComm.Parameters.Add("@p2", SqlDbType.VarChar);
sqlCommmandType = CommandType.Text;
sqlComm.Connection = sqlConn; // 设置连接对象关联的命令对象
```
```csharp
```csharp
public static void BulkToDB(DataTable dt)
{
using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString))
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn))
{
bulkCopy.DestinationTableName = "BulkTestTable";
bulkCopy.BatchSize = dt.Rows.Count;
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
throw ex;
}
}
}
}
```
为了构建一个测试环境，我们创建了一个简单的DataTable获取方法：
```csharp
public static DataTable GetTableSchema()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]
{
new DataColumn("Id",typeof(int)),
new DataColumn("UserName",typeof(string)),
new DataColumn("Pwd",typeof(string))
});
return dt;
}
```
```csharp
static void Main(string[] args)
{
Stopwatch sw = new Stopwatch();
for (int multiply = 0; multiply < 10; multiply++)
{
DataTable dt = Bulk.GetTableSchema();
for (int count = multiply 100000; count < (multiply + 1) 100000; count++)
{
DataRow r = dt.NewRow();
r[0] = count;
r[1] = string.Format("User-{0}", count multiply);
r[2] = string.Format("Pwd-{0}", count multiply);
dt.Rows.Add(r);
}
sw.Start();
Bulk.BulkToDB(dt);
sw.Stop();
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
}
Console.ReadLine();
}
```
高效数据库操作：超越Bulk的速度挑战
