/// <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;
}
}
