How to insert multiple records into database with SqlParameter in C#

Question

I am trying to run the following code:

using (SqlConnection conn = new SqlConnection(connstr))
{
    conn.Open();

    StringBuilder sqlStr = new StringBuilder("INSERT into Customers values ( @name, @address, @city, @state)");

    SqlCommand cmd = new SqlCommand(sqlStr.ToString(), conn);
    cmd.Parameters.Add(new SqlParameter("@name", "John Smith"));
    cmd.Parameters.Add(new SqlParameter("@address", "123 Main St."));
    cmd.Parameters.Add(new SqlParameter("@city", "Detroit"));
    cmd.Parameters.Add(new SqlParameter("@state", "Michigan"));

    cmd.ExecuteReader();

    cmd.Parameters["@name"].Value = "William Jones";
    cmd.Parameters["@address"].Value = "500 Blanchard Ave";
    cmd.Parameters["@city"].Value = "Chicago";
    cmd.Parameters["@state"].Value = "Illinois";

    cmd.ExecuteReader();
}

However, I am getting an error. I have tried multiple other ways, but nothing seems to work. What is the proper way to insert multiple records using SqlParameters?

Answer 1

First of all cmd.ExecuteReader(); is used on data retrieval and not not the proper way in doing insert.

Use

cmd.ExecuteNonQuery();

Change your code to this:

Create a method that has 4 parameter where you pass name,address,city and state that includes your insert code

private void InsertCustomers(string name,string address,string city,string state)
{
    using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    StringBuilder sqlStr = new StringBuilder("INSERT into Customers values ( @name, @address, @city, @state)");
                    SqlCommand cmd = new SqlCommand(sqlStr.ToString(), conn);
                    cmd.Parameters.Add(new SqlParameter("@name", name));
                    cmd.Parameters.Add(new SqlParameter("@address", address));
                    cmd.Parameters.Add(new SqlParameter("@city", city));
                    cmd.Parameters.Add(new SqlParameter("@state", state));
                    cmd.ExecuteNonQuery();

                }

}

then on your click or any events that you want to insert new customer just do

InsertCustomers("A","B","C","D");

InsertCustomers("E","F","G","H");

And maybe you need to start learning the basic first on ADO.NET

The C# Station ADO.NET Tutorial

After completing the tutorial, you will now be familiarize on ado.net.

Best Regards