|
|
|
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
'
' Manage()
'
' Populates table and perform a simple query
'
Sub Manage()
Dim quantity As Integer
Dim price, total As Double
Dim itemnumb, custnumb, ordrnumb, custname As String
Dim isOrderFound, isItemFound As Boolean
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(ControlChars.NewLine + ControlChars.Tab + "Query Results")
Try
' get the first order
isOrderFound = recordCustOrdr.First()
While (isOrderFound) ' for each order in the CustomerOrders table
' fetch order number
ordrnumb = recordCustOrdr.GetFieldAsString(2)
' fetch customer number
custnumb = recordCustOrdr.GetFieldAsString(3)
' fetch name from CustomerMaster table based on customer number
recordCustMast.Clear()
recordCustMast.SetFieldAsString(0, custnumb)
If (recordCustMast.Find(FIND_MODE.EQ) <> True) Then
Continue While
End If
custname = recordCustMast.GetFieldAsString(4)
' fetch item price from OrderItems table
recordOrdrItem.Clear()
recordOrdrItem.SetFieldAsString(2, ordrnumb)
' define a recordset to scan only items applicable to Me order
recordOrdrItem.RecordSetOn(6)
isItemFound = recordOrdrItem.First()
total = 0
While (isItemFound) ' for each order item in OrderItems table
' fetch item quantity
quantity = recordOrdrItem.GetFieldAsSigned(1)
' fetch item number
itemnumb = recordOrdrItem.GetFieldAsString(3)
' fetch item price from ItemMaster table based on item number
recordItemMast.Clear()
recordItemMast.SetFieldAsString(2, itemnumb)
recordItemMast.Find(FIND_MODE.EQ)
price = recordItemMast.GetFieldAsFloat(1)
' calculate order total
total += (price * quantity)
isItemFound = recordOrdrItem.Next()
End While
recordOrdrItem.RecordSetOff()
' output data to stdout
Console.WriteLine(ControlChars.Tab + ControlChars.Tab + "{0,-20}{1,-8}", custname, total)
' read next order
If (recordCustOrdr.Next() <> True) Then
isOrderFound = False
End If
End While
Catch E As CTException
Handle_Exception(E)
End Try
End Sub
'
' Add_CustomerMaster_Records()
'
' This function adds records to table CustomerMaster from an
' array of strings
'
Public Structure CUSTOMER_DATA
' struct members
Public number, zipcode, state, rating, name, address, city As String
' struct constructor
Public Sub New(ByVal number As String, ByVal zipcode As String, ByVal state As String, ByVal rating As String, ByVal name As String, ByVal address As String, ByVal city As String)
Me.number = number
Me.zipcode = zipcode
Me.state = state
Me.rating = rating
Me.name = name
Me.address = address
Me.city = city
End Sub
End Structure
Sub Add_CustomerMaster_Records()
Dim data(3) As CUSTOMER_DATA
data(0) = New CUSTOMER_DATA("1000", "92867", "CA", "1", "Bryan Williams", "2999 Regency", "Orange")
data(1) = New CUSTOMER_DATA("1001", "61434", "CT", "1", "Michael Jordan", "13 Main", "Harford")
data(2) = New CUSTOMER_DATA("1002", "73677", "GA", "1", "Joshua Brown", "4356 Cambridge", "Atlanta")
data(3) = New CUSTOMER_DATA("1003", "10034", "MO", "1", "Keyon Dooling", "19771 Park Avenue", "Columbia")
Dim nRecords As Integer = data.Length
Delete_Records(recordCustMast)
Console.WriteLine(ControlChars.Tab + "Add records in table CustomerMaster...")
Try
Dim i As Integer
For i = 0 To nRecords - 1
recordCustMast.Clear()
' populate record buffer with data
recordCustMast.SetFieldAsString(0, data(i).number)
recordCustMast.SetFieldAsString(1, data(i).zipcode)
recordCustMast.SetFieldAsString(2, data(i).state)
recordCustMast.SetFieldAsString(3, data(i).rating)
recordCustMast.SetFieldAsString(4, data(i).name)
recordCustMast.SetFieldAsString(5, data(i).address)
recordCustMast.SetFieldAsString(6, data(i).city)
' add record
recordCustMast.Write()
Next
Catch E As CTException
Handle_Exception(E)
End Try
End Sub
'
' Add_CustomerOrders_Records()
'
' This function adds records to table CustomerOrders from an
' array of strings
'
Public Structure ORDER_DATA
' struct members
Public orderdate, promisedate, ordernum, customernum As String
' struct constructor
Public Sub New(ByVal orderdate As String, ByVal promisedate As String, ByVal ordernum As String, ByVal customernum As String)
Me.orderdate = orderdate
Me.promisedate = promisedate
Me.ordernum = ordernum
Me.customernum = customernum
End Sub
End Structure
Sub Add_CustomerOrders_Records()
Dim data(1) As ORDER_DATA
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")
Dim nRecords As Integer = data.Length
Dim orderdate As CTDate = New CTDate()
Dim promisedate As CTDate = New CTDate()
Delete_Records(recordCustOrdr)
Console.WriteLine(ControlChars.Tab + "Add records in table CustomerOrders...")
Try
Dim i As Integer
For i = 0 To nRecords - 1
recordCustOrdr.Clear()
orderdate.StringToDate(data(i).orderdate, DATE_TYPE.MDCY_DATE)
promisedate.StringToDate(data(i).promisedate, DATE_TYPE.MDCY_DATE)
' populate record buffer with data
recordCustOrdr.SetFieldAsDate(0, orderdate)
recordCustOrdr.SetFieldAsDate(1, promisedate)
recordCustOrdr.SetFieldAsString(2, data(i).ordernum)
recordCustOrdr.SetFieldAsString(3, data(i).customernum)
' add record
recordCustOrdr.Write()
Next
Catch E As CTException
Handle_Exception(E)
End Try
End Sub
'
' Add_OrderItems_Records()
'
' This function adds records to table OrderItems from an
' array of strings
'
Public Structure ORDERITEM_DATA
' struct members
Public sequencenum, quantity As Integer
Public ordernum, itemnum As String
' struct constructor
Public Sub New(ByVal sequencenum As Integer, ByVal quantity As Integer, ByVal ordernum As String, ByVal itemnum As String)
Me.sequencenum = sequencenum
Me.quantity = quantity
Me.ordernum = ordernum
Me.itemnum = itemnum
End Sub
End Structure
Sub Add_OrderItems_Records()
Dim data(3) As ORDERITEM_DATA
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")
Dim nRecords As Integer = data.Length
Delete_Records(recordOrdrItem)
Console.WriteLine(ControlChars.Tab + "Add records in table OrderItems...")
Try
Dim i As Integer
For i = 0 To nRecords - 1
recordOrdrItem.Clear()
' populate record buffer with data
recordOrdrItem.SetFieldAsSigned(0, data(i).sequencenum)
recordOrdrItem.SetFieldAsSigned(1, data(i).quantity)
recordOrdrItem.SetFieldAsString(2, data(i).ordernum)
recordOrdrItem.SetFieldAsString(3, data(i).itemnum)
' add record
recordOrdrItem.Write()
Next
Catch E As CTException
Handle_Exception(E)
End Try
End Sub
'
' Add_ItemMaster_Records()
'
' This function adds records to table ItemMaster from an
' array of strings
'
Public Structure ITEM_DATA
' struct members
Public weight As Integer
Public price As CTMoney
Public itemnum, description As String
' struct constructor
Public Sub New(ByVal weight As Integer, ByVal price As CTMoney, ByVal itemnum As String, ByVal description As String)
Me.weight = weight
Me.price = price
Me.itemnum = itemnum
Me.description = description
End Sub
End Structure
Sub Add_ItemMaster_Records()
Dim data(3) As ITEM_DATA
Data(0) = New ITEM_DATA(10, 1995, "1", "Hammer")
Data(1) = New ITEM_DATA(3, 999, "2", "Wrench")
Data(2) = New ITEM_DATA(4, 1659, "3", "Saw")
Data(3) = New ITEM_DATA(1, 398, "4", "Pliers")
Dim nRecords As Integer = Data.Length
Delete_Records(recordItemMast)
Console.WriteLine(ControlChars.Tab + "Add records in table ItemMaster...")
Try
Dim i As Integer
For i = 0 To nRecords - 1
recordItemMast.Clear()
' populate record buffer with data
recordItemMast.SetFieldAsSigned(0, Data(i).weight)
recordItemMast.SetFieldAsMoney(1, Data(i).price)
recordItemMast.SetFieldAsString(2, Data(i).itemnum)
recordItemMast.SetFieldAsString(3, Data(i).description)
' add record
recordItemMast.Write()
Next
Catch E As CTException
Handle_Exception(E)
End Try
End Sub
'
' Delete_Records()
'
' This function deletes all the records in the table
'
Sub Delete_Records(ByVal record As CTRecord)
Dim found As Boolean
Console.WriteLine(ControlChars.Tab + "Delete records...")
Try
' read first record
found = record.First()
While (found) ' while records are found
' delete record
record.Delete()
' read next record
found = record.Next()
End While
Catch E As CTException
Handle_Exception(E)
End Try
End Sub
