/// <summary>등록 상품 뷰 Paging 조회</summary> public BmItemVwPagingRs GetItemVwPagingList(BmItemVwPagingRq pDataRq) { try { using (SqlConn = new SqlConnection(ConnectionString)) { using (TransactionScope scope = new TransactionScope()) { try { SqlConn.Open(); var result = dac.GetItemVwPagingList(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 BmItemVwPagingRs GetItemVwPagingList(BmItemVwPagingRq pDataRq) { try { #region SetQuery StringBuilder sbQuery = new StringBuilder(@"SELECT COUNT(I.Seq) as 'TotalRowCnt' FROM tbItem I JOIN tbItemProduct IP ON IP.Seq = I.Seq AND 1=1 --@@ItemProductId --@@ItemProductName WHERE 1=1 --@@ItemId --@@ItemName SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY I.RegDt DESC) AS 'RowNum' , IP.ItemProductId , IP.ItemProductName , I.* FROM tbItem I JOIN tbItemProduct IP ON IP.Seq = I.Seq --@@ItemProductId --@@ItemProductName WHERE 1=1 --@@ItemId --@@ItemName ) A "); if (string.IsNullOrEmpty(pDataRq.Item.ItemId) == false) sbQuery = sbQuery.Replace("--@@ItemId", " AND I.ItemId LIKE '%' + @ItemId + '%'"); if (string.IsNullOrEmpty(pDataRq.Item.ItemName) == false) sbQuery = sbQuery.Replace("--@@ItemName", " AND I.ItemName LIKE '%' + @ItemName + '%'"); if (string.IsNullOrEmpty(pDataRq.Item.ItemProductId) == false) sbQuery = sbQuery.Replace("--@@ItemProductId", " AND IP.ItemProductId LIKE '%' + @ItemProductId + '%'"); if (string.IsNullOrEmpty(pDataRq.Item.ItemProductName) == false) sbQuery = sbQuery.Replace("--@@ItemProductName", " AND IP.ItemProductName LIKE '%' + @ItemProductName + '%'"); sbQuery.AppendLine(" WHERE RowNum BETWEEN (@PageSize * @CurPage) + 1 AND ((@PageSize * @CurPage) + @PageSize)"); #endregion SetQuery BmItemVwPagingRs result = new BmItemVwPagingRs(); 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 (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; if (string.IsNullOrEmpty(pDataRq.Item.ItemProductId) == false) cmd.Parameters.Add("@ItemProductId", SqlDbType.VarChar, 20).Value = pDataRq.Item.ItemProductId; if (string.IsNullOrEmpty(pDataRq.Item.ItemProductName) == false) cmd.Parameters.Add("@ItemProductName", SqlDbType.VarChar, 100).Value = pDataRq.Item.ItemProductName; #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 = ConvertToBmVwItemMgmt(ds.Tables[1]); } } da.Dispose(); cmd.Dispose(); return result; } catch (Exception ex) { throw ex; } }