减少与数据库的交互

因为新手所以会犯一些不经意的错误，导致程序运行效率低下，在数据量小的时候还可以接受，但当数据量超过一定量级，就会造成用户体验度急骤下降。

近期参与一个仓库管理系统的开发，需要在出库时以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

 

转载于:https://www.cnblogs.com/yjlemail/archive/2012/08/30/2664531.html