ado.net已经有了sqlBulkCopy, 但是那个用xml格式，网络传输数据量太大。
自己实现了一个，传输尽量少的字节。 性能没对比过，有需要的自己拿去测试。
using System.Data.SqlClient;
namespace RaywindStudio.DAL {
/// <summary>
/// MSSQL批量插入表
/// </summary>
public static class SqlBulkInsert {
private static bool initsql = false;
/// <summary>
/// SQL批量插入表 过程
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fields">字段，逗号分隔</param>
/// <param name="Values">字段值，逗号分隔</param>
/// <param name="RowSplit">Values行间分隔符</param>
/// <param name="RowCount">Values行数</param>
/// <param name="conn">Sql Connection</param>
/// <returns></returns>
public static bool BulkInsert(string tableName, string fields, string Values, string RowSplit, int RowCount,
SqlConnection conn) {
initSql(conn);
string ret= SqlAdo.ExecuteScalar("EXEC [dbo].[pSqlBulkInsert]"
+ " @tableName=N'" + tableName
+ "',@fields=N'" + fields
+ "',@values=N'" + Values
+ "',@split=N'" + RowSplit
+ "',@rowCount=" + RowCount.ToString(), conn).ToString();
return ret == "";
}
private static void initSql(SqlConnection sqlconn) {
if (!initsql) {
SqlAdo.ExecuteNonQuery(
@"IF not EXISTS (SELECT * FROM dbo.SysObjects
WHERE ID = object_id(N'[fGetArrayStr]')
and OBJECTPROPERTY(ID,'IsScalarFunction')=1)
begin
exec(
'CREATE function fGetArrayStr
(
@str nvarchar(max), --字符串
@split nvarchar(10), --分隔符
@index int --取第几个元素
)
returns nvarchar(4000)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 set @location =len(@str)+1
return substring(@str,@start,@location-@start)
end')
end", sqlconn);
SqlAdo.ExecuteNonQuery(
@"IF not EXISTS (SELECT * FROM dbo.SysObjects
WHERE ID = object_id(N'[pSqlBulkInsert]')
and OBJECTPROPERTY(ID,'IsProcedure')=1)
begin
exec(
'Create proc pSqlBulkInsert
@tableName nvarchar(50),
@fields nvarchar(500),
@values nvarchar(max),
@split nvarchar(5),
@rowCount int
as
declare @next int =1
declare @sql nvarchar(500)=N' Insert Into '+@tableName +N'('+@fields +N') Values('
declare @sqlTmp nvarchar(4000)
Begin tran
while @next<=@rowCount
begin
set @sqlTmp=@sql+dbo.fGetArrayStr(@values,@split,@next)+N')'
Exec(@sqlTmp)
if(@@error<>0)
begin
rollback
return -1
end
set @next=@next+1
end
commit
return 0", sqlconn);
initsql = true;
}
}
}
}