public IEnumerable <string> GetCities(string prefix, string state, int maxResults)
{
string query = string.Format(
@"(State = '{0}' OR '{0}' = '')
AND City LIKE '{1}%'",
InputValidator.EncodeQueryStringParameter(state),
InputValidator.EncodeQueryStringParameter(prefix));
DataView view = new DataView(_data.Zip);
view.RowFilter = query;
view.Sort = "City";
string previousCity = null;
int rowsRemaining = maxResults;
foreach (DataRowView viewRow in view)
{
PostalInfoLookupDataSet.ZipRow row = (PostalInfoLookupDataSet.ZipRow)viewRow.Row;
//The following is to remove duplicate cities, because there is no select DISTINCT clause on in-memory datasets.
if (row.City != previousCity)
{
previousCity = row.City;
yield return(row.City);
if (--rowsRemaining == 0)
{
yield break;
}
}
}
}
public void EncodeQueryStringParameterEscapesQuotes()
{
string inputParameter = "'";
string expected = "''";
string actual = InputValidator.EncodeQueryStringParameter(inputParameter);
Assert.AreEqual(expected, actual);
}
public Product GetProductBySku(string productSku)
{
productSku = InputValidator.EncodeQueryStringParameter(productSku);
OrdersManagementDataSet.ProductsRow[] productRows = repository.Products.Select(string.Format(CultureInfo.InvariantCulture, "ProductSKU = '{0}'", productSku)) as OrdersManagementDataSet.ProductsRow[];
if (productRows.Length > 0)
{
return(TranslateFromProductsRowToProductEntity(productRows[0]));
}
return(null);
}
public Customer GetCustomerByName(string name)
{
name = InputValidator.EncodeQueryStringParameter(name);
DataRow[] rows = repository.Customers.Select(String.Format(CultureInfo.CurrentCulture, "CompanyName='{0}'", name));
if (rows != null && rows.Length > 0)
{
return(TranslateFromCustomerRowToCustomerEntity(rows[0] as OrdersManagementDataSet.CustomersRow));
}
return(null);
}
public IList <Customer> GetCustomersByNamePrefix(string namePrefix)
{
namePrefix = InputValidator.EncodeQueryStringParameter(namePrefix);
DataRow[] rows = repository.Customers.Select(String.Format(CultureInfo.CurrentCulture, "CompanyName LIKE '{0}*'", namePrefix));
IList <Customer> customers = new List <Customer>(rows.Length);
foreach (OrdersManagementDataSet.CustomersRow customersRow in rows)
{
customers.Add(TranslateFromCustomerRowToCustomerEntity(customersRow));
}
return(customers);
}
public IList <Product> SearchProducts(string searchText)
{
searchText = InputValidator.EncodeQueryStringParameter(searchText);
OrdersManagementDataSet.ProductsRow[] productRows = repository.Products.Select(string.Format(CultureInfo.InvariantCulture, "ProductName like '*{0}*' OR Description like '*{0}*' OR ProductSku like '{0}*'", searchText)) as OrdersManagementDataSet.ProductsRow[];
IList <Product> productList = new List <Product>();
foreach (OrdersManagementDataSet.ProductsRow row in productRows)
{
productList.Add(TranslateFromProductsRowToProductEntity(row));
}
return(productList);
}
public ICollection <Order> SearchOrders(string searchText)
{
searchText = InputValidator.EncodeQueryStringParameter(searchText);
string query = string.Format(CultureInfo.InvariantCulture, "OrderName like '*{0}*' OR Description like '*{0}*'", searchText);
OrdersManagementDataSet.OrdersRow[] ordersRows = repository.Orders.Select(query) as OrdersManagementDataSet.OrdersRow[];
ICollection <Order> orderList = new Collection <Order>();
foreach (OrdersManagementDataSet.OrdersRow row in ordersRows)
{
Order order = TranslateFromOrdersRowToOrderEntityWithDetails(row);
orderList.Add(order);
}
return(orderList);
}
public IList <Customer> SearchCustomers(string companyName, string city, string state, string zipCode, string address)
{
companyName = InputValidator.EncodeQueryStringParameter(companyName);
city = InputValidator.EncodeQueryStringParameter(city);
state = InputValidator.EncodeQueryStringParameter(state);
zipCode = InputValidator.EncodeQueryStringParameter(zipCode);
address = InputValidator.EncodeQueryStringParameter(address);
// LIKE comparisons are done under the premise that there are not any NULL values in the DataStore.
DataRow[] rows = repository.Customers.Select(String.Format(CultureInfo.CurrentCulture, "CompanyName LIKE '*{0}*' AND (City LIKE '*{1}*') AND (Region LIKE '*{2}*') AND (PostalCode LIKE '*{3}*') AND (Address LIKE '*{4}*')", companyName, city, state, zipCode, address));
IList <Customer> customers = new List <Customer>(rows.Length);
foreach (OrdersManagementDataSet.CustomersRow customersRow in rows)
{
customers.Add(TranslateFromCustomerRowToCustomerEntity(customersRow));
}
return(customers);
}
public IEnumerable <ZipCode> GetZipCodes(string prefix, string city, string state, int maxResults)
{
string query = string.Format(@"(State = '{0}' OR '{0}' = '')
AND (City = '{1}' OR '{1}' = '')
AND ZipCode LIKE '{2}%'" ,
InputValidator.EncodeQueryStringParameter(state),
InputValidator.EncodeQueryStringParameter(city),
InputValidator.EncodeQueryStringParameter(prefix));
DataView view = new DataView(_data.Zip);
view.RowFilter = query;
for (int i = 0; i < view.Count && i < maxResults; ++i)
{
PostalInfoLookupDataSet.ZipRow row =
(PostalInfoLookupDataSet.ZipRow)(view[i].Row);
yield return(_data.ZipRowToZipCode(row));
}
}
