SQL批量插入表类 SqlBulkInsert
时间：2021-07-01 10:21:17
帮助过：18人阅读
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 }
View Code
SQL批量插入表类 SqlBulkInsert
标签：and ini namespace back erro init 测试 exe img
