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