我正在尝试从源获取数据并保存到数据库中，但是即使声明标量变量，我也会面临此问题。在下面找到我尝试的方法。我面临的问题：system.data.sqlclient.sqlexception：'必须声明标量变量" @col_shipping_price"。'
class Program
{
static void Main(string[] args)
{
XmlSerializer deserializer = new XmlSerializer(typeof(AmazonEnvelope));
TextReader reader = new StreamReader(@"C:Users*********Desktop16315550943018039.xml");
object obj = deserializer.Deserialize(reader);
AmazonEnvelope XmlData = (AmazonEnvelope)obj;
reader.Close();
SqlConnection cnn = new SqlConnection(@"Data Source=ABDUL-TPSTPSSQLSERVER;Initial Catalog=Zoho_Amz_API;User ID=zohoapiservice;Password=**********");
cnn.Open();
for (int i = 0; i < XmlData.Message.Count; i++)
{
string sqlquery = "if not exists (select * from tbl_AMZ_API_sample where col_sku like '" + XmlData.Message[i].Order.OrderItem.SKU + "') insert into tbl_AMZ_API_sample(col_amazon_order_id, col_merchant_order_id, col_purchase_date, col_last_updated_date, col_order_status, col_fulfillment_channel, col_sales_channel, col_order_channel, col_url, col_ship_service_level, col_product_name, col_sku, col_asin, col_number_of_items, col_item_status, col_quantity, col_currency, col_item_price, col_item_tax, col_shipping_price, col_shipping_tax, col_gift_wrap_price, col_gift_wrap_tax, col_item_promotion_discount, col_ship_promotion_discount, col_ship_city, col_ship_state, col_ship_postal_code, col_ship_country, col_promotion_ids, col_is_business_order, col_purchase_order_number, col_price_designation, col_fulfilled_by, col_last_update_time) values(@col_amazon_order_id, @col_merchant_order_id, @col_purchase_date, @col_last_updated_date, @col_order_status, @col_fulfillment_channel, @col_sales_channel, @col_order_channel, @col_url, @col_ship_service_level, @col_product_name, @col_sku, @col_asin, @col_number_of_items, @col_item_status, @col_quantity, @col_currency, @col_item_price, @col_item_tax, @col_shipping_price, @col_shipping_tax, @col_gift_wrap_price, @col_gift_wrap_tax, @col_item_promotion_discount, @col_ship_promotion_discount, @col_ship_city, @col_ship_state, @col_ship_postal_code, @col_ship_country, @col_promotion_ids, @col_is_business_order, @col_purchase_order_number, @col_price_designation, @col_fulfilled_by, @col_last_update_time)";
SqlCommand cmd = new SqlCommand(sqlquery, cnn);
for (int j = 0; j < XmlData.Message[i].Order.OrderItem.ItemPrice.Component.Count; j++)
{
cmd.Parameters.AddWithValue("@col_amazon_order_id", XmlData.Message[i].Order.AmazonOrderID);
if (XmlData.Message[i].Order.MerchantOrderID == null)
{
cmd.Parameters.AddWithValue("@col_merchant_order_id", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@col_merchant_order_id", XmlData.Message[i].Order.MerchantOrderID);
}
cmd.Parameters.AddWithValue("@col_purchase_date", XmlData.Message[i].Order.PurchaseDate);
cmd.Parameters.AddWithValue("@col_last_updated_date", Global.unique.ToString());
cmd.Parameters.AddWithValue("@col_order_status", XmlData.Message[i].Order.OrderStatus);
cmd.Parameters.AddWithValue("@col_fulfillment_channel", XmlData.Message[i].Order.FulfillmentData.FulfillmentChannel);
cmd.Parameters.AddWithValue("@col_sales_channel", XmlData.Message[i].Order.SalesChannel);
cmd.Parameters.AddWithValue("@col_ship_service_level", XmlData.Message[i].Order.FulfillmentData.ShipServiceLevel);
cmd.Parameters.AddWithValue("@col_product_name", XmlData.Message[i].Order.OrderItem.ProductName);
cmd.Parameters.AddWithValue("@col_sku", XmlData.Message[i].Order.OrderItem.SKU);
cmd.Parameters.AddWithValue("@col_asin", XmlData.Message[i].Order.OrderItem.ASIN);
cmd.Parameters.AddWithValue("@col_number_of_items", XmlData.Message[i].Order.OrderItem.NumberOfItems);
cmd.Parameters.AddWithValue("@col_item_status", XmlData.Message[i].Order.OrderItem.ItemStatus);
cmd.Parameters.AddWithValue("@col_quantity", XmlData.Message[i].Order.OrderItem.Quantity);
cmd.Parameters.AddWithValue("@col_currency", XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Amount.Currency);
switch (XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Type)
{
case "Principal":
cmd.Parameters.AddWithValue("@col_item_price", XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Amount.Text);
cmd.Parameters.AddWithValue("@col_item_tax", XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Amount.Text);
break;
case "Shipping":
cmd.Parameters.AddWithValue("@col_shipping_price", XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Amount.Text);
break;
case "GiftWrap":
cmd.Parameters.AddWithValue("@col_gift_wrap_price", XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Amount.Text);
break;
case "Shipping-Tax":
cmd.Parameters.AddWithValue("@col_shipping_tax", XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Amount.Text);
break;
default:
cmd.Parameters.AddWithValue("@col_gift_wrap_tax", XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Amount.Text);
break;
}
if (XmlData.Message[i].Order.OrderItem.Promotion == null)
{
cmd.Parameters.AddWithValue("@col_item_promotion_discount", 0);
cmd.Parameters.AddWithValue("@col_ship_promotion_discount", 0);
cmd.Parameters.AddWithValue("@col_promotion_ids", 0);
}
else
{
cmd.Parameters.AddWithValue("@col_item_promotion_discount", XmlData.Message[i].Order.OrderItem.Promotion.ItemPromotionDiscount);
cmd.Parameters.AddWithValue("@col_ship_promotion_discount", XmlData.Message[i].Order.OrderItem.Promotion.ShipPromotionDiscount);
cmd.Parameters.AddWithValue("@col_promotion_ids", XmlData.Message[i].Order.OrderItem.Promotion.PromotionIDs);
}
cmd.Parameters.AddWithValue("@col_ship_city", XmlData.Message[i].Order.FulfillmentData.Address.City);
cmd.Parameters.AddWithValue("@col_ship_state", XmlData.Message[i].Order.FulfillmentData.Address.State);
cmd.Parameters.AddWithValue("@col_ship_postal_code", XmlData.Message[i].Order.FulfillmentData.Address.PostalCode);
cmd.Parameters.AddWithValue("@col_ship_country", XmlData.Message[i].Order.FulfillmentData.Address.Country);
cmd.Parameters.AddWithValue("@col_is_business_order", XmlData.Message[i].Order.IsBusinessOrder);
cmd.Parameters.AddWithValue("@col_purchase_order_number", XmlData.Message[i].Order.PurchaseOrderNumber);
cmd.Parameters.AddWithValue("@col_price_designation", XmlData.Message[i].Order.OrderItem.PriceDesignation);
cmd.Parameters.AddWithValue("@col_fulfilled_by", XmlData.Message[i].Order.FulfilledBy);
cmd.Parameters.AddWithValue("@col_Order_Channel", DBNull.Value);
cmd.Parameters.AddWithValue("@col_url", DBNull.Value);
Console.WriteLine(XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Type);
cmd.ExecuteNonQuery();
}
}
Console.ReadKey();
}
}
有人帮助。
您显然知道如何使用参数化查询，所以为什么不在查询的SELECT部分中使用它：
... like '" + XmlData.Message[i].Order.OrderItem.SKU + "' ...
您还应该在那里使用参数。LIKE可以更改为=，除非XmlData.Message[i].Order.OrderItem.SKU包括通配符，这似乎不太可能。
关于您的问题：
您在查询的INSERT中有@col_shipping_price显然是参数。但是，如果XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Type在switch()中为Shipping时，您才会设置此参数。也就是说，在所有其他情况下，@col_shipping_price都保持在查询中，并且没有替换。SQL Server认为它是一个变量，并且正确地抱怨没有声明它。似乎其他一些参数可能会出现同样的问题。
您在这里有一些选择。
- 始终替换所有参数。如果您没有实际值，可能会使用
DBNull。除非在应有的列上没有零约束。如果您这样做，也无需在每次迭代中重建查询。创建一次，可选地使用Prepare()，然后只需更改迭代中参数的值即可。性能可能会受益。 - 根据
XmlData.Message[i].Order.OrderItem.ItemPrice.Component[j].Type构建不同的查询。这样，您就可以简单地将未使用参数的占位符排除在外。
使用AddWithValue()时，您应该要小心，甚至避免完全使用它。它需要猜测数据库上列的数据类型，有时会惨败并产生有趣的错误。更好地使用具有显式类型参数的Add()过载。关于此主题的内容：" addwithvalue是邪恶的"