Insert Collection into SQL Server Table in C#

Question

Trying to insert a collection into a table in SQL Server 2014. Here's my code it executes without error but when I check my table - nothing is added. My collection object orders isn't empty either I can see it has 3 members when debugging it.

 IEnumerable<CompleteOrderDetails> orders;
 JoinDetails(doc, ns, xmlFragment1, out orders);

 string connectionstring = null;
 SqlConnection conn;
 connectionstring = "Data Source = DANNY; Initial Catalog = Alliance; Integrated Security = SSPI";

 using (conn = new SqlConnection(connectionstring))
 {
    string customerInsert = "INSERT INTO AmazonCustomer (AddressLine1, AddressLine2, AddressLine3, City, StateOrRegion, AmazonOrderId, PostalCode, Title, ItemPrice, ShippingPrice, Quantity) " + 
                            "VALUES (@AddressLine1, @AddressLine2, @AddressLine3, @City, @StateOrRegion, @AmazonOrderId, @PostalCode, @Title, @ItemPrice, @ShippingPrice, @Quantity)";

    using (SqlCommand query = new SqlCommand(customerInsert))
    {
        query.Connection = conn;

        foreach (var order in orders)
        {
            query.Parameters.Add("@AmazonOrderId", SqlDbType.NVarChar, 150).Value = order.AmazonOrderId;
            query.Parameters.Add("@Name", SqlDbType.NVarChar, 150).Value = order.Name;
            query.Parameters.Add("@AddressLine1", SqlDbType.NVarChar, 150).Value = order.AddressLine1;
            query.Parameters.Add("@AddressLine2", SqlDbType.NVarChar, 150).Value = order.AddressLine2;
            query.Parameters.Add("@AddressLine3", SqlDbType.NVarChar, 150).Value = order.AddressLine3;
            query.Parameters.Add("@City", SqlDbType.NVarChar, 150).Value = order.City;
            query.Parameters.Add("@StateOrRegion", SqlDbType.NVarChar, 150).Value = order.StateOrRegion;
            query.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 150).Value = order.PostalCode;
            query.Parameters.Add("@Title", SqlDbType.NVarChar, 150).Value = order.Title;
            query.Parameters.Add("@ItemPrice", SqlDbType.NVarChar, 150).Value = order.ItemPrice;
            query.Parameters.Add("@ShippingPrice", SqlDbType.NVarChar, 150).Value = order.ShippingPrice;
            query.Parameters.Add("@Quantity", SqlDbType.NVarChar, 150).Value = order.Quantity;
        }

        conn.Open();
        conn.Close();
    }            
}

