Пример #1
0
        /*
         #####################################################################
         # Q2
         #              customerCity._City
         #              customerCity._Quantity
         #####################################################################
         */
        public static List <CustomersCities> GetCustomersCities()
        {
            List <CustomersCities> customerCities = new List <CustomersCities>();
            CustomersCities        customerCity;

            if (Connect())
            {
                using (SqlCommand sqlCommand = new SqlCommand("Q2", _Connection))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "sp_GetPersonsCities";
                    SqlDataReader reader;
                    try
                    {
                        reader = sqlCommand.ExecuteReader();
                    }
                    catch (Exception ex)
                    {
                        return(null);
                    }
                    while (reader.Read())
                    {
                        customerCity           = new CustomersCities();
                        customerCity._City     = Convert.ToString(reader[0]);
                        customerCity._Quantity = Convert.ToInt32(reader[1]);
                        customerCities.Add(customerCity);
                    }
                    reader.Close();
                }
            }
            else
            {
                return(null);
            }


            return(customerCities);
        }
        /*
         #####################################################################
         # Q2
         #              customerCity._City
         #              customerCity._Quantity
         #####################################################################
         */
        public static List <CustomersCities> GetCustomersCities()
        {
            string Q2Query = "select a.city ,count( *) personcount from person p " +
                             " inner join ADDRESS a on p.ADDRESSCODE = a.ADDRESSCODE " +
                             " group by a.city " +
                             " order by personcount desc ,city asc ";
            List <CustomersCities> customerCities = new List <CustomersCities>();

            if (Connect())
            {
                //we are going to call query called Q2
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = Q2Query;
                    sqlCommand.Connection  = _Connection;
                    SqlDataReader reader;
                    try { reader = sqlCommand.ExecuteReader(); }
                    catch (Exception ex)
                    {
                        return(null);
                    }
                    //Converting query results to PersonPhoneAddress objects
                    while (reader.Read())
                    {
                        CustomersCities customerCity = new CustomersCities();
                        customerCity._City     = Convert.ToString(reader[0]);
                        customerCity._Quantity = Convert.ToInt32(reader[1]);
                        customerCities.Add(customerCity);
                    }
                    reader.Close();
                }
            }

            return(customerCities);
        }