void LoadData() { string clientName = txtName.Value.Trim(); string clientID = txtMerchantID.Text.Trim(); string startDate = txtStartTime.Value.Trim(); string oprID = txtOprID.Value.Trim(); string endDate = txtEndTime.Value.Trim(); WebTradeCondition condition = new WebTradeCondition(); condition.ClientID = clientID; if (!string.IsNullOrEmpty(startDate)) { condition.StartDate = DateTime.Parse(startDate); } if (!string.IsNullOrEmpty(endDate)) { condition.EndDate = DateTime.Parse(endDate).AddDays(1); } HYCashierDAL.CahierMgrDAL dal = new HYCashierDAL.CahierMgrDAL(); condition.OprID = oprID; int?count = 0; count = dal.GetTradeInfoCount(condition); if (count == null) { AspNetPager1.RecordCount = 0; } else { AspNetPager1.RecordCount = (int)count; } DataTable dt = dal.GetTradeInfoPager(condition, AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex); dataGrid.DataSource = dt; dataGrid.DataBind(); }
public DataTable GetTradeInfoPager(WebTradeCondition queryCondition, int pageSize, int pageIndex) { DataTable dt = null; string sql = @"select tradetime, t1.localorderno,t1.clientID,t1.amount,t1.oprid,t1.tradestatus, t1.tradetype, t2.clientName from tradeinfo t1 left join clientinfo t2 on t1.clientid=t2.clientid where tradestatus='Ok' "; List <MySqlParameter> paras = new List <MySqlParameter>(); if (!string.IsNullOrEmpty(queryCondition.ClientID)) { sql += " and t1.ClientID=?ClientID"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?ClientID"; para.Value = queryCondition.ClientID; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.ClientName)) { sql += " and t2.ClientName like ?ClientName"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?ClientName"; para.Value = "%" + queryCondition.ClientName + "%"; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.OprID)) { sql += " and OprID=?OprID"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?OprID"; para.Value = queryCondition.OprID; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.LocalFlowNo)) { sql += " and LocalOrderNo=?LocalOrderNo"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?LocalOrderNo"; para.Value = queryCondition.LocalFlowNo; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.ServerFlowNo)) { sql += " and ServerFlowNo=?ServerFlowNo"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?ServerFlowNo"; para.Value = queryCondition.ServerFlowNo; para.Direction = ParameterDirection.Input; paras.Add(para); } if (queryCondition.StartDate != null) { sql += " and TradeTime>=?StartDate"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?StartDate"; para.Value = queryCondition.StartDate; para.Direction = ParameterDirection.Input; para.DbType = DbType.DateTime; paras.Add(para); } if (queryCondition.EndDate != null) { sql += " and TradeTime<?EndDate"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?EndDate"; para.Value = queryCondition.EndDate; para.Direction = ParameterDirection.Input; para.DbType = DbType.DateTime; paras.Add(para); } int startIndex = pageSize * (pageIndex - 1); sql += " limit " + startIndex.ToString() + " ," + pageSize.ToString();; dt = DbHelper.ExecuteDataTable(sql, paras.ToArray()); return(dt); }
public int GetTradeInfoCount(WebTradeCondition queryCondition) { int count = 0; string sql = @"select count(t1.localorderno) from tradeinfo t1 left join clientinfo t2 on t1.clientID=t2.clientID where tradestatus='Ok' "; List <MySqlParameter> paras = new List <MySqlParameter>(); if (!string.IsNullOrEmpty(queryCondition.ClientID)) { sql += " and t1.ClientID=?ClientID"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?ClientID"; para.Value = queryCondition.ClientID; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.OprID)) { sql += " and OprID=?OprID"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?OprID"; para.Value = queryCondition.OprID; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.LocalFlowNo)) { sql += " and LocalOrderNo=?LocalOrderNo"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?LocalOrderNo"; para.Value = queryCondition.LocalFlowNo; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.ServerFlowNo)) { sql += " and ServerFlowNo=?ServerFlowNo"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?ServerFlowNo"; para.Value = queryCondition.ServerFlowNo; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.ClientName)) { sql += " and t2.ClientName like ?ClientName"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?ClientName"; para.Value = "%" + queryCondition.ClientName + "%"; para.Direction = ParameterDirection.Input; paras.Add(para); } if (queryCondition.StartDate != null) { sql += " and TradeTime>=?StartDate"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?StartDate"; para.Value = queryCondition.StartDate; para.Direction = ParameterDirection.Input; para.DbType = DbType.DateTime; paras.Add(para); } if (queryCondition.EndDate != null) { sql += " and TradeTime<?EndDate"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?EndDate"; para.Value = queryCondition.EndDate; para.Direction = ParameterDirection.Input; para.DbType = DbType.DateTime; paras.Add(para); } object obj = DbHelper.ExecuteScalar(sql, paras.ToArray()); if (obj != null) { count = int.Parse(obj.ToString()); } return(count); }
public int GetTradeInfoCount(WebTradeCondition queryCondition) { int count = 0; string sql = "select count(*) from tradeinfo where 1=1 "; List <MySqlParameter> paras = new List <MySqlParameter>(); if (!string.IsNullOrEmpty(queryCondition.ClientID)) { sql += " and ClientID=?ClientID"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?ClientID"; para.Value = queryCondition.ClientID; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.OprID)) { sql += " and OprID=?OprID"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?OprID"; para.Value = queryCondition.OprID; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.LocalFlowNo)) { sql += " and LocalOrderNo=?LocalOrderNo"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?LocalOrderNo"; para.Value = queryCondition.LocalFlowNo; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.ServerFlowNo)) { sql += " and ServerFlowNo=?ServerFlowNo"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?ServerFlowNo"; para.Value = queryCondition.ServerFlowNo; para.Direction = ParameterDirection.Input; paras.Add(para); } if (queryCondition.StartDate != null) { sql += " and TradeTime>=?StartDate"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?StartDate"; para.Value = queryCondition.StartDate; para.Direction = ParameterDirection.Input; para.DbType = DbType.DateTime; paras.Add(para); } if (queryCondition.EndDate != null) { sql += " and TradeTime<?EndDate"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?EndDate"; para.Value = queryCondition.EndDate; para.Direction = ParameterDirection.Input; para.DbType = DbType.DateTime; paras.Add(para); } count = (int)DbHelper.ExecuteScalar(sql, paras.ToArray()); return(count); }
public DataTable GetTradeInfoPager(WebTradeCondition queryCondition, int pageSize, int pageIndex) { DataTable dt = null; string sql = "select * from tradeinfo where 1=1 "; List <MySqlParameter> paras = new List <MySqlParameter>(); if (!string.IsNullOrEmpty(queryCondition.ClientID)) { sql += " and ClientID=?ClientID"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?ClientID"; para.Value = queryCondition.ClientID; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.OprID)) { sql += " and OprID=?OprID"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?OprID"; para.Value = queryCondition.OprID; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.LocalFlowNo)) { sql += " and LocalOrderNo=?LocalOrderNo"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?LocalOrderNo"; para.Value = queryCondition.LocalFlowNo; para.Direction = ParameterDirection.Input; paras.Add(para); } if (!string.IsNullOrEmpty(queryCondition.ServerFlowNo)) { sql += " and ServerFlowNo=?ServerFlowNo"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?ServerFlowNo"; para.Value = queryCondition.ServerFlowNo; para.Direction = ParameterDirection.Input; paras.Add(para); } if (queryCondition.StartDate != null) { sql += " and TradeTime>=?StartDate"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?StartDate"; para.Value = queryCondition.StartDate; para.Direction = ParameterDirection.Input; para.DbType = DbType.DateTime; paras.Add(para); } if (queryCondition.EndDate != null) { sql += " and TradeTime<?EndDate"; MySqlParameter para = new MySqlParameter(); para.ParameterName = "?EndDate"; para.Value = queryCondition.EndDate; para.Direction = ParameterDirection.Input; para.DbType = DbType.DateTime; paras.Add(para); } int startIndex = pageSize * pageIndex; sql += " limit " + startIndex.ToString() + " ," + pageSize.ToString();; dt = DbHelper.ExecuteDataTable(sql, paras.ToArray()); return(dt); }