Understanding of nested SQL in C#

Question

using (var connection = new SqlConnection(...))
{
    string sql = "SELECT * FROM tableA";
    using (var command = new SqlCommand(sql,connection))
    {
        using (var reader = command.ExecuteReader(...))
        {
            //***************Sample Start
            string sql2 = "INSERT into tableB(column1) VALUES('"+reader["column1"]+"')";
            using (var command2 = new SqlCommand(sql2,connection))
            {
                ...
            } 
            //***************Sample End
        }
    }
}

By using the above code snippet, I believe its the best practice to deal with SQL in C#. Now after I retrieve a list of records from tableA, for each of the row I would like to insert into tableB.

However, it's throwing an exception

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

I know this problem can be solved by creating another method and insert into the table from there, I'm wondering if there is any other way. Thanks for any input.

Answer 1

You need to use a different sql connection for the insert than for the select...

...but we can do even better. You can re-write this to be one sql statement, like so:

INSERT into tableB(column1)
    SELECT column1 FROM tableA

And then run it all at once like this:

string sql = "INSERT into tableB(column1, column2) SELECT column1, @othervalue As column2 FROM tableA;";
using (var connection = new SqlConnection(...))
using (var command = new SqlCommand(sql,connection))
{
    command.Paramters.Add("@othervalue", SqlDbType.NVarChar, 50).Value = "something";

    connection.Open();
    command.ExecuteNonQuery();
}

The single sql statement is typically much faster, and you end up with less code, too. I understand that this is likely a simplified example of your real query, but I promise you: you can re-write it all as one statement.

Additionally, sometimes you still want to do some client-side processing or display with the new records after the insert or update. In that case, you still only need to send one call to the database, but there will be two separate sql statements in that single call. The final code would look more like this:

string sql = "INSERT into tableB(column1, column2) SELECT column1, @othervalue As column2 FROM tableA;"
sql += "SELECT columnn1, @othervalue As column2 FROM tableA;";

using (var connection = new SqlConnection(...))
using (var command = new SqlCommand(sql,connection))
{
    command.Paramters.Add("@othervalue", SqlDbType.NVarChar, 50).Value = "something";

    connection.Open();
    using (var reader = command.ExecuteReader() )
    {
        while (reader.Read() )
        {
           //...
        }
    }
}

And because someone else brought up MARS (multiple active result sets), I'll add that while this can work, I've had mixed results using it for inserts/updates. It seems to work best when everything that shares a connection is only doing reads.