使用具有最小/部分更新存储过程的 DataTable
3.33/5 (5投票s)
2006年2月27日
6分钟阅读
54220
874
概述了使用 SqlDataAdapter 从 DataTable 执行更新的替代方法，以实现最小更新。
引言
有时希望最小化对数据库表的更新，例如，如果某些行中的某些值在特定条件下被有效锁定，或者如果希望将审计跟踪保留在最少的日志条目中。
本文概述的技术允许通过对 System.Data.DataSet 的更改来执行数据库表中行的部分更新。这意味着并非每一行中的所有列都会被更新 - 只有在 System.Data.DataSet 中实际修改了数据值的那些列才会更新。
这是通过结合使用具有正确选择的 DataSet.PrimaryKey 属性的 DataSet、专门编写以确保最小更新的存储过程以及下面详细介绍的 UpdateHelper 类来实现的。
背景
在 .NET 中处理关系数据的主要方法是使用 System.Data.DataSet。DataSet 结合 System.Data.SqlClient.SqlDataAdapter 是将数据获取到 SQL Server 实例并从中存储数据的强大方法。但是，SqlDataAdapter.Update() 方法的底层机制意味着对 DataTable 中行的任何更改都将导致在数据库表中更新整行。
导致此类问题的示例是，一旦行被使用，某些值就不能更改。如果应用程序的编写方式是直接与数据库交互，那么这不是问题；应用程序代码可以调整以处理特定情况。但是，如果您在多个应用程序层之间传递数据或处理许多表，那么使用标准的 SqlDataAdapter.Update() 将是首选，但由于 Update() 会尝试更新所有列，因此您的数据库表实际上被锁定了。
这就是这项技术发挥作用的地方，因为它以与 SqlDataAdapter 类似的方式自动化了更新过程。
使用代码
UpdateHelper.Update() 旨在替代 SqlDataAdapter.Update()，并与为此目的专门编写的存储过程结合使用。
首先要注意的是，在此代码中，对于不进行更新的任何存储过程参数，都会传递 NULL - 明显的限制是您无法使用存储过程将任何值设置为 null。
另一个需要注意的点是 DataSet 不会从数据库表中重新填充，假定对 DataSet 所做的更改将出现在数据库表中 - 如果您使用 UpdateHelper.Update() 将新行插入到使用标识列或任何其他自动编号机制的表中，那么建议在执行 UpdateHelper.Update() 后使用 SqlDataAdapter 重新填充 DataSet。
填充 DataSet
这和往常一样完成，下面的示例使用了示例项目中使用的强类型 DataSet
PartialUpdateDS ds = new PartialUpdateDS();
SqlConnection conn = new SqlConnection("Server=SERVERNAME;" +
"Integrated Security=sspi;" +
"Database=PartialUpdateTest");
da.SelectCommand = new SqlCommand("Select configId," +
"forUnitType,data from UnitConfig",conn);
da.Fill(ds.UnitConfig);
更新数据库
当我们想将更改保存到数据库时，我们只需将对 SqlDataAdapter 的调用替换为对 UpdateHandler 的调用。下面的示例使用了示例项目中使用的强类型 DataSet
UpdateHelper updh = new UpdateHelper();
updh.UpdateCommand = GetUnitConfigUpdateCommand();
updh.UpdateCommand.Connection.Open();
updh.Update(ds.UnitConfig);
运行示例应用程序
这是一个简单的 Visual Studio 2005 项目，用于演示 UpdateHelper 在下面列出的存储过程中的使用。
示例应用程序设置
运行示例应用程序需要执行几个步骤。
- 下载 Zip 文件并将其解压缩到本地驱动器。
- 运行 SQL 脚本 <installation dir>\PartialUpdatesFromDataSet_app\PartialUpdateDB.sql - 这将创建测试数据库。
- 如有必要，修改 <installation dir>\PartialUpdatesFromDataSet_app\PartialUpdateTest.exe.config 中的
ConnectionString属性以指向新创建的数据库（例如，如果您的 SQL Server 实例在另一台计算机上或不使用 SSPI）。 - 执行 <installation dir>\PartialUpdatesFromDataSet_app\PartialUpdateTest.exe 中的应用程序。
示例项目设置
步骤与上面相同，只是文件夹将是 PartialUpdatesFromDataSet_vs2003，并且需要更改 App.config 而不是 PartialUpdateTest.exe.config。
执行
应用程序运行时，您将看到 UnitConfig 表。要看到使用 SqlDataAdapter.Update() 时出现的问题，请修改 configId 为 1 或 2 的 data 列，然后按“Update”。即使您只应该修改了 data 列，我们仍然会收到一个由存储过程触发的 SqlException，告诉您 UnitConfig.forUnitType 不能被更改。
现在使用 UpdateHelper.Update() 执行相同的操作 - 在按“Update”之前勾选“Use UpdateHelper” CheckBox，新值将成功保存到数据库。
UpdateHelper 将成功更新配置 1 和 2 的数据值，但不会更新 forUnitType 列，因为它已存在于关系中。
SqlDataAdapter 根本无法更新配置 1 和 2。
SqlDataAdapter 和 UpdateHelper 都可以更新配置 3 和 4，因为它们未被分配（即，未在 Unit 表中引用）。
代码解释
示例存储过程
下面是示例应用程序中使用的存储过程。需要注意的点是
- 它在执行相关更新之前会检查每个参数是否不为 null。
- 在某些情况下（即，如果 UnitConfig 行在 Unit 表中被引用），它将不允许更新
UnitConfig.forUnitType列。
此存储过程可以与 SqlDataAdapter 和 UpdateHandler 一起使用，但是，如果相关行在 Unit 表中使用，则 SqlDataAdapter.Update() 方法将始终失败，即使只修改了 data 列。
ALTER PROCEDURE dbo.p_UnitConfig_Update
(
@configId INT,
@forUnitType INT = NULL,
@data NVARCHAR(50) = NULL
)
AS
SET NOCOUNT ON
BEGIN TRANSACTION
-- deal with changes to the forUnitType column
IF @forUnitType IS NOT NULL
BEGIN
-- we can only update UnitConfig.forUnitType
-- if this entry is not
-- referenced by any entry in the Unit table
-- note that this check would be
-- better placed in a trigger
IF EXISTS
(
SELECT * FROM Unit
INNER JOIN UnitConfig
ON Unit.configId = UnitConfig.configId
AND Unit.configId = @configId
)
BEGIN
RAISERROR('UnitConfig.forUnitType cannot be changed
while the UnitConfig is in use',16,1)
ROLLBACK TRANSACTION
RETURN -100
END
UPDATE UnitConfig
SET forUnitType = @forUnitType
WHERE configId = @configId
IF @@Error <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Error updating into
UnitConfig.forUnitType',16,1)
RETURN -100
END
END
-- we can update UnitConfig.data regardless
-- of wether this entry is used in the Unit table
IF @data IS NOT NULL
BEGIN
UPDATE UnitConfig
SET data = @data
WHERE configId = @configId
IF @@Error <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Error updating UnitConfig.data',16,1)
RETURN -100
END
END
COMMIT TRANSACTION
RETURN
UpdateHelper 类
用于实现此目的的类非常简单，仅由 SqlCommand 属性和 Update() 过程组成。
对于新行和已删除的行，UpdateHelper.Update() 以与 SqlDataAdapter.Update() 类似的方式执行 InsertCommand 和 DeleteCommand（但应注意，这绝不是 SqlDataAdapter.Update() 方法功能的完整实现）。
当它遇到已修改的 DataRow 时，它会将 DataRowVersion.Original 条目与 DataRowVersion.Current 条目相同的任何 DataRow 值替换为 DBNull.Value。
为了确保更新正确的数据库条目，它使用 DataTable.PrimaryKey 属性来确保存储过程中始终设置数据库表的主键。因此，始终需要确保 DataTable 是使用准确反映数据库表主键的主键构建的。
public class UpdateHelper
{
private SqlCommand _upd;
private SqlCommand _ins;
private SqlCommand _del;
public SqlCommand UpdateCommand{get{return _upd;}set{_upd=value;}}
public SqlCommand InsertCommand{get{return _ins;}set{_ins=value;}}
public SqlCommand DeleteCommand{get{return _del;}set{_del=value;}}
public void Update(DataTable dt)
{
DataColumn [] pk = dt.PrimaryKey;
DataRowCollection drc = dt.Rows;
SqlCommand cmd;
foreach (DataRow currRow in drc)
{
if(currRow.RowState == DataRowState.Modified)
{
#region Update using minimal updates
cmd=_upd;
foreach (SqlParameter param in cmd.Parameters)
{
object current = currRow[param.SourceColumn];
//if this column has changed
if(!current.Equals(currRow[param.SourceColumn,DataRowVersion.Original]))
{
//update it
param.Value=current;
}
else
{
//if it is the same check if it is in the primary key
foreach ( DataColumn c in pk )
{
//if it is in the PK
if(c.ColumnName.Equals(param.SourceColumn))
{
//include the value
param.Value=current;
break;
}
else
{
//otherwise trigger the stored procedure
//to ignore it by setting it to DBNull.Value
param.Value=DBNull.Value;
}
}
}
}
#endregion
}
else
{
#region Update using all parameters
switch ( currRow.RowState )
{
case DataRowState.Deleted:
cmd=_del;
break;
case DataRowState.Added:
cmd=_ins;
break;
case DataRowState.Unchanged:
continue;
default:
throw new DataAccessException(currRow.RowState.ToString()+
" is not handled here.");
}
foreach (SqlParameter param in cmd.Parameters)
{
param.Value=currRow[param.SourceColumn];
}
#endregion
}
cmd.ExecuteNonQuery();
}
}
}
注释
最好将 forUnitType 列上的附加约束放在触发器中，这样就没人可以直接操作表并导致问题；为了清晰起见，我在本示例的存储过程中包含了此检查。