SPROC 更新记录：如何处理未更改的值
我从 DAL 调用更新 SPROC，将表的所有（！）字段作为参数传递。对于最大的表来说，总共有 78 个。 我传递了所有这些参数，即使可能只有一个值发生了变化。
这对我来说似乎效率很低，我想知道如何做得更好。
我可以将所有参数定义为可选参数，并且只传递更改的参数，但我的 DAL 不知道哪些值发生了更改，因为我只是将模型对象传递给它。
我可以在更新之前在表上进行选择并比较这些值以找出哪些值发生了更改，但这可能会带来很大的开销，而且（？）
我有点卡在这里......我对你的想法非常感兴趣的这个。
编辑：忘记提及：我正在使用 C#（Express Edition）和 SQL 2008（也是 Express）。我“自己”编写的 DAL（使用 这篇文章）。 它可能不是最新的最先进的方法（从 2006 年开始，可以说是“Linq 之前的版本”，但 Linq 无论如何都只适用于 Express 中的本地 SQL 实例），但我的主要目标是学习 C#，所以我我想这还不错。
I'm calling a update SPROC from my DAL, passing in all(!) fields of the table as parameters. For the biggest table this is a total of 78.
I pass all these parameters, even if maybe just one value changed.
This seems rather inefficent to me and I wondered, how to do it better.
I could define all parameters as optional, and only pass the ones changed, but my DAL does not know which values changed, cause I'm just passing it the model - object.
I could make a select on the table before updateing and compare the values to find out which ones changed but this is probably way to much overhead, also(?)
I'm kinda stuck here ... I'm very interested what you think of this.
edit: forgot to mention: I'm using C# (Express Edition) with SQL 2008 (also Express). The DAL I wrote "myself" (using this article).
Its maybe not the latest state of the art way (since its from 2006, "pre-Linq" so to say but Linq works only for local SQL instances in Express anyways) of doing it, but my main goal was learning C#, so I guess this isn't too bad.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您可以更改 DAL（在进行更改时，一旦从新模式“重新生成”层，则不会丢弃更改），我建议传递一个包含要更改值的列的结构，以及一个包含关键列和值的结构用于更新。
这可以使用哈希表来完成，如果模式已知，则在“新”更新函数中操作它应该相当容易。
如果这是自动 DAL，则这些是使用 DAL 的一些缺点
If you can change the DAL (without changes being discarded once the layer is "regenerated" from the new schema when changes are made), i would recomend passing a structure containing the column being changed with values, and a structure kontaing key columns and values for the update.
This can be done using hashtables, and if the schema is known, should be fairly easy to manipulate this in the "new" update function.
If this is an automated DAL, these are some of the drawbacks using DALs
您可以在模型对象中实现日志化更改跟踪。这样，您可以通过每次设置新值时保存属性的先前值来跟踪对象中的任何更改。
可以通过以下两种方式之一存储此信息：
在第一个解决方案中，您可以轻松地在基类中实现此功能，并通过继承让它在所有模型对象中运行。
在第二个解决方案中，您需要创建某种容器类，该容器类将保留对所创建的任何模型对象的引用和唯一标识符，并在中央存储中记录其状态的所有更改。
这类似于许多 ORM（对象关系映射）框架都是通过这种方式实现此类功能的。
You could implement journalized change tracking in your model objects. This way you could keep track of any changes in your objects by saving the previous value of a property every time a new value is set.
This information could be stored in one of two ways:
In the first solution, you could easily implement this functionality in a base class and have it run in all model objects through inheritance.
In the second solution, you need to create some kind of container class that will keep a reference and a unique identifier to any model object that is created and record all changes in its state in a central store.
This is similar to the way many ORM (Object-Relational Mapping) frameworks achieve this kind of functionality.
有现成的 ORM 可以相对较好地支持此类场景。编写自己的 ORM 将使您失去许多这样的功能。
我发现“object.Save()”模式会导致这种行为，但是您没有理由需要遵循该模式（虽然我个人不喜欢 object.Save()，但我觉得我' m 占少数）。
您的数据层可以通过多种方式了解发生了什么变化，其中大多数都由现成的 ORM 支持。您还可以使 UI 和/或业务层足够智能，以便将这些知识传递到数据层。
我更喜欢的两个选项：
只采用集合的方法
容易改变的参数。
完全在飞行中。
There are off the shelf ORMs that support these kinds of scenarios relatively well. Writing your own ORM will leave you without many features like this.
I find the "object.Save()" pattern leads to this kind of behavior, but there is no reason you need to follow that pattern (while I'm not personally a fan of object.Save(), I feel like I'm in the minority).
There are multiple ways your data layer can know what changed and most of them are supported by off the shelf ORMs. You could also potentially make the UI and/or business layer's smart enough to pass that knowledge into the data layer.
Two options that I prefer:
methods that only take the set of
parameters that tend to change.
completely on the fly.