Best/Proper way to run an insert stored procedure in a loop c#

Question

I'm stuck on the means to properly and best run a stored procedure to insert records through a foreach loop.

Here is what I have so far.

    string constr = ConfigurationManager.ConnectionStrings["CurrencyDb"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constr)) {
    con.Open();
        foreach (ListItem i in DependenciesListBox.Items) {
            if (i.Selected) {
                using (SqlCommand cmd = new SqlCommand("dbo.InsertDependency", con)) {
                    try {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@CurrencyId", scopeidentity);
                        cmd.Parameters.AddWithValue("@DependencyId", i.Value);
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException sqlex) {
                        throw new Exception("SQL Exception on insert. " + sqlex.Message);
                    }
                    catch (Exception ex) {
                        throw new Exception("Error adding dependencies. " + ex.Message);
                    }
                }
            }
        }

        foreach (ListItem i in AffectedListBox.Items) {
            if (i.Selected) {
                using (SqlCommand cmd = new SqlCommand("dbo.InsertAffected", con)) {
                    try {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@DependencyId", scopeidentity);
                        cmd.Parameters.AddWithValue("@CurrencyId", i.Value);
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException sqlex) {
                        throw new Exception("SQL Exception on insert. " + sqlex.Message);
                    }
                    catch (Exception ex) {
                        throw new Exception("Error adding affected apps. " + ex.Message);
                    }
                }
            }
        }

        //Loops through Platform list box and for each item that's selected, add a record into the platform table in the database.
        foreach (ListItem i in PlatformListBox.Items) {
            if (i.Selected) {
                using (SqlCommand cmd = new SqlCommand("dbo.InsertPlatform", con)) {
                    try {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@CurrencyId", scopeidentity);
                        cmd.Parameters.AddWithValue("@PlatformId", i.Value);
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException sqlex) {
                        throw new Exception("SQL Exception on insert. " + sqlex.Message);
                    }
                    catch (Exception ex) {
                        throw new Exception("Error adding platforms. " + ex.Message);
                    }
                }
            }
        }
    }

Doing it this way, I get the following error (Updated)

Uncaught Error: Sys.WebForms.PageRequestManagerServerErrorException: Error adding dependencies. The connection was not closed. The connection's current state is open.
    at Function.Error.create (MsAjaxJs?v=c42ygB2U07n37m_Sfa8ZbLGVu4Rr2gsBo7MvUEnJeZ81:1)
    at Sys.WebForms.PageRequestManager._createPageRequestManagerServerError (MsAjaxJs?v=c42ygB2U07n37m_Sfa8ZbLGVu4Rr2gsBo7MvUEnJeZ81:1)
    at Sys.WebForms.PageRequestManager._parseDelta (MsAjaxJs?v=c42ygB2U07n37m_Sfa8ZbLGVu4Rr2gsBo7MvUEnJeZ81:1)
    at Sys.WebForms.PageRequestManager._onFormSubmitCompleted (MsAjaxJs?v=c42ygB2U07n37m_Sfa8ZbLGVu4Rr2gsBo7MvUEnJeZ81:1)
    at Array.<anonymous> (MsAjaxJs?v=c42ygB2U07n37m_Sfa8ZbLGVu4Rr2gsBo7MvUEnJeZ81:1)
    at MsAjaxJs?v=c42ygB2U07n37m_Sfa8ZbLGVu4Rr2gsBo7MvUEnJeZ81:1
    at Sys.Net.WebRequest.completed (MsAjaxJs?v=c42ygB2U07n37m_Sfa8ZbLGVu4Rr2gsBo7MvUEnJeZ81:1)
    at XMLHttpRequest._onReadyStateChange (MsAjaxJs?v=c42ygB2U07n37m_Sfa8ZbLGVu4Rr2gsBo7MvUEnJeZ81:1)

Would anyone be able to let me know what the best practice is for doing this kind of thing? Thank you so much in advance.

Please add your error message as text, not an image. It makes it easier to read, and search for. — mason
– mason, Commented Mar 20, 2017 at 14:32

Igor · Accepted Answer · 2017-03-20 14:30:53Z

1

Don't call con.Open(); multiple times. Move it to

using (SqlConnection con = new SqlConnection(constr)) {
  con.Open();
  ...

See the first row in the Exceptions table:

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.open(v=vs.110).aspx

edited Mar 20, 2017 at 14:30

answered Mar 20, 2017 at 14:28

Igor

15.9k1 gold badge29 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

David Over a year ago

I knew it was going to be something silly. Let me try this out, thank you.

Igor Over a year ago

@David - because you have con.Open(); in three different loops - remove all of them and leave only one as shown

David Over a year ago

Yes I removed all three and left one right after using (SqlConnection con = new SqlConnection(constr)) { I'll update my code.

David Over a year ago

Ok, I'll do so via text this time.

Igor Over a year ago

@David - you are running old code, make sure the binaries are rebuilt

|

Collectives™ on Stack Overflow

Best/Proper way to run an insert stored procedure in a loop c#

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related