Inserting a List of Data results in a connection error

Question

I am trying to enter a list of data objects to my table using a loop. But during the loop, the logic abruptly stops with this error:

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

Here's my code:

List<TransactionAccount> accounts = new List<TransactionAccount>();

try
   {
        using (con = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO Accounts (name, category) VALUES (@name, @category)";
            cmd = new SqlCommand(query, con);
            con.Open();
    
            foreach (TransactionAccount account in accounts)
            {
                cmd.Parameters.AddWithValue("@name", account.name);
                cmd.Parameters.AddWithValue("@category", account.category);
                int exec = cmd.ExecuteNonQuery();
            }
    
            con.Close();
        }
    }
    catch (Exception ex)
    {
                    
    }

The error doesn't make sense because I close the connection at the end of the loop. What should I change here?

when do you see this error? inside the foreach loop? or during the foreach loop? How many times the loop iterate before the error? — Chetan, Nov 29 '22 at 03:35
The error message clearly indicates a **DataReader** is open and causing a disturbance in the force, which may mean one was left open elsewhere. `AddWithValue` wont work well in a loop. An empty catch is also a bad idea — Ňɏssa Pøngjǣrdenlarp, Nov 29 '22 at 03:36
You ought to populate a `DataTable` with all the data and then save it all in a single batch by calling `Update` on a data adapter. [Here](https://www.vbforums.com/showthread.php?469872-Retrieving-and-Saving-Data-in-Databases&p=3112830&viewfull=1#post3112830)'s one I prepared earlier. — jmcilhinney, Nov 29 '22 at 04:34

score 1 · Accepted Answer · answered Nov 29 '22 at 03:44

I would suggest to instead of calling the query again and again, better pass a table variable. it will save multiple calls to the database query. and will improve the performance of your code.

you can write a store procedure which would take the table variable as a parameter.

and in the stored procedure, you can run a loop for this table variable and insert the records.

And coming to your original question, I suspect you have declared cmd as a class member and using it across multiple queries which are causing issues. you should declare it inside your method.

and please avoid AddWithValue you can use Parameters.Add, Here is why?

Also, Please utilize the return value of ExecuteNonQuery like logging it.

You were right. I was reusing another cmd. As for AddWithValue, isn't .Add deprecated? Thanks for your feedback! — Jay, Nov 29 '22 at 06:07
@Jay please refer to this https://stackoverflow.com/questions/9999751/difference-between-parameters-addstring-object-and-parameters-addwithvalue — Vivek Nuna, Nov 29 '22 at 06:14

Inserting a List of Data results in a connection error

1 Answers1