Presentation – Working with Oracle Database in VB.Net with ODP.Net and Visual API Toolkit; Enhancing Visual Manufacturing 9.0.0

7 08 2017

August 7, 2017

A few weeks ago I gave a presentation to a regional group of ERP users, some with an Oracle Database backend and some with a Microsoft SQL Server backend.  The original intention for the presentation was to demonstrate an application package that I created the previous year which maintains and manages production equipment maintenance unplanned work orders as well as preventive maintenance work orders in the Visual Manufacturing ERP system, in addition to the production work orders that the Visual Manufacturing ERP system supports out of the box.  My custom application package was created specifically to use an Oracle Database backend with ODP.Net data access using the Microsoft Visual Studio 2010 version of VB.Net (the VB.Net language is similar to C#, essentially just with slightly different grammar rules), and the application package could not be purchased by other companies.

A bit of a dilemma as I planned to show a computer package that was not only not available for commercial purchase, but also where the majority of the audience members were using a Microsoft SQL Server backend.  The version of the Visual Manufacturing ERP package (8.0.0) that was released roughly two years ago introduced a “free” database platform independent API Toolkit that may be used with Visual Studio 2010 and later to not only retrieve information from the ERP database, but also create various types of document transactions in the ERP system.  A “free” database platform independent API, ah… the perfect solution to the bit of a dilemma I had with the presentation.  I told the organizer of the regional ERP group that I could probably create a version of the preventive and unplanned maintenance package that uses the “free” database platform independent API for 99+% of its tasks, and that reproduces the core functionality of the Oracle Database specific version that I wrote a year earlier, despite the fact that a year earlier the API Toolkit was incompatible with Oracle Database due to a design flaw (triggered whenever any column in a specific database table contained more than 64 characters), and the API Toolkit documentation ranges from OK but not very useful, to incomplete and inaccurate.  My five slide presentation morphed to a lengthy presentation with nearly 90 slides, as the focus of the presentation shifted from demonstrating how I extended the capabilities of an ERP system to manage equipment maintenance tasks, to documenting how to beat persuade the “free” database platform independent API Toolkit into submission to accomplish various other tasks in addition to reproducing the core functionality of the application that I created a year ago.  I believe that as a result of my presentation the API Toolkit is now much better documented, although I did leave a few stones unturned.

The presentation and associated files may be downloaded from this link.  The Crystal Reports plugin that is used by the project is a bit difficult to find through internet searches on the manufacturer’s website, so I downloaded those files and made them available from this link.  These files are located on Google Drive, click the down pointing arrow near the top-right of the Google Drive page to download the files (see the download picture in this article for assistance).


The following is a slightly edited version of the description of the presentation that I sent to the organizer of the regional ERP group roughly a month before the meeting.

Visual Manufacturing offers many useful built-in features for manufacturing environments. One task that either is not available or is poorly implemented/integrated into Visual Manufacturing is unplanned maintenance and preventive maintenance for equipment (resources) used in the manufacturing process. As customers demand that their suppliers demonstrate the existence of formal preventive maintenance programs, there is a growing need for a robust maintenance package – and it is especially helpful if that package directly integrates with Visual Manufacturing without any extra cost add-ons. The MFC Equipment Maintenance package, which was developed internally last year to help formalize and standardize the process of equipment maintenance in an environment with 25+ maintenance workers, will be demonstrated at the next meeting. The package essentially behaves as an extension to Visual Manufacturing’s Shop Resource Maintenance module, allowing maintenance staff to quickly generate, release, and close maintenance work orders in Visual Manufacturing to meet the requirements of both unplanned maintenance as well as preventive maintenance. As the maintenance work orders are maintained in the Visual Manufacturing database, all the native Visual modules for creating labor tickets, purchasing, issuing parts to work orders, and rolling up costs for accounting work just as they do with normal production work orders.

An equipment maintenance starter package, which primarily uses the free Visual 8/9 API Toolkit to duplicate the core functionality of the MFC Equipment Maintenance package, will also be demonstrated, with the VB.Net source code provided to attendees. Additional methods to exploit the Visual 8/9 API Toolkit will also be shown, including extended examples of a tool crib self-checkout application, a fast purchase order receiving application with barcode label printing, and a few other techniques for using the Visual 8/9 API Toolkit (including handling unexpected errors) will also be shown.

Needless to say, I ended up expanding the scope of the presentation a bit, as I found that the documentation of the API Toolkit left a lot of functionality undocumented.  Demonstrating how Visual Manufacturing could be extended to manage unplanned maintenance and planned maintenance was the original intention of the presentation, but it is interesting to see how the scope expands once database independent approaches are implemented.  Below is a screen capture of one of the main windows in the MFC Equipment Maintenance application, which is used to manage the maintenance of individual pieces of equipment used in the production of customer ordered parts.  Oddly, some of the commercially available equipment maintenance packages charge unexpectedly high premiums for features such as maintaining more than two machine counters for a resource, storing basic warranty information, and integration with ERP/accounting programs.

The “starter” version of the program that I created for the presentation, which uses the API Toolkit to reproduce the core program functionality, is of course not as full featured, but allows maintaining additional information about resource IDs that are defined in Visual Manufacturing, creating and printing unplanned maintenance work orders, creating preventive maintenance templates (masters) and then applying those templates for resource IDs, and creating and printing preventive maintenance work orders.  Below is a screen capture showing the main window for the “starter” version of the program that was included with the presentation.

The bottom of the window in both cases is a HTML web page that shows recent unplanned maintenance work order history for the selected resource ID, as well as repair parts that were issued through Visual Manufacturing to those work orders.  The methods for retrieving that information from the database are a bit different when using ODP.Net compared to using the database independent methods that are exposed by the Visual API Toolkit.  Before showing those differences, however, I thought that it would be best to first potentially confuse the issue by showing five slightly different VB.Net coding syntaxes to perform essentially the same set of operations using the API Toolkit (this helps to demonstrate different coding methods used by different programmers and a potential source for confusion while referring to the documentation for the API Toolkit).  The task to be accomplished is to update an unplanned maintenance work order description, the associated maintenance operation’s estimated labor hours, and the associated description for the maintenance operation to repair the problem that is defined in the work order description.

Method 1:

objWO.Load("W", strWorkorderBaseID, strWorkorderLotID, "0")
 
For i = 0 To objWO.Tables.Count - 1
   If objWO.Tables(i).TableName = "WORKORDER_BINARY" Then
       If objWO.Tables(i).Rows.Count > 0 Then
           objWO.Tables(i).Rows(0).Item("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureNote.Text)
           objWO.Tables(i).Rows(0).Item("BITS_LENGTH") = Len(txtFailureNote.Text)
           objWO.Tables(i).Rows(0).Item("TYPE") = "D"
       End If
   End If
   If objWO.Tables(i).TableName = "OPERATION" Then
       If objWO.Tables(i).Rows.Count > 0 Then
           objWO.Tables(i).Rows(0).Item("RUN") = Convert.ToDouble(txtFailureEstimatedHours.Text)
           objWO.Tables(i).Rows(0).Item("RUN_HRS") = Convert.ToDouble(txtFailureEstimatedHours.Text)
       End If
   End If
   If objWO.Tables(i).TableName = "OPERATION_BINARY" Then
       If objWO.Tables(i).Rows.Count > 0 Then
           objWO.Tables(i).Rows(0).Item("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureRepair.Text)
           objWO.Tables(i).Rows(0).Item("BITS_LENGTH") = Len(txtFailureRepair.Text)
           objWO.Tables(i).Rows(0).Item("TYPE") = "D"
       End If
   End If
Next i

Method 2:

objWO.Load("W", strWorkorderBaseID, strWorkorderLotID, "0")
 
For i = 0 To objWO.Tables.Count - 1
    If objWO.Tables(i).TableName = "WORKORDER_BINARY" Then
        If objWO.Tables(i).Rows.Count > 0 Then
            objWO.Tables(i).Rows(0)("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureNote.Text)
            objWO.Tables(i).Rows(0)("BITS_LENGTH") = Len(txtFailureNote.Text)
            objWO.Tables(i).Rows(0)("TYPE") = "D"
        End If
    End If
    If objWO.Tables(i).TableName = "OPERATION" Then
        If objWO.Tables(i).Rows.Count > 0 Then
            objWO.Tables(i).Rows(0)("RUN") = Convert.ToDouble(txtFailureEstimatedHours.Text)
            objWO.Tables(i).Rows(0)("RUN_HRS") = Convert.ToDouble(txtFailureEstimatedHours.Text)
        End If
    End If
    If objWO.Tables(i).TableName = "OPERATION_BINARY" Then
        If objWO.Tables(i).Rows.Count > 0 Then
            objWO.Tables(i).Rows(0)("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureRepair.Text)
            objWO.Tables(i).Rows(0)("BITS_LENGTH") = Len(txtFailureRepair.Text)
            objWO.Tables(i).Rows(0)("TYPE") = "D"
        End If
    End If
Next i

Method 3:

objWO.Load("W", strWorkorderBaseID, strWorkorderLotID, "0")
 
If objWO.Tables("WORKORDER_BINARY").Rows.Count > 0 Then
    objWO.Tables("WORKORDER_BINARY").Rows(0).Item("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureNote.Text)
    objWO.Tables("WORKORDER_BINARY").Rows(0).Item("BITS_LENGTH") = Len(txtFailureNote.Text)
    objWO.Tables("WORKORDER_BINARY").Rows(0).Item("TYPE") = "D"
End If
 
If objWO.Tables("OPERATION").Rows.Count > 0 Then
    objWO.Tables("OPERATION").Rows(0).Item("RUN") = Convert.ToDouble(txtFailureEstimatedHours.Text)
    objWO.Tables("OPERATION").Rows(0).Item("RUN_HRS") = Convert.ToDouble(txtFailureEstimatedHours.Text)
End If
 
If objWO.Tables("OPERATION_BINARY").Rows.Count > 0 Then
    objWO.Tables("OPERATION_BINARY").Rows(0).Item("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureRepair.Text)
    objWO.Tables("OPERATION_BINARY").Rows(0).Item("BITS_LENGTH") = Len(txtFailureRepair.Text)
    objWO.Tables("OPERATION_BINARY").Rows(0).Item("TYPE") = "D"
End If

Method 4:

objWO.Load("W", strWorkorderBaseID, strWorkorderLotID, "0")
 
If objWO.Tables("WORKORDER_BINARY").Rows.Count > 0 Then
    objWO.Tables("WORKORDER_BINARY").Rows(0)("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureNote.Text)
    objWO.Tables("WORKORDER_BINARY").Rows(0)("BITS_LENGTH") = Len(txtFailureNote.Text)
    objWO.Tables("WORKORDER_BINARY").Rows(0)("TYPE") = "D"
End If
 
If objWO.Tables("OPERATION").Rows.Count > 0 Then
    objWO.Tables("OPERATION").Rows(0)("RUN") = Convert.ToDouble(txtFailureEstimatedHours.Text)
    objWO.Tables("OPERATION").Rows(0)("RUN_HRS") = Convert.ToDouble(txtFailureEstimatedHours.Text)
End If
 
If objWO.Tables("OPERATION_BINARY").Rows.Count > 0 Then
    objWO.Tables("OPERATION_BINARY").Rows(0)("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureRepair.Text)
    objWO.Tables("OPERATION_BINARY").Rows(0)("BITS_LENGTH") = Len(txtFailureRepair.Text)
    objWO.Tables("OPERATION_BINARY").Rows(0)("TYPE") = "D"
End If

Method 5:

Dim drRow As Lsa.Data.DataRow
 
objWO.Load("W", strWorkorderBaseID, strWorkorderLotID, "0")
 
If objWO.Tables("WORKORDER_BINARY").Rows.Count > 0 Then
    drRow = objWO.Tables("WORKORDER_BINARY").Rows(0)
    drRow("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureNote.Text)
    drRow("BITS_LENGTH") = Len(txtFailureNote.Text)
    drRow("TYPE") = "D"
End If
 
If objWO.Tables("OPERATION").Rows.Count > 0 Then
    drRow = objWO.Tables("OPERATION").Rows(0)
    drRow("RUN") = Convert.ToDouble(txtFailureEstimatedHours.Text)
    drRow("RUN_HRS") = Convert.ToDouble(txtFailureEstimatedHours.Text)
End If
 
If objWO.Tables("OPERATION_BINARY").Rows.Count > 0 Then
    drRow = objWO.Tables("OPERATION_BINARY").Rows(0)
    drRow("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureRepair.Text)
    drRow("BITS_LENGTH") = Len(txtFailureRepair.Text)
    drRow("TYPE") = "D"
End If

Method #5 above allows working with the API Toolkit using syntax that is somewhat similar to the syntax that is used with ODP.Net, where individual rows are processed one at a time with an OracleDataReader object, or bind variable values are specified for creating a single row with an OracleCommand object.

Let’s take a look at how the information at the bottom of the program’s main window is retrieved first in the application that I created last year, and then in the “starter” version that uses the API Toolkit.  In programming, it is typically best from a performance point of view to perform tasks either once or not at all (don’t perform unnecessary tasks).  As such, I tend to set up SQL statements that may be executed multiple times in various procedures in the form’s Load event.  When processing data, I try to minimize the number of executions of SQL statements sent to the database instance by carefully crafting SQL statements and then using what used to be termed as Control Break processing to act on the returned data.  This method improves performance over alternative methods, such as sending a unique SQL statement to the database instance to retrieve the list of issued part IDs for each work order row that is retrieved from the database.  I also try to use bind variables where possible to reduce the number of hard parses, and minimize the performance impact of the necessary hard parses.  At the top of the form code modules, I add the following Imports entries to make it easy to use ODP.Net for database access (note that a reference to Oracle.DataAccess.dll was also added):

Imports System.Data
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

The variable to store the reference to the SQL statement is declared in the form’s variables section near the top of the form code (immediately after the Public Class line):

Dim comRecentMaintenanceUM As OracleCommand

The connection to the Oracle Database is established in a login form, and that Oracle.DataAccess.Client.OracleConnection object (name may be shortened if the Imports entries are used) is given the name dbVMFG.  In the form’s Load procedure the SQL statement associated with the OracleCommand variable is specfied.

strSQL = "SELECT /*+ LEADING(RO) */" & vbCrLf
strSQL = strSQL & " RO.ID," & vbCrLf
strSQL = strSQL & " RO.FAILURE_MODE," & vbCrLf
strSQL = strSQL & " RO.ENTRY_NOTE," & vbCrLf
strSQL = strSQL & " RO.REPAIR_NOTE," & vbCrLf
strSQL = strSQL & " RO.START_TIME," & vbCrLf
strSQL = strSQL & " RO.END_TIME," & vbCrLf
strSQL = strSQL & " RO.RESOURCE_IS_DOWN," & vbCrLf 'Added 6/20/2017 CH
strSQL = strSQL & " UW.WORKORDER_BASE_ID," & vbCrLf
strSQL = strSQL & " UW.WORKORDER_LOT_ID," & vbCrLf
strSQL = strSQL & " O.RUN_HRS," & vbCrLf
strSQL = strSQL & " O.ACT_RUN_HRS," & vbCrLf
strSQL = strSQL & " R.PART_ID," & vbCrLf
strSQL = strSQL & " P.DESCRIPTION," & vbCrLf
strSQL = strSQL & " R.CALC_QTY," & vbCrLf
strSQL = strSQL & " R.ISSUED_QTY" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " CUSTAPP.RESOURCE_MAINT_UM_WO UW," & vbCrLf
strSQL = strSQL & " CUSTAPP.RESOURCE_OUTAGE RO," & vbCrLf
strSQL = strSQL & " OPERATION O," & vbCrLf
strSQL = strSQL & " REQUIREMENT R," & vbCrLf
strSQL = strSQL & " PART P" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " RO.RESOURCE_ID= :1" & vbCrLf
strSQL = strSQL & " AND RO.OUTAGE_DELETED='N'" & vbCrLf
strSQL = strSQL & " AND RO.START_TIME>=TRUNC(SYSDATE-120)" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_TYPE=UW.WORKORDER_TYPE" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_BASE_ID=UW.WORKORDER_BASE_ID" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_LOT_ID=UW.WORKORDER_LOT_ID" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_SPLIT_ID=UW.WORKORDER_SPLIT_ID" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_TYPE=O.WORKORDER_TYPE" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_BASE_ID=O.WORKORDER_BASE_ID" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_LOT_ID=O.WORKORDER_LOT_ID" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_SPLIT_ID=O.WORKORDER_SPLIT_ID" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SUB_ID='0'" & vbCrLf
strSQL = strSQL & " AND O.SEQUENCE_NO=10" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_TYPE=R.WORKORDER_TYPE(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SUB_ID=R.WORKORDER_SUB_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.SEQUENCE_NO=R.OPERATION_SEQ_NO(+)" & vbCrLf
strSQL = strSQL & " AND R.PART_ID=P.ID(+)" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " RO.START_TIME DESC," & vbCrLf
strSQL = strSQL & " R.PART_ID"
 
comRecentMaintenanceUM = New OracleCommand(strSQL, dbVMFG)
comRecentMaintenanceUM.CommandType = CommandType.Text
comRecentMaintenanceUM.CommandTimeout = 30
comRecentMaintenanceUM.BindByName = False
comRecentMaintenanceUM.Parameters.Add("resource_id", OracleDbType.Varchar2, 15, "", ParameterDirection.Input)

As with the API Toolkit example code shown above, there is a slightly different syntax that may be used to set up the OracleCommand variable, a method that seems to be a little more clear because the SQL statement’s beginning and end is indented, but this formatting method is bit more difficult to adapt to C# as C# does not have an equivalent for the With syntax:

comRecentMaintenanceUM = New OracleCommand
With comRecentMaintenanceUM
    strSQL = "SELECT /*+ LEADING(RO) */" & vbCrLf
    strSQL = strSQL & " RO.ID," & vbCrLf
    strSQL = strSQL & " RO.FAILURE_MODE," & vbCrLf
    strSQL = strSQL & " RO.ENTRY_NOTE," & vbCrLf
    strSQL = strSQL & " RO.REPAIR_NOTE," & vbCrLf
    strSQL = strSQL & " RO.START_TIME," & vbCrLf
    strSQL = strSQL & " RO.END_TIME," & vbCrLf
    strSQL = strSQL & " RO.RESOURCE_IS_DOWN," & vbCrLf 'Added 6/20/2017 CH
    strSQL = strSQL & " UW.WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & " UW.WORKORDER_LOT_ID," & vbCrLf
    strSQL = strSQL & " O.RUN_HRS," & vbCrLf
    strSQL = strSQL & " O.ACT_RUN_HRS," & vbCrLf
    strSQL = strSQL & " R.PART_ID," & vbCrLf
    strSQL = strSQL & " P.DESCRIPTION," & vbCrLf
    strSQL = strSQL & " R.CALC_QTY," & vbCrLf
    strSQL = strSQL & " R.ISSUED_QTY" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & " CUSTAPP.RESOURCE_MAINT_UM_WO UW," & vbCrLf
    strSQL = strSQL & " CUSTAPP.RESOURCE_OUTAGE RO," & vbCrLf
    strSQL = strSQL & " OPERATION O," & vbCrLf
    strSQL = strSQL & " REQUIREMENT R," & vbCrLf
    strSQL = strSQL & " PART P" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & " RO.RESOURCE_ID= :1" & vbCrLf
    strSQL = strSQL & " AND RO.OUTAGE_DELETED='N'" & vbCrLf
    strSQL = strSQL & " AND RO.START_TIME>=TRUNC(SYSDATE-120)" & vbCrLf
    strSQL = strSQL & " AND RO.REPAIR_WO_TYPE=UW.WORKORDER_TYPE" & vbCrLf
    strSQL = strSQL & " AND RO.REPAIR_WO_BASE_ID=UW.WORKORDER_BASE_ID" & vbCrLf
    strSQL = strSQL & " AND RO.REPAIR_WO_LOT_ID=UW.WORKORDER_LOT_ID" & vbCrLf
    strSQL = strSQL & " AND RO.REPAIR_WO_SPLIT_ID=UW.WORKORDER_SPLIT_ID" & vbCrLf
    strSQL = strSQL & " AND UW.WORKORDER_TYPE=O.WORKORDER_TYPE" & vbCrLf
    strSQL = strSQL & " AND UW.WORKORDER_BASE_ID=O.WORKORDER_BASE_ID" & vbCrLf
    strSQL = strSQL & " AND UW.WORKORDER_LOT_ID=O.WORKORDER_LOT_ID" & vbCrLf
    strSQL = strSQL & " AND UW.WORKORDER_SPLIT_ID=O.WORKORDER_SPLIT_ID" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_SUB_ID='0'" & vbCrLf
    strSQL = strSQL & " AND O.SEQUENCE_NO=10" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_TYPE=R.WORKORDER_TYPE(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_SUB_ID=R.WORKORDER_SUB_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.SEQUENCE_NO=R.OPERATION_SEQ_NO(+)" & vbCrLf
    strSQL = strSQL & " AND R.PART_ID=P.ID(+)" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & " RO.START_TIME DESC," & vbCrLf
    strSQL = strSQL & " R.PART_ID"
 
    .CommandText = strSQL
    .Connection = dbVMFG
    .CommandType = CommandType.Text
    .CommandTimeout = 30
    .BindByName = False
    .Parameters.Add("resource_id", OracleDbType.Varchar2, 15, "", ParameterDirection.Input)
End With

The procedure (actually a function) , using ODP.Net, that retrieves the recent unplanned maintenance history for a resource ID and the repair parts issued to those work orders is shown below:

Public Function RetrieveRecentMaintenance() As Boolean
    Dim strColor(4) As String
    Dim intToggle As Integer = 0
    Dim intToggle2 As Integer = 0
    Dim intColor As Integer = 0
    Dim intLastID As Integer = 0
    Dim intOffset As Integer = 0
    Dim snpData As OracleDataReader
    Dim sbHTML As StringBuilder = New StringBuilder()

    comRecentMaintenanceUM.Parameters("resource_id").Value = cboResourceID.Text

    strColor(0) = "bgcolor=#ffffff"
    strColor(1) = "bgcolor=#eeeeee"
    strColor(2) = "bgcolor=#ddddff"
    strColor(3) = "bgcolor=#ededff"

    sbHTML.Append("<HTML>" & vbCrLf)
    sbHTML.Append("<head>" & vbCrLf)
    sbHTML.Append("<title>Recent Maintenance for Resource</title>" & vbCrLf)
    sbHTML.Append("<style>" & vbCrLf)
    sbHTML.Append("<!--" & vbCrLf)
    sbHTML.Append("table { font-size: 8pt; font-family: Arial }" & vbCrLf)
    sbHTML.Append("-->" & vbCrLf)
    sbHTML.Append("</style>" & vbCrLf)
    sbHTML.Append("</head>" & vbCrLf)
    sbHTML.Append("<body>" & vbCrLf)
    sbHTML.Append("<table width=100% border=0 cellpadding=3 cellspacing=0>" & vbCrLf)
    sbHTML.Append("<tr bgcolor=#8080FF>" & vbCrLf)
    sbHTML.Append("<td align=""center"">Failure Mode</td>" & vbCrLf)
    sbHTML.Append("<td>Start Time</td>" & vbCrLf)
    sbHTML.Append("<td>End Time</td>" & vbCrLf)
    sbHTML.Append("<td>Description of Problem</td>" & vbCrLf)
    sbHTML.Append("<td>Repair Procedure</td>" & vbCrLf)
    sbHTML.Append("<td>Est Hrs</td>" & vbCrLf)
    sbHTML.Append("<td>Act Hrs</td>" & vbCrLf)
    sbHTML.Append("<td>Repair WO</td>" & vbCrLf)
    sbHTML.Append("</tr>" & vbCrLf)
 
    Try
        snpData = comRecentMaintenanceUM.ExecuteReader
        If snpData.IsClosed = False Then
            snpData.FetchSize = comRecentMaintenanceUM.RowSize * 1000
            Do While (snpData.Read())
                If intLastID <> snpData("id") Then
                    'New unplanned maintenance
                    intToggle = 1 - intToggle
                
                    intLastID = snpData("id")
                    'Output the row
                    sbHTML.Append("<tr " & strColor(intToggle) & ">" & vbCrLf)
                    sbHTML.Append("<td>" & snpData("failure_mode") & "</td>" & vbCrLf)
                    If Not (IsDBNull(snpData("start_time"))) Then
                        If Not (IsDBNull(snpData("end_time"))) Then
                            sbHTML.Append("<td>" & Convert.ToDateTime(snpData("start_time")).ToString("g") & "</td>" & vbCrLf)
                            sbHTML.Append("<td>" & Convert.ToDateTime(snpData("end_time")).ToString("g") & "</td>" & vbCrLf)
                        Else
                            sbHTML.Append("<td bgcolor=#ff0000>" & Convert.ToDateTime(snpData("start_time")).ToString("g") & "</td>" & vbCrLf)
                            sbHTML.Append("<td bgcolor=#ff0000>&nbsp;</td>" & vbCrLf)
                        End If
                    Else
                        sbHTML.Append("<td bgcolor=#eeeeee>&nbsp;</td>" & vbCrLf)
                        sbHTML.Append("<td bgcolor=#eeeeee>&nbsp;</td>" & vbCrLf)
                    End If
                    If Not (IsDBNull(snpData("entry_note"))) Then
                        sbHTML.Append("<td>" & snpData("entry_note") & "</td>" & vbCrLf)
                    Else
                        sbHTML.Append("<td>&nbsp;</td>" & vbCrLf)
                    End If
                    If Not (IsDBNull(snpData("repair_note"))) Then
                        sbHTML.Append("<td>" & snpData("repair_note") & "</td>" & vbCrLf)
                    Else
                        sbHTML.Append("<td>&nbsp;</td>" & vbCrLf)
                    End If
                    If Not (IsDBNull(snpData("run_hrs"))) Then
                        sbHTML.Append("<td align=""right"">" & Format(snpData("run_hrs"), "#,##0.00") & "</td>" & vbCrLf)
                    Else
                        sbHTML.Append("<td align=""right"">0.00</td>" & vbCrLf)
                    End If
                    If Not (IsDBNull(snpData("act_run_hrs"))) Then
                        sbHTML.Append("<td align=""right"">" & Format(snpData("act_run_hrs"), "#,##0.00") & "</td>" & vbCrLf)
                    Else
                        sbHTML.Append("<td align=""right"">0.00</td>" & vbCrLf)
                    End If
                    If snpData("resource_is_down") = "Y" Then
                        sbHTML.Append("<td bgcolor=#bb1100>" & snpData("workorder_base_id") & "/" & snpData("workorder_lot_id") & "</td>" & vbCrLf)
                    Else
                        sbHTML.Append("<td bgcolor=#dddd00>" & snpData("workorder_base_id") & "/" & snpData("workorder_lot_id") & "</td>" & vbCrLf)
                    End If
                    sbHTML.Append("</tr>" & vbCrLf)
                End If
                
                If Not (IsDBNull(snpData("calc_qty"))) Then
                    intToggle2 = 1 - intToggle2
                    'Output a part row
                    sbHTML.Append("<tr " & strColor(intToggle2 + 2) & ">" & vbCrLf)
                    sbHTML.Append("<td>&nbsp;</td>" & vbCrLf)
                    sbHTML.Append("<td>&nbsp;</td>" & vbCrLf)
                    sbHTML.Append("<td>&nbsp;</td>" & vbCrLf)
                    
                    If Not (IsDBNull(snpData("part_id"))) Then
                        If Not (IsDBNull(snpData("description"))) Then
                        sbHTML.Append("<td>" & snpData("part_id") & " - " & snpData("description") & "</td>" & vbCrLf)
                    Else
                        sbHTML.Append("<td>" & snpData("part_id") & " - </td>" & vbCrLf)
                    End If
                Else
                    sbHTML.Append("<td>&nbsp;</td>" & vbCrLf)
                End If

                sbHTML.Append("<td align=""right"">Est Qty, Issued Qty:</td>" & vbCrLf)
                If Not (IsDBNull(snpData("calc_qty"))) Then
                    sbHTML.Append("<td align=""right"">" & Format(snpData("calc_qty"), "#,##0.00") & "</td>" & vbCrLf)
                Else
                    sbHTML.Append("<td align=""right"">0.00</td>" & vbCrLf)
                End If
                If Not (IsDBNull(snpData("issued_qty"))) Then
                    sbHTML.Append("<td align=""right"">" & Format(snpData("issued_qty"), "#,##0.00") & "</td>" & vbCrLf)
                Else
                    sbHTML.Append("<td align=""right"">0.00</td>" & vbCrLf)
                End If
                sbHTML.Append("<td>&nbsp;</td>" & vbCrLf)
                sbHTML.Append("</tr>" & vbCrLf)
            End If
        Loop
        sbHTML.Append("</table>" & vbCrLf)
        snpData.Close()
    End If
Catch ex As Exception

End Try

sbHTML.Append("</body></html>")
wbRecentMaintenanceHistory.DocumentText = sbHTML.ToString()

RetrieveRecentMaintenance = True
sbHTML = Nothing
End Function

Using the database platform independent API Toolkit, rather than ODP.Net, forces changes to the code as the GeneralQuery object is very picky about the contents and syntax of SQL statements that are submitted.  Additionally, I tried to use fewer custom tables to store the information in the sample project that I included with the presentation than I did with the application that I created last year.  Also, in this version of the procedure for the “starter” application, I did not use a StringBuilder variable, but instead a normal String variable, so the display speed related to building the HTML code will be a bit slower than it would be otherwise.  To reduce potential confusion for people reviewing the code, I put the SQL statement directly into the function, rather than in the form’s Load procedure:

Public Function RetrieveRecentMaintenance() As Boolean
    Dim i As Integer
    Dim intToggle As Integer = 0
    Dim intToggle2 As Integer = 0
    Dim intColor As Integer = 0
    Dim intOffset As Integer = 0
    Dim strLastID As String = ""
    Dim strSQL As String = ""
    Dim strHTML As String = ""
    Dim strColor(4) As String
    Dim gqResource As Lsa.Shared.GeneralQuery
    Dim drRow As Lsa.Data.DataRow
 
    gqResource = New Lsa.Shared.GeneralQuery(strMDatabase)
 
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & " WO.BASE_ID AS WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & " WO.LOT_ID AS WORKORDER_LOT_ID," & vbCrLf
    strSQL = strSQL & " WO.USER_6 AS START_TIME," & vbCrLf
    strSQL = strSQL & " WO.USER_7 AS END_TIME," & vbCrLf
    strSQL = strSQL & " WO.USER_8 AS FAILURE_MODE," & vbCrLf
    strSQL = strSQL & " WO.USER_9 AS REPORTED_BY," & vbCrLf
    strSQL = strSQL & " WO.USER_10 AS RESOURCE_ID," & vbCrLf
    strSQL = strSQL & " WB.BITS AS ENTRY_NOTE," & vbCrLf
    strSQL = strSQL & " OB.BITS AS REPAIR_NOTE," & vbCrLf
    strSQL = strSQL & " O.RUN_HRS," & vbCrLf
    strSQL = strSQL & " O.ACT_RUN_HRS," & vbCrLf
    strSQL = strSQL & " R.PART_ID," & vbCrLf
    strSQL = strSQL & " P.DESCRIPTION," & vbCrLf
    strSQL = strSQL & " R.CALC_QTY," & vbCrLf
    strSQL = strSQL & " R.ISSUED_QTY" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & " VMFG.WORK_ORDER WO," & vbCrLf
    strSQL = strSQL & " VMFG.WORKORDER_BINARY WB," & vbCrLf
    strSQL = strSQL & " VMFG.OPERATION O," & vbCrLf
    strSQL = strSQL & " VMFG.OPERATION_BINARY OB," & vbCrLf
    strSQL = strSQL & " VMFG.REQUIREMENT R," & vbCrLf
    strSQL = strSQL & " VMFG.PART P" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & " WO.TYPE='W'" & vbCrLf
    strSQL = strSQL & " AND WO.BASE_ID LIKE ?" & vbCrLf
    strSQL = strSQL & " AND WO.SPLIT_ID='0'" & vbCrLf
    strSQL = strSQL & " AND WO.SUB_ID='0'" & vbCrLf
    strSQL = strSQL & " AND WO.STATUS IN ('U','F','R','C')" & vbCrLf
    strSQL = strSQL & " AND WO.CREATE_DATE>= ?" & vbCrLf
    strSQL = strSQL & " AND WO.TYPE=O.WORKORDER_TYPE" & vbCrLf
    strSQL = strSQL & " AND WO.BASE_ID=O.WORKORDER_BASE_ID" & vbCrLf
    strSQL = strSQL & " AND WO.LOT_ID=O.WORKORDER_LOT_ID" & vbCrLf
    strSQL = strSQL & " AND WO.SPLIT_ID=O.WORKORDER_SPLIT_ID" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_SUB_ID='0'" & vbCrLf
    strSQL = strSQL & " AND O.SEQUENCE_NO=10" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_TYPE=R.WORKORDER_TYPE(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_SUB_ID=R.WORKORDER_SUB_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.SEQUENCE_NO=R.OPERATION_SEQ_NO(+)" & vbCrLf
    strSQL = strSQL & " AND R.PART_ID=P.ID(+)" & vbCrLf
    strSQL = strSQL & " AND WB.TYPE(+) ='D'" & vbCrLf
    strSQL = strSQL & " AND WO.TYPE=WB.WORKORDER_TYPE(+)" & vbCrLf
    strSQL = strSQL & " AND WO.BASE_ID=WB.WORKORDER_BASE_ID(+)" & vbCrLf
    strSQL = strSQL & " AND WO.LOT_ID=WB.WORKORDER_LOT_ID(+)" & vbCrLf
    strSQL = strSQL & " AND WO.SPLIT_ID=WB.WORKORDER_SPLIT_ID(+)" & vbCrLf
    strSQL = strSQL & " AND WO.SUB_ID=WB.WORKORDER_SUB_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_TYPE=OB.WORKORDER_TYPE(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_BASE_ID=OB.WORKORDER_BASE_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_LOT_ID=OB.WORKORDER_LOT_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_SPLIT_ID=OB.WORKORDER_SPLIT_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.WORKORDER_SUB_ID=OB.WORKORDER_SUB_ID(+)" & vbCrLf
    strSQL = strSQL & " AND O.SEQUENCE_NO=OB.SEQUENCE_NO(+)" & vbCrLf
    strSQL = strSQL & " AND OB.TYPE(+) ='D'"
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & " WO.CREATE_DATE DESC," & vbCrLf
    strSQL = strSQL & " WO.USER_6 DESC," & vbCrLf
    strSQL = strSQL & " R.PART_ID"
 
    gqResource.Prepare("-", strSQL)
    'gqResource.Parameters(0) = "EMUM-" & cboResourceID.Text & "-____" 'Begins with EMUM- and ends with -YYYY
    gqResource.Parameters(0) = "EMUM-" & cboResourceID.Text & "-%" 'Begins with EMUM- and ends with -YYYY or -YY
    gqResource.Parameters(1) = DateAdd(DateInterval.Day, -120, Date.Today)
 
    strColor(0) = "bgcolor=#ffffff"
    strColor(1) = "bgcolor=#eeeeee"
    strColor(2) = "bgcolor=#ddddff"
    strColor(3) = "bgcolor=#ededff"
 
    strHTML = "<HTML>" & vbCrLf
    strHTML = strHTML & "<head>" & vbCrLf
    strHTML = strHTML & "<title>Recent Maintenance for Resource</title>" & vbCrLf
    strHTML = strHTML & "<style>" & vbCrLf
    strHTML = strHTML & "<!--" & vbCrLf
    strHTML = strHTML & "table { font-size: 8pt; font-family: Arial }" & vbCrLf
    strHTML = strHTML & "-->" & vbCrLf
    strHTML = strHTML & "</style>" & vbCrLf
    strHTML = strHTML & "</head>" & vbCrLf
    strHTML = strHTML & "<body>" & vbCrLf
    strHTML = strHTML & "<table width=100% border=0 cellpadding=3 cellspacing=0>" & vbCrLf
    strHTML = strHTML & "<tr bgcolor=#8080FF>" & vbCrLf
    strHTML = strHTML & "<td align=""center"">Failure Mode</td>" & vbCrLf
    strHTML = strHTML & "<td>Start Time</td>" & vbCrLf
    strHTML = strHTML & "<td>End Time</td>" & vbCrLf
    strHTML = strHTML & "<td>Description of Problem</td>" & vbCrLf
    strHTML = strHTML & "<td>Repair Procedure</td>" & vbCrLf
    strHTML = strHTML & "<td>Est Hrs</td>" & vbCrLf
    strHTML = strHTML & "<td>Act Hrs</td>" & vbCrLf
    strHTML = strHTML & "<td>Repair WO</td>" & vbCrLf
    strHTML = strHTML & "</tr>" & vbCrLf
 
    Try
        gqResource.Execute()
    
        If (gqResource.Tables(0).Rows.Count > 0) Then
            For i = 0 To gqResource.Tables(0).Rows.Count - 1
                drRow = gqResource.Tables(0).Rows(i)
                If strLastID <> (drRow("workorder_base_id") & drRow("workorder_lot_id")) Then
                    'New unplanned maintenance
                    intToggle = 1 - intToggle
               
                    strLastID = drRow("workorder_base_id") & drRow("workorder_lot_id")
                    'Output the row
                    strHTML = strHTML & "<tr " & strColor(intToggle) & ">" & vbCrLf
                    strHTML = strHTML & "<td>" & drRow("failure_mode") & "</td>" & vbCrLf
                    If Not (IsDBNull(drRow("start_time"))) Then
                        If Not (IsDBNull(drRow("end_time"))) Then
                            strHTML = strHTML & "<td>" & drRow("start_time") & "</td>" & vbCrLf
                            strHTML = strHTML & "<td>" & drRow("end_time") & "</td>" & vbCrLf
                        Else
                            strHTML = strHTML & "<td bgcolor=#ff0000>" & drRow("start_time") & "</td>" & vbCrLf
                            strHTML = strHTML & "<td bgcolor=#ff0000>&nbsp;</td>" & vbCrLf
                        End If
                    Else
                        strHTML = strHTML & "<td bgcolor=#eeeeee>&nbsp;</td>" & vbCrLf
                        strHTML = strHTML & "<td bgcolor=#eeeeee>&nbsp;</td>" & vbCrLf
                    End If
                    If Not (IsDBNull(drRow("entry_note"))) Then
                        strHTML = strHTML & "<td>" & System.Text.Encoding.UTF8.GetString(drRow("entry_note")) & "</td>" & vbCrLf
                    Else
                        strHTML = strHTML & "<td>&nbsp;</td>" & vbCrLf
                    End If
                    If Not (IsDBNull(drRow("repair_note"))) Then
                        strHTML = strHTML & "<td>" & System.Text.Encoding.UTF8.GetString(drRow("repair_note")) & "</td>" & vbCrLf
                    Else
                        strHTML = strHTML & "<td>&nbsp;</td>" & vbCrLf
                    End If
                    If Not (IsDBNull(drRow("run_hrs"))) Then
                        strHTML = strHTML & "<td align=""right"">" & Format(drRow("run_hrs"), "#,##0.00") & "</td>" & vbCrLf
                    Else
                        strHTML = strHTML & "<td align=""right"">0.00</td>" & vbCrLf
                    End If
                    If Not (IsDBNull(drRow("act_run_hrs"))) Then
                        strHTML = strHTML & "<td align=""right"">" & Format(drRow("act_run_hrs"), "#,##0.00") & "</td>" & vbCrLf
                    Else
                        strHTML = strHTML & "<td align=""right"">0.00</td>" & vbCrLf
                    End If
                    strHTML = strHTML & "<td>" & drRow("workorder_base_id") & "/" & drRow("workorder_lot_id") & "</td>" & vbCrLf
                    strHTML = strHTML & "</tr>" & vbCrLf
                End If
 
                If Not (IsDBNull(drRow("calc_qty"))) Then
                    intToggle2 = 1 - intToggle2
                    'Output a part row
                    strHTML = strHTML & "<tr " & strColor(intToggle2 + 2) & ">" & vbCrLf
                    strHTML = strHTML & "<td>&nbsp;</td>" & vbCrLf
                    strHTML = strHTML & "<td>&nbsp;</td>" & vbCrLf
                    strHTML = strHTML & "<td>&nbsp;</td>" & vbCrLf
                    If Not (IsDBNull(drRow("part_id"))) Then
                        If Not (IsDBNull(drRow("description"))) Then
                            strHTML = strHTML & "<td>" & drRow("part_id") & " - " & drRow("description") & "</td>" & vbCrLf
                        Else
                            strHTML = strHTML & "<td>" & drRow("part_id") & " - </td>" & vbCrLf
                        End If
                    Else
                        strHTML = strHTML & "<td>&nbsp;</td>" & vbCrLf
                    End If
  
                    strHTML = strHTML & "<td align=""right"">Est Qty, Issued Qty:</td>" & vbCrLf
                    If Not (IsDBNull(drRow("calc_qty"))) Then
                        strHTML = strHTML & "<td align=""right"">" & Format(drRow("calc_qty"), "#,##0.00") & "</td>" & vbCrLf
                    Else
                        strHTML = strHTML & "<td align=""right"">0.00</td>" & vbCrLf
                    End If
                    If Not (IsDBNull(drRow("issued_qty"))) Then
                        strHTML = strHTML & "<td align=""right"">" & Format(drRow("issued_qty"), "#,##0.00") & "</td>" & vbCrLf
                    Else
                        strHTML = strHTML & "<td align=""right"">0.00</td>" & vbCrLf
                    End If
                    strHTML = strHTML & "<td>&nbsp;</td>" & vbCrLf
                    strHTML = strHTML & "</tr>" & vbCrLf
                End If
            Next i
        End If 'If (gqResource.Tables(0).Rows.Count > 0)
    Catch ex As Exception
 
    End Try
    strHTML = strHTML & "</table>" & vbCrLf
    strHTML = strHTML & "</body></html>"
  
    wbRecentMaintenanceHistory.DocumentText = strHTML
 
    RetrieveRecentMaintenance = True
    gqResource.Dispose()
End Function

The presentation slides and support documents, of course, dive into greater detail about how the sample application works.


The presentation topics extended beyond merely reproducing the core functionality of the equipment maintenance application that I created a year ago, taking a deep dive into the undocumented areas of the API Toolkit.  Below are a few screen captures from the presentation, demonstrating various capabilities of the Visual API Toolkit (click the pictures for a larger view).  The top section of the API Toolkit Demo window shows how to connect to the Visual Manufacturing database using three different methods, as well as how to retrieve basic information from the API Toolkit.  The first tab (top-left in the below picture) shows how to create and copy Visual Manufacturing work orders using the API Toolkit for equipment maintenance type tasks (procedures may be generalized for working with normal production work orders).  The second tab (top-right in the below picture) shows how to perform various transactions in Visual Manufacturing using the API Toolkit.

The Toolkit functionality is divided into seven categories.  The first of those categories, Financials (bottom-left in the below picture), allows working with standard financials accounts payable and receivable type documents.  Clicking one of the Load buttons generally retrieves a single document from the database, and displays the header information for that document in the table at the bottom-left of the application window.  Documents tend to contain more than one result table (child tables), so the bottom-right of the API Toolkit Demo window contains a grid that shows all of the retrieved table names, column names, column data types, whether or not the column permits NULL values, and whether or not the column is provided a default value if one is not provided in your application code – the standard documentation for the most part excludes this type of helpful information.  The various Browse buttons potentially retrieve all of the related document headers and the specified columns without retrieving any sub-tables.  The second category, Inventory (bottom-right), allows working with part IDs and creating inventory transactions in Visual Manufacturing.

The third category, Purchasing (top-left in the below picture) allows working with purchase orders and receiving purchased items into inventory.  The fourth category, Sales (top-left in the below picture) allows working with customer orders and related types of documents.  The fifth category, Shared (bottom-left in the below picture) is more or less a catch-all for maintaining various types of lists in the database, such as employees, product codes, and commodity codes.  The sixth category, ShopFloor (bottom-right in the below picture) is used to manage work orders, create labor tickets for those work orders, and manage the resource IDs.

The final category, Trace (not pictured), is rather limited, and is used to maintain or review part trace (such as mill heat code) information.  Whenever a Browse button is clicked in the API Toolkit Demo main window, the demo application uses Reflection to obtain additional (otherwise unpublished) information about the selected API Toolkit item, retrieving all of the item’s methods (functions) and calling parameter types – this information is displayed in a secondary popup window.  For those methods that return DataRow objects, the bottom section of the window shows the various columns found in the DataRow, as well as their data types and default value, and sample VB.Net code to set the value for the table column.

Scrolling the top half of the window down shows that the demo program also attempts to retrieve the actual parent, child, and grandchild database table names from the API Toolkit, along with the key columns that are used for joining those tables:

Information from the popup window may be sent into Microsoft Excel by clicking the Excel icon, with the program providing additional VB.Net compatible code usage examples, thus allowing rapid use of unfamiliar portions of the API Toolkit.  The top half of the popup window is shown on the Calling Code tab in the Excel spreadsheet:

The bottom half of the window is transferred to the second tab, Method Columns, of the generated spreadsheet:

The below pictures show slides from the presentation that show the other example applications that were included in the API Toolkit Demo application.  The first picture shows an attempt to adapt a barcode labor collection application to use the API Toolkit to create and edit labor tickets – the example was implemented with mixed results, as indicated on the slide:

The next two slides show the Simplified Receiving application in use, receiving purchase items into Visual’s inventory using the API Toolkit:

The last three slides show a tool crib self-checkout application that translates standardizes UPC barcode labels into their corresponding Visual Manufacturing part IDs, and if the employee using the application has an in-process labor ticket, that work order’s information is retrieved automatically when the employee’s ID number is entered:


While there is not much Oracle Database specific content in the presentation, it is somewhat interesting to see the lengths that I had to go through to use a database agnostic API to perform various tasks in the database.  Some of those tasks would have been much easier and faster to implement using pure Oracle methods, but given that the majority of the audience used SQL Server and not Oracle Database and the fact that the API Toolkit was previously poorly documented (despite having hundreds of pages of documentation), the two month development time for the presentation was likely time well spent.





Huge Presentation – Working with Oracle Database in C#, VBScript, and Excel; Enhancing Visual Manufacturing 8.0.0

2 11 2016

November 2, 1016

In late September 2016 I gave a somewhat long presentation that lasted roughly four and a half hours.  Surprisingly, the only glitch that I encountered during the live demonstration portion of the presentation was due to an apparent bug, at least in Excel 2010, that causes long object names (specifically checkbox names on the YearEnd tab) to be discarded when the .XLSX file format is used rather than the .XLS file format.  This Huge Presentation required roughly two months to put together, and half of that time was spent updating the contents of a presentation that I originally gave in 2009 that originally required three months to assemble.  I initially used Windows 10 and Excel 2016 for most of the updates, and then found significant compatibility problems when trying to test the live demonstration portion of the presentation with Windows 7 and Excel 2010… so, I spent literally days fixing the presentation contents so that Windows 7 and Excel 2010 were supported again.

As far as I am aware, the live presentation was not recorded.  However, I managed to sneak out a copy of the slide deck of the presentation (210+ slides, most with an extensive Notes section); the full source code of the examples – some of which were written in C#, Visual Basic 6.0, VBScript, Excel macro language, or the macro language used by Visual Manufacturing; and more.  The presentation and support files that I created as part of the presentation may be downloaded from Google Drive at this link.  The Crystal Reports free runtime support files, which are required to create PDF versions of Crystal Report files from a VBScript, and within the C# IIS example project, may be downloaded from Google Drive at this link. On the Google Drive site, click the Download button to save the Visual8MacrosAndExcel.zip file to your computer.  Once the download completes, extract the contents of that file to a new C:\Visual8MacrosAndExcel folder in the root of the C:\ drive (some of the examples specifically refer to this folder name – adjust the examples as necessary if you extract the files to a different location).  Some of the examples may also require a C:\Visual folder to be created.

presentation-download

So, what is in the presentation and support files?  A huge collection of programming examples that are usable essentially out of the box for people using Infor’s Visual Manufacturing 8.0.0 with an Oracle Database backend.

  • For people using older versions of Visual Manufacturing with an Oracle Database backend, take a look at the 2009 version of the presentation which may be downloaded from the VMIUG-TEC Yahoo group’s files area.
  • For people using Visual Manufacturing 8.0.0 with a SQL Server backend, all I can say is good luck with converting the code examples – a few people have successfully converted a handful of the examples.
  • For people who have no experience with Visual Manufacturing, but are interested in programming against an Oracle Database backend, the examples in the presentation could be very helpful.
  • For people who are bored, just want to listen to roughly 4.5 hours of computer generated speech (likely sounds best on Windows 10), or suffering from severe sleep insomnia, download the presentation and click the Read to Me button on every slide in the presentation.

So, what is in the presentation?  A partial list:

  • 135+ VBScript or Visual Manufacturing macros.
  • At least 13 custom helper EXE or DLL files, including a few to help specifically with minor cases of sleep insomnia, that may be used within VBScript, Excel macros, and other programs.  There is even a custom helper that allows inserting a web page into just about any program window.
  • C# ASP.Net (IIS) project that allows selecting data from the database based on date ranges or other criteria, with the results returned to the client in web pages using HTML tables (Repeater control or the “hard way” with HTML table code created manually), or potentially sent to Microsoft Excel or PDF files (with Crystal Report files as the report source).
  • Potential solutions for various problems encountered when trying to use the C# project with IIS.
  • Visual Manufacturing 8.0.0 “macro encyclopedia” – listing/detailing essentially every macro variable that is available in Visual Manufacturing 8.0.0.
  • Executing Microsoft Excel macros, Excel functions, and using Excel user forms from within a VBScript or Visual Manufacturing macro.
  • Printing a label to a Zebra printer that supports the ZPL language using a VBScipt file with the help of an Excel macro.
  • Generating multiple Microsoft Outlook emails from a VBScript file that accesses an Oracle Database.
  • Determining if the person logged into the computer is a member of an Active Directory group within a VBScript or Visual macro so that an action may be allowed or denied.
  • Basics of writing SQL statements.
  • Enabling the Developer/macro functionality in Microsoft Excel 2016.
  • Methods of retrieving and using Oracle Database data in Microsoft Excel, and potentially pushing data back into the database.
  • Executing Windows Management Instrumentation (WMI) calls in Microsoft Excel against the local computer and/or other computers in the Active Directory domain.  For domain administrators, for example, this functionality would permit retrieving a list of every running process on every computer in the domain, and then selectively killing specific processes running on a specific remote computer (or starting a process on that remote computer).
  • Extracting most or all of the icons that are built into Microsoft Excel.

If you find the presentation or the support files helpful, feel free to leave a comment.  If you need assistance with some of the examples, I will do what I can to help as time permits.  If you need help converting the examples to work with a SQL Server backend… you are on the wrong blog – I cannot help with that conversion.

(Updated with a few pictures from the presentation slides November 3, 2016):

C# ASP.Net Project:

part-search-in-edge

Part Search page running in the Edge browser in Windows 10

Part Search in Visual.png

Part Search page running in the Visual Manufacturing banner – top half of output

 

part-search-in-visual2

Part Search page running in the Visual Manufacturing banner – bottom half of output

 

VMBrowse in Visual.png

VMBrowse page in the Visual Manufacturing banner – Part Browsing

 

VMBrowse in Visual2.png

VMBrowse page in the Visual Manufacturing banner – Work Order Browsing

 

Labor Search in Manufacturing Window.png

Labor Ticket search page embedded into the Manufacturing Window

 

Excel Sample Project:

windows-management-instrumentation-in-excel

Windows Management Instrumentation control panel in Excel

 

Extract Excel Icons.png

Extract Excel’s built-in icons

 

resource-check-in-excel

Resource Check – a very light-weight version of Nagios

 

Resource Check in Excel2.png

Resource Check – graphical view, red shows down status

 

exploded-structure-viewer-in-excel

Exploded Structure Viewer

 

exploring-foreign-keys-query-of-excel

Showing database table relationships using defined foreign keys – Querying Excel

 

material-planning-in-excel

Material Planning in Excel

 

material-planning-in-excel2

Multi-level Material Planning review in Excel

 

shop-order-drill-down-in-excel

Multi-level cost analysis in Excel

 

 





Programming for Style and Beautiful Black Boxes – is Fast Performance an Afterthought?

24 06 2016

June 24, 2016

I have been working with an Enterprise Resource Planning (ERP) system platform for a bit over 16 years.  Through various methods of observation, including the use of 10046 extended Oracle SQL traces, Process Monitor traces, Wireshark traces, and just general observation of the various components of the ERP system, I noticed a strong emphasis on code modularization.  In some cases, excessive modularization that causes interesting patterns to appear in Oracle SQL traces and Process Monitor traces.  For example, a Process Monitor trace might show repeated patterns: access to the same Windows registry entries, followed by file path searches to access the same set of files, followed by communication with the Oracle Database server, followed by accesses to additional Windows registry entries, and additional files – this sequence of events might repeat hundreds, thousands, or millions of times as if the program were executing in a tight loop.

I am currently in the role of a senior developer, with opportunities for Oracle DBA work, direct support of the same ERP system, Citrix Server support, and whatever else requires attention from a software perspective (plus I have the opportunity to work with a great team of fellow IT people and dabble a bit with the hardware side as well).  This transition took place a couple of months ago, right around the time of one of my previous blog posts.  I have written a couple of interesting programs in the last couple of months, including a system for the Maintenance department to use to track planned and unplanned equipment maintenance, tightly integrating into the ERP system.  I have also spent some time reviewing the programming source code and modifying programs written by a fairly well known consulting firm that specializes in writing custom software for the ERP system.

The developer of those programs was brilliant in his programming style – so brilliant, that as a mere senior developer I have difficulty tracking some of his programs’ execution from one black box procedure to another, with some variables being passed by memory address location and modified in the second black box, and other variables packaged into a new variable as comma delimited lists that are then passed into the second black box.  The second black box may call a third black box that separates the comma delimited list passed in by the first black box.  The second black box, now having the de-delimited list of variables, may then pass a SQL statement to another black box to retrieve a value from the database, and then call that same black box again to retrieve another value from the same table row in the database.  This programmer was brilliant, burying some important code three, four, five, or six black box levels deep, far beyond my capability as an outside developer to track the program execution (at least without instrumenting the code to write to a log file, indicating I am now in black box ABCD, I received values “M,N,O,P,Q” and translated that into R=M, S=O, T=P, U=Q, and am preparing to execute black box EFGH passing in variables J, K, and L).  It is a brilliant design, until someone has to debug the execution.  Why did this label print claiming that there were 50 parts in this weighed parts bin, when the parts bin next to it from the previous work order lot, filled to about the same level with the same type of part, has a label indicating that its part bin contained 390 parts?  (This is an actual problem that I investigated in the last week – buried deeply in the black box design I found the culprit, a SQL statement with ROWNUM in the WHERE clause where the SQL statement also contained an ORDER BY clause.)

While debugging another programming project written by the same consulting firm, I found an interesting example of brilliant programming style and crafting black boxes that left me wondering, is brilliant programming style and crafting black boxes more important than application performance?  Is not inefficiencies in application performance a bug?

I stumbled across a short code block in the other programming project that was retrieving information from an Oracle database.  As an outsider, I thought that roughly 21.5 seconds was an excessive amount of time for this short code block to execute, retrieving information about 3,083 objects in a database table and populating a treeview control in the user interface.  Here is the VB.Net code that I found:

    Private Sub TubsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TubsToolStripMenuItem.Click
        Try
            Dim i As Integer
            Dim strSQL As String, rs As ADODB.Recordset, rs2 As ADODB.Recordset
            Dim Anchor As Point = New Point(0, MenuStrip1.Height)

            Dim rsTub As Data.DataTable
            Dim dt As Data.DataRow
            Dim OldCursor As Windows.Forms.Cursor
            OldCursor = Windows.Forms.Cursor.Current
            Windows.Forms.Cursor.Current = Windows.Forms.Cursors.WaitCursor
            Label1.Visible = False
            Label2.Visible = False
            DataGridView1.Visible = False
            DataGridView2.Visible = False
            Button1.Visible = False
            Button3.Visible = False
            btnAddNew.Visible = False
            '
            '   Display the information for all the tubs.
            '
            tv1.BeginUpdate()
            tv1.Nodes.Clear()

            '
            '   Add the root level node.
            '
            tv1.Nodes.Add("Tubs")

            '
            '   Get the information from the database.
            '

            strSQL = "Select Distinct TubID from MTS_TubAttributes"
            rs = DBSelect(strSQL)
            '
            '   Add this information to rsTub.
            '
            rsTub = New Data.DataTable
            rsTub.Columns.Add("TUBID", Type.GetType("System.String"))
            rsTub.Columns.Add("TUB", Type.GetType("System.Int32"))

            While Not rs.EOF
                dt = rsTub.NewRow
                dt("TUBID") = rs("TubID").Value
                dt("TUB") = CDbl(rs("TubID").Value)
                rsTub.Rows.Add(dt)
                rs.MoveNext()
            End While
            Call SafeRSCloseAndClean(rs)
            '
            '   Done with that, sort the tubs by tub
            '

            Dim strExpr As String
            Dim strSort As String
            strExpr = "TubID IS NOT NULL"
            ' Sort descending by column named CompanyName.
            strSort = "Tub ASC"
            Dim foundRows() As DataRow
            ' Use the Select method to find all rows matching the filter.
            foundRows = rsTub.Select(strExpr, strSort)
            For i = 0 To foundRows.GetUpperBound(0)
                tv1.Nodes(0).Nodes.Add(foundRows(i).Item("TubID"))
                '
                '   Add the details.
                '
                strSQL = "Select * from MTS_TubAttributes Where TubID = '" & foundRows(i).Item("tubID") & "' Order By AttributeID"
                rs2 = DBSelect(strSQL)
                While Not rs2.EOF
                    tv1.Nodes(0).Nodes(i).Nodes.Add(rs2("AttributeID").Value & " - " & rs2("AttributeValue").Value)
                    rs2.MoveNext()
                End While
                Call SafeRSCloseAndClean(rs2)
            Next
            foundRows = Nothing
            rsTub.Dispose()
            tv1.EndUpdate()
            MakeTV_Visible()
            Windows.Forms.Cursor.Current = OldCursor
        Catch ex As Exception
            Call LogError("TubsMenuItem", Err.Number, Err.Description)
        End Try
    End Sub

Is that brilliant style with beautiful black boxes?

  1. Create a SQL statement to select a distinct list of TUBID objects from the MTS_TUBATTRIBUTES table
  2. Pass that SQL statement to a black box to retrieve a recordset from the database
  3. Manipulate the results into a sorted list
  4. Loop through the sorted list building SQL statements (with literals rather than bind variables) to retrieve the attributes rows that describe the TUBID from the same MTS_TUBATTRIBUTES table
  5. Pass the generated SQL statement to a black box to retrieve a second recordset from the database
  6. Create the treeview control nodes from the second recordset rows.
  7. Close the second recordset by passing it to another black box.
  8. Jump back to step 4 another 3,082 times.

Now imagine, instead of the program running on a computer that is on the same local network as the database server (with a typical ping time of less that 0.001 seconds), what would happen if this program were run on a computer that has a WAN connection to the database server (let’s assume a 0.060 second, or 60ms typical ping time).  In a best case scenario (which is not possible), just sending the 3,083 distinct SQL statements to the database to be parsed (without waiting for a recordset to be returned) will waste at least 185 seconds (3,083 * 0.060 = 184.98), even if it takes the Oracle Database server 0.000000 seconds to hard parse each of the 3,083 unique SQL statements.  That is a fantastic way to look busy without actually completing much, if any, useful work.

I have a hard time accepting inefficient design as anything but a bug, even if it requires deviating from brilliant style with fantastic black boxes, so I retrofitted the brilliant style using something that I seem to recall being called “control break logic”.  I decreased the 3,084 SQL statement count to a single SQL statement, and pre-expanded the top-most node in the treeview control to save the end user one extra click.  I did not remove the DBSelect or the SafeRSCloseAndClean black boxes (one might wonder if the DBSelect black box is opening a database connection to send the SQL statement to the database, and the SafeRSCloseAndClean black box is closing that database connection), nor did I convert the code to use Oracle’s .Net Oracle.DataAccess objects rather than ADO, or clean up much of the formatting to match my typical conventions.  Here is the end result of the “control break logic” implementation:

    Private Sub TubsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TubsToolStripMenuItem.Click
        Try

            Dim i As Integer
            Dim intNodeCount As Integer = -1
            Dim strLastTub As String = ""
            Dim strSQL As String, rs As ADODB.Recordset
            Dim Anchor As Point = New Point(0, MenuStrip1.Height)

            Dim OldCursor As Windows.Forms.Cursor
            OldCursor = Windows.Forms.Cursor.Current
            Windows.Forms.Cursor.Current = Windows.Forms.Cursors.WaitCursor
            Label1.Visible = False
            Label2.Visible = False
            DataGridView1.Visible = False
            DataGridView2.Visible = False
            Button1.Visible = False
            Button3.Visible = False
            btnAddNew.Visible = False
            '
            '   Display the information for all the tubs.
            '
            tv1.BeginUpdate()
            tv1.Nodes.Clear()

            '
            '   Add the root level node.
            '
            tv1.Nodes.Add("Tubs")

            '
            '   Get the information from the database.
            '

            strSQL = "SELECT" & vbCrLf
            strSQL = strSQL & "  *" & vbCrLf
            strSQL = strSQL & "FROM" & vbCrLf
            strSQL = strSQL & "  MTS_TUBATTRIBUTES" & vbCrLf
            strSQL = strSQL & "WHERE" & vbCrLf
            strSQL = strSQL & "  TUBID IS NOT NULL" & vbCrLf
            strSQL = strSQL & "ORDER BY" & vbCrLf
            strSQL = strSQL & "  TO_NUMBER(TUBID)," & vbCrLf
            strSQL = strSQL & "  ATTRIBUTEID," & vbCrLf
            strSQL = strSQL & "  ATTRIBUTEVALUE"

            rs = DBSelect(strSQL)

            strLastTub = ""

            Do While Not (rs.EOF)
                If strLastTub <> rs("tubid").Value Then
                    'New tubid
                    intNodeCount = intNodeCount + 1
                    tv1.Nodes(0).Nodes.Add(rs("TubID").Value)
                    strLastTub = rs("tubid").Value
                End If

                tv1.Nodes(0).Nodes(intNodeCount).Nodes.Add(rs("AttributeID").Value & " - " & rs("AttributeValue").Value)

                rs.MoveNext()
            Loop
            Call SafeRSCloseAndClean(rs)

            If tv1.Nodes(0).Nodes.Count > 2 Then
                tv1.Nodes(0).Expand()
            End If

            tv1.EndUpdate()
            MakeTV_Visible()
            Windows.Forms.Cursor.Current = OldCursor
        Catch ex As Exception
            Call LogError("TubsMenuItem", Err.Number, Err.Description)
        End Try
    End Sub

Did the above code change result in a performance improvement?  The client computer (which is a bit slow), completed the above procedure in roughly 5.2 seconds, a pretty healthy improvement from the original 21.5 seconds observed with the original code.  The result would have been even more impressive running over a WAN connection that has a 0.60 second (60ms) ping time – the 184.98 seconds wasted just sending the 3,083 distinct SQL statements to the database to be parsed (actual execution and retrieval of the resultsets would have significantly added to that time) was completely eliminated, and properly setting the array fetch size (rather than leaving it set at the ADO default) would further enhanced performance over the WAN connection.

Maybe a 4.13 factor improvement in performance is not significant enough to consider this change as an improvement?  As a fun experiment, I commented out the following lines in the code of my modified version of the code so that the treeview control is not populated:

tv1.Nodes(0).Nodes.Add(rs("TubID").Value)

tv1.Nodes(0).Nodes(intNodeCount).Nodes.Add(rs("AttributeID").Value & " - " & rs("AttributeValue").Value)

I then re-timed the execution of the modified procedure – it now completes in less than 0.2 seconds.  With that in mind, on this particular computer populating the treeview probably takes about 4.9 seconds to complete (4.9 + 0.2 seconds is within 0.1 seconds of the measured time for the original execution of modified procedure) regardless if the code is running in the original unmodified procedure or my modified version of that procedure.  If we subtract out the uncontrollable treeview update time (there is a way to improve this performance further), then the unmodified procedure completes in 21.5 – 4.9 = 16.6 seconds, while my modified version of the same procedure completes in less than 0.2 seconds, so an actual 83 factor improvement when the program is run on a computer that is located in the same LAN as the Oracle Database server.  Something is bugging me, but it is not this procedure’s performance problem any longer.


Thoughts?  Is excessive code procedure modularization a goal to achieve?  Is excessive code procedure modularization something to avoid as much as possible?  Or is there a happy medium (a play on words here, using this definition: a person claiming to be in contact with the spirits of the dead and to communicate between the dead and the living) to the headache that excessive code procedure modularization seems to cause for me?





C# Web-Based Forms with Oracle Database Access

20 02 2016

February 20, 2016

I have been experimenting a bit with ASP.Net, having used classic ASP (with VBScript) for generating various web-based reports somewhat extensively more than a decade ago.  While before I used little more than Microsoft’s Notepad application and occasionally Microsoft FrontPage for HTML syntax verification, the development environment that I have been using for ASP.Net is the free Visual Studio Express 2015 for Web development environment.  To say that the development approach is different is a bit of an understatement, of course some credit is due to the transition from VBScript to C# (or its close cousin VB.Net).

I created a sample project using C#, ASP.Net, and an Oracle Database connection to demonstrate some of the functionality, reproducing a date selector control format that I originally created for use in Visual Basic 6.0 in early 2002, as shown at the top-right of the picture below:

CWebNCMRReport

First, we will create a table in the database using a modified script from another article that I posted six years ago:

CREATE TABLE EMPLOYEE_ATTEND_TEST AS
SELECT
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,16)),
          0,'MIKE',
          1,'ROB',
          2,'SAM',
          3,'JOE',
          4,'ERIC',
          5,'BILL',
          6,'DENNIS',
          7,'PATRICK',
          8,'JIMMY',
          9,'JOHN',
          10,'SARAH',
          11,'JILL',
          12,'PHILLIS',
          13,'STEPHANY',
          14,'DENISE',
          15,'MARTHA') EMPLOYEE_ID,
  TRUNC(SYSDATE)-ROUND(DBMS_RANDOM.VALUE(0,1000)) SHIFT_DATE,
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,10)),
          0,'VAC',
          1,'HOL',
          2,'BEREAVE',
          3,'JURY',
          4,'ABS',
          5,'EXCUSE',
          6,'MIL',
          'OTHER') INDIRECT_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=3000;

The above script creates a table with 3,000 rows, randomly assigning employee names and different attendance absence codes to each of those rows.  The goal of the sample project is to allow a user of the web form to make a date and/or employee name selection, and then have the matching rows from the database table displayed in a HTML table on the web page using alternating colors for each table row.  There are of course potential areas of improvement, leaving open the possibility of a follow up article (and an opportunity for code improvement suggestions from readers of this article).

To start, we need to create a new project in Visual Studio Express 2015 for Web (the directions should be similar for the full version of Visual Studio).  From the File menu, select New Project… Under the Templates heading, expand Visual C#, then select Web.  Give the project a useful name (I named the project WebsiteTestingApplication), select an appropriate parent folder for the project (I selected C:\Projects\CodeCSharp\), make certain that there is a checkmark next to Create directory for solution, then click the OK button.

CWebCreateNewProject

Next, select the Empty template (if not already selected), make certain that Host in the cloud is not selected, then click the OK button.

CWebCreateNewProject2

In the Visual Studio IDE (integrated development environment), right-click the project in the Solution Explorer, select Add, and then click New Item…

CWebAddNewItem

In the Add New Item dialog, under the Visual C# heading at the left make certain that Web is selected, then select Web Form in the center pane.  Give the form an appropriate name (I selected DatabaseQueryTest.aspx – this name will be shown in the web browser address bar), then click the Add button:

CWebAddNewItem2

The starting pre-generated HTML code for the page should then appear in the IDE.  Feel free to specify a table for the page between the <title> </title> elements (I specified C# Website Database Test – hoopercharles.wordpress.com), and change the form name (id) attribute (I specified frmWebsiteDatabaseTest):

CWebNewASPXPageMinorModifications

Since my development computer is running a 64 bit version of Windows, it is best to specify that the Platform Target for the application is either 32 bit (x86) or 64 bit (x64).  I will select x86 so that the project will use the 32 bit Oracle Client on the computer as well as the 32 bit Crystal Reports runtime drivers.  From the Project menu, select WebsiteTestApplication Properties, click the Build heading at the left, then change the Platform target to x86:

CWebProjectPropertiesPlatformTarget

Next, we will add references to the project, which will permit querying the Oracle database, and potentially displaying Crystal Report files.  From the Project menu, select Add Reference…:

CWebProjectAddReference

While this article will not use the Crystal Reports functionality, select the items as shown below by placing checkmarks to the left of the items (if these items are not in the list, installing the free Crystal Reports XI runtime should add these items to the list of selectable items) after clicking the Extensions option at the left:

CWebProjectAddReference2

Scroll down to the Oracle.DataAccess item and place a checkmark next to that item (note: the 64 bit driver may be selected even though we selected x86 as the Platform Target, as a result, we may need to fix this reference item later).  Click the OK button:

CWebProjectAddReference3

Once the references are added, view the C# code that is associated with the DatabaseQueryTest.aspx page by right-clicking that page in the Solution Explorer and selecting View Code: (selecting View Markup will redisplay the HTML code that previously appeared on screen, while View Designer attempts to show an editable preview of the page):

CWebViewCode

We need to add a few using entries near the top of the C# code to simplify the process of using the items exposed by the add-ons that were just referenced, as shown below:

CWebViewCode2

To the list of using entries, add:

using Oracle.DataAccess.Client;                 // ODP.NET Oracle managed provider
using Oracle.DataAccess.Types;                  // ODP.NET Oracle managed provider
using CrystalDecisions.Shared;                  // Crystal Reports support
using CrystalDecisions.CrystalReports.Engine;   // Crystal Reports support
//using CrystalDecisions.Windows.Forms;         // Crystal Reports support, not needed for this project

(Note that there is a minor issue with the default list of using clauses when using database access techniques, but this problem will be addressed later.)

Below the opening bracket below the public partial class DatabaseQueryTest : System.Web.UI.Page line, add the following code, which will create variables for a connection to the Oracle database, create two variables for reading resultsets from the database, create four database command variables (for specifying the SQL statements and bind variable definitions), and a Boolean variable that tracks whether or not the database connection attempt was successful:

        OracleConnection dbDatabase;
        OracleDataReader snpEmployeeID;
        OracleDataReader snpAttendance;
        OracleCommand comEmployeeID;
        OracleCommand comAttendanceSD;
        OracleCommand comAttendanceEmp;
        OracleCommand comAttendanceSDEmp;
        
        Boolean blConnected = false;

Save All of the project, then press the F5 key to run the web page.  On my 64 bit development computer with x86 specified as the Platform Target, I immediately received an error stating that there was a mismatch between the processor architecture of the project and the Oracle.DataAccess dll that has a processor architecture of AMD64:

CWebPossibleProblem32Bit64Bit

If you encounter this problem, expand References in the Solution Explorer window, right click Oracle.DataAccess and then select Remove from the menu.  Next, from the Project menu select Add Reference…, click the Browse button, then manually locate and add the 32 bit version of the Oracle.DataAccess.dll file.  On my development computer I have a 32 bit Oracle client with the necessary odp.net drivers installed in the folder C:\Oracle\product\11.2.0\client_1\ so in the Select the files to reference window I browsed to the folder: C:\Oracle\product\11.2.0\client_1\odp.net\bin\4, selected the Oracle.DataAccess.dll file, and then clicked the Add button:

CWebPossibleProblem32Bit64BitFix

Save All files in the project again, then press the F5 key to run the project (the current aspx page in the project).  If errors such as “The name ‘CommandType’ does not exist in the current context” or “The name ‘ParameterDirection’ does not exist in the current context” appear (note that this error may not appear until the code is added to the Page_Load procedure near the end of this article):

CWebPossibleProblemSystemData

There is a good chance that System.Data is not included in the using statement at the head of the code module.  In such a case, add the following line before the using Oracle.DataAccess.Client; line:

using System.Data;

Press the F5 key again, and the blank web page should appear without complaint.

Next, we need to switch back to the Markup view for the DatabaseQueryTest.aspx page.  In the Solution Explorer, right-click the DatabaseQueryTest.aspx file and select View Markup:

CWebViewMarkup

Between the div and /div elements below the <form id=”frmWebsiteDatabaseTest” runat=”server”> line we need to add HTML code to create the date selector region of the web page.  In this case, I used a HTML table to control the positioning of the items that should appear in the date selector region:

CWebMarkupForCalendarSelect

(WordPress decided to steal and destroy the HTML code that I posted, refer to the picture above and type the code shown – the full style entry is shown below.)

style="position:absolute; max-width:275px; max-height:400px; left:5px; top:5px; border:inset; overflow:auto; background-color:#ffffff; background-image: linear-gradient(90deg, #ffffff, #ccccdd); box-shadow: 3px 3px 12px #010101;"

 

Next, take a look at the preview of the page to see the results of adding the above code.  In the Solution Explorer, right-click DatabaseQueryTest.aspx and select View Designer:

CWebAddDateSelectionCode

The Designer should show a window similar to what appears below:

CWebAddDateSelectionCode2

The buttons appearing on the page do nothing yet, so we need to create the C# code that is associated with the buttons.  Double-click the Today button to create the generic code that is associated with that button being clicked.  When the Today button is double-clicked, a new tab named DatabaseQueryTest.aspx.cs will open next to the existing DatabaseQueryTest.aspx tab.  Switch back to the DatabaseQueryTest.aspx tab and double-click the Yesterday button to create the generic code that is associated with that button being clicked.  Repeat this process for the remaining buttons, working from left to right, top to bottom.

Below is the C# code that I created for each of the 14 buttons – copy this code into the appropriate location in the code found on the DatabaseQueryTest.aspx.cs tab (note that the Page.ClientScript.RegisterStartupScript code seems to be necessary, otherwise a date selector button will need to be clicked twice before the page is correctly updated):

        protected void cmdToday_Click(object sender, EventArgs e)
        {
            cboStartDate.Text = DateTime.Today.ToShortDateString();
            cboEndDate.Text = DateTime.Today.ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmdYesterday_Click(object sender, EventArgs e)
        {
            cboStartDate.Text = DateTime.Today.AddDays(-1).ToShortDateString();
            cboEndDate.Text = DateTime.Today.AddDays(-1).ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmdThisWeek_Click(object sender, EventArgs e)
        {
            Int32 i = 0;
            DateTime varStartDate;
            for (i = 0; i <= 6; i++)
            {
                if (DateTime.Today.AddDays(-i).DayOfWeek == DayOfWeek.Monday)
                {
                    varStartDate = DateTime.Today.AddDays(-i);
                    cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString();
                    cboEndDate.Text = varStartDate.AddDays(6).ToShortDateString();
                    break;
                }
            }
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmdLastWeek_Click(object sender, EventArgs e)
        {
            Int32 i = 0;
            DateTime varStartDate;
            for (i = 0; i <= 6; i++)
            {
                if (DateTime.Today.AddDays(-i).DayOfWeek == DayOfWeek.Monday)
                {
                    varStartDate = DateTime.Today.AddDays(-i - 7);
                    cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString();
                    cboEndDate.Text = varStartDate.AddDays(6).ToShortDateString();
                    break;
                }
            }
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmdThisMonth_Click(object sender, EventArgs e)
        {
            DateTime varStartDate;
            varStartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString();
            cboEndDate.Text = varStartDate.AddMonths(1).AddDays(-1).ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmdLastMonth_Click(object sender, EventArgs e)
        {
            DateTime varStartDate;
            varStartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1).AddMonths(-1);
            cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString();
            cboEndDate.Text = varStartDate.AddMonths(1).AddDays(-1).ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmdYeartoDate_Click(object sender, EventArgs e)
        {
            DateTime varStartDate;
            varStartDate = new DateTime(DateTime.Today.Year, 1, 1);
            cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString();
            cboEndDate.Text = DateTime.Today.ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmdLastYear_Click(object sender, EventArgs e)
        {
            DateTime varStartDate;
            varStartDate = new DateTime(DateTime.Today.Year - 1, 1, 1);
            cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString();
            cboEndDate.Text = varStartDate.AddMonths(12).AddDays(-1).ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmd7Days_Click(object sender, EventArgs e)
        {
            cboStartDate.Text = DateTime.Today.AddDays(-6).ToShortDateString();
            cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmd30Days_Click(object sender, EventArgs e)
        {
            cboStartDate.Text = DateTime.Today.AddDays(-29).ToShortDateString();
            cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmd60Days_Click(object sender, EventArgs e)
        {
            cboStartDate.Text = DateTime.Today.AddDays(-59).ToShortDateString();
            cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmd90Days_Click(object sender, EventArgs e)
        {
            cboStartDate.Text = DateTime.Today.AddDays(-89).ToShortDateString();
            cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmd120Days_Click(object sender, EventArgs e)
        {
            cboStartDate.Text = DateTime.Today.AddDays(-119).ToShortDateString();
            cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }
        protected void cmd180Days_Click(object sender, EventArgs e)
        {
            cboStartDate.Text = DateTime.Today.AddDays(-179).ToShortDateString();
            cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
            // Force a page load so that the Repeater table is updated
            Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
        }

Save All of the project, then press the F5 key to run the web page.  You should then see something similar to the below screen capture, and the buttons in the date selector region should almost work (we have not added any date entries into the DropDownList controls).  We also have not yet added the Select Employee ID section of the page:

CWebApplicationTest1

Switch back to the Markup view, and locate the /div line that follows the /table line.  We will now add the HTML code that allows selecting an employee ID, the asp:Repeater code that will handle the display of the resultset that is returned from the Oracle database, and a simple area with an instruction for the end user of the web page.  Directly below the /div line that follows the /table line add the code shown below that begins with the div style line:

CWebMarkupForEmployeeSelect

The code (almost formatted, missing the leading spaces and blank lines:

<div style="position:absolute; max-width:260px; max-height:400px; left:300px; top:5px; border:inset; overflow:auto; background-color:#ffffff; background-image: linear-gradient(90deg, #ffffff, #ccccdd); box-shadow: 3px 3px 12px #010101;">
<asp:Label ID="lblEmployeeID" Width="250px" Text="Select Employee ID" runat="server"></asp:Label>
<asp:DropDownList ID="cboEmployeeID" style="position:relative;left:5px;" Width="250px" runat="server" AutoPostBack="true"></asp:DropDownList>
</div>
<div style="position:absolute; max-width:400px; max-height:500px; left:10px; top:260px; overflow:auto; box-shadow: 3px 3px 12px #010101;">
<asp:Repeater id="tblAttendance" runat="server">
<HeaderTemplate>
<table border="1" style="border-collapse: collapse; min-width:400px;max-width:400px">
<tr style="background-color:#8080FF">
<th>Employee ID</th>
<th>Shift Date</th>
<th>Indirect Code</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr style="background-color:#ffffff">
<td><%# Eval("employee_id") %></td>
<td style="text-align:right"><%# Eval("shift_date") %></td>
<td><%# Eval("indirect_id") %></td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr style="background-color:#eeeeee">
<td><%# Eval("employee_id") %></td>
<td style="text-align:right"><%# Eval("shift_date") %></td>
<td><%# Eval("indirect_id") %></td>
</tr>
</AlternatingItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</div>
<div style="position:absolute; max-width:260px; max-height:150px; left:300px; top:200px; border:inset; overflow:auto; background-color:#ffffff; background-image: linear-gradient(90deg, #fcfc08, #ffd800); box-shadow: 3px 3px 12px #010101;">
<asp:Label ID="lblInstructions" Width="250px" Text="" runat="server"></asp:Label>
</div>

Switch back to the C# code (the DatabaseQueryTest.aspx.cs tab) and locate the Page_Load procedure, which automatically executes when the web page is rendered for the client browser.  Change the Page_Load procedure so that it includes the code shown below (change MyUserName, MyPassword, and MyDBName to appropriate values to permit a connection to your database:

        protected void Page_Load(object sender, EventArgs e)
        {
            String strSQL = "";
            String strUserName = "";
            String strPassword = "";
            String strConnection = "";
            Int32 i = 0;
            // Connection Pooling article: http://www.oracle.com/technetwork/issue-archive/2006/06-jul/o46odp-097508.html
            //   connection pooling documentation: https://docs.oracle.com/cd/E11882_01/win.112/e23174/featConnecting.htm#ODPNT169
            //   connection pooling is enabled by default, may be turned off by specifying Pooling=false in the strConnection variable
            
            // Connect to the database - connection pooling is used to minimize the number of actual new database connections required
            strUserName = "MyUserName";
            strPassword = "MyPassword";
            strConnection = "Data Source=MyDBName;User Id=" + strUserName + ";Password=" + strPassword +";";
            strConnection = strConnection + "Pooling=true;Min Pool Size=10;Max Pool Size=50;Decr Pool Size=2;Connection Lifetime=1200;Connection Timeout=60;";
            try
            {
                dbDatabase = new OracleConnection(strConnection);
                dbDatabase.Open();
                blConnected = true;
            }
            catch
            {
                blConnected = false;
            }
            if (!Page.IsPostBack)
            {
                if (blConnected == true)
                {
                    lblInstructions.Text = "Select a date range and/or an employee ID from the list.";
                    strSQL = "SELECT DISTINCT" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ID" + "\n";
                    strSQL = strSQL + "FROM" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ATTEND_TEST" + "\n";
                    strSQL = strSQL + "ORDER BY" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ID";
                    comEmployeeID = new OracleCommand(strSQL, dbDatabase);
                    comEmployeeID.CommandType = CommandType.Text;
                    comEmployeeID.CommandTimeout = 30;
                    comEmployeeID.BindByName = false;

                    // Need to start at 30 days in the future so that cmdThisMonth does not generate an error
                    for (i = -30; i <= 1000; i++)
                    {
                        cboStartDate.Items.Add(DateTime.Today.AddDays(-i).ToShortDateString());
                        cboEndDate.Items.Add(DateTime.Today.AddDays(-i).ToShortDateString());
                    }
                    cboStartDate.Text = DateTime.Today.ToShortDateString();
                    cboEndDate.Text = DateTime.Today.ToShortDateString();
                    // Retrieve the employee ID list
                    cboEmployeeID.Items.Add("");
                    snpEmployeeID = comEmployeeID.ExecuteReader();
                    if (!snpEmployeeID.IsClosed)
                    {
                        while (snpEmployeeID.Read())
                        {
                            cboEmployeeID.Items.Add(snpEmployeeID["employee_id"].ToString());
                        }
                        snpEmployeeID.Close();
                    }
                }
            }
            else
            {
                // Not the first time the page is displayed
                if (blConnected == true)
                {
                    strSQL = "SELECT" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ID," + "\n";
                    strSQL = strSQL + "  SHIFT_DATE," + "\n";
                    strSQL = strSQL + "  INDIRECT_ID" + "\n";
                    strSQL = strSQL + "FROM" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ATTEND_TEST" + "\n";
                    strSQL = strSQL + "WHERE" + "\n";
                    strSQL = strSQL + "  SHIFT_DATE BETWEEN :1 AND :2" + "\n";
                    strSQL = strSQL + "ORDER BY" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ID," + "\n";
                    strSQL = strSQL + "  SHIFT_DATE";
                    comAttendanceSD = new OracleCommand(strSQL, dbDatabase);
                    comAttendanceSD.CommandType = CommandType.Text;
                    comAttendanceSD.CommandTimeout = 30;
                    comAttendanceSD.BindByName = false;
                    comAttendanceSD.Parameters.Add("start_date", OracleDbType.Date, 8, "", ParameterDirection.Input);
                    comAttendanceSD.Parameters.Add("end_date", OracleDbType.Date, 8, "", ParameterDirection.Input);
                    strSQL = "SELECT" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ID," + "\n";
                    strSQL = strSQL + "  SHIFT_DATE," + "\n";
                    strSQL = strSQL + "  INDIRECT_ID" + "\n";
                    strSQL = strSQL + "FROM" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ATTEND_TEST" + "\n";
                    strSQL = strSQL + "WHERE" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ID = :1" + "\n";
                    strSQL = strSQL + "ORDER BY" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ID," + "\n";
                    strSQL = strSQL + "  SHIFT_DATE";
                    comAttendanceEmp = new OracleCommand(strSQL, dbDatabase);
                    comAttendanceEmp.CommandType = CommandType.Text;
                    comAttendanceEmp.CommandTimeout = 30;
                    comAttendanceEmp.BindByName = false;
                    comAttendanceEmp.Parameters.Add("employee_id", OracleDbType.Varchar2, 15, "", ParameterDirection.Input);
                    strSQL = "SELECT" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ID," + "\n";
                    strSQL = strSQL + "  SHIFT_DATE," + "\n";
                    strSQL = strSQL + "  INDIRECT_ID" + "\n";
                    strSQL = strSQL + "FROM" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ATTEND_TEST" + "\n";
                    strSQL = strSQL + "WHERE" + "\n";
                    strSQL = strSQL + "  SHIFT_DATE BETWEEN :1 AND :2" + "\n";
                    strSQL = strSQL + "  AND EMPLOYEE_ID = :3" + "\n";
                    strSQL = strSQL + "ORDER BY" + "\n";
                    strSQL = strSQL + "  EMPLOYEE_ID," + "\n";
                    strSQL = strSQL + "  SHIFT_DATE";
                    comAttendanceSDEmp = new OracleCommand(strSQL, dbDatabase);
                    comAttendanceSDEmp.CommandType = CommandType.Text;
                    comAttendanceSDEmp.CommandTimeout = 30;
                    comAttendanceSDEmp.BindByName = false;
                    comAttendanceSDEmp.Parameters.Add("start_date", OracleDbType.Date, 8, "", ParameterDirection.Input);
                    comAttendanceSDEmp.Parameters.Add("end_date", OracleDbType.Date, 8, "", ParameterDirection.Input);
                    comAttendanceSDEmp.Parameters.Add("employee_id", OracleDbType.Varchar2, 15, "", ParameterDirection.Input);
                    if (cboEmployeeID.Text != "")
                    {
                        if (cboStartDate.Text != "")
                        {
                            // Employee ID and Start Date are specified
                            comAttendanceSDEmp.Parameters["start_date"].Value = DateTime.ParseExact(cboStartDate.Text, "d", ifpCulture);
                            comAttendanceSDEmp.Parameters["end_date"].Value = DateTime.ParseExact(cboEndDate.Text, "d", ifpCulture);
                            comAttendanceSDEmp.Parameters["employee_id"].Value = cboEmployeeID.Text;
                            snpAttendance = comAttendanceSDEmp.ExecuteReader();
                            snpAttendance.FetchSize = comAttendanceSDEmp.RowSize * 100;
                        }
                        else
                        {
                            // Employee ID is specified, Start Date is not specified
                            comAttendanceEmp.Parameters["employee_id"].Value = cboEmployeeID.Text;
                            snpAttendance = comAttendanceEmp.ExecuteReader();
                            snpAttendance.FetchSize = comAttendanceEmp.RowSize * 100;
                        }
                    }
                    else
                    {
                        // Employee ID is not specified, assume that the Start Date must be specified
                        comAttendanceSD.Parameters["start_date"].Value = DateTime.ParseExact(cboStartDate.Text, "d", ifpCulture);
                        comAttendanceSD.Parameters["end_date"].Value = DateTime.ParseExact(cboEndDate.Text, "d", ifpCulture);
                        snpAttendance = comAttendanceSD.ExecuteReader();
                        snpAttendance.FetchSize = comAttendanceSD.RowSize * 100;
                    }
                    if (!snpAttendance.IsClosed)
                    {
                        
                        
                        tblAttendance.DataSource = snpAttendance;
                        tblAttendance.DataBind();
                        // Web page does not show the correct information until the next postback?
                        
                        snpAttendance.Close();
                        
                    }
                
                }
            }
            if (blConnected == true)
            {
                // Disconnect from the database, because connection pooling is enabled, the connection might not actually close immediately
                try
                {
                    dbDatabase.Close();
                }
                catch { }
                blConnected = false;
            }
        }

Save All of the project, then press the F5 key to run the web page.  If the programming code is correct and complete, it should now be possible to select an Employee ID, click a button to select a date range, and then see a HTML rendered table in a scrollable viewport as shown below:

CWebPageInAction

Note that while developing this article I had a couple of issues where the iisexpress.exe webserver stopped responding, resulting in a completely blank page as shown below, with the never ending message “Waiting for localhost” appearing:

CWebServerEventuallyNotReponding

There probably is a good method to recover from this problem, but as of yet I have not found that method.  If you encounter this problem, find the IIS Express icon in the task tray, right-click that icon, and select Exit.  I found that I then had to close and reopen the project in Visual Studio Express.

CWebServerEventuallyNotReponding2

I will likely add additional information to this article in the next couple of days – it appears that I may have a way to post the HTML code that was removed by WordPress, and I will try to add additional explanation for the reasoning behind the C# code in the Page_Load procedure.

What is the challenge?  How might you improve this sample project to do something interesting with the project?





Oracle DBA, IT Manager, or Something Else

14 02 2016

February 14, 2016

In September 2014 I posted an article on this blog where I mentioned that I am the IT Manager and an Oracle Database administrator at a company that specializes in welding and machining very large metal parts – in the article I attempted a bit of humor while trying to explain a few items.  I have a small confession to make, I have been the only IT person at the company for the last 38 months.  As you might imagine, working 11+ hour days for six years (yes, six years is longer than 38 months) was nothing short of a never ending fun experience, and sometimes seemingly without an ending.

In the last month I put together a somewhat interesting document.  As some readers of this blog probably know, I have read a few books about Oracle Database, programming, Microsoft Exchange, server administration, and a few other topics (my personal technical book library six years ago), and have published a few articles about some of those topics on this blog.  My Oracle Database knowledge is reasonably good, I suppose (after probably reading roughly 30 books on the subject and contributing to another), but that is only one piece of a complex jigsaw puzzle of knowledge in the IT field.  So, what is the interesting document that I created as I neared by 16th anniversary with the company?
What are the skills required for the job of IT Manager and Oracle DBA, or what exactly does my job encompass (since it is easy to assume that I am just the person that hands out the printer ink cartridges when the change me light flashes 🙂 ).  I am not yet sure what exactly the interesting document will be used for, but it might be used for ISO documentation purposes.  I split the document into two sections: skills for the person who is able to sit upright in a chair, successfully breathe at regular intervals, and occasionally blink once for no, or blink twice for yes (Essential Skills); skills in addition to the essential skills that are actually needed for the person with the role of IT Manager and Oracle DBA at the company (Additional Skills).

Essential Skills:

  • Database: Oracle Database 11.2 management: emphasis on RMAN backup, RMAN recovery, RMAN cloning, Oracle specific SQL syntax including analytics (experience with Oracle Database 10.2, 11.1, or 12.1 should be sufficient)
  • Email: Microsoft Exchange Server 2013 management (Exchange Server 2007 or 2010 experience is helpful, but not sufficient unless the person is willing to read a couple of books)
  • Programming: Visual Basic 6.0 (MRP for Visual 6.5.2, touchscreen Windows 8.1 tablet projects, and many other custom programs were written in this language), VB.Net, C# (MRP for Visual 8.0.0 was written in C#), and Zebra ZPL (used for creating part labels for receiving, shipping, and moving)
  • ERP: Visual Manufacturing 6.5.2 support
  • Phone system: Mitel 5000 with digital, analog, VOIP circuits, paging, and fax reception (note that the Mitel 3000 series is significantly different, while the old Inter-Tel phone system shares similar programming configuration options). Person should know how to change phone programming to change names, reset passwords, reset voicemail boxes, punch down digital and analog phone circuits, proper phone system shut down procedure.
  • Barcode: Previously worked with or created code 3 of 9 barcodes, code 128 barcodes, and UPC barcodes. Experience with Symbol, Motorola, or Zebra mobile computers (primarily MC3090 and MC3190).
  • Microsoft Office: Experience with Microsoft Office versions 2007 and 2010 (experience with versions 2013 and 2016 recommended for future upgrades)
  • Server operating systems: Windows Server 2012, Windows Server 2008 R2, Windows Server 2003 R2, Red Hat Linux, Fedora Core 22 Linux, BusyBox v1.16 (Synology NAS)
  • Desktop operating systems: Windows 10 Pro (for future rollout), Windows 8.1 Pro (tablets only), Windows 7 Pro (32 bit and 64 bit), Windows Vista Business (32 bit), Windows XP Pro (primarily shop floor computers, but also Burn Shop supervisor and UT inspector)
  • Printer support: Basic maintenance and troubleshooting of various Dell, Zebra, Canon, Ricoh/Lanier/Gestetner (advanced maintenance covered by printer repair company) printers, and Xerox (advanced maintenance covered by printer repair company) large format plotter/scanner.
  • Wiring: Cat 5e and Cat 6 network cable wiring and termination, serial cable wiring, phone wiring into model 66 blocks, fiber optic connections with pre-made cable.
  • Help Desk: Capable of translating complex computer terminology into common English, able to walk end users through procedures over the phone and in person, emphasis on customer service (speed of response and problem resolution).
  • Firewall and VPN: Familiar with SonicWall firewall and VPN configuration.
  • EDI: Experience with TrustedLink EDI and common EDI documents (810, 830, 850, 856, etc.).
  • Network: Experience managing gigabit network switches, wireless access points, and VLANs.

Additional Skills:

  • Database: Person has extensive Oracle performance tuning experience that is relevant to databases without the Tuning or Diagnostic packs. Person has achieved Oracle Certified Master status (Oracle Certified Professional is a very light-weight certification by comparison), person is a member of the OakTable Network, or person is an Oracle Ace Director.
  • Email: Person planned and executed an upgrade from Microsoft Exchange 5.5 to Exchange 2007 or later, or Exchange 2007 to 2013 or 2016). Person has experience with spam filtering products such as SpamStopsHere or MessageLabs. Person has written programs to submit email using an SMTP interface.  Experience working with self-signed security certificates and third party certificates.
  • Programming: Experience writing Microsoft Office macros (Excel, Word, Power Point, Outlook, Access), experience writing VBScript (Visual Manufacturing macro language, AutoCAD macro language), experience writing GCODE (used by CNC machines), experience writing PowerShell scripts (used by Microsoft Exchange 2007 and later), experience writing HTML (used by Web Reports, Report Server, K&M website), experience writing Javascript and PHP (used by Web Reports, K&M website), experience writing Linux and DOS batch scripts (used by K&M website and backup software), experience creating ASP and ASP.Net websites (Web Reports), experience maintaining programs created by other software developers
  • ERP: Experience upgrading Visual Manufacturing, experience utilizing Infor’s COM Object and/or Infor’s API Toolkit (for programmatically creating transactions in Visual), experience with the underlying database object model used by Visual Manufacturing (knowledge of specific tables, columns, indexes, security model). Experience filing support requests with Infor.
  • Phone system: Experience with T1 DCS and T1 PRI circuits (type of T1 circuit currently used at K&M), extensive programming and diagnostic experience with a Mitel 5000 PBX including troubleshooting of offsite voice over IP phones that traverse firewalls. Experience creating and/or maintaining SMDR call logging through TCP/IP and/or serial connection.
  • Barcode: Experience troubleshooting Zebra barcode printers; experience troubleshooting barcode scanning problems; able to describe how code 3 of 9 barcodes are encoded and write a computer program to locate and decode a code 3 of 9 barcode on a scanned page; experience programming and backing up Symbol, Motorola, or Zebra mobile computers (primarily MC3090 and MC3190)
  • Common commercial programs: Able to troubleshoot, install, and configure license managers for a variety of primarily engineering focused programs. Programs include AutoCAD 2014, PTC Creo Parametric, MasterCAM, PC-DMIS, Vericut, Verisurf, Shadow Protect, Trend Micro Enterprise Security Suite, Sage Fixed Assets, TrustedLink EDI, Microsoft Office, Adobe Acrobat Standard Edition, Visual Manufacturing, Oracle Database
  • Servers: Extensive experience administering and configuring Windows servers; experience with group policy in Active Directory; experience with a variety of Linux based systems (primarily Red Hat derived); experience with Synology NAS units (configuring as domain members, network time sources, Windows SMB shares, FTP targets, Nagios network monitoring); experience with WordPress configuration, utilization and security (for the K&M website); experience with SSH (TelNet, TightVNC, Putty, Remote Desktop, GoToMyPC); experience specifying server hardware configurations (including RAM, RAID levels, operating systems, redundancy); experience diagnosing and repairing Dell server hardware. Experience maintaining four hour support contracts for servers. Experience configuring Fail2Ban, and software firewalls (iptables, firewalld, Windows firewall service).
  • Desktops: Extensive experience troubleshooting hardware problems (replace bad power supply, identify faulty motherboard, upgrade memory, replace other components including hard drives and DVD drives). Troubleshoot attached devices including check scanners, postage meters, printers, mice/keyboards, wedge barcode scanners. Clean computers of spyware and viruses with and without the assistance of virus/spyware scanning software (educate users about virus warning signs to avoid infection).
  • Printer support: Program printers for network connectivity, and install as network managed printers controlled by Windows Server 2012. Perform diagnostics and repair of printers, copiers, and all-in-one devices that are not under warranty (such as replacing the print head in Zebra printers or installing maintenance kit parts). Manage printer ink and toner supplies with predictive re-ordering of supplies.
  • Wiring: Understand when fiber optic and/or shielded cable must be used rather than typical Cat 5e or Cat 6 cable, with the ability to terminate fiber optic cables. Experience wiring and troubleshooting paging amplifiers, page delay units, and speakers.
  • Help Desk: Experience converting vague support requests into actionable assistance. Capable of providing precise and fluid written and verbal responses. Able to proactively train fellow employees. Able to write clear documentation for procedures and custom computer programs.
  • Firewall and VPN: Experience configuring and maintaining SonicWall NSA series firewalls: redundant Internet routes with fail-over, multiple static IP addresses per port, firewall log monitoring (to identify attempted hacking attacks and Internet stability issues), opening and restricting ports or IP addresses, etc. Experience configuring and maintaining SonicWall SSL VPN servers: authentication through Active Directory attached RADIUS server, creating client-side connection and disconnection scripts, establishing remote connection policies through Active Directory, troubleshooting remote connectivity of computers and IP phones.
  • EDI: Experience using Trusted Link EDI software to communicate with remote EDI servers using the SFTP protocol and WinSCP scripts. Experience creating and maintaining export and import functionality between Visual Manufacturing and the Trusted Link EDI software using map flat files, raw EDI, and custom developed software that reads and creates transactions in Visual Manufacturing. Experience scripting in Trusted Link EDI. Experience programmatically printing EDI documents to PDF files (using BlackIce print driver software) with filenames that are derived from the contents of the EDI printed documents.
  • Network: Experience diagnosing and resolving connectivity issues using Wireshark (or similar packet capture tool), experience with various command line diagnostic utilities (nslookup, netstat, nbtstat, dig, traceroute). Experience recommending and configuring core and remote network switches, as well as troubleshooting managed and unmanaged network switches and routers (HP, Dell, and Cisco-Linksys).  Experience ordering, configuring, and supporting Internet access delivered through fiber optic, cable TV providers, DSL providers, and wireless providers. Experience working with multimode fiber optic with GBIC and stand-alone fiber to RJ45 converters. Experience working with the Nagios network monitoring utility (compiled to run on Synology NAS units).
  • Inter-departmental support: Able to assist engineering with reconfiguring Fanuc and Tosnic CNC controls, and troubleshoot serial cable connections to support GCODE program transfers with CNC machines. Able to understand basic procedures of Quality, Engineering, Purchasing, and Accounting to facilitate process and efficiency improvements.

No, my job position does not require that I be an expert at everything that I touch, so long as I never make a mistake at anything that I touch…  ORA-00060: Deadlock Detected.





See Sharp Objects, Run with Sharp Objects, Crash with Sharp Objects – Obscure and Incorrect Error Messages in Commercial Products

15 01 2016

January 15, 2016

Since October 2015 I have been fighting with an application programming interface (API) that works with an enterprise resource planning (ERP) system.  The API, with uses Microsoft .Net 4.0 or higher, is intended to simplify the process of automating the creation of various types of documents within the ERP system.  For example, the API could be used to quickly create 1,000 new part numbers in the ERP system, using an Excel spreadsheet as the source data (a custom program would need to read the Excel spreadsheet and plug that information into the formatting syntax expected by the API).  The API could also be used for creating work orders in the ERP system which would then be used to produce the parts, receive purchased parts into inventory, issue parts in inventory to work orders, receive completed parts into inventory, create shipping documents to send the parts to customers, generate invoice records to accept payment from customers, and a variety of other types of documents.  I have been using Microsoft C# 2015 to interface with the API, and the ERP company helpfully includes a standalone executable (.EXE) that is able to test the API to make certain that all is working well with the API communication to the Oracle database through the Oracle Managed Data Provider through .Net.  This API replaces an older API that exposed a COM interface, which I previously used extensively to automate the creation of various types of documents.

Why have I been fighting with the application programming interface for this ERP system?  To err is human, but two errors does not compute.  While trying to determine how to use the API, I copied a code sample from the API documentation into a new project in Microsoft C# 2015.  The documentation indicates that the methods for using the API are similar to those that I used in the older API that exposed a COM interface, so it appeared that the process would be relatively easy transition to the new API.  That was my thought, any way, until I attempted to run the program.

SeeSharp2FailedEnableConstraintsC

That cannot be good.  I suppose that it is possible that the database schema has non-enabled constraints, as it had just gone through a conversion from an earlier version of the ERP system to the latest version a couple of days prior.  Let’s check:

SELECT
  CONSTRAINT_NAME,
  CONSTRAINT_TYPE,
  TABLE_NAME,
  STATUS
FROM
  DBA_CONSTRAINTS
WHERE
  OWNER='SYSADM'
  AND STATUS<>'ENABLED';
 
no rows selected

I guess that is not the cause of the error.  Let’s take the code out of a try block, and run the program again (click the picture for a larger view):

SeeSharp2FailedEnableConstraintsCExcept

A System.Data.ConstraintException in LsaCore.dll, Failed to enable constraints.  One or more rows contain values violating non-null, unique, or foreign-key constraints error.  Ouch, what did I do wrong?  I was just trying to create a new part number in the database, and the exception was thrown before any part specifications were provided.  Maybe it is because I am trying to develop on a 64 bit computer, or maybe there is some missing Windows registry entry that I still need to create?

Let’s try the standalone executable (.EXE) that the ERP provider distributes to test the API – on a 32 bit computer that was used for a test deployment of the new version of the ERP system.  This computer was an otherwise fresh install of Windows 7 with all security updates installed prior to the deployment of the new version of the ERP system (click the picture for a larger view):

SeeSharp2FailedEnableConstraintsAPITest

That “Failed to enable constraints.  One or more rows contain values violating non-null, unique, or foreign-key constraints.” error message is a bit too familiar at this point.  I need a 10046 trace at level 12 to see what might be triggering the constraints error.  Logging into the database as the SYS user using SQL*Plus, I created an after logon trigger to enable a 10046 trace for my C# test program as well as the API test tool provided with the ERP system:

CREATE OR REPLACE TRIGGER LOGON_CAPTURE_10046 AFTER LOGON ON DATABASE
DECLARE
  SHOULD_EXECUTE INTEGER;
  TRACEFILE VARCHAR2(150);
BEGIN
  SELECT DECODE(SUBSTR(UPPER(PROGRAM),1,5),'VISUA',1,0)+DECODE(SUBSTR(UPPER(PROGRAM),1,5),'VMFGC',1,0) INTO SHOULD_EXECUTE FROM V$SESSION WHERE SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1);
  IF SHOULD_EXECUTE > 0 THEN
    TRACEFILE := 'ALTER SESSION SET TRACEFILE_IDENTIFIER = '''||USER||TO_CHAR(SYSDATE,'YYMMDDHH24MI')||'''';
    EXECUTE IMMEDIATE TRACEFILE;
    EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED';
    EXECUTE IMMEDIATE 'ALTER SESSION SET TIMED_STATISTICS=TRUE';
    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
  END IF;
END;
/

Repeating the test with my C# test program, I found that a few 10046 trace files were created on the database server each time I ran the C# test program.  One of the 10046 trace files included:

PARSE ERROR #425887032:len=42 dep=0 uid=159 oct=3 lid=159 tim=2402951856734 err=904
SELECT DB_VERSION FROM APPLICATION_GLOBAL
WAIT #425887032: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=2402951856800
WAIT #425887032: nam='SQL*Net break/reset to client' ela= 559 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=2402951857369
WAIT #425887032: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2402951857387

That is interesting.  There is no column named DB_VERSION in the APPLICATION_GLOBAL table, but there is a column named DBVERSION.  Maybe that is the cause of the Failed to enable constraints error?  After double-checking the table definition, the column is in fact supposed to be named DBVERSION, not DB_VERSION, so maybe the programmer that created the API DLLs made a typo?

Taking a look at another trace file, I found another potential problem:

PARSE ERROR #415492776:len=45 dep=0 uid=159 oct=3 lid=159 tim=2402951781868 err=942
SELECT DB_VERSION FROM VQ_APPLICATION_GLOBAL
WAIT #415492776: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=2402951781960
WAIT #415492776: nam='SQL*Net break/reset to client' ela= 4892 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=2402951786862
WAIT #415492776: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2402951786911

There is no table in the database named VQ_APPLICATION_GLOBAL, and per the table creation script for the ERP system, that table probably should not exist.  Maybe the programmer that created the API DLLs made another typo?  Creating a virtual DB_VERSION column to see if there is any improvement:

ALTER TABLE APPLICATION_GLOBAL ADD (DB_VERSION NVARCHAR2(10) GENERATED ALWAYS AS (TRIM(DBVERSION)));

Well, that took care of the Failed to enable constraints error, but now I have a new problem when I run the C# test program:

SeeSharp2DataspaceNameVMFGC

What does “Dataspace name VMFG not found” mean?  Is that an improvement over the Failed to enable constraints error?   Let’s give the standalone executable (.EXE) that the ERP provider distributes to test the API a go on the 32 bit computer (click the picture for a larger view):

SeeSharp2DataspaceNameVMFGAPITest

“Dataspace name VMFG not found” – at least there is some level of consistency.

Switching back to the C# test program, I decided to enable a bit more of the options in the Exception Settings window (click the picture for a larger view):

SeeSharp2DataspaceNameVMFGCExcDetail

So, the “Dataspace name VMFG not found” error was thrown in the LsaDataLogic.TableDefinition.LoadTableDefinitions procedure in LsaCore.  That certainly is specific.  But, where is “VMFG” coming from, that is not the name of the database that I am trying to work with, although that is the name of a database that is frequently configured for this ERP system.  For fun I opened one of the API DLL files using Wordpad (click the picture for a larger view):

SeeSharpObjects2VMFGInDLLSource

I see VMFG. prefixing what appears to be every table that is in a SELECT type SQL statement within the DLL.  After checking with one of the most experienced ERP support people at the ERP company, the “Dataspace name VMFG not found” error was a complete mystery.  No one in support had ever seen that particular error message.  The “Failed to enable constraints.  One or more rows contain values violating non-null, unique, or foreign-key constraints.” error that I saw earlier was also a complete mystery at that line in the C# test code.

Taking another look at the multiple 10046 trace files that were created before I tried creating the virtual DB_VERSION column, I found that the API was actually sending at least four invalid SQL statements to the database:

SELECT DB_VERSION FROM APPLICATION_GLOBAL
 
SELECT DB_VERSION FROM VQ_APPLICATION_GLOBAL
 
SELECT DBVERSION , SITE_ID , COMPANY_MANAGER FROM APPLICATION_GLOBAL
 
SELECT APP_VERSION , PAY_HELP , HR_HELP FROM APPLICATION

The API was also using two different techniques to identify the name of the primary key column for a table – that table does not, and should not have a primary key per the table creation script for the ERP system:

SELECT COLS.COLUMN_NAME AS COLNAME , COLS.POSITION AS PKCOLSEQNUM FROM ALL_CONSTRAINTS CONS , ALL_CONS_COLUMNS COLS WHERE COLS.TABLE_NAME = 'APPLICATION_GLOBAL' AND CONS.CONSTRAINT_TYPE = 'P' AND CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME AND CONS.OWNER = COLS.OWNER ORDER BY COLS.TABLE_NAME , COLS.POSITION
 
SELECT I.INDEX_NAME AS INDEX_NAME FROM USER_INDEXES I WHERE I.TABLE_NAME = 'APPLICATION_GLOBAL' AND I.TABLE_OWNER = 'SYSADM' AND I.UNIQUENESS = 'UNIQUE' ORDER BY I.INDEX_NAME

Wow!  I asked the experienced ERP support person if any of the above makes sense.  I am not sure if I ever received a response to that question.

So, I enabled tracing for the Oracle Managed Data Provider for .Net driver used by the API with a modification to the MACHINE.CONFIG file found in the folder C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config.  Just before the end of the configSections section of the file (about ½ way through the file – just before the line </configSections>) I added the following line:

    <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess" />

Next, just before the </configuration> line at the end of the file I added the following lines (specify the correct location of the tnsname.ora file on the tns_admin line):

  <oracle.manageddataaccess.client>
    <version number="*">
       <settings>
          <setting name="tns_admin" value="C:\Oracle\product\11.2.0\client_1\network\admin\" />
          <setting name="TraceOption" value="1" />
          <setting name="TraceLevel" value="127" />
          <setting name="TraceFileLocation" value="C:\Trace\" />
       </settings>
    </version>
  </oracle.manageddataaccess.client>

I then created a folder named Trace in the root of the C: drive (note that if UAC (User Access Control) is enabled, it may be necessary to grant everyone full permissions to that folder if it is located in the root of the C: drive).  The generated trace files initially did not seem to provide any additional insight into the problem beyond what was found in the 10046 trace files.

Earlier this week the experienced ERP support person arranged for an cross-continent video conference call with several other people at the ERP company to help identify the source of the problems that I experienced with the API for the ERP system.  That video conference call lasted a bit over two and a half hours, with no solution, but the conference call included a demonstration that the API for the ERP system does work with the sample database that is used by the ERP company.  The support person even went so far as to record and send to me a Process Monitor trace of the successful test execution, and he also enabled a 10046 trace of the successful test execution.  We only spent a couple of minutes scanning through the 10046 trace files during the call.  Oddly enough, during the successful test execution, I noticed that the following two SQL statements appeared in his 10046 trace files with parse errors:

SELECT DB_VERSION FROM APPLICATION_GLOBAL
  
SELECT DB_VERSION FROM VQ_APPLICATION_GLOBAL

Interesting, and when I pointed out those two parse errors (again, along with the other two that I mentioned earlier) to the people on the video conference call, no one knew why those SQL statements were attempted to be executed.  Along with the Process Monitor trace, the senior support person sent a copy of their sample database to me, exported using Datapump, so that I could try to understand just what was corrupt on the essentially barebones Windows 7 32 bit computer.

After finding that the ERP company’s sample database worked fine with my essentially barebones Windows 7 32 bit computer, I made a somewhat important conclusion.  The “Dataspace name VMFG not found” error is bad news, essentially it means that the API does not think that it is accessing a Visual Manufacturing database.  The DB_VERSION queries against the APPLICATION_GLOBAL table and VQ_APPLICATION_GLOBAL are apparently supposed to fail!  But why could someone on that 2.5+ hour cross-continent video conference call not come to the same conclusion?  I know that two of the people on the conference call have been heavily involved with the ERP system development for well over 15 years, so the people are undoubtedly very knowledgeable about the subject.

After dropping the virtual column that I created, I then repeated the C# test program’s tests with the two databases.  Comparing the trace files for my company’s database and the ERP company’s sample database, I found a difference (see the picture below – click for a larger view).

SeeSharp2TraceCompare

Ah, I found a difference.  With the sample database, after the API’s DLL performed an OracleDataReader.Read() call, it executed an OracleDataReader.NextResult() call.  With my company’s database, after the OracleDataReader.Read() call, the API’s DLL performed an OracleDataReader.Fill() call followed by an OracleDataReader.Close() call.  Buy why?  Here is the SQL statement that appeared in the trace file just prior to the highlighted line:

SELECT ACTIVATION_KEY , ACTIVITY_UDF_LABELS , ALLOW_EMAIL_DOC , AUDIT_REPORT_TIMES , BARCODE_MULTI_JOB ,
 COMPANY_ADDR_1 , COMPANY_ADDR_2 , COMPANY_ADDR_3 , COMPANY_CITY , COMPANY_COUNTRY , COMPANY_NAME ,
 COMPANY_STATE , COMPANY_ZIPCODE , CONFIGURATION_KEY , CUST_PRICE_EFF_REQ , CUST_UDF_LABELS , DBVERSION ,
 DEF_OLDEST_INV_TYPE , DOC_UDF_LABELS , DOCUMENT_DIRECTORY , DRAWING_FILE_PATH , DRAWING_LOCATOR ,
 DRAWING_VIEWER , EURO_CURRENCY_ID , FILTER_AVAIL_TRACE , ID , INSTALL_DATE , LAST_CONVERT_DATE ,
 MAXIMUM_USERS , MFG_INTERFACE_USED , PART_UDF_LABELS , PLM_ENABLED , PLM_URL , PROGRAM_PATCH_LVL ,
 PROJ_UDF_LABELS , PURC_QUOTE_TYPE , SD_SUBGROUP_MIN , SERIAL_NO , SESSION_TIMELIMIT , SHOP_UDF_LABELS ,
 SQLBASE_DATABASE_VERSION , SSO_ENABLED , STORE_MACROS_IN_DB , TABLE_PATCH_LVL , TRIGGER_PATCH_LVL ,
 VALIDATE_LOOKUPS , VEND_UDF_LABELS , VISUAL_USER_GROUP , VQ_DBNAME , VQ_DIRECTORY , VQ_ENABLED ,
 VQ_QUERY_USE , VR_UPDATES , WFL_CMNT_PWD_REQ , WO_PRIORITY_PATH , XBAR_SUBGROUP_MIN 
FROM APPLICATION_GLOBAL APPLICATION_GLOBAL 
ORDER BY ID

The Oracle Managed Data Provider’s OracleCommand object for that SQL statement, when I executed it in another C# test program, indicated that the expected row length is 10,238 bytes.  A problem? After trying a couple of changes in my database’s APPLICATION_GLOBAL table, I noticed that the PART_UDF_LABELS, VEND_UDF_LABELS, and CUST_UDF_LABELS columns in the ERP company’s sample database were all set to NULL.  Below is the contents of the PART_UDF_LABELS column in my company’s APPLICATION_GLOBAL table:

 "% of Plate","Heat Code/Material","Customer ID","Gross Weight","Vendor","Price Each","Surcharge","Freight","EAU (Cpy to Planning Tab)","FAB/COMB"

What, a difference between a sample database and a production database (one with 20+ years of transaction history)?  🙂

I set the PART_UDF_LABELS, VEND_UDF_LABELS, and CUST_UDF_LABELS columns to all be NULL, as they are in the sample database.  All tests in API test program were successful with my company’s database with all three of those columns set to NULL.  For sake of completeness, I then copied only the original PART_UDF_LABELS column value from my company’s database to the sample database, and managed to cause the API test program to fail with the sample database.  Interesting…

I reported my findings to the senior support person at the ERP company.  While waiting for a follow up from him (he needs to be able to reproduce my results), I performed some additional testing.  The PART_UDF_LABELS, VEND_UDF_LABELS, and CUST_UDF_LABELS columns, as well as several other columns in that table, are defined as NVARCHAR2(250) data types, allowing up to 250 characters to be stored per row.  The PLM_URL column in that table is defined as NVARCHAR2(2000), allowing up to 2,000 characters to be stored per row.  I found that by putting the following value into the PART_UDF_LABELS column, the ERP company’s API test program would complete successfully:

"% of Plate","Heat Code/Material","Customer ID","Gross Weight"

Adding back a few more characters to that column value caused the ERP company’s API test program to fail the tests:

"% of Plate","Heat Code/Material","Customer ID","Gross Weight","Vendor"

I found that if the PART_UDF_LABELS, VEND_UDF_LABELS, and CUST_UDF_LABELS columns contained up to 64 characters, the ERP company’s API test program completed the tests successfully.  65 characters caused the API test program to fail the tests.  But, what about that column (PLM_URL) that permits up to 2,000 characters to be stored, surely that column cannot have a 64 character limit for companies that need to automate document creation through the ERP company’s API?  Yep, more than 64 characters in that column will also cause the API tests to fail.

To err is human, but two errors does not compute (or was it three errors identified in this article).





See Sharp Objects, Run with Sharp Objects, Crash with Sharp Objects – Nothing but .Net, C#, and ODP.NET – 1

5 12 2015

December 5, 2015

I have been writing computer programs for a few years – the first more than 30 years ago, and have written programs in a variety of languages including C++, Cobol, Pascal, BASIC, Visual Basic, eVB, a couple of scripting languages (including JavaScript and VBScript), and a few others that are long forgotten (I could read some Fortran, but never had a need for the language).  I used Visual Basic for DOS years ago, and after toying with Visual Basic 2.0 for an hour, I wondered about the purpose of that “toy”, but interesting, programming language that allowed me to draw text boxes and drop-down lists on a form.  I bought and read cover to cover a number of books that described methods to utilize the quick development capabilities of Visual Basic to draw a user interface, and then jump out of the limited safety net of Visual Basic to harness the functionality provided by direct access to the Windows API.  The classic Visual Basic 6.0 is my favorite language so far, allowing for quick development, fast performance, and its ability to produce programs that are compatible with every Windows version from Windows NT 4.0 (and Windows 95) to Windows 10.

In my opinion, Microsoft mutated the clean, quick development programming language in Visual Basic 6.0 with the release of VB.Net.  A decade ago I had to write a program that would run on a Symbol MC3090 handheld computer with built-in barcode scanner (Symbol was later acquired by Motorola, part of Motorola’s operations were acquired by Google, and apparently Zebra is the current owner of this technology).  The MC3090 supported programs that were written in the .Net languages, such as VB.Net and C#, but not the eVB language (language was similar to VB 6.0) that was supported on the earlier Symbol PDT8146.  After a bit of head scratching, I wrote the program in VB.Net 2005.  Not only did the VB.Net solution work, it was also significantly more stable than the eVB developed solution on the PDT8146 (the hardware change may be the primary reason for the change in stability).  I still found VB.Net’s language to be cumbersome to use compared to Visual Basic 6.0 (which is very similar to the macro language in Microsoft Excel, Access, PowerPoint, Word, and Outlook) and VBScript (which is the macro language used by the ERP system where I work).

The earlier versions of the ERP system used where I work offered a COM interface to an API (application programming interface) that could be used for a variety of activities, such as creating inventory transactions, creating purchase orders, modifying customer orders, creating work orders to produce the parts ordered by the customers, and creating the shipping records when the completed parts ship to the customers.  I relied heavily on this COM interface API with Visual Basic 6.0 (or Excel, or the macro language in the ERP system) to automate many processes that previously required manual data entry into the ERP system.  Fast forward a decade, or so, and the most recent versions of the ERP system offers only a .Net compatible API to replace the COM API.  Not only is it a .Net compatible API, but it is also not compatible with Visual Studio 2005 (I thought that .Net was supposed to mark the end of DLL Hell?).  Great, another opportunity to be forced into not using VB 6.0 for development.

I am in the process of translating a MRP (material requirement planning) program that I created 10 to 12 years ago in VB 6.0 into C# to permit the use of the new .Net API.  The program has some fairly complex programming logic that uses recursive programming to drill into the multi-level BOMs (bill of materials) – in some simple cases the multi-level BOM is associated with a single work order, while in more complex cases the multi-level BOM is associated with child, grandchild, great-grandchild, and great-great-grandchild work orders.  The goal of the MRP program is to take the open customer orders in the ERP system, and to plan the top-level, child, grandchild, great-grandchild, and great-great-grandchild work orders and the associated purchased parts so that the purchased parts are available when needed by the work orders, the parts produced by the great-great-grandchild work orders are available when required by the great-grandchild work orders, the parts produced by the great-grandchild work orders are available when needed by the grandchild work orders, the parts produced by the grandchild work orders are available when required by the child work orders, the parts produced by the child work orders are available when required by the top-level work orders, and the parts produced by the top-level work orders are available on the dates specified in the customer order.  The ideal batch (lot) quantities, of course, could be different at each of the work order levels, and the quantity due to the customer on a given date will also typically fluctuate from one day to the next.

A simple illustration of the intended end-goal of the MRP program is shown in the picture below, which is the screen capture of another program that I wrote probably 15 years ago.  The customer orders (the demand) are shown on the left half of the screen capture, and the top-level work orders (the supply) are shown on the right half of the screen capture.  The quantity ordered by the customer for shipment on a given day is variable, but the work order batch (lot) sizes are standardized by part ID.  The intended goal is to permit the completion of the production of the ordered parts one weekday (Monday through Friday) earlier than the customer’s requested ship date (so that the part from a top-level work order could complete at 11:50 PM and then ship out to the customer at 5 AM the following morning).

seesharpobjectsoverview2

After using C# for a little while, for some reason the language reminds me of an odd, but very usable cross-pollination of Visual Basic 2.0 and Borland C++ that might have happened a bit over two decades ago.  Running with sharp objects is not so bad, although there are still exceptions.

After that long introduction, I will show a quick demonstration of accessing Oracle database using Visual Basic 6.0 and C# (I will use the 2005 version of C# – the newer versions provide some alternate syntax that saves a few keystrokes while programming).  The VB 6.0 sample program uses Microsoft ADO (ActiveX Data Objects) and the Oracle Provider for OLE DB (an optional feature installed with the Oracle client, see this article).  The C# sample program uses ODP.Net (Oracle Data Provider for .NET, an optional feature installed with the Oracle client, may also be installed later from a download).

Some general guidelines for programs that interact with Oracle databases:

  • Open a database connection, and keep it open for the duration of the program, if possible.
  • Use bind variables when possible, rather than specifying literals (constants) in the SQL statement that change from one execution to the next (there are a few exceptions to this rule).  Doing so helps to minimize the performance penalty of excessive hard parses.
  • Use a consistent format for all SQL statements submitted to the database – doing so maximizes the chances that a SQL statement will not need to be hard parsed when a similar SQL statement (possibly with just one extra space in the SQL statement) is already in Oracle’s library cache.
  • Perform tasks once, if possible, rather than over and over again.
  • Minimize the number of round trips to the database.  If an operation may be executed within a loop in your program, sending requests to the database for each iteration through the loop, think outside the loop to try to construct a single SQL statement that accomplishes the same task.  For instance, retrieve the required information into program variables either before or after the loop, or perform the processing within the database instance.

For this article, we will create a couple of tables and a sequence in the database for use in the program by using SQL*Plus:

CREATE TABLE PO(
  ORDER_ID VARCHAR2(15),
  VENDOR_ID VARCHAR2(15),
  ORDER_DATE DATE,
  SHIP_TO_ID NUMBER(4),
  PRIMARY KEY (ORDER_ID));
 
CREATE TABLE PO_LINE(
  ORDER_ID VARCHAR2(15),
  LINE_NO NUMBER(4),
  PART_ID VARCHAR2(30),
  QTY NUMBER(14,4),
  PRICE NUMBER(14,4),
  DUE_DATE DATE,
  PRIMARY KEY (ORDER_ID, LINE_NO));
 
CREATE TABLE PO_TRANS(
  TRANSACTION_ID NUMBER(14),
  VENDOR_ID VARCHAR2(15),
  PART_ID VARCHAR2(30),
  QTY NUMBER(14,4),
  DUE_DATE DATE,
  UNKNOWN NUMBER(14,4),
  PRIMARY KEY (TRANSACTION_ID));
 
CREATE SEQUENCE PO_TRANS_ID START WITH 1000;

The first step for VB 6.0 is to add a reference to one of the Microsoft ActiveX Data Objects Library versions.  To access this window, select References… from the Project menu:

SeeSharpObjectsDataAccessReferenceVB6-2

The first step for C# is to add a reference to Oracle.DataAccess.  To access this window, select Add Reference… from the Project menu:

SeeSharpObjectsDataAccessReferenceCS-2

C# also requires a couple of using entries to be added to the top of the program form and class code sections.  For example (the last two lines):

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Oracle.DataAccess.Client; // ODP.NET Oracle managed provider
using Oracle.DataAccess.Types; // ODP.NET Oracle managed provider

 

Since I only want to create a single connection to the database, and maintain that connection for potential use within a couple of program forms or code modules, in VB 6.0 I will create a module with a Global variable to maintain the database connection (note that I did not include the New keyword before ADODB, while I have not personally performed a comparison of the performance, a couple of books indicate that including the New keyword incurs a performance penalty with every access of that variable).

Global dbConnection As ADODB.Connection

In C# I would also want to have the ability to maintain that database connection for potential use within a couple of program forms or class modules, so in C# we will create a new class that I will name Global.  Inside the class module we will modify the class definition so that it is public, and create a public static variable named dbConnection that is of the type OracleConnection – the public static variable will be accessible from other code sections in the program without having to create an instance of the Global class.  The Global class should appear similar to what is seen below:

using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Client; // ODP.NET Oracle managed provider
using Oracle.DataAccess.Types; // ODP.NET Oracle managed provider
 
namespace SeeSharpObjectsSample
{
    public class Global
    {
        public static OracleConnection dbConnection;
    }
}

In VB 6.0 we will add some code to the Form_Load event procedure to connect to the database.  In VB 6.0, double-click the form’s background to open the code module for the Form_Load event procedure.  In the procedure we will declare a strSQL variable that will be used later in the Form_Load procedure and a strConnection variable.  On Error Resume Next will permit the program to recover gracefully if a command, such as the connection attempt, fails.  The code will connect to the or1212 database (most likely using the tnsnames.ora file to locate the database), connecting as the testuser user which also has a password of testuser, instruct the computer to set the array fetch size to 100 (to retrieve up to 100 rows with a single database fetch call), and to allow up to 40 seconds for the connection to complete.  If the connection fails, an error message is displayed on screen, and the program ends:

Private Sub Form_Load()
    Dim strSQL As String
    Dim strConnection As String
    
    On Error Resume Next
    
    strConnection = "Provider=OraOLEDB.Oracle;Data Source=or1212;User ID=testuser;Password=testuser;FetchSize=100;"
    
    Set dbConnection = New ADODB.Connection
    dbConnection.ConnectionTimeout = 40
    dbConnection.CursorLocation = adUseClient
    dbConnection.ConnectionString = strConnection
    
    dbConnection.Open
    
    If (dbConnection.State <> 1) Then
        MsgBox "Not connected to the database, program ending." & vbCrLf & Error
        End
    Else
        MsgBox "Connected"
    End If
End Sub

In C# we will add some code to the _Load event procedure to connect to the database.  In C#, double-click the form’s background to open the code module for the _Load event procedure.  In the procedure we will declare a strSQL variable that will be used later in the _Load procedure and a strConnection variable.  The try { } and catch { } blocks permit the program to recover gracefully if a command, such as the connection attempt, fails.  The code will connect to the or1212 database (most likely using the tnsnames.ora file to locate the database), connecting as the testuser user which also has a password of testuser, and to allow up to 40 seconds for the connection to complete.  In C# the array fetch size cannot be defaulted to 100, instead the array fetch size must be specified in the parameters of the individual OracleDataReader objects.  If the connection fails, an error message is displayed on screen, and the program ends:

private void frmSeeSharpObjects_Load(object sender, EventArgs e)
{
    string strSQL = "";
    string strConnection = "Data Source=or1212;User ID=testuser;Password=testuser;Connection Timeout=40;";
    
    try
    {
        Global.dbConnection = new OracleConnection(strConnection);
        
        Global.dbConnection.Open();
        MessageBox.Show("Connected");
    }
    catch (OracleException ex)
    {
        MessageBox.Show ("Not connected to the database, program ending." + "\n" + ex.Message.ToString() );
        Application.Exit();
    }
}

At this point, both of the programs should be able to run and connect to the database (Connected should appear in a window on the screen).  I happen to be using the 64 bit version of Windows for development, and need to use the 32 bit Oracle Client (and ODP.NET), so I encountered a few complications – Visual Studio added a reference to the 64 bit Oracle.DataAccess.dll.  As a result, I received an error similar to “Data provider internal error(-3000)”.  Completely fixing this issue required a little bit of work, and might require a different procedure on your computer.  First, I had to select projectname Properties from the Project menu and change the Platform target from Any CPU to x86:

SeeSharpObjectsPropertiesPlatform

I then saw the following warning in Visual Studio: “Assembly generation — Referenced assembly ‘Oracle.DataAccess.dll’ targets a different processor”.  The properties for the Oracle.DataAccess reference in the Solution Explorer showed that the Path property pointed to the 64 bit DLL.  In my case, the 32 bit DLL was in a different Oracle home, specifically located at C:\Oracle\product\11.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll – I did not see any simple method to quickly reference the 32 bit DLL, rather than the 64 bit DLL.  So, I right-clicked Oracle.DataAccess in the References list and selected Remove from the menu.  To add the reference to the 32 bit DLL, I selected Add Reference… from the Project menu, selected the Browse tab, navigated to the C:\Oracle\product\11.2.0\client_1\ODP.NET\bin\2.x folder (the 32 bit ODP.NET driver may be in a different folder on your computer), selected the Oracle.DataAccess.dll file and then clicked OK:

SeeSharpObjectsBrowseReference

The above steps fixed the problems, and allowed the C# program to connect to the database (I thought that .Net was supposed to mark the end of DLL Hell?).  Moving on…

Next, I want to set up ADO Command objects in the VB 6.0 project and OracleCommand objects in the C# project to insert into the three test tables, and to execute a SELECT statement.  Since the routines for inserting into the tables and selecting the data could be called many times in the program, I only want to set up these command objects once, and then use those command objects multiple times.  In the General section of the VB 6.0 form code (above the Private Sub Form_Load() ) line, add the following to create four ADO command objects:

Dim comInsertPO As ADODB.Command
Dim comInsertPOLine As ADODB.Command
Dim comInsertPOTrans As ADODB.Command
Dim comSelectPO As ADODB.Command

In the C# form code, below the opening bracket of the line “public partial class frmSeeSharpObjects : Form“, add the following lines (note, frmSeeSharpObjects is the name of the form that I created for this project):

        OracleCommand comInsertPO;
        OracleCommand comInsertPOLine;
        OracleCommand comInsertPOTrans;
        OracleCommand comSelectPO;

In the VB 6.0 form code, just before the End Sub in the Form_Load() event procedure, we will add additional code to create and configure the four ADO command objects.  ADO uses ? characters as bind variable placeholders:

    Set comInsertPO = New ADODB.Command
    With comInsertPO
        strSQL = "INSERT INTO PO(" & vbCrLf
        strSQL = strSQL & "  ORDER_ID," & vbCrLf
        strSQL = strSQL & "  VENDOR_ID," & vbCrLf
        strSQL = strSQL & "  ORDER_DATE," & vbCrLf
        strSQL = strSQL & "  SHIP_TO_ID)" & vbCrLf
        strSQL = strSQL & "VALUES(" & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?)"
    
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbConnection
        'Add the bind variables
        .Parameters.Append .CreateParameter("order_id", adVarChar, adParamInput, 15, "12345678910")
        .Parameters.Append .CreateParameter("vendor_id", adVarChar, adParamInput, 15, "ABCDEFGHIJKLM")
        .Parameters.Append .CreateParameter("order_date", adDate, adParamInput, 8, Now)
        .Parameters.Append .CreateParameter("ship_to_id", adNumeric, adParamInput, 8, 0)
    End With
        
    Set comInsertPOLine = New ADODB.Command
    With comInsertPOLine
        strSQL = "INSERT INTO PO_LINE(" & vbCrLf
        strSQL = strSQL & "  ORDER_ID," & vbCrLf
        strSQL = strSQL & "  LINE_NO," & vbCrLf
        strSQL = strSQL & "  PART_ID," & vbCrLf
        strSQL = strSQL & "  QTY," & vbCrLf
        strSQL = strSQL & "  PRICE," & vbCrLf
        strSQL = strSQL & "  DUE_DATE)" & vbCrLf
        strSQL = strSQL & "VALUES(" & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?)"
    
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbConnection
        'Add the bind variables
        .Parameters.Append .CreateParameter("order_id", adVarChar, adParamInput, 15, "12345678910")
        .Parameters.Append .CreateParameter("line_no", adNumeric, adParamInput, 8, 1)
        .Parameters.Append .CreateParameter("part_id", adVarChar, adParamInput, 15, "ABCDEFGHIJKLM")
        .Parameters.Append .CreateParameter("qty", adDouble, adParamInput, 15, 1)
        .Parameters.Append .CreateParameter("price", adDouble, adParamInput, 15, 1)
        .Parameters.Append .CreateParameter("due_date", adDate, adParamInput, 15)
    End With
    
    Set comInsertPOTrans = New ADODB.Command
    With comInsertPOTrans
        strSQL = "INSERT INTO PO_TRANS(" & vbCrLf
        strSQL = strSQL & "  TRANSACTION_ID," & vbCrLf
        strSQL = strSQL & "  VENDOR_ID," & vbCrLf
        strSQL = strSQL & "  PART_ID," & vbCrLf
        strSQL = strSQL & "  QTY," & vbCrLf
        strSQL = strSQL & "  DUE_DATE," & vbCrLf
        strSQL = strSQL & "  UNKNOWN)" & vbCrLf
        strSQL = strSQL & "VALUES(" & vbCrLf
        strSQL = strSQL & "  PO_TRANS_ID.NEXTVAL," & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?," & vbCrLf
        strSQL = strSQL & "  ?)"
   
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbConnection
        'Add the bind variables
        .Parameters.Append .CreateParameter("vendor_id", adVarChar, adParamInput, 15, "ABCDEFGHIJKLM")
        .Parameters.Append .CreateParameter("part_id", adVarChar, adParamInput, 15, "ABCDEFGHIJKLM")
        .Parameters.Append .CreateParameter("qty", adDouble, adParamInput, 15, 1)
        .Parameters.Append .CreateParameter("due_date", adDate, adParamInput, 15)
        .Parameters.Append .CreateParameter("unknown", adDouble, adParamInput, 15, 1)
    End With
    
    Set comSelectPO = New ADODB.Command
    With comSelectPO
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  P.VENDOR_ID," & vbCrLf
        strSQL = strSQL & "  PL.PART_ID," & vbCrLf
        strSQL = strSQL & "  PL.QTY," & vbCrLf
        strSQL = strSQL & "  PL.DUE_DATE" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  PO P," & vbCrLf
        strSQL = strSQL & "  PO_LINE PL" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  P.ORDER_ID=PL.ORDER_ID" & vbCrLf
        strSQL = strSQL & "  AND P.ORDER_DATE>=TRUNC(SYSDATE-30)" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  P.VENDOR_ID," & vbCrLf
        strSQL = strSQL & "  PL.PART_ID"
        
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbConnection
    End With

Now to add the equivalent code to the _Load event procedure code in C# just before the closing } of the procedure (ODP.NET uses : followed by an identifier as bind variable placeholders):

strSQL = "INSERT INTO PO(" + "\n";
strSQL = strSQL + " ORDER_ID," + "\n";
strSQL = strSQL + " VENDOR_ID," + "\n";
strSQL = strSQL + " ORDER_DATE," + "\n";
strSQL = strSQL + " SHIP_TO_ID)" + "\n";
strSQL = strSQL + "VALUES(" + "\n";
strSQL = strSQL + " :1," + "\n";
strSQL = strSQL + " :2," + "\n";
strSQL = strSQL + " :3," + "\n";
strSQL = strSQL + " :4)";
 
comInsertPO = new OracleCommand(strSQL, Global.dbConnection);
//comInsertPO.CommandText = strSQL;
comInsertPO.CommandType = CommandType.Text;
comInsertPO.CommandTimeout = 30;
comInsertPO.BindByName = false;
 
//Add the bind variables
comInsertPO.Parameters.Add("order_id", OracleDbType.Varchar2, 15, "12345678910", ParameterDirection.Input);
comInsertPO.Parameters.Add("vendor_id", OracleDbType.Varchar2, 15, "ABCDEFGHIJKLM", ParameterDirection.Input);
comInsertPO.Parameters.Add("order_date", OracleDbType.Date, 8, DateTime.Now, ParameterDirection.Input);
comInsertPO.Parameters.Add("ship_to_id", OracleDbType.Long, 8, 0, ParameterDirection.Input);
 
strSQL = "INSERT INTO PO_LINE(" + "\n";
strSQL = strSQL + " ORDER_ID," + "\n";
strSQL = strSQL + " LINE_NO," + "\n";
strSQL = strSQL + " PART_ID," + "\n";
strSQL = strSQL + " QTY," + "\n";
strSQL = strSQL + " PRICE," + "\n";
strSQL = strSQL + " DUE_DATE)" + "\n";
strSQL = strSQL + "VALUES(" + "\n";
strSQL = strSQL + " :1," + "\n";
strSQL = strSQL + " :2," + "\n";
strSQL = strSQL + " :3," + "\n";
strSQL = strSQL + " :4," + "\n";
strSQL = strSQL + " :5," + "\n";
strSQL = strSQL + " :6)";
 
comInsertPOLine = new OracleCommand(strSQL, Global.dbConnection);
//comInsertPOLine.CommandText = strSQL;
comInsertPOLine.CommandType = CommandType.Text;
comInsertPOLine.CommandTimeout = 30;
comInsertPOLine.BindByName = false;
 
//Add the bind variables
comInsertPOLine.Parameters.Add("order_id", OracleDbType.Varchar2, 15, "12345678910", ParameterDirection.Input);
comInsertPOLine.Parameters.Add("line_no", OracleDbType.Long, 8, 1, ParameterDirection.Input);
comInsertPOLine.Parameters.Add("part_id", OracleDbType.Varchar2, 15, "ABCDEFGHIJKLM", ParameterDirection.Input);
comInsertPOLine.Parameters.Add("qty", OracleDbType.Double, 15, 1, ParameterDirection.Input);
comInsertPOLine.Parameters.Add("price", OracleDbType.Double, 15, 1, ParameterDirection.Input);
comInsertPOLine.Parameters.Add("due_date", OracleDbType.Date, 15, ParameterDirection.Input);
 
strSQL = "INSERT INTO PO_TRANS(" + "\n";
strSQL = strSQL + " TRANSACTION_ID," + "\n";
strSQL = strSQL + " VENDOR_ID," + "\n";
strSQL = strSQL + " PART_ID," + "\n";
strSQL = strSQL + " QTY," + "\n";
strSQL = strSQL + " DUE_DATE," + "\n";
strSQL = strSQL + " UNKNOWN)" + "\n";
strSQL = strSQL + "VALUES(" + "\n";
strSQL = strSQL + " PO_TRANS_ID.NEXTVAL," + "\n";
strSQL = strSQL + " :1," + "\n";
strSQL = strSQL + " :2," + "\n";
strSQL = strSQL + " :3," + "\n";
strSQL = strSQL + " :4," + "\n";
strSQL = strSQL + " :5)";
 
comInsertPOTrans = new OracleCommand(strSQL, Global.dbConnection);
//comInsertPOTrans.CommandText = strSQL;
comInsertPOTrans.CommandType = CommandType.Text;
comInsertPOTrans.CommandTimeout = 30;
comInsertPOTrans.BindByName = false;
 
//Add the bind variables
comInsertPOTrans.Parameters.Add("vendor_id", OracleDbType.Varchar2, 15, "ABCDEFGHIJKLM", ParameterDirection.Input);
comInsertPOTrans.Parameters.Add("part_id", OracleDbType.Varchar2, 15, "ABCDEFGHIJKLM", ParameterDirection.Input);
comInsertPOTrans.Parameters.Add("qty", OracleDbType.Double, 15, 1, ParameterDirection.Input);
comInsertPOTrans.Parameters.Add("due_date", OracleDbType.Date, 15, ParameterDirection.Input);
comInsertPOTrans.Parameters.Add("unknown", OracleDbType.Double, 15, 1, ParameterDirection.Input);
 
strSQL = "SELECT" + "\n";
strSQL = strSQL + " P.VENDOR_ID," + "\n";
strSQL = strSQL + " PL.PART_ID," + "\n";
strSQL = strSQL + " PL.QTY," + "\n";
strSQL = strSQL + " PL.DUE_DATE" + "\n";
strSQL = strSQL + "FROM" + "\n";
strSQL = strSQL + " PO P," + "\n";
strSQL = strSQL + " PO_LINE PL" + "\n";
strSQL = strSQL + "WHERE" + "\n";
strSQL = strSQL + " P.ORDER_ID=PL.ORDER_ID" + "\n";
strSQL = strSQL + " AND P.ORDER_DATE>=TRUNC(SYSDATE-30)" + "\n";
strSQL = strSQL + "ORDER BY" + "\n";
strSQL = strSQL + " P.VENDOR_ID," + "\n";
strSQL = strSQL + " PL.PART_ID";
 
comSelectPO = new OracleCommand(strSQL, Global.dbConnection);
//comSelectPO.CommandText = strSQL;
comSelectPO.CommandType = CommandType.Text;
comSelectPO.CommandTimeout = 30;

It should be possible to run the program now without generating errors, but the program will not do much other than connect to the database at this point.  Now that the basic setup is out of the way, we will create a Command Button in VB 6.0 (a Button in C#) and set its name to cmdProcess.  In VB 6.0, double-click the command button to access the Click event code for the command button.  Add the following code, which will add a few rows to the PO and PO_LINE tables, select some of the rows from those tables with a join on the ORDER_ID column in each table, and then insert rows into the PO_TRANS table using the SELECT resultset.  Note that it is possible to generate a 10046 trace at level 4 (with bind variables) by removing the character on the line below the On Error Resume Next line.  Note too that it is only necessary to specify the value of bind variables if those values must change after each INSERT is executed:

Private Sub cmdProcess_Click()
    Dim snpData As ADODB.Recordset
    
    On Error Resume Next
    
    'dbConnection.Execute "ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'"
    
    dbConnection.BeginTrans
    
    comInsertPO("order_id") = "A10000"
    comInsertPO("vendor_id") = "BUBBA GUMP"
    comInsertPO("order_date") = DateAdd("d", -62, Date)
    comInsertPO("ship_to_id") = 1
    comInsertPO.Execute
    
    comInsertPO("order_id") = "A10001"
    comInsertPO("order_date") = DateAdd("d", -25, Date)
    comInsertPO.Execute
    
    comInsertPO("order_id") = "A10002"
    comInsertPO("vendor_id") = "POPPA GUMP"
    comInsertPO("order_date") = DateAdd("d", -20, Date)
    comInsertPO("ship_to_id") = 1
    comInsertPO.Execute
    
    comInsertPO("order_id") = "A10003"
    comInsertPO("order_date") = DateAdd("d", -2, Date)
    comInsertPO("ship_to_id") = 1
    comInsertPO.Execute
    
    comInsertPO("order_id") = "A10004"
    comInsertPO("vendor_id") = "POPPA SMURF"
    comInsertPO("order_date") = DateAdd("d", -1, Date)
    comInsertPO("ship_to_id") = 1
    comInsertPO.Execute
    
    comInsertPOLine("order_id") = "A10000"
    comInsertPOLine("line_no") = 1
    comInsertPOLine("part_id") = "SPAM"
    comInsertPOLine("qty") = 5
    comInsertPOLine("price") = 10.25
    comInsertPOLine("due_date") = DateAdd("d", -1, Date)
    comInsertPOLine.Execute
    
    comInsertPOLine("line_no") = 2
    comInsertPOLine("due_date") = DateAdd("d", 3, Date)
    comInsertPOLine.Execute
    
    comInsertPOLine("line_no") = 3
    comInsertPOLine("due_date") = DateAdd("d", 6, Date)
    comInsertPOLine.Execute
    comInsertPOLine("order_id") = "A10001"
    comInsertPOLine("line_no") = 1
    comInsertPOLine("part_id") = "STEAK"
    comInsertPOLine("qty") = 3
    comInsertPOLine("price") = 21.85
    comInsertPOLine("due_date") = DateAdd("d", -20, Date)
    comInsertPOLine.Execute
    
    comInsertPOLine("line_no") = 2
    comInsertPOLine("qty") = 4
    comInsertPOLine("due_date") = DateAdd("d", 10, Date)
    comInsertPOLine.Execute
    
    comInsertPOLine("line_no") = 3
    comInsertPOLine("qty") = 3
    comInsertPOLine("due_date") = DateAdd("d", 15, Date)
    comInsertPOLine.Execute
    comInsertPOLine("order_id") = "A10002"
    comInsertPOLine("line_no") = 1
    comInsertPOLine("part_id") = "CHUCKBURGER"
    comInsertPOLine("qty") = 3
    comInsertPOLine("price") = 15.01
    comInsertPOLine("due_date") = DateAdd("d", 10, Date)
    comInsertPOLine.Execute
    
    comInsertPOLine("order_id") = "A10003"
    comInsertPOLine("line_no") = 1
    comInsertPOLine("part_id") = "BACON"
    comInsertPOLine("qty") = 3
    comInsertPOLine("price") = 16.49
    comInsertPOLine("due_date") = DateAdd("d", 9, Date)
    comInsertPOLine.Execute
    
    comInsertPOLine("line_no") = 2
    comInsertPOLine("part_id") = "EGGS"
    comInsertPOLine("qty") = 19
    comInsertPOLine("price") = 0.49
    comInsertPOLine("due_date") = DateAdd("d", 9, Date)
    comInsertPOLine.Execute
    
    comInsertPOLine("order_id") = "A10004"
    comInsertPOLine("line_no") = 1
    comInsertPOLine("part_id") = "ROASTBEEF"
    comInsertPOLine("qty") = 200
    comInsertPOLine("price") = 11.99
    comInsertPOLine("due_date") = DateAdd("d", 30, Date)
    comInsertPOLine.Execute
    Set snpData = comSelectPO.Execute
    
    If (snpData.State <> 1) Then
        MsgBox "SQL statement did not execute."
    Else
        If (snpData.EOF = True) Then
            MsgBox "No rows returned from the database."
        Else
            Do While Not (snpData.EOF)
                comInsertPOTrans("vendor_id") = snpData("vendor_id")
                comInsertPOTrans("part_id") = snpData("part_id")
                comInsertPOTrans("qty") = snpData("qty") * 1.5
                comInsertPOTrans("due_date") = DateAdd("m", -1, snpData("due_date"))
                comInsertPOTrans("unknown") = Rnd * 100
                comInsertPOTrans.Execute
                
                snpData.MoveNext
            Loop
        End If
        
        snpData.Close
    End If
    
    If Err = 0 Then
        dbConnection.CommitTrans
        
        MsgBox "Processing finished successfully."
    Else
        dbConnection.RollbackTrans
        
        MsgBox "Could not update the database due to an error." & vbCrLf & Error
    End If
End Sub

Now to add the nearly equivalent C# code – the only major difference is that the C# code jumps out of the try block to the catch block when the first error is encountered, while in the VB 6.0 code, due to the usage of the On Error Resume Next command, the processing simply continues when an error occurs.  Note that it is possible to generate a 10046 trace at level 4 (with bind variables) by removing the // characters on the two lines just after the try block begins:

private void cmdProcess_Click(object sender, EventArgs e)
{
    Random rndSequence = new Random();
    OracleDataReader snpData;
    OracleTransaction oraTransaction;
    oraTransaction = Global.dbConnection.BeginTransaction(IsolationLevel.ReadCommitted);
 
    try
    {
        //OracleCommand comTrace = new OracleCommand("ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'", Global.dbConnection);
        //comTrace.ExecuteNonQuery();
        
        comInsertPO.Parameters["order_id"].Value = "A10000";
        comInsertPO.Parameters["vendor_id"].Value = "BUBBA GUMP";
        comInsertPO.Parameters["order_date"].Value = DateTime.Today.AddDays(-62);
        comInsertPO.Parameters["ship_to_id"].Value = 1;
        comInsertPO.ExecuteNonQuery();
        
        comInsertPO.Parameters["order_id"].Value = "A10001";
        comInsertPO.Parameters["order_date"].Value = DateTime.Today.AddDays(-25);
        comInsertPO.ExecuteNonQuery();
        
        comInsertPO.Parameters["order_id"].Value = "A10002";
        comInsertPO.Parameters["vendor_id"].Value = "POPPA GUMP";
        comInsertPO.Parameters["order_date"].Value = DateTime.Today.AddDays(-20);
        comInsertPO.Parameters["ship_to_id"].Value = 1;
        comInsertPO.ExecuteNonQuery();
        
        comInsertPO.Parameters["order_id"].Value = "A10003";
        comInsertPO.Parameters["order_date"].Value = DateTime.Today.AddDays(-2);
        comInsertPO.Parameters["ship_to_id"].Value = 1;
        comInsertPO.ExecuteNonQuery();
        
        comInsertPO.Parameters["order_id"].Value = "A10004";
        comInsertPO.Parameters["vendor_id"].Value = "POPPA SMURF";
        comInsertPO.Parameters["order_date"].Value = DateTime.Today.AddDays(-1);
        comInsertPO.Parameters["ship_to_id"].Value = 1;
        comInsertPO.ExecuteNonQuery();
        
        comInsertPOLine.Parameters["order_id"].Value = "A10000";
        comInsertPOLine.Parameters["line_no"].Value = 1;
        comInsertPOLine.Parameters["part_id"].Value = "SPAM";
        comInsertPOLine.Parameters["qty"].Value = 5;
        comInsertPOLine.Parameters["price"].Value = 10.25;
        comInsertPOLine.Parameters["due_date"].Value = DateTime.Today.AddDays(-1);
        comInsertPOLine.ExecuteNonQuery();
        
        comInsertPOLine.Parameters["line_no"].Value = 2;
        comInsertPOLine.Parameters["due_date"].Value = DateTime.Today.AddDays(3);
        comInsertPOLine.ExecuteNonQuery();
        
        comInsertPOLine.Parameters["line_no"].Value = 3;
        comInsertPOLine.Parameters["due_date"].Value = DateTime.Today.AddDays(6);
        comInsertPOLine.ExecuteNonQuery();
        
        comInsertPOLine.Parameters["order_id"].Value = "A10001";
        comInsertPOLine.Parameters["line_no"].Value = 1;
        comInsertPOLine.Parameters["part_id"].Value = "STEAK";
        comInsertPOLine.Parameters["qty"].Value = 3;
        comInsertPOLine.Parameters["price"].Value = 21.85;
        comInsertPOLine.Parameters["due_date"].Value = DateTime.Today.AddDays(-20);
        comInsertPOLine.ExecuteNonQuery();
        
        comInsertPOLine.Parameters["line_no"].Value = 2;
        comInsertPOLine.Parameters["qty"].Value = 4;
        comInsertPOLine.Parameters["due_date"].Value = DateTime.Today.AddDays(10);
        comInsertPOLine.ExecuteNonQuery();
        
        comInsertPOLine.Parameters["line_no"].Value = 3;
        comInsertPOLine.Parameters["qty"].Value = 3;
        comInsertPOLine.Parameters["due_date"].Value = DateTime.Today.AddDays(15);
        comInsertPOLine.ExecuteNonQuery();
        
        comInsertPOLine.Parameters["order_id"].Value = "A10002";
        comInsertPOLine.Parameters["line_no"].Value = 1;
        comInsertPOLine.Parameters["part_id"].Value = "CHUCKBURGER";
        comInsertPOLine.Parameters["qty"].Value = 3;
        comInsertPOLine.Parameters["price"].Value = 15.01;
        comInsertPOLine.Parameters["due_date"].Value = DateTime.Today.AddDays(10);
        comInsertPOLine.ExecuteNonQuery();
        
        comInsertPOLine.Parameters["order_id"].Value = "A10003";
        comInsertPOLine.Parameters["line_no"].Value = 1;
        comInsertPOLine.Parameters["part_id"].Value = "BACON";
        comInsertPOLine.Parameters["qty"].Value = 3;
        comInsertPOLine.Parameters["price"].Value = 16.49;
        comInsertPOLine.Parameters["due_date"].Value = DateTime.Today.AddDays(9);
        comInsertPOLine.ExecuteNonQuery();
        
        comInsertPOLine.Parameters["line_no"].Value = 2;
        comInsertPOLine.Parameters["part_id"].Value = "EGGS";
        comInsertPOLine.Parameters["qty"].Value = 19;
        comInsertPOLine.Parameters["price"].Value = 0.49;
        comInsertPOLine.Parameters["due_date"].Value = DateTime.Today.AddDays(9);
        comInsertPOLine.ExecuteNonQuery();
        
        comInsertPOLine.Parameters["order_id"].Value = "A10004";
        comInsertPOLine.Parameters["line_no"].Value = 1;
        comInsertPOLine.Parameters["part_id"].Value = "ROASTBEEF";
        comInsertPOLine.Parameters["qty"].Value = 200;
        comInsertPOLine.Parameters["price"].Value = 11.99;
        comInsertPOLine.Parameters["due_date"].Value = DateTime.Today.AddDays(30);
        comInsertPOLine.ExecuteNonQuery();
        
        snpData = comSelectPO.ExecuteReader();
        if (snpData.IsClosed)
        {
            MessageBox.Show("SQL statement did not execute.");
        }
        else
        {
            if (!snpData.HasRows)
            {
                MessageBox.Show("No rows returned from the database.");
            }
            else
            {
                //snpData.FetchSize = comSelectPO.RowSize * 100;
                while (snpData.Read())
                {
                    comInsertPOTrans.Parameters["vendor_id"].Value = snpData["vendor_id"];
                    comInsertPOTrans.Parameters["part_id"].Value = snpData["part_id"];
                    comInsertPOTrans.Parameters["qty"].Value = Convert.ToDouble(snpData["qty"]) * 1.5;
                    comInsertPOTrans.Parameters["due_date"].Value = Convert.ToDateTime(snpData["due_date"]).AddMonths(-1);
                    comInsertPOTrans.Parameters["unknown"].Value = rndSequence.NextDouble() * 100
                    comInsertPOTrans.ExecuteNonQuery();
                 } //while (snpData.Read())
            } //else of if (!snpData.HasRows)
 
            snpData.Close();
        }
        oraTransaction.Commit();
        MessageBox.Show("Processing finished successfully.");
    }
    catch (Exception ex)
    {
        oraTransaction.Rollback();
        MessageBox.Show("Could not update the database due to an error." + "\n" + ex.Message.ToString());
    }
}

With the above code in place, it should be possible to run the program and click the command button on the form to insert rows into the PO, PO_LINE, and PO_TRANS tables.  Of course, it is only possible to click that command button once without an error appearing on screen in either the VB 6.0 or C# programs, respectively:
SeeSharpObjectsUniqueConstraintVB  SeeSharpObjectsUniqueConstraint

To re-execute the code in the command button’s Click event, the tables must first be cleared of rows using SQL*Plus:

TRUNCATE TABLE PO;
TRUNCATE TABLE PO_LINE;
TRUNCATE TABLE PO_TRANS;

As a test, I executed the cmdProcess button in VB 6.0, and then the following SQL statement in SQL*Plus to see what ended up in the PO_TRANS table:

SELECT
  *
FROM
  PO_TRANS
ORDER BY
  TRANSACTION_ID;

Here is the output that I received (notice, no SPAM) from the processing in VB 6.0:

TRANSACTION_ID VENDOR_ID       PART_ID            QTY DUE_DATE     UNKNOWN
-------------- --------------- ----------- ---------- --------- ----------
          1057 BUBBA GUMP      STEAK              4.5 15-OCT-15    70.5547
          1058 BUBBA GUMP      STEAK                6 15-NOV-15    53.3424
          1059 BUBBA GUMP      STEAK              4.5 20-NOV-15    57.9519
          1060 POPPA GUMP      BACON              4.5 14-NOV-15    28.9562
          1061 POPPA GUMP      CHUCKBURGER        4.5 15-NOV-15    30.1948
          1062 POPPA GUMP      EGGS              28.5 14-NOV-15     77.474
          1063 POPPA SMURF     ROASTBEEF          300 04-DEC-15     1.4018

I then truncated the three tables and executed the cmdProcess button in C#.  Here is the output of the above SQL statement after executing the cmdProcess button in C#:

TRANSACTION_ID VENDOR_ID       PART_ID            QTY DUE_DATE     UNKNOWN
-------------- --------------- ----------- ---------- --------- ----------
          1064 BUBBA GUMP      STEAK              4.5 15-OCT-15    55.5915
          1065 BUBBA GUMP      STEAK                6 15-NOV-15    65.7347
          1066 BUBBA GUMP      STEAK              4.5 20-NOV-15    83.0905
          1067 POPPA GUMP      BACON              4.5 14-NOV-15    19.0149
          1068 POPPA GUMP      CHUCKBURGER        4.5 15-NOV-15    73.2245
          1069 POPPA GUMP      EGGS              28.5 14-NOV-15    61.9526
          1070 POPPA SMURF     ROASTBEEF          300 04-DEC-15    93.9571

The output is identical, with the exception of the UNKNOWN column, which is a pseudo-random number between 0 and 99.9999.  A job well done, with one complication.  What is the complication?  Think about the code logic for a minute before scrolling down.

Thought about the complication?  No, it is not that this blog article required approximately 11 hours to write (a large percentage of that time is due to WordPress’ auto-formatting of the article).  The code violates the fifth bullet point that is listed at the start of the article.  The insert into the PO_TRANS table could have been performed by sending a single SQL statement to the database, rather than using a while or Do While loop and performing an execution of the INSERT statement for each row in the resultset.

If you would like to use a free version of C#, Microsoft offers the Express edition with no apparent licensing restrictions and a more powerful Community edition that carries licensing restrictions.  Both versions may be downloaded from Microsoft’s website.

On a remotely related note, a week ago a Microsoft Windows technician called about a computer problem that I was having.  My first thought was, “how did he know I was translating a VB 6.0 program to C#?”  My second thought was, “which problem?”  Sadly, I immediately recognized this call as one of those “Microsoft” support scam calls, and decided that the person on the other end of the line was not equipped to walk with, let along run with, sharp objects.  As a result, I decided to terminate the call rather quickly:
“Microsoft” support scammer: I am calling about a problem with your computer.
Me: A computer? (in a concerned voice)
“Microsoft” support scammer: Yes, your laptop computer.
Me: I don’t have a laptop computer.
“Microsoft” support scammer: Oh, your Windows tower computer.
Me: I don’t have a computer.
“Microsoft” support scammer: (Click)

The scammer gave up a little too easily – I had planned to ask her if she knew how to troubleshoot sticky key problems on my typewriter.  That is the second Microsoft tech that gave up before we had a chance to troubleshoot the virtual machine.