/// <summary>
/// Method to print customers in predefined text
/// </summary>
/// <param name="customers">the customers to print</param>
/// <param name="selection">the selection to print</param>
public static void Print(CustomerCollection customers, int selection)
{
string divider = new string('-', 68);
SelectionListCollection provinces = new SelectionListCollection();
Console.WriteLine($"Customer listing for {provinces.Provinces(selection)}");
Console.WriteLine();
Console.WriteLine($"{"CompanyName",-31} {"City",8} {"Prov",14} {"Postal",3} {"Hold"}");
Console.WriteLine(divider);
foreach (Customer customer in customers)
{
Console.WriteLine($"{customer.CompanyName,-35} {customer.City,-14} {customer.Province,-4} {customer.PostalCode,-7} {customer.CreditHold}");
}
}
/// <summary>
/// Method for retrieving customers list from Database
/// </summary>
/// <param name="selection">the selection to set</param>
/// <returns>the customers</returns>
public static CustomerCollection GetCustomers(int selection)
{
CustomerCollection customers;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SelectionListCollection selectionList = new SelectionListCollection();
string provinceFilter = selectionList.Provinces(selection);
string query;
if (provinceFilter.Equals("ALL"))
{
query = @"SELECT CompanyName, Address, City, Province, PostalCode, CreditHold
FROM Customer
ORDER BY CompanyName";
}
else
{
query = @"SELECT CompanyName, Address, City, Province, PostalCode, CreditHold
FROM Customer
WHERE Province = @province
ORDER BY CompanyName";
}
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@province", provinceFilter);
connection.Open();
customers = new CustomerCollection();
using (SqlDataReader reader = cmd.ExecuteReader())
{
string companyName;
string address = null;
string city = null;
string province = null;
string postalCode = null;
bool creditHold = false;
while (reader.Read())
{
companyName = reader["CompanyName"] as string;
if (!reader.IsDBNull(1))
{
address = reader["Address"] as string;
}
if (!reader.IsDBNull(2))
{
city = reader["City"] as string;
}
if (!reader.IsDBNull(3))
{
province = reader["Province"] as string;
}
if (!reader.IsDBNull(4))
{
postalCode = reader["PostalCode"] as string;
}
if (!reader.IsDBNull(5))
{
creditHold = reader["CreditHold"] as bool? ?? false;
}
customers.Add(new Customer(companyName, address, city, province, postalCode, creditHold));
address = null;
city = null;
province = null;
postalCode = null;
creditHold = false;
}
}
}
return(customers);
}
}