/// <summary>
/// 以dataset更新数据库
/// </summary>
/// <param name="ds_source">导入更新(insert,delete,update)数据</param>
public bool UseDataSetUpdateDataBase(DataSet ds_source)
{
bool _return = false;
try
{
for (int i = 0; i < ds_source.Tables.Count; i++)
{
//*
MySqlDataAdapter sda = new MySqlDataAdapter("select * from " + ds_source.Tables[i].TableName, this.connectionString);
DataTable datatable = new DataTable();
sda.Fill(datatable);
datatable.PrimaryKey = new DataColumn[] { datatable.Columns[datatable.Columns[0].ColumnName.ToString()] };
//解决DataTable.Merge(DataTable)合并数据而导致列类型冲突问题
//↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
DataTable targertDatatable = UpdateDataTableDataType(datatable, ds_source.Tables[i]);
datatable.Merge(targertDatatable);
MySqlCommandBuilder cmdBuilder = new MySqlCommandBuilder(sda);
MySqlCommand cmd = cmdBuilder.GetUpdateCommand();
cmd.CommandTimeout = 0;
sda.UpdateCommand = cmd;
sda.Update(datatable);
}
}
catch (Exception ex)
{
throw ex;
}
return _return;
}
/// <summary>
/// 修改数据表DataTable列的类型和记录值(步骤:1.克隆表结构,2.复制表数据,3.返回结果)
/// </summary>
/// <param name="sourceDataTable">克隆表结构</param>
/// <param name="targDataTable">复制表数据</param>
/// <returns></returns>
private DataTable UpdateDataTableDataType(DataTable sourceDataTable,DataTable targetDataTable)
{
DataTable datatableResult = new DataTable();
datatableResult = sourceDataTable.Clone();
foreach (DataRow row in targetDataTable.Rows)
{
DataRow rowNew = datatableResult.NewRow();
foreach (DataColumn column in targetDataTable.Columns)
{
rowNew[column.ColumnName.ToString()] = row[column.ColumnName.ToString()];
}
datatableResult.Rows.Add(rowNew);
}
return datatableResult;
}
/// 以dataset更新数据库
/// </summary>
/// <param name="ds_source">导入更新(insert,delete,update)数据</param>
public bool UseDataSetUpdateDataBase(DataSet ds_source)
{
bool _return = false;
try
{
for (int i = 0; i < ds_source.Tables.Count; i++)
{
//*
MySqlDataAdapter sda = new MySqlDataAdapter("select * from " + ds_source.Tables[i].TableName, this.connectionString);
DataTable datatable = new DataTable();
sda.Fill(datatable);
datatable.PrimaryKey = new DataColumn[] { datatable.Columns[datatable.Columns[0].ColumnName.ToString()] };
//解决DataTable.Merge(DataTable)合并数据而导致列类型冲突问题
//↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
DataTable targertDatatable = UpdateDataTableDataType(datatable, ds_source.Tables[i]);
datatable.Merge(targertDatatable);
MySqlCommandBuilder cmdBuilder = new MySqlCommandBuilder(sda);
MySqlCommand cmd = cmdBuilder.GetUpdateCommand();
cmd.CommandTimeout = 0;
sda.UpdateCommand = cmd;
sda.Update(datatable);
}
}
catch (Exception ex)
{
throw ex;
}
return _return;
}
/// <summary>
/// 修改数据表DataTable列的类型和记录值(步骤:1.克隆表结构,2.复制表数据,3.返回结果)
/// </summary>
/// <param name="sourceDataTable">克隆表结构</param>
/// <param name="targDataTable">复制表数据</param>
/// <returns></returns>
private DataTable UpdateDataTableDataType(DataTable sourceDataTable,DataTable targetDataTable)
{
DataTable datatableResult = new DataTable();
datatableResult = sourceDataTable.Clone();
foreach (DataRow row in targetDataTable.Rows)
{
DataRow rowNew = datatableResult.NewRow();
foreach (DataColumn column in targetDataTable.Columns)
{
rowNew[column.ColumnName.ToString()] = row[column.ColumnName.ToString()];
}
datatableResult.Rows.Add(rowNew);
}
return datatableResult;
}