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.