/// <summary> /// Method to populateSelectionList of provinces /// </summary> private void populateSelectionList() { try { Console.WriteLine("Select province filter:"); provinces = new CustomertRepository().GetProvinces(); if (provinces != null && provinces.Count() > NULL_PROVINCES) { int count = 1; foreach (string provinceList in provinces) { Console.WriteLine($"{count,10}: {provinceList}"); count++; } } else { Console.WriteLine("No provinces to select"); } } catch (SqlException ex) { Console.WriteLine("Data Access Error\n\n{0}", ex.Message); } catch (Exception ex) { Console.WriteLine("Processing Error\n\n{0}", ex.Message); } }
/// <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 province list from Database /// </summary> /// <returns>the provinces</returns> public SelectionListCollection GetProvinces() { SelectionListCollection provinces; using (SqlConnection connection = new SqlConnection(connectionString)) { string query; query = @"SELECT DISTINCT Province FROM Customer"; using (SqlCommand cmd = new SqlCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = query; cmd.Connection = connection; connection.Open(); provinces = new SelectionListCollection(); using (SqlDataReader reader = cmd.ExecuteReader()) { string province = null; while (reader.Read()) { if (!reader.IsDBNull(0)) { province = reader["Province"] as string; } provinces.Add(province); province = null; } provinces.Add("ALL"); provinces.Add("EXIT"); } } } return(provinces); }
/// <summary> /// Method to populateSelectionList of provinces /// </summary> private void populateSelectionList() { Console.WriteLine("Select province filter:"); provinces = new CustomertRepository().GetProvinces(); if (provinces != null && provinces.Count() > NULL_PROVINCES) { int count = COUNT_START; foreach (string selectionList in provinces) { Console.WriteLine($"{count,10}: {selectionList}"); count++; } } else { Console.WriteLine("No provinces to select"); Environment.Exit(EXIT_CODE); } }
/// <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); } }