使用gridview删除命令从多个表中删除

发布于 2024-11-07 03:51:09 字数 1128 浏览 4 评论 0原文

我有一个利用 sql 数据源的网格视图。现在我想使用网格视图的删除命令删除一条记录，问题是单个记录基于从多个表收集的信息，所以我必须从所有这些表中删除部分信息才能完全删除该记录，这是我的表结构。

项目
ProjectArticles
ArticleStatus
ProjectAssignments

Projects 是一个表，其中包含有关项目的完整信息，例如项目标题、客户名称等，它具有主键“project_id”

ProjectArticles 是一个表，其中包含有关与一个项目关联的文章数的信息，例如，如果一个项目有 3 篇文章，则该表有 3 行，其中包含以下数据

article_id  project_id

1 --------- 1 
2 --------- 1 
3 --------- 1

，其中“article_id”是主键。

文章状态是一个表，其中包含有关一篇文章的信息，例如

status_id- article_id- filename ---- writer_status- editor_status- sales_status

1 -------- 1 --------- Any filename -- done --------- pending ------ pending

“status_id”是主键

最后 ProjectAssignments 是一个表，其中包含有关将哪个项目分配给哪个作者的数据，它以 assignment_id 作为主键，并使用 project_id 作为外键，类似于上面所示。

有什么想法如何删除包含其他表中所有相关信息的完整项目吗？

我已经做了这个示例查询，但它不起作用，sql Management Studio 说“无法解析查询文本”。

DELETE P, A FROM Projects AS P, ProjectArticles AS A WHERE P.project_id = A.project_id AND P.project_id = @project_id

在上面的查询文本中，我只是使用了两个表来检查它是否工作，但它不工作，任何帮助将不胜感激。

原文

I have a grid view utilizing sql data source. Now I want to delete a record using grid view's delete command, the problem is that a single record is based on information collected from multiple tables, so I have to delete parts of information from all those tables to completely remove the record, here is my table structure.

Projects
ProjectArticles
ArticleStatus
ProjectAssignments

Projects is a table with complete information about projects like, project title, client name etc, it has primary key "project_id"

ProjectArticles is a table that has information about how many articles are associated with one project for example if a project has 3 articles then this table has 3 rows with the following data

article_id  project_id

1 --------- 1 
2 --------- 1 
3 --------- 1

where "article_id" is the primary key.

Article status is a table with information regarding one article such as

status_id- article_id- filename ---- writer_status- editor_status- sales_status

1 -------- 1 --------- Any filename -- done --------- pending ------ pending

"status_id" is primary key

And finally ProjectAssignments is a table with data about which project is assigned to which writer it has assignment_id as primary key and using project_id as foreign key similar as shown above.

Any ideas how can I delete a complete project with all the relevant information in other tables ?

I have made this example query but it is not working, sql management studio saying "unable to parse query text".

DELETE P, A FROM Projects AS P, ProjectArticles AS A WHERE P.project_id = A.project_id AND P.project_id = @project_id

In the query text above I just used two tables to check if its working or not but its not working, any help will be appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

老街孤人 2024-11-14 03:51:09

当您想要删除与用作外键的特定主键相关的所有行时，级联非常有用
在参考表中。

例如：- 主表或子表
Tab1(Id int (主键) ,名称 varchar(10))
子表
Tab2(Class int, Id int (refrenctial key of Tab1(Id),address varchar(10))

现在，当您想从记录中删除 1 个数字 id 时，“ON DELETE CASCADE”是最好的，但当您只想删除一个时
记录在子表中，则事务就好了。

回复收藏 0 原文

魔法少女 2024-11-14 03:51:09

有几种方法可以解决这个问题。

您可以在 SQL Server 中声明约束并设置 Cascade。当您根据所使用的 SQL 服务器进行删除时，您可以从 P where 条件级联删除，这将告诉 SQL 服务器进行删除，然后按照所有引用向下级联此删除。

如果内存中包含所有内容，通常您可以在 DAL 中设置这样的条件，如果 DAL 支持级联，则在 DAL 中设置级联。风险在于，您会遇到并发问题，并且会在最终确定加载成功之前将所有可能引用的数据加载到内存中。

请注意，级联通常是关闭的，因为如果出现问题，它可能会变成严重的问题。许多人更愿意编写一个结论性查询，将您的条件按顺序删除作为事务。

Begin Transaction
    Delete from Tables_Child where ID in (--your query)
    Delete from Tables_Child2 where ID in (--your query)
    (your_Query)
Commit Transaction

这样你就知道将要发生什么，并且可以控制它，而不会陷入循环。

There are a few ways to attack this.

You can declare your constraints in the SQL server and set Cascade on. When you delete depending on the SQL server you are using you can Delete from P where condition Cascade this will tell the SQL server to delete and then follow all references to cascade this delete down.

Often you can set such a condition in a DAL if you have everything in memory though, setting cascade on in the DAL if it supports it. Risk is that you have a concurrency issue and an issue to load all possible referenced data in memory before you know conclusively that it will succeed.

Note that cascade is usually turned off because it can turn into a serious problem if you get something wrong. Many people would prefer to write a conclusive query that deletes using your condition in order as a transaction.

Begin Transaction
    Delete from Tables_Child where ID in (--your query)
    Delete from Tables_Child2 where ID in (--your query)
    (your_Query)
Commit Transaction

That way you know what is about to occur and you can control it without ending up with cycles.

回复收藏 0 原文