/// <summary>등록 상품 Paging 조회</summary> public BmItemPagingRs GetItemPagingList(BmItemPagingRq pDataRq) { try { using (SqlConn = new SqlConnection(ConnectionString)) { using (TransactionScope scope = new TransactionScope()) { try { SqlConn.Open(); var result = dac.GetItemPagingList(pDataRq); scope.Complete(); return result; } catch (Exception ex) { throw ex; } finally { SqlConn.Dispose(); } } } } catch (Exception ex) { WriteLog("Exception", ex.Message); throw; } }
/// <summary>등록 상품 Paging 조회</summary> public BmItemPagingRs GetItemPagingList(BmItemPagingRq pDataRq) { try { #region SetQuery StringBuilder sbQuery = new StringBuilder(@"SELECT COUNT(Seq) AS 'TotalRowCnt' FROM tbItem I WHERE 1=1 --@@I.ItemProductSeq --@@I.ItemId --@@I.ItemName SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY I.RegDt DESC) AS 'RowNum' , I.* , ISNULL(SUM(IQ.Quantity), 0) AS 'ItemQuantity' , ISNULL(SUM(IQ.SalesCount), 0) AS 'ItemSalesCount' , ISNULL(SUM(I.Price * IQ.SalesCount), 0) AS 'ItemSalesPrice' FROM tbItem I LEFT JOIN tbItemQuantity IQ ON I.Seq = IQ.ItemSeq WHERE 1=1 --@@I.ItemProductSeq --@@I.ItemId --@@I.ItemName GROUP BY I.Seq , I.ItemId , I.ItemProductSeq , I.ItemName , I.Descript , I.Price , I.State , I.Registrant , I.RegDt , I.Modifyer , I.ModifyDt ) A WHERE 1=1"); if (pDataRq.Item.ItemProductSeq > 0) sbQuery = sbQuery.Replace("--@@I.ItemProductSeq", " AND I.ItemProductSeq = @ItemProductSeq"); if (string.IsNullOrEmpty(pDataRq.Item.ItemId) == false) sbQuery = sbQuery.Replace("--@@I.ItemId", " AND I.ItemId LIKE '%' + @ItemId + '%'"); if (string.IsNullOrEmpty(pDataRq.Item.ItemName) == false) sbQuery = sbQuery.Replace("--@@I.ItemName", " AND I.ItemName LIKE '%' + @ItemName + '%'"); sbQuery.AppendLine(" AND RowNum BETWEEN (@PageSize * @CurPage) + 1 AND ((@PageSize * @CurPage) + @PageSize)"); #endregion SetQuery BmItemPagingRs result = new BmItemPagingRs(); SqlCommand cmd = new SqlCommand(); cmd.Connection = SqlConn; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = sbQuery.ToString(); #region Set Parameters cmd.Parameters.Add("@PageSize", SqlDbType.Int, 0).Value = pDataRq.Paging.PageSize; cmd.Parameters.Add("@CurPage", SqlDbType.Int, 0).Value = pDataRq.Paging.CurPage; if (pDataRq.Item.ItemProductSeq > 0) cmd.Parameters.Add("@ItemProductSeq", SqlDbType.Int, 0).Value = pDataRq.Item.ItemProductSeq; if (string.IsNullOrEmpty(pDataRq.Item.ItemId) == false) cmd.Parameters.Add("@ItemId", SqlDbType.VarChar, 20).Value = pDataRq.Item.ItemId; if (string.IsNullOrEmpty(pDataRq.Item.ItemName) == false) cmd.Parameters.Add("@ItemName", SqlDbType.VarChar, 100).Value = pDataRq.Item.ItemName; #endregion Set Parameters SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); if (ds.Tables[0].Rows.Count == 1) { result.TotalCount = Convert.ToInt32(ds.Tables[0].Rows[0]["TotalRowCnt"].ToString()); if (result.TotalCount > 0 && ds.Tables[1].Rows.Count > 0) { result.List = ConvertToBmItem(ds.Tables[1]); } } da.Dispose(); cmd.Dispose(); return result; } catch (Exception ex) { throw ex; } }