SQL BULK INSERT Table class Sqlbulkinsert

ADO has a sqlbulkcopy, but the one in XML format, the amount of data transmitted by the network is too large. Implement one yourself, transfer as few bytes as possible. Performance is not compared, you need to take the test.

1 usingSystem.Data.SqlClient;2 3 namespaceRaywindstudio.dal {4 5     /// <summary>6     ///MSSQL BULK INSERT Table7     /// </summary>8      Public Static classSqlbulkinsert {9 Ten         Private Static BOOLInitsql =false; One  A         /// <summary> -         ///SQL BULK Insert Table procedure -         /// </summary> the         /// <param name= "TableName" >Table name</param> -         /// <param name= "Fields" >fields, comma-delimited</param> -         /// <param name= "Values" >field values, comma delimited</param> -         /// <param name= "Rowsplit" >values between rows separator</param> +         /// <param name= "RowCount" >Values Row number</param> -         /// <param name= "conn" >SQL Connection</param> +         /// <returns></returns> A          Public Static BOOLBulkinsert (stringTableName,stringFieldsstringValues,stringRowsplit,intRowCount, at SqlConnection conn) { - INITSQL (conn); -             stringRet= Sqlado.executescalar ("EXEC [dbo]. [Psqlbulkinsert]" -+"@tableName =n '"+TableName -+"', @fields =n '"+ Fields -+"', @values =n '"+Values in+"', @split =n '"+Rowsplit -+"', @rowCount ="+rowcount.tostring (), conn). ToString (); to             returnRET = ="0"; +         } -         Private Static voidInitsql (SqlConnection sqlconn) { the             if(!initsql) { * Sqlado.executenonquery ( $                 @"IF not EXISTS (SELECT * FROM dbo. SysObjectsPanax Notoginseng WHERE ID = object_id (N ' [Fgetarraystr] ') - and OBJECTPROPERTY (ID, ' isscalarfunction ') =1) the begin + EXEC ( A ' CREATE function fgetarraystr the                                 ( + @str nvarchar (max),--String - @split nvarchar (10),--delimiter $ @index INT--Take the first few elements $                                     ) - returns nvarchar (4000) -  as the begin - declare @location intWuyi declare @start int the declare @next int - declare @seed int Wu set @str =ltrim (RTrim (@str)) - Set @start =1 About Set @next =1 $ set @seed =len (@split) - set @location =charindex (@split, @str) - While @location <>0 and @index > @next - begin A set @[email protected][email protected] + set @location =charindex (@split, @str, @start) the Set @[email protected]+1 - End $ if @location =0 set @location =len (@str) +1 the return substring (@str, @start, @[email protected]) the end ') the End", sqlconn); the  - Sqlado.executenonquery ( in                     @"IF not EXISTS (SELECT * FROM dbo. SysObjects the WHERE ID = object_id (N ' [Psqlbulkinsert] ') the and OBJECTPROPERTY (ID, ' isprocedure ') =1) About begin the EXEC ( the ' Create proc Psqlbulkinsert the @tableName nvarchar (), + @fields nvarchar (+), - @values nvarchar (max), the @split nvarchar (5),Bayi @rowCount int the  as the declare @next int =1 - declare @sql nvarchar ($) =n ' Insert into ' [email protected] +n ' (' [email protected] + N ') Values (' - declare @sqlTmp nvarchar (4000) the Begin Tran the While @next <[email protected] the begin the set @[email protected]+dbo.fgetarraystr (@values, @split, @next) +n ') ' - Exec (@sqlTmp) the if (@ @error <>0) the begin the rollback94 return-1 the End the Set @[email protected]+1 the End98 Commit About return 0", sqlconn); - 101Initsql =true;102             }103         }104     } the}

View Code

SQL BULK INSERT Table class Sqlbulkinsert