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