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).
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:
The first step for C# is to add a reference to Oracle.DataAccess. To access this window, select Add Reference… from the Project menu:
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:
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:
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:
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.
Charles Hooper's Oracle Notes 
Recent Comments