减少与数据库的交互
因为新手所以会犯一些不经意的错误,导致程序运行效率低下,在数据量小的时候还可以接受,但当数据量超过一定量级,就会造成用户体验度急骤下降。
近期参与一个仓库管理系统的开发,需要在出库时以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();
}
}
#endregion
return 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