VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > temp > 简明python教程 >

  • Datatable批量导入到表

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

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

相关教程