private void Add_CustomerOrder_Records()
{
DateTime dt = DateTime.Now;
string line = dt.ToString();
Log(line + "\tAdd Customer Order Records...");
ORDER_DATA[] data = new ORDER_DATA[2];
data[0] = new ORDER_DATA("09/01/2002", "09/05/2002", "1", "1001");
data[1] = new ORDER_DATA("09/02/2002", "09/06/2002", "2", "1002");
SqlSpCommand insert_cmd = NewSqlSpCommand();
String insert_stmt = "INSERT INTO custordr VALUES (?,?,?,?)";
insert_cmd.Prepare(insert_stmt);
foreach (ORDER_DATA order in data)
{
insert_cmd.Parameter[0].Value = order.orderdate;
insert_cmd.Parameter[1].Value = order.promdate;
insert_cmd.Parameter[2].Value = order.ordernum;
insert_cmd.Parameter[3].Value = order.custnum;
insert_cmd.Execute();
}
return;
}
private void Add_OrderItems_Records()
{
DateTime dt = DateTime.Now;
string line = dt.ToString();
Log(line + "\tAdd Order Item Records...");
ORDERITEM_DATA[] data = new ORDERITEM_DATA[4];
data[0] = new ORDERITEM_DATA(1, 2, "1", "1");
data[1] = new ORDERITEM_DATA(2, 1, "1", "2");
data[2] = new ORDERITEM_DATA(3, 1, "1", "3");
data[3] = new ORDERITEM_DATA(1, 3, "2", "3");
SqlSpCommand insert_cmd = NewSqlSpCommand();
String insert_stmt = "INSERT INTO ordritem VALUES (?,?,?,?)";
insert_cmd.Prepare(insert_stmt);
foreach (ORDERITEM_DATA item in data)
{
insert_cmd.Parameter[0].Value = item.seqnumber;
insert_cmd.Parameter[1].Value = item.quantity;
insert_cmd.Parameter[2].Value = item.ordernum;
insert_cmd.Parameter[3].Value = item.itemnum;
insert_cmd.Execute();
}
return;
}
private void Add_ItemMaster_Records()
{
DateTime dt = DateTime.Now;
string line = dt.ToString();
Log(line + "\tAdd Item Master Records...");
ITEM_DATA[] data = new ITEM_DATA[4];
data[0] = new ITEM_DATA(10, new Decimal(19.95), "1", "Hammer");
data[1] = new ITEM_DATA(3, new Decimal(9.99), "2", "Wrench");
data[2] = new ITEM_DATA(4, new Decimal(16.59), "3", "Saw");
data[3] = new ITEM_DATA(1, new Decimal(3.98), "4", "Pliers");
int nRecords = data.Length;
SqlSpCommand insert_cmd = NewSqlSpCommand();
String insert_stmt = "INSERT INTO itemmast VALUES (?,?,?,?) ";
insert_cmd.Prepare(insert_stmt);
foreach (ITEM_DATA item in data)
{
insert_cmd.Parameter[0].Value = item.weight;
insert_cmd.Parameter[1].Value = item.price;
insert_cmd.Parameter[2].Value = item.itemnum;
insert_cmd.Parameter[3].Value = item.description;
insert_cmd.Execute();
}
}
private void Add_CustomerMaster_Records()
{
DateTime dt = DateTime.Now;
string line = dt.ToString();
Log(line + "\tAdd Customer Master Records...");
DATA_RECORD[] data = new DATA_RECORD[4];
data[0] = new DATA_RECORD("1000", "92867", "CA", "1", "Bryan Williams", "2999 Regency", "Orange");
data[1] = new DATA_RECORD("1001", "61434", "CT", "1", "Michael Jordan", "13 Main", "Harford");
data[2] = new DATA_RECORD("1002", "73677", "GA", "1", "Joshua Brown", "4356 Cambridge", "Atlanta");
data[3] = new DATA_RECORD("1003", "10034", "MO", "1", "Keyon Dooling", "19771 Park Avenue", "Columbia");
SqlSpCommand insert_cmd = NewSqlSpCommand();
String insert_stmt = "INSERT INTO custmast VALUES (?,?,?,?,?,?,?) ";
insert_cmd.Prepare(insert_stmt);
foreach (DATA_RECORD record in data)
{
insert_cmd.Parameter[0].Value = record.number;
insert_cmd.Parameter[1].Value = record.zipcode;
insert_cmd.Parameter[2].Value = record.state;
insert_cmd.Parameter[3].Value = record.rating;
insert_cmd.Parameter[4].Value = record.name;
insert_cmd.Parameter[5].Value = record.address;
insert_cmd.Parameter[6].Value = record.city;
insert_cmd.Execute();
}
}
private void Init()
{
DateTime dt = DateTime.Now;
string line = dt.ToString();
Log(line + " INIT");
SqlSpCommand init_cmd = NewSqlSpCommand();
init_cmd.ExecuteNonQuery("DROP TABLE custmast");
init_cmd.Execute();
}
private void Display_Query()
{
DateTime dt = DateTime.Now;
string line = dt.ToString();
Log(line + "\tDisplay Order Items...");
// 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
SqlSpCommand display_cmd = NewSqlSpCommand();
String query_stmt =
"SELECT cm_custname \"Name\", SUM(im_itempric * oi_quantity) \"Total\" " +
"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"
;
int rv = display_cmd.Prepare(query_stmt);
if (rv == 0)
{
SqlSpRow RSrow = NewResultSetRow();
display_cmd.Execute();
while (display_cmd.MoveNext())
{
RSrow[0].Value = display_cmd.CurrentRow[0].Value;
RSrow[1].Value = display_cmd.CurrentRow[1].Value;
ResultSetAddRow(RSrow);
}
}
else
{
Err("Prepare error " + rv.ToString());
}
}
private void Display_Records()
{
DateTime dt = DateTime.Now;
string line = dt.ToString();
Log(line + "\tDisplay Records...");
SqlSpCommand display_cmd = NewSqlSpCommand();
String query_stmt = "SELECT cm_custnumb, cm_custname FROM custmast";
display_cmd.Prepare(query_stmt);
display_cmd.Execute();
SqlSpRow RSrow = NewResultSetRow();
while (display_cmd.MoveNext())
{
RSrow[0].Value = display_cmd.CurrentRow[0].Value;
RSrow[1].Value = display_cmd.CurrentRow[1].Value;
ResultSetAddRow(RSrow);
}
}