|
|
|
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
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 cm_custnumb, 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);
}
}
//
// Add_CustomerMaster_Records()
//
// This function adds records to table CustomerMaster from an
// array of strings
//
static void Add_CustomerMaster_Records()
{
String[] data = {
"('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
"('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
"('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
"('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
};
Console.WriteLine("\tAdd records in table CustomerMaster...");
Delete_Records("custmast");
try
{
// add one record at time to table
for (int i = 0; i < data.Length; i++)
{
cmd.CommandText = "INSERT INTO custmast VALUES " + data[i];
cmd.ExecuteNonQuery();
}
}
catch (CtreeSqlException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}
//
// Add_CustomerOrders_Records()
//
// This function adds records to table CustomerOrders from an
// array of strings
//
static void Add_CustomerOrders_Records()
{
String[] data = {
"('09/01/2002','09/05/2002','1','1001')",
"('09/02/2002','09/06/2002','2','1002')"
};
Console.WriteLine("\tAdd records in table CustomerOrders...");
Delete_Records("custordr");
try
{
// add one record at time to table
for (int i = 0; i < data.Length; i++)
{
cmd.CommandText = "INSERT INTO custordr VALUES " + data[i];
cmd.ExecuteNonQuery();
}
}
catch (CtreeSqlException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}
//
// Add_OrderItems_Records()
//
// This function adds records to table OrderItems from an
// array of strings
//
static void Add_OrderItems_Records()
{
String[] data = {
"(1,2,'1','1')",
"(2,1,'1','2')",
"(3,1,'1','3')",
"(1,3,'2','3')"
};
Console.WriteLine("\tAdd records in table OrderItems...");
Delete_Records("ordritem");
try
{
// add one record at time to table
for (int i = 0; i < data.Length; i++)
{
cmd.CommandText = "INSERT INTO ordritem VALUES " + data[i];
cmd.ExecuteNonQuery();
}
}
catch (CtreeSqlException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}
//
// Add_ItemMaster_Records()
//
// This function adds records to table ItemMaster from an
// array of strings
//
static void Add_ItemMaster_Records()
{
String[] data = {
"(10,19.95,'1','Hammer')",
"(3, 9.99,'2','Wrench')",
"(4, 16.59,'3','Saw')",
"(1, 3.98,'4','Pliers')"
};
Console.WriteLine("\tAdd records in table ItemMaster...");
Delete_Records("itemmast");
try
{
// add one record at time to table
for (int i = 0; i < data.Length; i++)
{
cmd.CommandText = "INSERT INTO itemmast VALUES " + data[i];
cmd.ExecuteNonQuery();
}
}
catch (CtreeSqlException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}
//
// Delete_Records()
//
// This function deletes all the records in a tables
//
static void Delete_Records(String Table)
{
Console.WriteLine("\tDelete records...");
try
{
cmd.CommandText = "DELETE FROM " + Table;
cmd.ExecuteNonQuery();
}
catch (CtreeSqlException e)
{
Handle_Exception(e);
}
catch (Exception e)
{
Handle_Exception(e);
}
}
