在 C# 中将集合插入到 SQL Server 表中
本文关键字：SQL Server 表中 插入 集合 | 更新日期: 2023-09-27 18:33:50
尝试将集合插入到 SQL Server 2014 中的表中。 这是我的代码，它执行时没有错误，但是当我检查我的表时 - 没有添加任何内容。 我的集合对象orders不为空，我在调试它时可以看到它有 3 个成员。
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();
}
}
从注释进行代码编辑
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);
}
}
}
}
}
打开和关闭连接而不实际执行任何操作。
此外，将参数的添加移到 foreach 循环之外，只需在循环中设置值即可。
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();
}
除了缺少ExecuteNonQuery调用的明显错误外，您的代码将再次失败，因为您在每个循环中添加了参数。这将导致由已定义的参数引起的异常。
您需要使用 清除参数集合
cmd.Parameters.Clear();
在每个循环开始时或更好地定义循环之前的参数，并在循环内仅更改值
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();
}
}
}
请注意，连接可以在循环开始时打开，而不是在每个循环时打开，而 using 语句会注意关闭和处置它，并且考虑到插入的性质，我还建议将所有内容封装在 SqlTransaction 中，以便您的代码更加"原子化"。
最后，查询中的某些内容不正确，集合中有一个参数占位符不匹配(@ID(，查询中有一个参数没有占位符(@name(。如果不修复这两个错误，您就会得到其他错误。我已经从查询和参数集合中删除了它们