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