因为新手所以会犯一些不经意的错误，导致程序运行效率低下，在数据量小的时候还可以接受，但当数据量超过一定量级，就会造成用户体验度急骤下降。
近期参与一个仓库管理系统的开发，需要在出库时以SKU为条件对库存进行检查，以免出库数量大于库存数量。原程序是一条一条取SKU然后与库存表进行对比，例如出库订单条数有100条，库存有1000条，那么就需发比对100*1000次，需要与数据库交互100次，这样效率实在低下。我们需要换一种思路，先将需要的库存数据一次性查出，使用 selec 需要的字段 from 库存表 where SKUID in (100行出库订单的 SKUID) ，这样只和数据库交互一次就可以取到需要的所有数据。然后以SKUID汇总出库订单的数量与查询结果对比即可完成与库存的比较。
#region 生成拣货单事件
public void btn_AddPickGoodsOrder_click(object send, EventArgs e) { #region 有效性检测 if (!chkPickGoods()) { MessageBox.Show("未选择订单", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } #endregion#region 检查库存
StateBar sb = new StateBar(); sb.Show("系统正在检查库存，请稍等...", true);In_GoodsSkuObject[] searchGoodsSkuIDObj = GetSearchGoodsNumberObj(frm.dgv_OutBoundOrderList, frm.long_custrmerId);//得到货物的GoodsID和扩展属性
GoodsNumberViewAndPropertyInfosObject[] resObj = CheckGoodsNumber.GetGoodsNumberByGoodsIDArr(searchGoodsSkuIDObj);//根据GoodsID和扩展属性得到库存数量string _ErroText="";
long currentSKUID; #region 循环验证拣货单库存 for (int i = 0; i < frm.dgv_OutBoundOrderList.RowCount;i++ ) { //currentSKUID = long.Parse(frm.dgv_OutBoundOrderList.Rows[i].Cells["dataGridViewTextBoxColumn5"].Value.ToString()); if (resObj[i].GoodsNumberViewObject != null && resObj[i].GoodsNumberViewObject.GoodsSkuID != null) { currentSKUID = (long)resObj[i].GoodsNumberViewObject.GoodsSkuID; } else { frm.dgv_OutBoundOrderList.Rows[i].Cells["GoodsCode"].ErrorText = "此货物暂时无库存"; _ErroText += "第" + (i + 1).ToString() + "行货物暂时无库存" + "\r\n"; continue; } if (resObj.Where(n => n.GoodsNumberViewObject.GoodsSkuID == currentSKUID).Count() > 0) { if (resObj.Where(n => n.GoodsNumberViewObject.GoodsSkuID == currentSKUID).First().GoodsNumberViewObject.StoreNumber >= Int32.Parse(frm.dgv_OutBoundOrderList.Rows[i].Cells["number"].Value.ToString())) { resObj.Where(n => n.GoodsNumberViewObject.GoodsSkuID == currentSKUID).First().GoodsNumberViewObject.StoreNumber = resObj.Where(n => n.GoodsNumberViewObject.GoodsSkuID == currentSKUID).First().GoodsNumberViewObject.StoreNumber - Int32.Parse(frm.dgv_OutBoundOrderList.Rows[i].Cells["number"].Value.ToString()); } else { frm.dgv_OutBoundOrderList.Rows[i].Cells["GoodsCode"].ErrorText = "此货物暂时无库存"; _ErroText += "第" + (i + 1).ToString() + "行货物暂时无库存" + "\r\n"; } } else { frm.dgv_OutBoundOrderList.Rows[i].Cells["GoodsCode"].ErrorText = "此货物暂时无库存"; _ErroText += "第" + (i + 1).ToString() + "行货物暂时无库存" + "\r\n"; } } #endregion sb.Close(); if (_ErroText != "") //检查库存 { MessageBox.Show(_ErroText, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } #endregion
#region 得到货物ID和扩展属性
/// <summary> /// 得到货物ID和扩展属性 /// </summary> /// <param name="dgv"></param> /// <param name="ClientID"></param> /// <returns></returns> private static In_GoodsSkuObject[] GetSearchGoodsNumberObj(XDataGridView dgv, long ClientID) { //int fixedcolumnCount = 28;//固定列个数 In_GoodsSkuObject[] searchObj = new In_GoodsSkuObject[dgv.Rows.Count]; DataGridViewRow dgvRow;#region 得到查询GoodsSkuID所需要的对象
for (int i = 0, j = 0; i < dgv.Rows.Count; i++, j = 0) { dgvRow = dgv.Rows[i]; searchObj[i] = new In_GoodsSkuObject(); searchObj[i].ClientID = ClientID;//客户编号 if (dgvRow.Cells["GoodsID"].Value != null && dgvRow.Cells["GoodsID"].Value.ToString() != "") { searchObj[i].GoodsID = Convert.ToInt64(dgvRow.Cells["GoodsID"].Value.ToString()); //货物ID } else { searchObj[i].GoodsID = 0; } //PropertyInfo1 if (dgv.Columns.Contains("PropertyInfo1") == true && dgvRow.Cells["PropertyInfo1"].Value != null && dgvRow.Cells["PropertyInfo1"].Value.ToString() != "") { searchObj[i].PropertyInfo1 = dgvRow.Cells["PropertyInfo1"].Value.ToString(); } //PropertyInfo2 if (dgv.Columns.Contains("PropertyInfo2") == true && dgvRow.Cells["PropertyInfo2"].Value != null && dgvRow.Cells["PropertyInfo2"].Value.ToString() != "") { searchObj[i].PropertyInfo2 = dgvRow.Cells["PropertyInfo2"].Value.ToString(); } //PropertyInfo3 if (dgv.Columns.Contains("PropertyInfo3") == true && dgvRow.Cells["PropertyInfo3"].Value != null && dgvRow.Cells["PropertyInfo3"].Value.ToString() != "") { searchObj[i].PropertyInfo3 = dgvRow.Cells["PropertyInfo3"].Value.ToString(); } //PropertyInfo4 if (dgv.Columns.Contains("PropertyInfo4") == true && dgvRow.Cells["PropertyInfo4"].Value != null && dgvRow.Cells["PropertyInfo4"].Value.ToString() != "") { searchObj[i].PropertyInfo4 = dgvRow.Cells["PropertyInfo4"].Value.ToString(); } //PropertyInfo5 if (dgv.Columns.Contains("PropertyInfo5") == true && dgvRow.Cells["PropertyInfo5"].Value != null && dgvRow.Cells["PropertyInfo5"].Value.ToString() != "") { searchObj[i].PropertyInfo5 = dgvRow.Cells["PropertyInfo5"].Value.ToString(); } //PropertyInfo6 if (dgv.Columns.Contains("PropertyInfo6") == true && dgvRow.Cells["PropertyInfo6"].Value != null && dgvRow.Cells["PropertyInfo6"].Value.ToString() != "") { searchObj[i].PropertyInfo6 = dgvRow.Cells["PropertyInfo6"].Value.ToString(); } //PropertyInfo7 if (dgv.Columns.Contains("PropertyInfo7") == true && dgvRow.Cells["PropertyInfo7"].Value != null && dgvRow.Cells["PropertyInfo7"].Value.ToString() != "") { searchObj[i].PropertyInfo7 = dgvRow.Cells["PropertyInfo7"].Value.ToString(); } //PropertyInfo8 if (dgv.Columns.Contains("PropertyInfo8") == true && dgvRow.Cells["PropertyInfo8"].Value != null && dgvRow.Cells["PropertyInfo8"].Value.ToString() != "") { searchObj[i].PropertyInfo8 = dgvRow.Cells["PropertyInfo8"].Value.ToString(); } //PropertyInfo9 if (dgv.Columns.Contains("PropertyInfo9") == true && dgvRow.Cells["PropertyInfo9"].Value != null && dgvRow.Cells["PropertyInfo9"].Value.ToString() != "") { searchObj[i].PropertyInfo9 = dgvRow.Cells["PropertyInfo9"].Value.ToString(); } //PropertyInfo10 if (dgv.Columns.Contains("PropertyInfo10") == true && dgvRow.Cells["PropertyInfo10"].Value != null && dgvRow.Cells["PropertyInfo10"].Value.ToString() != "") { searchObj[i].PropertyInfo10 = dgvRow.Cells["PropertyInfo10"].Value.ToString(); } //PropertyInfo11 if (dgv.Columns.Contains("PropertyInfo11") == true && dgvRow.Cells["PropertyInfo11"].Value != null && dgvRow.Cells["PropertyInfo11"].Value.ToString() != "") { searchObj[i].PropertyInfo11 = dgvRow.Cells["PropertyInfo11"].Value.ToString(); } //PropertyInfo12 if (dgv.Columns.Contains("PropertyInfo12") == true && dgvRow.Cells["PropertyInfo12"].Value != null && dgvRow.Cells["PropertyInfo12"].Value.ToString() != "") { searchObj[i].PropertyInfo12 = dgvRow.Cells["PropertyInfo12"].Value.ToString(); } } #endregionreturn searchObj;
} #endregion
#region 批量查询可用库存数量
/// <summary> /// 根据GoodsID和扩展属性 /// 批量查询可用库存数量 /// </summary> /// <param name="In_GoodsSkuObject">存储GoodsID、Client、扩展属性</param> /// <returns>存储可用库存数量的数组</returns> public static GoodsNumberViewAndPropertyInfosObject[] GetGoodsNumberByGoodsIDArr(In_GoodsSkuObject[] GoodsSkuInfos) { GoodsNumberViewAndPropertyInfosObject[] Result = new GoodsNumberViewAndPropertyInfosObject[GoodsSkuInfos.Length]; try { IGoodsNumberView Proxy = SPF.Create<IGoodsNumberView>("GoodsNumberView_SRV"); Result = Proxy.SelectAvailableGoodsNumberArr(GoodsSkuInfos); } catch (Exception exception) { throw exception; } return Result; }#endregion