.Net Core cursor within a cursor

Question

I'm busy working on a .Net Core Web Application and I was wondering if it is possible to execute a cursor within another cursor.

I have the following code which I thought would work

string string = null;
SqlCommand collections_cur = null;
SqlCommand headings_cur = null;
int cl_idno = 0;

sqlserv.Open();

string = " select * from collections " +
         " where cl_idno >= @cl_idno ";
collections_cur = new SqlCommand(string, sqlserv);

string = " select * from headings " +
         " where hd_cl_idno = @hd_cl_idno ";
headings_cur = new SqlCommand(string, sqlserv);

collections_cur.Parameters.Add(new SqlParameters("cl_idno", 1));
using(SqlDataReader reader1 = collections_cur.ExecuteReader())
{

    while(reader1.Read())
    {

        // some stuff

        cl_idno = reader1.GetInt32(0);

        headings_cur.Parameters.Add(new SqlParameters("hd_cl_idno", cl_idno));
        using(SqlDataReader reader2 = headings_cur.ExecuteReader())
        {

            while(reader2.Read())
            {

                // more stuff

            }

        }

    }

}

sqlserv.Close();

The above is crashing though with the following error

InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

I thought that it would treat reader1 and reader2 separately but I'm not sure how to use a second DataReader object correctly.

PS

I know that I could do this with a single cursor using

select * from collections
left join headings on hd_cl_idno = cl_idno
where cl_idno >= 1

But I am using this simplified code so that the question is easy to read. There are instances where I need a cursor within a cursor and this code is just to illustrate the problem

Answer 1

It seems this is frowned upon by C# developers but I have a solution anyway if it might benefit anyone. You should probably use it at your own risk or not use it all but it works for me.

I have made the following adjustments to the code

//
// define and initialize variables
//

string credentials = null;
SqlConnection sqlserv = null;
string lv_string = null;
SqlCommand collections_cur = null;
SqlCommand headings_cur = null;
int cl_idno = 0;

//
// connect to database
// MultipleActiveResultSets=True was added
// to allow usage of multiple cursors
//

credentials = "Data Source=(localdb)\\MSSQLLocalDB;" +
              "Initial Catalog=rescratch;" +
              "Integrated Security=True;" +
              "Connect Timeout=30;" +
              "Encrypt=False;" +
              "TrustServerCertificate=True;" +
              "ApplicationIntent=ReadWrite;" +
              "MultipleActiveResultSets=True;" +
              "MultiSubnetFailover=False";

sqlserv = new SqlConnection(credentials);

sqlserv.Open();

//
// declare cursors
//

lv_string = " select * from collections " +
            " where cl_idno >= @cl_idno ";
collections_cur = new SqlCommand(lv_string, gv_sqlserv);
collections_cur.Parameters.Add(new SqlParameters("cl_idno", 0));

lv_string = " select * from headings " +
            " where hd_cl_idno = @hd_cl_idno ";
headings_cur = new SqlCommand(lv_string, gv_sqlserv);
headings_cur.Parameters.Add(new SqlParameters("hd_cl_idno", 0));

//
// main cursor
//

// set parameter
collections_cur.Parameters["cl_idno"].Value = 1;
using(SqlDataReader lv_reader1 = collections_cur.ExecuteReader())
{

    while(lv_reader1.Read())
    {

        // some stuff

        cl_idno = lv_reader1.GetInt32(0);

        //
        // sub cursor
        //

        // set parameter
        headings_cur.Parameters["hd_cl_idno"].Value = cl_idno;
        using(SqlDataReader lv_reader2 = headings_cur.ExecuteReader())
        {

            while(lv_reader2.Read())
            {

                // more stuff

            }

        }

    }

}

sqlserv.Close();