废话不多，先上码先
using System;
using System.Text;
using System.Data;
namespace Method
{
class Contrast
{
/// <summary>
/// 对比表数据
/// </summary>
/// <param name="oldDT">原表</param>
/// <param name="newDT">新表</param>
/// <param name="keyName">参考列名（通常为主键）</param>
/// <param name="addDT">新增数据的内存表</param>
/// <param name="updateDT">更新数据的内存表</param>
/// <param name="deleteDT">删除数据的内存表</param>
public void ContrastDataTable(DataTable oldDT, DataTable newDT, string keyName, out DataTable addDT, out DataTable updateDT, out DataTable deleteDT)
{
//拷贝增、改、删的三个表的结构
addDT = newDT.Clone();
updateDT = newDT.Clone();
deleteDT = newDT.Clone();
//表的列数
int columnCount = newDT.Columns.Count;
//定义视图
DataView oldDV = oldDT.DefaultView;
DataView newDV = newDT.DefaultView;
//获得更新和删除的数据
foreach (DataRowView drv in oldDV)
{
newDV.RowFilter = keyName + " = '" + drv[keyName] + "'";
if (newDV.Count > 0)
{
if (ContrastData(drv, newDV[0]))
{
updateDT.Rows.Add(newDV[0].Row.ItemArray);
continue;
}
}
else
{
deleteDT.Rows.Add(drv.Row.ItemArray);
}
}
//获得添加的数据
newDV.RowFilter = string.Empty;//清空过滤条件
foreach (DataRowView drv in newDV)
{
oldDV.RowFilter = keyName + " = '" + drv[keyName] + "'";
if (oldDV.Count == 0)
{
addDT.Rows.Add(drv.Row.ItemArray);
}
}
}
/// <summary>
/// 数据比较
/// </summary>
/// <param name="dv1">原表视图</param>
/// <param name="dv2">新表视图</param>
/// <returns></returns>
private bool ContrastData(DataRowView dv1, DataRowView dv2)
{
//行里有一项不一样，立刻返回true
object value1;
object value2;
for (int i = 0; i < dv1.Row.ItemArray.Length; i++)
{
value1 = dv1[i].ToString();
value2 = dv2[i].ToString();
if (!value1.Equals(value2))
{
return true;
}
}
return false;
}
/// <summary>
/// 创建SQL语句
/// </summary>
/// <param name="addDT">新增数据的内存表</param>
/// <param name="updateDT">更新数据的内存表</param>
/// <param name="deleteDT">删除数据的内存表</param>
/// <param name="tableName">接受更新的表的表名</param>
/// <param name="keyName">参考列名（通常为主键）</param>
/// <param name="addSql">增加数据的SQL语句</param>
/// <param name="updateSql">更新数据的SQL语句</param>
/// <param name="deleteSql">删除数据的SQL语句</param>
public void CreateSql(DataTable addDT, DataTable updateDT, DataTable deleteDT, string tableName, string keyName, out string addSql, out string updateSql, out string deleteSql)
{
//添加数据的SQL语句拼写
{
StringBuilder addSqlSB = new StringBuilder();
for (int i = 0; i < addDT.Rows.Count; i++)
{
StringBuilder addSqlSB2 = new StringBuilder("INSERT INTO [" + tableName + "](");
for (int j = 0; j < addDT.Columns.Count; j++)
{
addSqlSB2.Append("[" + addDT.Columns[j].ColumnName + "], ");
}
addSqlSB2.Remove(addSqlSB2.Length - 2, 2);//除掉末尾的“, ”
addSqlSB2.Append(") VALUES(");
for (int j = 0; j < addDT.Columns.Count; j++)
{
if (addDT.Rows[i][j] == DBNull.Value)
{
addSqlSB2.Append("NULL, ");
}
else
{
addSqlSB2.Append("'" + FormatValue(addDT.Rows[i][j]) + "', ");
}
}
addSqlSB2.Remove(addSqlSB2.Length - 2, 2);//除掉末尾的“, ”
addSqlSB2.Append(");");
addSqlSB.Append(addSqlSB2);
}
addSql = addSqlSB.ToString();
}
//更新数据的SQL语句拼写
{
StringBuilder updateSqlSB = new StringBuilder();
for (int i = 0; i < updateDT.Rows.Count; i++)
{
StringBuilder updateSqlSB2 = new StringBuilder("UPDATE [" + tableName + "] SET ");
for (int j = 0; j < updateDT.Columns.Count; j++)
{
if (!updateDT.Columns[j].ColumnName.Equals(keyName))
{
if (updateDT.Rows[i][j] == DBNull.Value)
{
updateSqlSB2.Append("[" + updateDT.Columns[j].ColumnName + "] = NULL, ");
}
else
{
updateSqlSB2.Append("[" + updateDT.Columns[j].ColumnName + "] = '" + FormatValue(updateDT.Rows[i][j]) + "', ");
}
}
}
updateSqlSB2.Remove(updateSqlSB2.Length - 2, 2);//除掉末尾的“, ”
updateSqlSB2.Append(" WHERE [" + keyName + "] = '" + updateDT.Rows[i][keyName] + "';");
updateSqlSB.Append(updateSqlSB2);
}
updateSql = updateSqlSB.ToString();
}
//删除数据的SQL语句拼写
{
if (deleteDT.Rows.Count == 0)
{
deleteSql = string.Empty;
}
else
{
StringBuilder deleteSqlSB = new StringBuilder("DELETE FROM [" + tableName + "] WHERE [" + keyName + "] IN(");
for (int i = 0; i < deleteDT.Rows.Count; i++)
{
deleteSqlSB.Append("'" + deleteDT.Rows[i][keyName] + "'");
if (i != deleteDT.Rows.Count - 1)
{
deleteSqlSB.Append(", ");
}
}
deleteSqlSB.Append(");");
deleteSql = deleteSqlSB.ToString();
}
}
}
/// <summary>
/// 格式化值
/// </summary>
/// <param name="o">要格化式的对象</param>
/// <returns>格式化后的对象</returns>
private object FormatValue(object o)
{
if (o != null)
{
string temp = Convert.ToString(o);
if (temp.Contains("'"))
{
temp = temp.Replace("'", "''");
}
return temp;
}
return null;
}
}
}
说明：
这是用代码比较表数据，其实效率并不高。但是用在不同的数据表的数据比较的话，目前似乎只能这样（才疏学浅）。若是同一种数据库，当然有更高级的方法，利用SQL语句直接比较（当然，数据库要支持）
写个SQLSERVER的吧
-- 查询已删除的记录
SELECT * FROM [OLD] WHERE [ID] IN((SELECT [ID] FROM [OLD]) EXCEPT (SELECT [ID] FROM [NEW]));
-- 查询已增加的记录
SELECT * FROM [NEW] WHERE [ID] IN((SELECT [ID] FROM [NEW]) EXCEPT (SELECT [ID] FROM [OLD]));
-- 查询已更新的记录
SELECT * FROM [NEW] WHERE [ID] IN (((SELECT [ID] FROM [OLD]) INTERSECT (SELECT [ID] FROM [NEW])) EXCEPT (SELECT [ID] FROM((SELECT * FROM [OLD]) INTERSECT (SELECT * FROM [NEW]))AS T));
利用数据库本身的对比是非常快的，本人亲测过。
其实代码慢的主要原因，是在对比是否有更新数据的那部分，也就是上面ContrastData()方法中的代码，获取增加和删除的数据还是很快的。