如何识别GridView中多表联合查询记录的来源表

这问题我太懂了——你现在的查询结果里只有各表的Id，但没法区分Id属于哪个表，自然没法准确执行删除。最简单的解决办法就是在每个子查询里硬编码一个来源表名称的字段，让每条记录都带上自己的“出身标记”。

步骤1：修改你的查询语句，添加来源表字段 #

在每个SELECT语句里，新增一个固定值的字段，比如'tbl_Subscribers' AS SourceTable，对应每个子查询的来源表。这样UNION ALL合并后的结果集就会多一列SourceTable，明确记录来自哪个表。

修改后的完整查询代码如下：

str += "(SELECT ";
str += "s.Id, s.EmailAddress As ColumnValue,";
str += "(SELECT Name FROM tbl_User_master WHERE Id = s.user_id) AS CreatedBy,";
str += "s.SubscriptionDateTime As CreatedDate,d.Count,";
str += "'tbl_Subscribers' AS SourceTable  -- 新增来源表标识";
str += "FROM ";
str += "(SELECT EmailAddress, COUNT(*) as Count ";
str += "FROM tbl_Subscribers ";
str += "WHERE user_id = '" + int.Parse(CommonLogic.GetSessionValue("user_id").ToString()) + "' ";
str += "GROUP BY EmailAddress ";
str += "HAVING COUNT(*) > 1) AS d ";
str += "INNER JOIN ";
str += "tbl_Subscribers s ON s.EmailAddress = d.EmailAddress)";
str += " UNION ALL ";
str += "(SELECT ";
str += "f.Id,f.EmailAddress As ColumnValue,";
str += "(SELECT Name FROM tbl_User_master WHERE Id = f.user_id) AS CreatedBy,";
str += "f.CreatedDate As CreatedDate,d.Count,";
str += "'tbl_From_master' AS SourceTable  -- 新增来源表标识";
str += "FROM ";
str += "(SELECT EmailAddress, COUNT(*) AS Count ";
str += "FROM tbl_From_master ";
str += "WHERE user_id = '" + int.Parse(CommonLogic.GetSessionValue("user_id").ToString()) + "' ";
str += "GROUP BY EmailAddress ";
str += "HAVING COUNT(*) > 1) AS d ";
str += "INNER JOIN ";
str += "tbl_From_master f ON f.EmailAddress = d.EmailAddress)";
str += " UNION ALL ";
str += "(SELECT ";
str += "c.Id,c.CategoryName As ColumnValue,(SELECT Name FROM tbl_User_master WHERE Id = c.user_id) As CreatedBy,NULL As CreatedDate,d.Count,";
str += "'tbl_Categories' AS SourceTable  -- 新增来源表标识";
str += "FROM (";
str += "SELECT CategoryName, COUNT(*) as Count ";
str += "FROM tbl_Categories WHERE user_id='" + int.Parse(CommonLogic.GetSessionValue("user_id").ToString()) + "' ";
str += "GROUP BY CategoryName ";
str += "HAVING COUNT(*) > 1";
str += ") AS d ";
str += "INNER JOIN tbl_Categories c ON c.CategoryName = d.CategoryName)";
str += " UNION ALL ";
str += "(SELECT ";
str += "t.Id,t.CategoryName As ColumnValue,(SELECT Name FROM tbl_User_master WHERE Id = t.user_id) As CreatedBy,NULL As CreatedDate,d.Count,";
str += "'tbl_Template_Categories' AS SourceTable  -- 新增来源表标识";
str += "FROM (";
str += "SELECT CategoryName, COUNT(*) as Count ";
str += "FROM tbl_Template_Categories WHERE user_id='" + int.Parse(CommonLogic.GetSessionValue("user_id").ToString()) + "' ";
str += "GROUP BY CategoryName ";
str += "HAVING COUNT(*) > 1";
str += ") AS d ";
str += "INNER JOIN tbl_Template_Categories t ON t.CategoryName = d.CategoryName)";
str += " UNION ALL ";
str += "(SELECT ";
str += "t.Id,t.TemplateName As ColumnValue,(SELECT Name FROM tbl_User_master WHERE Id = t.user_id) As CreatedBy,t.Created_date As CreatedDate,d.Count,";
str += "'tbl_Template_master' AS SourceTable  -- 新增来源表标识";
str += "FROM (";
str += "SELECT TemplateName, COUNT(*) as Count ";
str += "FROM tbl_Template_master WHERE user_id='" + int.Parse(CommonLogic.GetSessionValue("user_id").ToString()) + "' ";
str += "GROUP BY TemplateName ";
str += "HAVING COUNT(*) > 1";
str += ") AS d";
str += " INNER JOIN tbl_Template_master t ON t.TemplateName = d.TemplateName)";

步骤2：在GridView中绑定SourceTable字段 #

你可以在GridView里新增一列，绑定SourceTable字段：

• 如果不想让用户看到，可以把这列设为Visible="False"（隐藏列）；
• 如果调试需要，也可以显示出来，方便验证。

比如ASPX里的列定义：

<asp:BoundField DataField="SourceTable" HeaderText="来源表" Visible="False" />

步骤3：删除时根据SourceTable执行对应操作 #

当用户选中记录执行删除时，你需要：

1. 获取选中记录的Id和SourceTable值；
2. 根据SourceTable的不同，拼接对应的DELETE语句（或者用参数化查询更安全）。

示例代码（伪代码）：

// 获取选中行的Id和SourceTable
int recordId = int.Parse(gridView.SelectedRow.Cells[0].Text);
string sourceTable = gridView.SelectedRow.Cells[5].Text; // 对应SourceTable列的索引

// 根据来源表执行删除
string deleteSql = $"DELETE FROM {sourceTable} WHERE Id = @Id";
// 用参数化查询避免SQL注入
SqlCommand cmd = new SqlCommand(deleteSql, connection);
cmd.Parameters.AddWithValue("@Id", recordId);
cmd.ExecuteNonQuery();

额外提醒：避免SQL注入风险 #

你现在的代码里直接拼接user_id到SQL语句里，这存在严重的SQL注入风险！建议改成参数化查询，比如：

// 示例：用参数代替字符串拼接
string subQuery = "SELECT EmailAddress, COUNT(*) as Count FROM tbl_Subscribers WHERE user_id = @UserId GROUP BY EmailAddress HAVING COUNT(*) > 1";
SqlCommand cmd = new SqlCommand(subQuery, connection);
cmd.Parameters.AddWithValue("@UserId", int.Parse(CommonLogic.GetSessionValue("user_id").ToString()));

这样既安全，又能避免因特殊字符导致的SQL语法错误。

内容的提问来源于stack exchange，提问作者shalin gajjar