下面是SQL语句！
1 private static string GetInsertInvMasterSql(bool isBestBuy)
2 {
3 StringBuilder sb = new StringBuilder(1000);
4 sb.AppendLine("IF EXISTS(SELECT * FROM InvMasterData WHERE Sku=@Sku)");
5 sb.AppendLine("UPDATE InvMasterData SET ");
6 sb.Append("Channel=@Channel,");
7 sb.Append("Title=@Title,");
8 sb.Append("Price=@Price,");
9 sb.Append("ListingType=@ListingType,");
10 sb.Append("ItemType=@ItemType,");
11 sb.Append("Template=@Template,");
12 sb.Append("Commission_Rate=@Commission_Rate,");
13 sb.Append("Upc=@Upc,");
14 sb.Append("BDC_SKU=@BDC_SKU,");
15 sb.Append("Note=@Note,");
16 if (isBestBuy)
17 {
18 sb.Append("BBY_SKU=@BBY_SKU,");
19 }
20 sb.Append("LastUpdate=GETDATE() ");
21 sb.Append("WHERE Sku=@Sku ");
22 sb.AppendLine("ELSE");
23 sb.AppendLine("INSERT INTO InvMasterData(");
24 sb.Append("Channel,Sku,Component,Title,Price,");
25 sb.Append("ListingType,ItemType,");
26 sb.Append("Template,Commission_Rate,Upc,BDC_SKU,");
27 if (isBestBuy)
28 {
29 sb.Append("BBY_SKU,");
30 }
31 sb.Append("Note,");
32 sb.Append("EnterDate,LastUpdate)");
33 sb.Append("VALUES(");
34 sb.Append("@Channel,@Sku,@Component,@Title,@Price,");
35 sb.Append("@ListingType,@ItemType,");
36 sb.Append("@Template,@Commission_Rate,@Upc,@BDC_SKU,");
37 if (isBestBuy)
38 {
39 sb.Append("@BBY_SKU,");
40 }
41 sb.Append("@Note,");
42 sb.Append("GETDATE(),GETDATE())");
43 return sb.ToString();
44 }
1 public static void InsertInvMasterData(BackgroundWorker bgWorker, bool isBestBuy, DataTable importDt, ConnectionStringSettings newChannelInvDb, ConnectionStringSettings omsDataConn,out DataTable notInInvSkuDt)
2 {
3 string functionMsg = "Function:InsertChannelDt(BackgroundWorker bgWorker, bool isBestBuy, DataTable importDt, ConnectionStringSettings importDataConn)" + _NewLine + _ClassMsg;
4 int rowCount = importDt.Rows.Count;
5 DataTable skuDt = GetDataTablePartColumns(importDt, "Sku");
6 Dictionary<string, string> skuAndComponentDict = GetSkuAndComponetDict(skuDt, omsDataConn);
7 List<string> notInInvSkuList = GetNotInInvSkuList(skuDt, omsDataConn);
8 notInInvSkuDt = new DataTable();
9 notInInvSkuDt.Columns.AddRange(new DataColumn[] { new DataColumn("Sku", typeof(string)), new DataColumn("Message", typeof(string)) });
10 SqlTransaction sqlTran = null;
11 try
12 {
13 SqlConnection sqlConn = new SqlConnection(newChannelInvDb.ConnectionString);
14 if (sqlConn.State == ConnectionState.Closed || sqlConn.State == ConnectionState.Broken)
15 {
16 sqlConn.Open();
17 }
18 string strSql = GetInsertInvMasterSql(isBestBuy);
19 sqlTran = sqlConn.BeginTransaction();
20 SqlCommand sqlCmd = new SqlCommand(strSql, sqlConn, sqlTran);
21 sqlCmd.CommandTimeout = 600;
22 for (int i = 0; i < rowCount; i++)
23 {
24 string sku = (importDt.Rows[i]["Sku"] ?? string.Empty).ToString();
25 int progressPer = (int)((((decimal)i + 1.0m) / (decimal)rowCount) * 95);
26 string userState = string.Format("Import...{0}/{1}", i + 1, rowCount);
27 bgWorker.ReportProgress(progressPer, userState);
28 sqlCmd.Parameters.Clear();
29 if (!notInInvSkuList.Contains(sku))
30 {
31 foreach (DataColumn dc in importDt.Columns)
32 {
33 string columnName = dc.ColumnName;
34 if (columnName.ToUpper() == "BDC SKU")
35 {
36 sqlCmd.Parameters.Add(new SqlParameter("@BDC_SKU", importDt.Rows[i][columnName]));
37 }
38 else if (columnName.ToUpper() == "BBY SKU")
39 {
40 sqlCmd.Parameters.Add(new SqlParameter("@BBY_SKU", importDt.Rows[i][columnName]));
41 }
42 else if (columnName.ToLower() == "commission rate")
43 {
44 sqlCmd.Parameters.Add(new SqlParameter("@Commission_Rate", importDt.Rows[i][columnName]));
45 }
46 else
47 {
48 sqlCmd.Parameters.Add(new SqlParameter("@" + columnName, importDt.Rows[i][columnName]));
49 }
50 }
51 sqlCmd.Parameters.Add(new SqlParameter("@Component", skuAndComponentDict[sku]));
52 sqlCmd.ExecuteNonQuery();
53 }
54 else
55 {
56 notInInvSkuDt.Rows.Add(sku, "Sku is not existed in OMS");
57 }
58 }
59 sqlTran.Commit();
60 sqlConn.Close();
61 sqlConn.Dispose();
62 bgWorker.ReportProgress(100, "Complete");
63 }
64 catch (Exception ex)
65 {
66 if (sqlTran != null)
67 {
68 sqlTran.Rollback();
69 }
70 string exMsg = "Exception: " + ex.ToString() + _NewLine + functionMsg;
71 throw new Exception(exMsg, ex);
72 }
73 }
导入的时候，先检查是否存在，存在就去更新，否则就导入！感觉速度好慢，如何提高，给点意见吧！
- 5 回答
- 0 关注
- 505 浏览
添加回答
举报
0/150
提交
取消