Thursday, 12 September 2013

Understanding of nested SQL in C#

Understanding of nested SQL in C#

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, its throwing
me an exception saying that 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.

No comments:

Post a Comment