SQLSERVER2008表值参数批量更新效率高

SQLServer2008表值参数（Table-Valued Parameter）批量更新数据。表值参数是SQLServer2008才有的一个新特性，使用这个新特性，我们可以把一个表类型作为参数传递到函数或存储过程里。

1目标要更新的表 DestTableName；

2创建一个自定义类型表，表结构与目标表结构一样 typeTableName;

3创建一个存储过程

A)声明一个自定义类型表参数 @typeTableName typeTableName readonly;

 B)insert into DestTableName(Field1,Field2,...) select Field1,Field2,.. from @typeTableName

示例程序实现：

using System;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using com.DataAccess;

namespace ConsoleAppInsertTest
{
class Program
{
static string connectionString = SqlHelper.ConnectionStringLocalTransaction; //数据库连接字符串
static int count = 1000000; //插入的条数
static void Main(string[] args)
{
//long commonInsertRunTime = CommonInsert();
//Console.WriteLine(string.Format("普通方式插入{1}条数据所用的时间是{0}毫秒", commonInsertRunTime, count));

long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert();
Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}条数据所用的时间是{0}毫秒", sqlBulkCopyInsertRunTime, count));

long TVPInsertRunTime = TVPInsert();
Console.WriteLine(string.Format("使用表值方式（TVP）插入{1}条数据所用的时间是{0}毫秒", TVPInsertRunTime, count));
}

/// <summary>
/// 普通调用存储过程插入数据
/// </summary>
/// <returns></returns>
private static long CommonInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();

string passportKey;
for (int i = 0; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
SqlParameter[] sqlParameter = { new SqlParameter("@passport", passportKey) };
SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassport", sqlParameter);
}
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}

/// <summary>
/// 使用SqlBulkCopy方式插入数据
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
private static long SqlBulkCopyInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();

DataTable dataTable = GetTableSchema();
string passportKey;
for (int i = 0; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
DataRow dataRow = dataTable.NewRow();
dataRow[0] = passportKey;
dataTable.Rows.Add(dataRow);
}

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
sqlBulkCopy.DestinationTableName = "Passport";
sqlBulkCopy.BatchSize = dataTable.Rows.Count;
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
if (dataTable!=null && dataTable.Rows.Count!=0)
{
sqlBulkCopy.WriteToServer(dataTable);
}
sqlBulkCopy.Close();
sqlConnection.Close();

stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}

private static long TVPInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();

DataTable dataTable = GetTableSchema();
string passportKey;
for (int i = 0; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
DataRow dataRow = dataTable.NewRow();
dataRow[0] = passportKey;
dataTable.Rows.Add(dataRow);
}

SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) };
SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter);

stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}

private static DataTable GetTableSchema()
{
DataTable dataTable = new DataTable();
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("PassportKey") });

return dataTable;
}

}
}