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;
}

}
}