C＃根据它的引用id检索用户id（循环内部循环） [英] C# to retrieve user id based on it's reference id (loop inside loop)

问题描述

最后我来到了这个但是有以下问题



我想在我输入的User_id之后通过Reference_id从表中引用彼此之后检索所有User_id下面的代码给出了确切的结果但它从2001到2005检索了所有User_id。



如果我从文本框输入2002作为User_id然后它我想要必须从2003 - 2005检索



Table_xyz列User_id有值= 2001,2002,2003,2004,2005

Table_xyz列Reference_id有价值= 2000,2001,2002,2003,2004

Finally i came to this but the having following issue

I want to retrieve all User_id's next after my entered User_id referencing each other from the table by Reference_id, the code below gives the exact result but it retrieves all User_id's from "2001 to 2005".

I want if i enter the "2002" as User_id from a textbox then it must retrieve from "2003 - 2005"

Table_xyz column User_id have value= 2001, 2002, 2003, 2004, 2005
Table_xyz column Reference_id have value= 2000, 2001, 2002, 2003, 2004

var gCmd = new SqlCommand(@"SELECT User_id FROM Table_xyz", nCon); 
SqlDataAdapter Sda = new SqlDataAdapter(gCmd); 
DataTable Dt = new DataTable(); Sda.Fill(Dt); 
for (int i = 0; i < Dt.Rows.Count; i++) 
{ 
    string referenceid = Dt.Rows[i]["User_id"].ToString(); 
    var gCmd1 = new SqlCommand(@"SELECT User_id FROM Table_xyz WHERE 
                                 Reference_id = '" + referenceid + "'", nCon); 
    SqlDataAdapter Sda1 = new SqlDataAdapter(gCmd1); 
    DataTable Dt1 = new DataTable(); 
    Sda1.Fill(Dt1); 
    Response.Write(referenceid); 
}

我的尝试：



我尝试将SELECT User_id FROM Table_xyz WHERE User_id ='2001'添加到第一个命令，但它只返回一个值，其中User_id匹配2001

What I have tried:

I tried adding "SELECT User_id FROM Table_xyz WHERE User_id = '2001'" to the first command but it returns only a single value where User_id matched "2001"

推荐答案

我认为你需要 SqlCommand.ExecuteScalar ，参见这里的示例： SqlCommand.ExecuteScalar Method（System.Data.SqlClient） [ ^ ]

I think you need SqlCommand.ExecuteScalar, see example here: SqlCommand.ExecuteScalar Method (System.Data.SqlClient)[^]

使用递归查询，您将能够在一次点击中检索所有记录：

Use a recursive query, and you'll be able to retrieve all records in one hit:

WITH cte As
(
    SELECT
        User_id,
        '/' + CAST(User_id As varchar(max)) + '/' As path
    FROM
        Table_xyz
    WHERE
        Reference_id = @ID
    
    UNION ALL
    
    SELECT
        B.User_id,
        A.path + CAST(B.User_id As varchar(max)) + '/'
    FROM
        cte As A
        INNER JOIN Table_xyz As B
        ON B.Reference_id = A.User_id
    WHERE
        A.path Not Like '%/' + CAST(B.User_id As varchar(max)) + '/%'
)
SELECT
    User_id
FROM
    cte
;

这篇关于C＃根据它的引用id检索用户id（循环内部循环）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！