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