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?

Answer 1

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 stored 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.