Manage

The manage step 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