Code Edits from Comments

  private static void ExecuteSqlTransaction(IEnumerable<CompleteOrderDetails> orders)
        {
            string connectionstring = null;
            SqlConnection conn;
            SqlTransaction transaction; 
            connectionstring = "Data Source = DANNY; Initial Catalog = Alliance; Integrated Security = SSPI";
            using (conn = new SqlConnection(connectionstring))
            {
                conn.Open();
                transaction = conn.BeginTransaction("Transaction");
                string customerInsert =
                    "INSERT INTO AmazonCustomer (Name, AddressLine1, AddressLine2, AddressLine3, City, StateOrRegion, AmazonOrderId, PostalCode, Title, ItemPrice, ShippingPrice, Quantity) VALUES (@Name, @AddressLine1, @AddressLine2, @AddressLine3, @City, @StateOrRegion, @AmazonOrderId, @PostalCode, @Title, @ItemPrice, @ShippingPrice, @Quantity)";

                using (SqlCommand query = new SqlCommand(customerInsert))
                {
                    query.Connection = conn;
                    query.Transaction = transaction;
                    query.Parameters.Add("@AmazonOrderId", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@Name", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@AddressLine1", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@AddressLine2", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@AddressLine3", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@City", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@StateOrRegion", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@Title", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@ItemPrice", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@ShippingPrice", SqlDbType.NVarChar, 150);
                    query.Parameters.Add("@Quantity", SqlDbType.NVarChar, 150);
                    try
                    {
                        foreach (var order in orders)
                        {
                            query.Parameters["@AmazonOrderId"].Value = order.AmazonOrderId ?? Convert.DBNull;
                            query.Parameters["@Name"].Value = order.Name ?? Convert.DBNull;
                            query.Parameters["@AddressLine1"].Value = order.AddressLine1 ?? Convert.DBNull;
                            query.Parameters["@AddressLine2"].Value = order.AddressLine2 ?? Convert.DBNull;
                            query.Parameters["@AddressLine3"].Value = order.AddressLine3 ?? Convert.DBNull;
                            query.Parameters["@City"].Value = order.City ?? Convert.DBNull;
                            query.Parameters["@StateOrRegion"].Value = order.StateOrRegion ?? Convert.DBNull;
                            query.Parameters["@PostalCode"].Value = order.PostalCode ?? Convert.DBNull;
                            query.Parameters["@Title"].Value = order.Title ?? Convert.DBNull;
                            query.Parameters["@ItemPrice"].Value = order.ItemPrice ?? Convert.DBNull;
                            query.Parameters["@ShippingPrice"].Value = order.ShippingPrice ?? Convert.DBNull;
                            query.Parameters["@Quantity"].Value = order.Quantity ?? Convert.DBNull;
                            query.ExecuteNonQuery();
                            transaction.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                        Console.WriteLine("   Message: {0}", ex.Message);

                        try
                        {
                            transaction.Rollback();
                        }
                        catch (Exception ex2)
                        {
                            Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                            Console.WriteLine("   Message: {0}", ex2.Message);
                        }
                    }
                }
            }
        }

Try to execute command

gabba
– gabba

2016-02-09 17:18:43 +00:00
Commented Feb 9, 2016 at 17:18 — gabba
– gabba, Commented Feb 9, 2016 at 17:18

squillman · Accepted Answer · 2016-02-09 17:30:26Z

5

You open and close the connection without actually executing anything.

Also, move the adding of the parameters outside of your foreach loop and just set the values within the loop.

using (SqlCommand query = new SqlCommand(customerInsert))
{
    query.Connection = conn;
    query.Parameters.Add("@AmazonOrderId", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@Name", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@AddressLine1", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@AddressLine2", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@AddressLine3", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@City", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@StateOrRegion", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@Title", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@ItemPrice", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@ShippingPrice", SqlDbType.NVarChar, 150);
    query.Parameters.Add("@Quantity", SqlDbType.NVarChar, 150);

    conn.Open();
    foreach (var order in orders)
    {
        query.Parameters["@AmazonOrderId"].Value = order.AmazonOrderId;
        query.Parameters["@Name"].Value = order.Name;
        query.Parameters["@AddressLine1"].Value = order.AddressLine1;
        query.Parameters["@AddressLine2"].Value = order.AddressLine2;
        query.Parameters["@AddressLine3"].Value = order.AddressLine3;
        query.Parameters["@City"].Value = order.City;
        query.Parameters["@StateOrRegion"].Value = order.StateOrRegion;
        query.Parameters["@PostalCode"].Value = order.PostalCode;
        query.Parameters["@Title"].Value = order.Title;
        query.Parameters["@ItemPrice"].Value = order.ItemPrice;
        query.Parameters["@ShippingPrice"].Value = order.ShippingPrice;
        query.Parameters["@Quantity"].Value = order.Quantity;
        query.ExecuteNonQuery();
    }
    conn.Close();
}

edited Feb 9, 2016 at 17:30

answered Feb 9, 2016 at 17:18

squillman

13.7k3 gold badges45 silver badges65 bronze badges

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

11 Comments

danny taki Over a year ago

LOL, ok so I need to open the connection then execute my sql command, then close the connection?

Gerino Over a year ago

Also, the foreach will just overwrite parameters, or make a mess of them - I'd say that the execute also should be in the brackets ;)

granadaCoder Over a year ago

Non - Atomic and RBAR. Bad practice. What happens when you have 10 items and the 6th one fails? The sooner you learn "set-based", the better you will be, IMHO.

squillman Over a year ago

@dann Non-atomic here means that a lot of other stuff can happen in the system while your foreach loop is executing. Some or a lot of these things can be undesirable to your situation. RBAR = "Row By Agonizing Row". It is a term for an iterative operation such as the foreach loop over your collection. They almost always have poor performance over large sets. Doing this in one single set operation will make it atomic and non-RBAR.

squillman Over a year ago

@danny It depends. One way would be to fill a datatable from your collection and send it in to a stored procedure using user defined table types. Then your stored procedure performs a set operation on the data. But yes, that starts to get into other questions.

|

Steve · Accepted Answer · 2016-02-09 18:38:17Z

3

Apart from the obvious error of the missing ExecuteNonQuery call, your code will fail again because you add the parameters at each loop. This will lead to an exception caused by a parameter already defined.

You need to clear the parameters collection with

cmd.Parameters.Clear();

at the start of each loop or better define the parameters before the loop and, inside the loop, change only the values

 using (conn = new SqlConnection(connectionstring))
 {
    string customerInsert = @"INSERT INTO AmazonCustomer 
        (AddressLine1, AddressLine2, AddressLine3, City, 
         StateOrRegion, AmazonOrderId, PostalCode, Title, 
         ItemPrice, ShippingPrice, Quantity) 
         VALUES (@AddressLine1, @AddressLine2, @AddressLine3, @City,
         @StateOrRegion, @AmazonOrderId, @PostalCode, @Title, 
         @ItemPrice, @ShippingPrice, @Quantity)";

    using (SqlCommand query = new SqlCommand(customerInsert))
    {
       query.Connection = conn;
       // Not used ???
       // query.Parameters.Add("@Name", SqlDbType.NVarChar, 150)
       query.Parameters.Add("@AddressLine1", SqlDbType.NVarChar, 150);
       query.Parameters.Add("@AddressLine2", SqlDbType.NVarChar, 150);
       query.Parameters.Add("@AddressLine3", SqlDbType.NVarChar, 150);
       query.Parameters.Add("@City", SqlDbType.NVarChar, 150);
       query.Parameters.Add("@StateOrRegion", SqlDbType.NVarChar, 150);
       query.Parameters.Add("@AmazonOrderId", SqlDbType.NVarChar, 150);
       query.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 150);
       query.Parameters.Add("@Title", SqlDbType.NVarChar, 150);
       query.Parameters.Add("@ItemPrice", SqlDbType.NVarChar, 150);
       query.Parameters.Add("@ShippingPrice", SqlDbType.NVarChar, 150);
       query.Parameters.Add("@Quantity", SqlDbType.NVarChar, 150);

       conn.Open(); 
       using(SqlTransaction tr = conn.BeginTransaction())
       {
           foreach (var order in orders)
           {
                // Not used ???
                // query.Parameters["@Name"].Value = order.Name;
                query.Parameters["@AddressLine1"].Value = order.AddressLine1;
                query.Parameters["@AddressLine2"].Value = order.AddressLine2;
                query.Parameters["@AddressLine3"].Value = order.AddressLine3;
                query.Parameters["@City"].Value = order.City;
                query.Parameters["@StateOrRegion"].Value = order.StateOrRegion;
                query.Parameters["@AmazonOrderId"].Value = order.AmazonOrderId;
                query.Parameters["@PostalCode"].Value = order.PostalCode;
                query.Parameters["@Title"].Value = order.Title;
                query.Parameters["@ItemPrice"].Value = order.ItemPrice;
                query.Parameters["@ShippingPrice"].Value = order.ShippingPrice;
                query.Parameters["@Quantity"].Value = order.Quantity;
                query.ExecuteNonQuery();
           }
           tr.Commit();
        }
    }            
}

Notice that the connection could be opened at the start of the loop and not at each loop, while the using statement take cares to close and dispose it, and given the nature of your inserts, I suggest also to encapsulate everything inside a SqlTransaction so your code is more 'atomic'.

Finally something is not correct in your query, there is a parameter placeholder that has not matching parameter in the collection (@ID) and there is a parameter that has no placeholder in the query (@name). Without fixing these two errors you get other errors. I have removed them from the query and from the parameters collection

edited Feb 9, 2016 at 18:38

answered Feb 9, 2016 at 17:30

Steve

217k22 gold badges242 silver badges296 bronze badges

4 Comments

danny taki Over a year ago

Yep! Nice catch, I actually changed that in my question after noticing it myself. (@ID) was the primary key of the table and i wanted to seed the value as records were added. I removed it from the original insert query, but I'm still having an issue with an error

The parameterized query '(@AmazonOrderId nvarchar(150),@Name nvarchar(150),@AddressLine1 ' expects the parameter '@AmazonOrderId', which was not supplied.

I think this is happening because there is a null value for AmazonOrderId in my collection.

Steve Over a year ago

Are you sure that all those fields are of type nvarchar? It seems not really correct with prices etc...

danny taki Over a year ago

Yes, nvarchar(150), I have SQL server open. I just wanted to create a side function that would store customer information in a sql table, so I'm not going to be doing anything with the data besides looking at it, so I think nvarchar should be fine.

Steve Over a year ago

As far as I can tell there is no reason for this error then (after removing the ID field and the @name parameter)

Collectives™ on Stack Overflow

Insert Collection into SQL Server Table in C#

2 Answers 2

11 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related