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);
                        }
                    }
                }
            }
        }

Answer 1

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();
}