SQL批量插入表类 SqlBulkInsert

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 == "0";
        }
        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;
            }
        }
    }
}