Using two SqlDataReaders in ASP.NET

Question

I have the following code that I use to process data from two tables, but I can't seem to make two SqlDataReaders to work with it. Any idea how to change it to make it work?

//SqlConnection cn = valid SQL Server 2008 connection

using (SqlCommand cmd = new SqlCommand("SELECT * FROM table1", cn))
{
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            int nVal1 = rdr.GetInt32(0);
            int nVal2 = rdr.GetInt32(1);
            //...
            int nValN = rdr.GetInt32(N);

            //Now I process the data read ...

            //After processing I need to use the results
            //to look up data from another table using 
            //this item's data

            using (SqlCommand cmd2 = new SqlCommand("SELECT * FROM table2 WHERE " + strSQLCondition, cn))
            {
                //But code below doesn't let me create another reader
                using (SqlDataReader rdr2 = cmd2.ExecuteReader())
                {
                    while (rdr2.Read())
                    {
                        //Process results
                    }
                }
            }

        }
    }
}

Dave Simione · Accepted Answer · 2013-04-25 00:12:50Z

3

By default, you can only use one active dataset on a connection. You have a few options. The three that come to mind are

Look up the data before hand and cache it
Use a second connection object for your inner command
Use MARS - http://msdn.microsoft.com/en-us/library/ms131686.aspx

answered Apr 25, 2013 at 0:12

Dave Simione

1,4412 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ahmd0 Over a year ago

Thanks. Opening another connection helps. I don't know why I didn't think of it myself :)

Collectives™ on Stack Overflow

Using two SqlDataReaders in ASP.NET

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related