Loop inside loop C#

Question

Hi i am having a problem writing C# code for my Web application.

 

I have an MS SQL Table named "Table_xyz" with two columns "User_id" & "Refrence_ID"

 

User_id	Reference_id
1	0
2	1
3	2
4	3
5	4

 

I just only want to retrieve all "User_id" based on it's "Refrence_ID" in a loop

 

My Command: Select User_id from Table_xyz where Reference_ID = '0' (This is only to start the loop)

 

The loop must start referencing only from the reference_id which i will enter(it may be any reference id from the table)

 

According to the above command the result will give "1" which is the parent "User_id", for the "Reference_ID" '0'

 

But the issue comes here, i want to keep this first result to retrieve the next result for second result and then for third and so on.

 

Means the result(returned value) will become the "Reference_ID" for upcoming query looping each other and this way it will find entire table.

 

My Code below:

 

try

{

nCon.Open();

var gCmd = new SqlCommand(@"SELECT User_id FROM Table_xyz WHERE Reference_id = '0'", nCon);

SqlDataAdapter Sda = new SqlDataAdapter(gCmd);

DataTable Dt = new DataTable();

Sda.Fill(Dt);

int i = 0;

do

{

if (i < Dt.Rows.Count)

{

Response.Write(Dt.Rows[i]["User_iD"]);

i++;

}

}

while (i < Dt.Rows.Count);

}

catch (Exception e) { Response.Write(e.Message); }

finally { nCon.Close(); }

Answer 1

I think you can do that in one shot (Avoiding the loop or recursion -at least not in code)

 

SELECT User_id
FROM Table_xyz
WHERE User_id
IN
(SELECT DISTINCT Reference_id FROM Table_xyz)

 so final code would be....

 

 

try
{
nCon.Open();
var gCmd = new SqlCommand(@"SELECT User_id
FROM Table_xyz
WHERE User_id
IN
(SELECT DISTINCT Reference_id FROM Table_xyz) ;" , nCon);
SqlDataAdapter Sda = new SqlDataAdapter(gCmd);
DataTable Dt = new DataTable();
Sda.Fill(Dt);
int i = 0;
do
{
if (i < Dt.Rows.Count)
{
Response.Write(Dt.Rows[i]["User_iD"]);
i++;
}
}
while (i < Dt.Rows.Count);
}
catch (Exception e) { Response.Write(e.Message); }
finally { nCon.Close(); }

Answer 2

You may need two queries to achieve that. First get all the reference ids and then loop through the result set to query the user ids. for example:

 

DataTable dt = new DataTable();
DataTable dt2 = new DataTable();
    using(SqlConnection connection = new SqlConnection(GetConnectionString())){
        connection.Open();
        string sql = "SELECT Reference_id FROM YourTable";
        using(SqlCommand cmd = new SqlCommand(sql,connection)){
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(dt);

                if(dt.Rows.Count > 0){
                      foreach(DataRow row in dt.Rows){

                           string sql2 = "SELECT User_id FROM YourTable WHERE ReferenceID = @0";
                                using(SqlCommand cmd2 = new SqlCommand(sql,connection)){
                               cmd2.Parameters.AddWithValue("@ReferenceID", Convert.ToInt32(rows[0].ToString()));
                                      SqlDataAdapter ad2 = new SqlDataAdapter(cmd2);
                                       ad2.Fill(dt2);
                     }
                }
        }

    }