private void Search() { try { CustomerQueryInfo queryInfo = new CustomerQueryInfo(); queryInfo.Number = CommonUtil.FilterInput(txtNumber.Text); queryInfo.Owner = CommonUtil.FilterInput(txtOwner.Text); queryInfo.Mobile = CommonUtil.FilterInput(txtMobile.Text); List<CustomerListInfo> customerList = customer.Search(queryInfo); dgvList.DataSource = customerList; } catch (Exception ex) { UserInterfaceExceptionHandler.HandlerException("查询客户信息失败!", ref ex); } }
/// <summary> /// 查询招揽信息 /// </summary> /// <returns>招揽信息集合</returns> public List<CustomerListInfo> Search(CustomerQueryInfo queryInfo) { List<CustomerListInfo> customerList = new List<CustomerListInfo>(); try { customerList = customer.Search(queryInfo); } catch (Exception ex) { BusinessExceptionHandler.HandlerException("查询招揽信息失败!", ex); } return customerList; }
/// <summary> /// 获得所有招揽信息集合 /// </summary> /// <returns>招揽信息集合</returns> public List<CustomerListInfo> Search(CustomerQueryInfo queryInfo) { IDbConnection connection = null; List<CustomerListInfo> solicitList = new List<CustomerListInfo>(); try { string field = "c.Number, c.Model, c.Displacement, b.Owner, c.BodyColor, c.RegisterDate, c.Mileage, c.UpdateDate " + "a.CustomerID, a.CarID "; // + 客户顾问Solicitor字段 string table = "Customer b, Car c"; string order = "ORDER BY c.Number"; StringBuilder filter = new StringBuilder(); #region 查询条件 filter.Append("and c.CustomerId=b.ID "); if (!string.IsNullOrWhiteSpace(queryInfo.Number)) filter.Append("and c.Number like '%'+@Number+'%' "); if (!string.IsNullOrWhiteSpace(queryInfo.Owner)) filter.Append("and b.Owner like '%'+@Owner+'%' "); if (!string.IsNullOrWhiteSpace(queryInfo.Mobile)) filter.Append("and b.Mobile like '%'+@Mobile+'%' "); #endregion string filterText = string.Empty; if (filter.Length > 0) { filterText = filter.ToString().TrimStart(' ').Remove(0, 3).Insert(0, " WHERE "); } string commandText = string.Format("SELECT COUNT(*) FROM {0} {1}", table, filterText); connection = base.CreateConnection(CarManageConfig.Instance.ConnectionString); queryInfo.TotalCount = base.ExecuteObject<int>(commandText: commandText, connection: connection, param: queryInfo); if (queryInfo.TotalCount.Equals(0)) return solicitList; int pageCount = queryInfo.TotalCount / queryInfo.PageSize + 1; if (queryInfo.TotalCount % queryInfo.PageSize != 0) pageCount++; int startIndex = queryInfo.PageIndex * queryInfo.PageSize; commandText = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3} LIMIT {4},{5}", field, table, filterText, order, startIndex, queryInfo.PageSize); solicitList = base.Query<CustomerListInfo>(commandText, connection, param: queryInfo).ToList(); } catch (Exception ex) { DataAccessExceptionHandler.HandlerException( "查询客户列表信息失败!", ex); } finally { CloseConnection(connection); } return solicitList; }