Datatable批量导入到表

 

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

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;
}