在非托管编程模式中工作的最佳实践是什么
我有一个查询
UPDATE dbo.M_Room
SET
//do something
WHERE PK_RoomId= @RoomId AND IsActive=1 AND FK_DepartmentId =@DepartmentId
现在假设 PK_RoomId 是我的 M_Room 的 Pk 并且是自动增量字段。 因此，根据这一点，我可以使用
WHERE PK_RoomId= @RoomId
而不是
WHERE PK_RoomId= @RoomId AND IsActive=1 AND FK_DepartmentId =@DepartmentId
如果我使用第二个条件而不是第一个条件，我可以克服什么所有威胁。 如果我们没有任何物理存在的关系/约束（PK、FK 等），并且由于数据库的非托管结构而无法实现。
在这种情况下您的建议是什么？ 为了保持数据一致，应该采取哪些措施。
I have a query
UPDATE dbo.M_Room
SET
//do something
WHERE PK_RoomId= @RoomId AND IsActive=1 AND FK_DepartmentId =@DepartmentId
Now suppose PK_RoomId is my Pk of M_Room and is autoincremented field.
So according to this I could have used
WHERE PK_RoomId= @RoomId
rather than
WHERE PK_RoomId= @RoomId AND IsActive=1 AND FK_DepartmentId =@DepartmentId
What all threats I could overcome if I use the second condition rather than 1st one.
If we don't have any relationship/Constraints(PK, FK etc.) physically exists and cant implement due to unmanaged structure of database.
What will be your recommendation in such scenario.
What all things should be done to keep data consistent.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为将
WHERE更改为WHERE PK_RoomId= @RoomId并不是一个好主意。第一部分（您要保留的部分）用于识别记录。第二部分（AND IsActive=1）用于根据房间是否处于活动状态来限制更新。关于最后一部分（AND FK_DepartmentId =@DepartmentId），这可能意味着有时您只想更新属于您指定的部门的房间。这也可能有用。您到底为什么要更改查询？
I don't think it's a good idea to change the
WHEREto justWHERE PK_RoomId= @RoomId. The first part (the part you want to keep) is for identifying the record. The second part (AND IsActive=1) is used to maybe restrict the update based on whether the room is active or not. About the last part (AND FK_DepartmentId =@DepartmentId), that could mean that sometimes you only want to update the room if it belongs to the department you specified. This could also be useful.Why exactly would you want to change the query?
如果您正在使用 READ-UNCOMMITTED 事务或根本没有事务，或者数据已经在某人的屏幕上放置了很长时间，则附加条件可以使您免于隐藏更新，假设您的 // do some 做了某事IsActive 列。
这也可能是防止出错的最后一道防线（看看房间是否不活跃，然后忘记利用这一事实）。
请务必检查两种情况下更新的行数。
你的倒数第二段表明 room_id 可能不是唯一的；如果是这样的话，你总会遇到麻烦。
就我个人而言，如果我怀疑隐藏的更新可能发生，我会倾向于明确检查它们，并且我认为这种防御编程错误的形式是不寻常的。
If you are using READ-UNCOMMITTED transactions or no transactions at all, or the data has been sitting round on someone's screen for a long time, the additional conditions could save you from a buried update, presuming that your // do something does something to the IsActive column.
It could also be a final guard against just getting it wrong (seeing if the room isn't active and then forgetting to make use of the fact).
Make sure to check the number of rows updated in either case.
Your second-last paragraph suggests the room_id may not be unique when it is supposed to be; you will always have trouble if that's the case.
Myself, I'd be inclined to check explicitly for buried updates if I suspected they may occur, and I'd think that form of defending against programming errors to be unusual.