private void Search()
{
try
{
BizLog bizLog = new BizLog();
BmUpdateHistoryPagingRq bmUpdateHistroyRq = new BmUpdateHistoryPagingRq();
bmUpdateHistroyRq.Paging.CurPage = ucPaging.CurPage;
bmUpdateHistroyRq.Paging.PageSize = ucPaging.PageSize;
if (!string.IsNullOrEmpty(tbxStartDate.Text) && !string.IsNullOrEmpty(tbxEndDate.Text))
{
bmUpdateHistroyRq.StartDate = Convert.ToDateTime(tbxStartDate.Text + " 00:00:00");
bmUpdateHistroyRq.EndDate = Convert.ToDateTime(tbxEndDate.Text + " 23:59:59");
}
if (ddlCmdType.SelectedValue != "-1") bmUpdateHistroyRq.UpdateHistory.CmdCode = Convert.ToInt32(ddlCmdType.SelectedValue);
if (ddlObjectType.SelectedValue != "-1") bmUpdateHistroyRq.UpdateHistory.ObjectType = ddlObjectType.SelectedValue;
if (!string.IsNullOrEmpty(tbxSearch.Text))
{
if (ddlSearchType.SelectedValue == "1") bmUpdateHistroyRq.UpdateHistory.ObjectSeq = Convert.ToInt32(tbxSearch.Text);
else if (ddlSearchType.SelectedValue == "2") bmUpdateHistroyRq.UpdateHistory.ObjectData = tbxSearch.Text;
else bmUpdateHistroyRq.UpdateHistory.Registrant = tbxSearch.Text;
}
var result = bizLog.GetUpdateHistoryPagingList(bmUpdateHistroyRq);
ucPaging.TotalRowCount = result.TotalCount;
rptUpdateHistoryList.DataSource = result.List;
rptUpdateHistoryList.DataBind();
}
catch (Exception ex)
{
cLib.WriteLog("Exception", ex.Message);
}
}
/// <summary>변경내역 Paging 조회</summary>
public BmUpdateHistoryPagingRs GetUpdateHistoryPagingList(BmUpdateHistoryPagingRq pDataRq)
{
try
{
using (SqlConn = new SqlConnection(ConnectionString))
{
using (TransactionScope scope = new TransactionScope())
{
try
{
SqlConn.Open();
var result = dac.GetUpdateHistoryPagingList(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 BmUpdateHistoryPagingRs GetUpdateHistoryPagingList(BmUpdateHistoryPagingRq pDataRq)
{
try
{
#region SetQuery
StringBuilder sbQuery = new StringBuilder(@"SELECT COUNT(Seq) AS 'TotalRowCnt' FROM tbUpdateHistory
WHERE 1=1
--@@CmdCode
--@@ObjectType
--@@ObjectSeq
--@@ObjectData
--@@PageMethod
--@@Registrant
--@@RegDt
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY RegDt DESC) AS 'RowNum'
, *
FROM
tbUpdateHistory
WHERE 1=1
--@@CmdCode
--@@ObjectType
--@@ObjectSeq
--@@ObjectData
--@@PageMethod
--@@Registrant
--@@RegDt
) A
WHERE 1=1");
if (pDataRq.UpdateHistory.CmdCode > 0)
sbQuery = sbQuery.Replace("--@@CmdCode", " AND CmdCode = @CmdCode");
if (string.IsNullOrEmpty(pDataRq.UpdateHistory.ObjectType) == false)
sbQuery = sbQuery.Replace("--@@ObjectType", " AND ObjectType = @ObjectType");
if (pDataRq.UpdateHistory.ObjectSeq > 0)
sbQuery = sbQuery.Replace("--@@ObjectSeq", " AND ObjectSeq = @ObjectSeq");
if (string.IsNullOrEmpty(pDataRq.UpdateHistory.ObjectData) == false)
sbQuery = sbQuery.Replace("--@@ObjectData", " AND ObjectData LIKE '%' + @ObjectData + '%'");
if (string.IsNullOrEmpty(pDataRq.UpdateHistory.PageMethod) == false)
sbQuery = sbQuery.Replace("--@@PageMethod", " AND PageMethod = @PageMethod");
if (string.IsNullOrEmpty(pDataRq.UpdateHistory.Registrant) == false)
sbQuery = sbQuery.Replace("--@@Registrant", " AND Registrant = @Registrant");
if (pDataRq.StartDate != null && pDataRq.EndDate != null)
sbQuery = sbQuery.Replace("--@@RegDt", " AND RegDt BETWEEN @StartDate AND @EndDate");
sbQuery.AppendLine(" AND RowNum BETWEEN (@PageSize * @CurPage) + 1 AND ((@PageSize * @CurPage) + @PageSize)");
#endregion SetQuery
BmUpdateHistoryPagingRs result = new BmUpdateHistoryPagingRs();
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.UpdateHistory.CmdCode > 0)
cmd.Parameters.Add("@CmdCode", SqlDbType.Int, 0).Value = pDataRq.UpdateHistory.CmdCode;
if (string.IsNullOrEmpty(pDataRq.UpdateHistory.ObjectType) == false)
cmd.Parameters.Add("@ObjectType", SqlDbType.VarChar, 100).Value = pDataRq.UpdateHistory.ObjectType;
if (pDataRq.UpdateHistory.ObjectSeq > 0)
cmd.Parameters.Add("@ObjectSeq", SqlDbType.Int, 0).Value = pDataRq.UpdateHistory.ObjectSeq;
if (string.IsNullOrEmpty(pDataRq.UpdateHistory.PageMethod) == false)
cmd.Parameters.Add("@PageMethod", SqlDbType.VarChar, 100).Value = pDataRq.UpdateHistory.PageMethod;
if (string.IsNullOrEmpty(pDataRq.UpdateHistory.ObjectData) == false)
cmd.Parameters.Add("@ObjectData", SqlDbType.VarChar).Value = pDataRq.UpdateHistory.ObjectData;
if (string.IsNullOrEmpty(pDataRq.UpdateHistory.Registrant) == false)
cmd.Parameters.Add("@Registrant", SqlDbType.VarChar, 20).Value = pDataRq.UpdateHistory.Registrant;
if (pDataRq.StartDate != null && pDataRq.EndDate != null)
{
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = pDataRq.StartDate;
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = pDataRq.EndDate;
}
#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 = ConvertToBmUpdateHistory(ds.Tables[1]);
}
}
da.Dispose();
cmd.Dispose();
return result;
}
catch (Exception ex)
{
throw ex;
}
}