1 using System.Data.SqlClient;  2   3 namespace RaywindStudio.DAL {  4   5     /// <summary>  6     /// MSSQL批量插入表  7     /// </summary>  8     public static class SqlBulkInsert {  9  10         private static bool initsql = false; 11  12         /// <summary> 13         /// SQL批量插入表 过程 14         /// </summary> 15         /// <param name="tableName">表名</param> 16         /// <param name="fields">字段，逗号分隔</param> 17         /// <param name="Values">字段值，逗号分隔</param> 18         /// <param name="RowSplit">Values行间分隔符</param> 19         /// <param name="RowCount">Values行数</param> 20         /// <param name="conn">Sql Connection</param> 21         /// <returns></returns> 22         public static bool BulkInsert(string tableName, string fields, string Values, string RowSplit, int RowCount, 23             SqlConnection conn) { 24             initSql(conn); 25             string ret= SqlAdo.ExecuteScalar("EXEC [dbo].[pSqlBulkInsert]" 26                 + " @tableName=N‘" + tableName 27                 + "‘,@fields=N‘" + fields 28                 + "‘,@values=N‘" + Values 29                 + "‘,@split=N‘" + RowSplit 30                 + "‘,@rowCount=" + RowCount.ToString(), conn).ToString(); 31             return ret == "0"; 32         } 33         private static void initSql(SqlConnection sqlconn) { 34             if (!initsql) { 35                 SqlAdo.ExecuteNonQuery( 36                 @"IF not EXISTS (SELECT * FROM dbo.SysObjects  37                             WHERE ID = object_id(N‘[fGetArrayStr]‘)  38                                 and OBJECTPROPERTY(ID,‘IsScalarFunction‘)=1)  39                             begin 40                             exec( 41                                 ‘CREATE function fGetArrayStr 42                                 ( 43                                     @str nvarchar(max), --字符串 44                                     @split nvarchar(10), --分隔符 45                                     @index int --取第几个元素 46                                     ) 47                                 returns nvarchar(4000) 48                                 as 49                                 begin 50                                     declare @location int 51                                     declare @start int 52                                     declare @next int 53                                     declare @seed int 54                                     set @str=ltrim(rtrim(@str)) 55                                     set @start=1 56                                     set @next=1 57                                     set @seed=len(@split) 58                                     set @location=charindex(@split,@str) 59                                     while @location<>0 and @index>@next 60                                     begin 61                                     set @start=@location+@seed 62                                     set @location=charindex(@split,@str,@start) 63                                     set @next=@next+1 64                                     end 65                                     if @location =0 set @location =len(@str)+1 66                                 return substring(@str,@start,@location-@start) 67                                 end‘) 68                             end", sqlconn); 69  70                 SqlAdo.ExecuteNonQuery( 71                     @"IF not EXISTS (SELECT * FROM dbo.SysObjects  72                         WHERE ID = object_id(N‘[pSqlBulkInsert]‘)  73                             and OBJECTPROPERTY(ID,‘IsProcedure‘)=1)  74                             begin 75                             exec( 76                                 ‘Create proc pSqlBulkInsert 77                                 @tableName nvarchar(50), 78                                 @fields nvarchar(500), 79                                 @values nvarchar(max), 80                                 @split nvarchar(5), 81                                 @rowCount int 82                                 as 83                                 declare @next int =1 84                                 declare @sql nvarchar(500)=N‘ Insert Into ‘+@tableName +N‘(‘+@fields +N‘) Values(‘ 85                                 declare @sqlTmp nvarchar(4000) 86                                 Begin tran     87                                     while @next<=@rowCount 88                                     begin 89                                     set @sqlTmp=@sql+dbo.fGetArrayStr(@values,@split,@next)+N‘)‘ 90                                     Exec(@sqlTmp) 91                                     if(@@error<>0) 92                                         begin  93                                             rollback 94                                             return -1            95                                         end 96                                         set @next=@next+1 97                                     end 98                                 commit 99                                 return 0", sqlconn);100 101                 initsql = true;102             }103         }104     }105 }