ASP.NET SqlDataSource 更新并创建 FK 引用
简短版本：
我有一个绑定到数据源的网格视图，其中有一个带有左连接的 SelectCommand，因为 FK 可以为空。更新时，如果 FK 为空，我想在 FK 表中创建一条记录，然后使用新记录 ID 更新父表。仅使用 SqlDataSources 可以做到这一点吗？
详细版本：
我有两个表：公司和地址。 Company.AddressId 列可以为空。在我的 ascx 页面上，我使用 SqlDataSource 选择公司和地址的左连接，并使用 GridView 来显示结果。通过让 SqlDataSource 的 UpdateCommand 和 DeleteCommand 执行由分号分隔的两个语句，我可以使用 GridView 的编辑和删除功能同时更新两个表。
我遇到的问题是 Company.AddressId 为空时。我需要做的是让数据源在地址表中创建一条记录，然后使用新的 Address.ID 更新公司表，然后照常继续更新。如果可能的话，为了一致性/简单性，我想仅使用数据源来执行此操作。是否可以让我的数据源执行此操作，或者向页面添加第二个数据源来处理其中的一些问题？
一旦我完成了这项工作，我可能就能弄清楚如何让它与 InsertCommand 一起工作，但如果你很高兴并且知道如何让它飞起来，也可以随时提供它。
谢谢。
The short version:
I have a grid view bound to a data source which has a SelectCommand with a left join in it because the FK can be null. On Update I want to create a record in the FK table if the FK is null and then update the parent table with the new records ID. Is this possible to do with just SqlDataSources?
The detailed version:
I have two tables: Company and Address. The column Company.AddressId can be null. On my ascx page I am using a SqlDataSource to select a left join of company and address and a GridView to display the results. By having my UpdateCommand and DeleteCommand of the SqlDataSource execute two statements separated by a semi-colon I am able to use the GridView's Edit and Delete functionality to update both table simultaneously.
The problem I have is when the Company.AddressId is null. What I need to have happen is have the data source create a record in the Address table and then update the Company table with the new Address.ID then proceed with the update as usual. I would like to do this with just data sources if possible for consistency/simplicity sake. Is it possible to have my data source do this, or perhaps add a second data source to the page to handle some of this?
Once I have that working I can probably figure out how to make it work with the InsertCommand as well but if you are on a roll and have an answer for how to make that fly as well feel free to provide it.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
明白为什么不能像您在这里所做的那样在 SqlDataSource 的两个语句中同时执行 INSERT 和 UPDATE 操作。
但是，正如您所知，如果您有大量流量或用户同时使用该应用程序，您可能会同时遇到问题，其中一个用户执行的操作会影响另一个用户，并且意外结果可能会级联并弄乱您的数据。一般来说，对于像您正在做的事情 - 涉及主键或外键的 INSERT 和 UPDATE，通常使用 SQL TRANSACTION。但是，您必须在 SQL 数据库上将它们作为 SQL 存储过程（或函数）执行。不过，您仍然可以从 SqlDataSource 调用它们，只需告诉它您正在调用存储过程即可。
I don't see any reason why it wouldn't be possible to do both an INSERT and UPDATE in two statements with SqlDataSource just like you are doing here.
However, just so you know, if you have a lot of traffic or users using the application at the same time, you can run into concurrently issues where one user does something that affects another user and unexpected results can cascade and mess up your data. In general, for things like what you are doing - INSERT and UPDATE involving primary or foreign keys, usually SQL TRANSACTIONs are used. But, you must execute them as SQL stored procedures (or functions), on your SQL database. You are still able to call them from your SqlDataSource however by simply telling it that you are calling a stored procedure.