c# 批量处理数据录入
2021-03-23 03:27
标签：comm add cmd 分享图片 orm oid mes testing ogr c# 批量处理数据录入 标签：comm add cmd 分享图片 orm oid mes testing ogr 原文地址：https://www.cnblogs.com/dqh123/p/9469634.html
1 c# 分批处理数据录入
2
3 //using System.Text;
4
5 //using System.Data;
6
7 //using System.Data.SqlClient;
8
9 //using System;
10
11 //namespace InsertEfficiency
12
13 //{
14
15 // class Program
16
17 // {
18
19 // static void Main(string[] args)
20
21 // {
22
23 // //构造数据源
24
25 // DataTable dt = new DataTable();
26
27 // dt.Columns.Add(new DataColumn("ID", typeof(int)));
28
29 // dt.Columns.Add(new DataColumn("CreateDateTime", typeof(DateTime)));
30
31 // dt.Columns.Add(new DataColumn("TestMethod", typeof(string)));
32
33
34
35 // for (int i = 1; i 36
37 // {
38
39 // dt.Rows.Add(new object[] { i, DateTime.Now, "ExecuteSqlBulkCopy" });
40
41 // }
42
43
44
45 // Test t = new Test();
46
47
48
49 // DateTime begin1 = DateTime.Now;
50
51 // t.ExecuteRowByRow(dt);
52
53 // DateTime end1 = DateTime.Now;
54
55 // Console.WriteLine("ExecuteRowByRow:{0}ms", (end1 - begin1).Milliseconds);
56
57
58
59 // DateTime begin2 = DateTime.Now;
60
61 // t.ExecuteOnce(dt);
62
63 // DateTime end2 = DateTime.Now;
64
65 // Console.WriteLine("ExecuteOnce:{0}ms", (end2 - begin2).Milliseconds);
66
67
68
69 // DateTime begin3 = DateTime.Now;
70
71 // t.ExecuteSqlBulkCopy(dt);
72
73 // DateTime end3 = DateTime.Now;
74
75 // Console.WriteLine("ExecuteSqlBulkCopy:{0}ms", (end3 - begin3).Milliseconds);
76
77
78
79 // Console.ReadLine();
80
81 // }
82
83 // }
84
85
86
87 // class Test
88
89 // {
90
91 // public Test()
92
93 // {
94
95 // }
96
97
98
99 // public void ExecuteRowByRow(DataTable dt)
100
101 // {
102
103 // using (SqlConnection conn = new SqlConnection(GetConnectionString))
104
105 // {
106
107 // conn.Open();
108
109 // for (int rowIndex = 0; rowIndex 110
111 // {
112
113 // DataRow dr = dt.Rows[rowIndex];
114
115
116
117 // SqlCommand cmd = new SqlCommand();
118
119 // cmd.Connection = conn;
120
121 // cmd.CommandText = string.Format("insert into TestTable values ({0},‘{1}‘,‘{2}‘)",
122
123 // dr[0].ToString(), dr[1].ToString(), "ExecuteRowByRow");
124
125 // cmd.ExecuteNonQuery();
126
127 // }
128
129 // }
130
131 // }
132
133 // public void ExecuteOnce(DataTable dt)
134
135 // {
136
137 // StringBuilder strSql = new StringBuilder();
138
139 // for (int rowIndex = 0; rowIndex 140
141 // {
142
143 // DataRow dr = dt.Rows[rowIndex];
144
145 // string sql = string.Format("insert into TestTable values ({0},‘{1}‘,‘{2}‘)",
146
147 // dr[0].ToString(), dr[1].ToString(), "ExecuteOnce");
148
149 // if (strSql.ToString().Length == 0)
150
151 // {
152
153 // strSql.Append(sql);
154
155 // }
156
157 // else
158
159 // {
160
161 // strSql.Append(";").Append(sql);
162
163 // }
164
165
166
167 // }
168
169 // using (SqlConnection conn = new SqlConnection(GetConnectionString))
170
171 // {
172
173 // conn.Open();
174
175 // SqlCommand cmd = new SqlCommand();
176
177 // cmd.Connection = conn;
178
179 // cmd.CommandText = strSql.ToString();
180
181 // cmd.ExecuteNonQuery();
182
183 // }
184
185 // }
186
187
188
189 // public void ExecuteSqlBulkCopy(DataTable dt)
190
191 // {
192
193 // using (SqlConnection conn = new SqlConnection(GetConnectionString))
194
195 // {
196
197 // SqlBulkCopy bulk = new SqlBulkCopy(conn);
198
199 // bulk.DestinationTableName = "TestTable";
200
201 // bulk.BatchSize = dt.Rows.Count;
202
203
204
205 // if (dt != null && dt.Rows.Count != 0)
206
207 // {
208
209 // conn.Open();
210
211 // bulk.WriteToServer(dt);
212
213 // }
214
215 // bulk.Close();
216
217 // }
218
219 // }
220
221
222
223 // string GetConnectionString
224
225 // {
226
227 // get
228
229 // {
230
231 // //return @"server=.\mssqlserver2005;database=test;uid=sa;pwd=123456";
232
233 // return @"server=SA-PC\SQLEXPRESS;database=test;uid=sa;pwd=123";
234
235 // }
236
237 // }
238
239 // }
240
241 //}
242
243 using System.Text;
244
245 using System.Data;
246
247 using System.Data.SqlClient;
248
249 using System.Threading;
250
251 using System;
252
253 namespace InsertEfficiency
254
255 {
256
257 class Program
258
259 {
260
261 static void Main(string[] args)
262
263 {
264
265 Test t = new Test();
266
267 t.Testing();
268
269 Console.ReadLine();
270
271 }
272
273
274
275 }
276
277
278
279 class Test
280
281 {
282
283 DataTable dt = new DataTable();
284
285
286
287 public Test()
288
289 {
290
291 CreateData();
292
293 }
294
295
296
297 private void CreateData()
298
299 {
300
301 //构造数据源
302
303 dt.Columns.Add(new DataColumn("ID", typeof(int)));
304
305 dt.Columns.Add(new DataColumn("CreateDateTime", typeof(DateTime)));
306
307 dt.Columns.Add(new DataColumn("TestMethod", typeof(string)));
308
309
310
311 for (int i = 1; i 30; i++)
312
313 {
314
315 dt.Rows.Add(new object[] { i, DateTime.Now, "ExecuteSqlBulkCopy" });
316
317 }
318
319 }
320
321
322
323 public void Testing()
324
325 {
326
327 for (int i = 0; i 20; i++)
328
329 {
330
331 Thread t = new Thread(new ParameterizedThreadStart(ExcuteTesting));
332
333 t.Start(i);
334
335 }
336
337 }
338
339
340
341 private void ExcuteTesting(object TreadNo)
342
343 {
344
345 //DateTime begin1 = DateTime.Now;
346
347 //ExecuteRowByRow();
348
349 //DateTime end1 = DateTime.Now;
350
351 //Console.WriteLine("Tread-{0}-ExecuteRowByRow:{1}ms", TreadNo, (end1 - begin1).Milliseconds);
352
353
354
355 DateTime begin2 = DateTime.Now;
356
357 ExecuteOnce();
358
359 DateTime end2 = DateTime.Now;
360
361 Console.WriteLine("Tread-{0}-ExecuteOnce:{1}ms", TreadNo, (end2 - begin2).Milliseconds);
362
363
364
365 //DateTime begin3 = DateTime.Now;
366
367 //ExecuteSqlBulkCopy();
368
369 //DateTime end3 = DateTime.Now;
370
371 //Console.WriteLine("Tread-{0}-ExecuteSqlBulkCopy:{1}ms", TreadNo, (end3 - begin3).Milliseconds);
372
373
374
375 //Console.WriteLine("Tread-{0} execute successfully\r\n", TreadNo);
376
377
378
379 }
380
381
382
383 private void ExecuteRowByRow()
384
385 {
386
387 using (SqlConnection conn = new SqlConnection(GetConnectionString))
388
389 {
390
391 SqlCommand cmd = new SqlCommand();
392
393 cmd.Connection = conn;
394
395 DataRow dr;
396
397 conn.Open();
398
399 SqlTransaction tran = conn.BeginTransaction();
400
401 try
402
403 {
404
405 for (int rowIndex = 0; rowIndex )
406
407 {
408
409 dr = dt.Rows[rowIndex];
410
411 cmd.CommandText = string.Format("insert into TestTable values ({0},‘{1}‘,‘{2}‘)",
412
413 dr[0].ToString(), dr[1].ToString(), "ExecuteRowByRow");
414
415 cmd.ExecuteNonQuery();
416
417 }
418
419 tran.Commit();
420
421 }
422
423 catch
424
425 {
426
427 tran.Rollback();
428
429 }
430
431 }
432
433 }
434
435
436
437 private void ExecuteOnce()
438
439 {
440
441 StringBuilder strSql = new StringBuilder();
442
443 for (int rowIndex = 0; rowIndex )
444
445 {
446
447 DataRow dr = dt.Rows[rowIndex];
448
449 string sql = string.Format("insert into TestTable values ({0},‘{1}‘,‘{2}‘)",
450
451 dr[0].ToString(), dr[1].ToString(), "ExecuteOnce");
452
453 if (strSql.ToString().Length == 0)
454
455 {
456
457 strSql.Append(sql);
458
459 }
460
461 else
462
463 {
464
465 strSql.Append(";").Append(sql);
466
467 }
468
469
470
471 }
472
473 using (SqlConnection conn = new SqlConnection(GetConnectionString))
474
475 {
476
477 SqlCommand cmd = new SqlCommand();
478
479 cmd.Connection = conn;
480
481 cmd.CommandText = strSql.ToString();
482
483 conn.Open();
484
485 cmd.ExecuteNonQuery();
486
487 }
488
489 }
490
491
492
493 public void ExecuteSqlBulkCopy()
494
495 {
496
497 using (SqlConnection conn = new SqlConnection(GetConnectionString))
498
499 {
500
501 SqlBulkCopy bulk = new SqlBulkCopy(conn);
502
503 bulk.DestinationTableName = "TestTable";
504
505 bulk.BatchSize = dt.Rows.Count;
506
507
508
509 if (dt != null && dt.Rows.Count != 0)
510
511 {
512
513 conn.Open();
514
515 bulk.WriteToServer(dt);
516
517 }
518
519 bulk.Close();
520
521 }
522
523 }
524
525
526
527 private string GetConnectionString
528
529 {
530
531 get
532
533 {
534
535 return @"server=SA-PC\SQLEXPRESS;database=test;uid=sa;pwd=123";
536
537 }
538
539 }
540
541 }
542
543 }
544