Loop inside loop C#

Abdur Razique

9y1.1k.NET

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;

{

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(); }

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
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(); }
Vincent Maverick Durano9y ago
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);
                     }
                }
        }

    }