Is there a way to have a SqlConnection that will write while an associated DataReader is open?

Question

Or what's the recommended way to read and write at the same time? Having two connections open is the only way? This is on a plain Windows Forms C# (.NET 3.5) app

using (SqlConnection conn = new SqlConnection(connStr)) {
    SqlCommand cmdRead = new SqlCommand("select stools from foo", conn);
    SqlDataReader rdr = cmdRead.ExecuteReader();
    SqlCommand cmdWrite = new SqlCommand("insert into bar values (@beer)", conn);
    SqlParameter beer = new SqlParameter("@beer", SqlDbType.Int);
    cmdWrite.Parameters.Add(beer);
    while(rdr.Read()) {
        int stools = rdr.GetInt32(0);
        cmdWrite.Parameters["@beer"].value = stools;
        //Next line fails for an open data reader associated to the "command"
        cmdWrite.ExecuteNonQuery(); 
    }
    rdr.Close()
}

This, OTOH, works, but looks ugly to me (besides of opening an extra connection)

using (SqlConnection connR = new SqlConnection(connStr)) {
    using (SqlConnection connW = new SqlConnection(connStr)) {
        SqlCommand cmdRead = new SqlCommand("select stools from foo", connR);
        SqlDataReader rdr = cmdRead.ExecuteReader();
        SqlCommand cmdWrite = new 
                         SqlCommand("insert into bar values (@beer)", connW);
        SqlParameter beer = new SqlParameter("@beer", SqlDbType.Int);
        cmdWrite.Parameters.Add(beer);
        while(rdr.Read()) {
            int stools = rdr.GetInt32(0);
            cmdWrite.Parameters["@beer"].value = stools;
            cmdWrite.ExecuteNonQuery(); 
        }
        rdr.Close()
    }
}

In this simple case, read all the stools, store them in a list, close the reader and then write them will work (as long as there aren't many stools in the database), but in more complex cases it starts being unwieldy and memory hungry, so that's also not desirable.

Answer 1

Why not having two methods: one for reading and one for writing, each using its own connection drawn from the connection pool.

Reading

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmdRead = new SqlCommand("select stools from foo", conn))
{
    conn.Open();  
    using (SqlDataReader rdr = cmdRead.ExecuteReader())
    {
        while(rdr.Read()) 
        {
            int stools = rdr.GetInt32(0);
        }
    }
}

Writing

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmdWrite = new SqlCommand("insert into bar values (@beer)", conn))
{
    conn.Open();
    SqlParameter beer = new SqlParameter("@beer", SqlDbType.Int);
    cmdWrite.Parameters.Add(beer);
    cmdWrite.Parameters["@beer"].value = stools;
    cmdWrite.ExecuteNonQuery(); 
}

And then just call them on different threads. This makes methods clearer and more specific.

Answer 2

You can't write while the DataReader is open, creating a second connection is needed in this situation, and IMHO it's not a design issue.

Answer 3

Well the obvious question is why are you doing this in code at all?

You're selecting from one table to write into another - that screams insert query to me.

Doubtless the real world example is more complex in which case you need two connections because datareaders work by holding the connection open 'til you're done and that's more or less that.

Answer 4

With SQL 2005 and later, you can use Multiple Active Result Sets (MARS):

http://msdn.microsoft.com/en-us/library/ms345109%28SQL.90%29.aspx

http://msdn.microsoft.com/en-us/library/ms131686.aspx

For this to work, you need to enable it in your connection string.