//
// Display_OrderItems()
//
// This function displays the contents of OrderItems table.
//
static void Display_OrderItems()
{
Console.WriteLine("\n\tOrderItems Table...");
try
{
// execute a query statement
cmd.CommandText = "SELECT * FROM ordritem";
rdr = (CtreeSqlDataReader)cmd.ExecuteReader();
// fetch and display each individual record
while (rdr.Read())
{
Console.WriteLine("\t {0} {1}", rdr.GetString(2), rdr.GetString(3));
}
rdr.Close();
}
catch (CtreeSqlException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}
//
// Display_Records()
//
// This function displays the contents of a table.
//
static void Display_Records()
{
Console.Write("\tDisplay records...");
try
{
cmd.CommandText = "SELECT * FROM custmast";
// get a resultset
rdr = (CtreeSqlDataReader)cmd.ExecuteReader();
// read the returned resultset
while (rdr.Read())
{
Console.WriteLine("\n\t\t{0} {1}", rdr.GetString(0), rdr.GetString(4));
}
// close the reader
rdr.Close();
}
catch (CtreeSqlException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}
//
// Manage()
//
// Populates table and perform a simple query
//
static void Manage()
{
Console.WriteLine("MANAGE");
// populate the tables with data
Add_CustomerMaster_Records();
Add_CustomerOrders_Records();
Add_OrderItems_Records();
Add_ItemMaster_Records();
// perform a query:
// list customer name and total amount per order
// name total
// @@@@@@@@@@@@@ $xx.xx
// for each order in the CustomerOrders table
// fetch order number
// fetch customer number
// fetch name from CustomerMaster table based on customer number
// for each order item in OrderItems table
// fetch item quantity
// fetch item number
// fetch item price from ItemMaster table based on item number
// next
// next
Console.WriteLine("\n\tQuery Results...");
try
{
cmd.CommandText = "SELECT cm_custname, SUM(im_itempric * oi_quantity) " +
"FROM custmast, custordr, ordritem, itemmast " +
"WHERE co_custnumb = cm_custnumb AND co_ordrnumb = oi_ordrnumb AND oi_itemnumb = im_itemnumb " +
"GROUP BY co_ordrnumb, cm_custname";
// get a resultset
rdr = (CtreeSqlDataReader)cmd.ExecuteReader();
// read the returned resultset
while (rdr.Read())
{
Console.WriteLine("\t\t{0:-20s} \t{1}", rdr.GetString(0), rdr.GetString(1));
}
// close the reader
rdr.Close();
}
catch (CtreeSqlException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}