/// <summary> /// Customer Inquiry /// </summary> /// <param name="firstName"></param> /// <param name="lastName"></param> /// <param name="paging"></param> /// <returns></returns> public List<CustomerInquiry> CustomerInquiry(string firstName, string lastName, DataGridPagingInformation paging) { string sortExpression = paging.SortExpression; if (paging.SortDirection != string.Empty) sortExpression = sortExpression + " " + paging.SortDirection; var customerQuery = Customers.AsQueryable(); if (firstName != null && firstName.Trim().Length > 0) { customerQuery = customerQuery.Where(c => c.FirstName.StartsWith(firstName)); } if (lastName != null && lastName.Trim().Length > 0) { customerQuery = customerQuery.Where(c => c.LastName.StartsWith(lastName)); } var customers = from c in customerQuery join p in PaymentTypes on c.PaymentTypeID equals p.PaymentTypeID select new { c.CustomerID, c.FirstName, c.LastName, c.EmailAddress, c.City, p.Description }; int numberOfRows = customers.Count(); customers = customers.OrderBy(sortExpression); var customerList = customers.Skip((paging.CurrentPageNumber - 1) * paging.PageSize).Take(paging.PageSize); paging.TotalRows = numberOfRows; paging.TotalPages = Utilities.CalculateTotalPages(numberOfRows, paging.PageSize); List<CustomerInquiry> customerInquiry = new List<CustomerInquiry>(); foreach (var customer in customerList) { CustomerInquiry customerData = new CustomerInquiry(); customerData.CustomerID = customer.CustomerID; customerData.FirstName = customer.FirstName; customerData.LastName = customer.LastName; customerData.EmailAddress = customer.EmailAddress; customerData.City = customer.City; customerData.PaymentTypeDescription = customer.Description; customerInquiry.Add(customerData); } return customerInquiry; }
/// <summary> /// Customer Inquiry /// </summary> /// <param name="firstName"></param> /// <param name="lastName"></param> /// <param name="paging"></param> /// <returns></returns> public List<CustomerInquiry> CustomerInquiry(string firstName, string lastName, DataGridPagingInformation paging) { List<Customer> customers = new List<Customer>(); string sortExpression = paging.SortExpression; int maxRowNumber; int minRowNumber; minRowNumber = (paging.PageSize * (paging.CurrentPageNumber - 1)) + 1; maxRowNumber = paging.PageSize * paging.CurrentPageNumber; StringBuilder sqlBuilder = new StringBuilder(); StringBuilder sqlWhereBuilder = new StringBuilder(); string sqlWhere = string.Empty; if (firstName != null && firstName.Trim().Length > 0) sqlWhereBuilder.Append(" c.FirstName LIKE @FirstName AND "); if (lastName != null && lastName.Trim().Length > 0) sqlWhereBuilder.Append(" c.LastName LIKE @LastName AND "); if (sqlWhereBuilder.Length > 0) sqlWhere = " WHERE " + sqlWhereBuilder.ToString().Substring(0, sqlWhereBuilder.Length - 4); sqlBuilder.Append(" SELECT COUNT(*) as total_records FROM Customers c "); sqlBuilder.Append(sqlWhere); sqlBuilder.Append(";"); sqlBuilder.Append(" SELECT * FROM ( "); sqlBuilder.Append(" SELECT (ROW_NUMBER() OVER (ORDER BY " + paging.SortExpression + " " + paging.SortDirection + ")) as record_number, "); sqlBuilder.Append(" c.*, p.Description as PaymentTypeDescription "); sqlBuilder.Append(" FROM Customers c "); sqlBuilder.Append(" INNER JOIN PaymentTypes p ON p.PaymentTypeID = c.PaymentTypeID "); sqlBuilder.Append(sqlWhere); sqlBuilder.Append(" ) Rows "); sqlBuilder.Append(" where record_number between " + minRowNumber + " and " + maxRowNumber); string sql = sqlBuilder.ToString(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = sql; sqlCommand.Connection = dbConnection; if (firstName != null && firstName.Trim().Length > 0) { sqlCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.VarChar); sqlCommand.Parameters["@FirstName"].Value = firstName + "%"; } if (lastName != null && lastName.Trim().Length > 0) { sqlCommand.Parameters.Add("@LastName", System.Data.SqlDbType.VarChar); sqlCommand.Parameters["@LastName"].Value = lastName + "%"; } SqlDataReader reader = sqlCommand.ExecuteReader(); reader.Read(); paging.TotalRows = Convert.ToInt32(reader["Total_Records"]); paging.TotalPages = Utilities.CalculateTotalPages(paging.TotalRows, paging.PageSize); reader.NextResult(); List<CustomerInquiry> customerList = new List<CustomerInquiry>(); while (reader.Read()) { CustomerInquiry customer = new CustomerInquiry(); DataReader dataReader = new DataReader(reader); customer.CustomerID = dataReader.GetGuid("CustomerID"); customer.FirstName = dataReader.GetString("FirstName"); customer.LastName = dataReader.GetString("LastName"); customer.EmailAddress = dataReader.GetString("EmailAddress"); customer.City = dataReader.GetString("City"); customer.PaymentTypeDescription = dataReader.GetString("PaymentTypeDescription"); customerList.Add(customer); } reader.Close(); return customerList; }