Datatable批量导入到表

封装批量提交数据到表，用于数据同步作业

1. private string GetSelectFieldNames(DataTable dataTable, string tableName = "", string strwhere = "")
2. {
3. if (dataTable == null || dataTable.Columns.Count == 0)
4. {
5. return "";
6. }
7. if (tableName.NotHasValue())
8. {
9. tableName = dataTable.TableName;
10. }
11. var columns = (from DataColumn column in dataTable.Columns select column.ColumnName).ToList();
13. string strColumns = string.Join(",", columns);
14. string strSql = string.Format("select {0} from {1} {2}", strColumns, tableName, strwhere);
15. return strSql;
16. }
18. /// <summary>
19. /// 批量全表数据同步
20. /// 该种方式可以支持报错情况下详细的字段错误信息
21. /// </summary>
22. /// <param name="dataTable"></param>
23. /// <param name="toTableName"></param>
24. /// <returns></returns>
25. public bool BulkCopyToDataTable(DataTable dataTable, string toTableName = "")
26. {
27. if (dataTable == null || dataTable.Rows.Count == 0)
28. {
29. Tools.Debug("提交的表为空");
30. return true;
31. }
32. if (toTableName.NotHasValue())
33. {
34. toTableName = dataTable.TableName;
35. }
36. Tools.Debug("一共提交" + dataTable.Rows.Count + "条数据到" + toTableName);
38. using (var connection = new SqlConnection(DbConnectionString))
39. {
41. string strSelectSql = GetSelectFieldNames(dataTable, toTableName, "where 1=2");
42. SqlTransaction tran =null;
43. try
44. {
45. connection.Open();
46. var newDatatable = new DataTable();
47. using (var myDataAdapter = new SqlDataAdapter(strSelectSql, connection))
48. {
50. myDataAdapter.Fill(newDatatable);
51. for (int j = 0; j < dataTable.Rows.Count; j++)
52. {
53. newDatatable.Rows.Add(dataTable.Rows[j].ItemArray);
54. }
56. using (var sqlcommanBuilder = new SqlCommandBuilder(myDataAdapter))
57. {
58. tran = connection.BeginTransaction();
59. myDataAdapter.SelectCommand.Transaction = tran;
60. myDataAdapter.Update(newDatatable);
61. tran.Commit();
62. }
63. myDataAdapter.Dispose();
64. }
65. }
66. catch (SqlException ex)
67. {
68. if (tran != null) tran.Rollback();
69. Tools.Debug(String.Format("同步平台表：{0} ,执行数据库：{1} ,报错： {2}", toTableName, DbConnectionString, ex.Message));
70. Tools.Error(ex);
71. return false;
72. }
73. }
74. return true;
75. }

复制代码

　　

来源：https://www.cnblogs.com/songconglai/p/11355838.html