/*
#####################################################################
# Q11
# transactionCity._City
# transactionCity._Quantity
#####################################################################
*/
public static List <TransactionsCities> TransactionsCities()
{
List <TransactionsCities> transactionsCities = new List <TransactionsCities>();
TransactionsCities transactionCity;
if (Connect())
{
using (SqlCommand sqlCommand = new SqlCommand("Q11", _Connection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_TransactionsCities";
// sqlCommand.Parameters.Add("@transactioncode", SqlDbType.VarChar).Value = transactionCode;
SqlDataReader reader;
try
{
reader = sqlCommand.ExecuteReader();
}
catch (Exception ex)
{
return(null);
}
while (reader.Read())
{
transactionCity = new TransactionsCities();
transactionCity._City = Convert.ToString(reader[0]);
transactionCity._Quantity = Convert.ToInt32(reader[1]);
transactionsCities.Add(transactionCity);
}
reader.Close();
}
}
return(transactionsCities);
}
/*
#####################################################################
# Q11
# transactionCity._City
# transactionCity._Quantity
#####################################################################
*/
public static List <TransactionsCities> TransactionsCities()
{
string Q11Query = " select a.CITY, count(*) transactions from ADDRESS a " +
" inner join store s on a.ADDRESSCODE= s.ADDRESSCODE " +
" inner join TRANSACTIONS t on t.STORECODE= s.STORECODE " +
" group by a.city " +
" order by transactions desc, a.CITY asc ";
List <TransactionsCities> transactionsCities = new List <TransactionsCities>();
TransactionsCities transactionCity;
if (Connect())
{
//we are going to call query called Q11
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = Q11Query;
sqlCommand.Connection = _Connection;
SqlDataReader reader;
try
{
reader = sqlCommand.ExecuteReader();
}
catch (Exception ex)
{
return(null);
}
//Converting query results to PersonPhoneAddress objects
while (reader.Read())
{
transactionCity = new TransactionsCities();
transactionCity._City = Convert.ToString(reader[0]);
transactionCity._Quantity = Convert.ToInt32(reader[1]);
transactionsCities.Add(transactionCity);
}
reader.Close();
}
}
return(transactionsCities);
}
