使用前数据库表必须有主键,并是自动增长的,红色代码为关键代码
/// <summary>
/// 插入或更新数据
/// </summary>
/// <param name="selSql">查询数据库表数据</param>
/// <param name="localDt">本地表</param>
/// <param name="identityDBId">数据库表标识ID</param>
/// <param name="identityLocalId">本地表标识ID</param>
/// <returns></returns>
public bool InsertOrUpdateDataTable(string selSql, DataTable localDt, string identityDBId, string identityLocalId)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter(selSql, con);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
// ------SqlCommandBuilder 对象来自动生成用于单表更新的 Transact-SQL 语句
DataTable dtDb = new DataTable();
da.Fill(dtDb);
DataColumnCollection dicDbColu = dtDb.Columns;
foreach (DataRow se in localDt.Rows)
{
string id = se[identityLocalId].ToString();
DataRow[] drs = dtDb.Select(identityDBId + "='" + id + "'");
if (drs != null && drs.Length > 0)
{
foreach (DataColumn c in localDt.Columns)
{
if (dicDbColu.Contains(c.ColumnName))
drs[0][dicDbColu[c.ColumnName]] = se[c.ColumnName];
}
}
else
{
DataRow dataRow = dtDb.NewRow();
foreach (DataColumn c in localDt.Columns)
{
dataRow[dicDbColu[c.ColumnName]] = se[c.ColumnName];
}
dtDb.Rows.Add(dataRow);
}
}
return da.Update(dtDb) > 0 ? true : false;
}
}