Sunday, January 29, 2012

Interfacing to an Access Database via Visual C# 2010 Express


Interfacing to an Access Database via Visual C# 2010 Express

 Background

Looking for something to do, I decided to look into something that I had previously done a few times for aviation projects.  That is, to interface an Access database with a Visual Basic program to
   1.  Maintain the database and
   2.   Use the database to support an aviation project.

The first such project was to read the debug output of a cross-compiler build to find the addresses assigned to procedures, variables, type declarations, etc for use in gathering flight data, code path iterations, etc and then, after the flight, to report it by comparing the output to the database values to report by variable name, etc.

The second was to maintain the relationship between ARINC-661 widget identifiers and the user application actions to be ran upon receiving the widget event or to produce output commands to modify the cockpit displays.  Then, from an aircraft project xml file, to produce the A661 xml Definition File (DF) for loading into the Display Processor and another, user application oriented output version of the project file (containing some non-A661 tags such as the enumerated values of the actions) to be loaded into the user application.   These Visual Basic applications to keep the user application in sync with the A661 display DF as the definition file changed – such as the inclusion of new or changed widget identifiers and their associated application actions.

Between the first project and the second, the versions of Access and Visual Basic had changed so the interface procedures and techniques had to also be somewhat changed and had to be reworked for accessing the database of the second set of projects.

Now, at home for my amusement, I thought I would try to interface with an Access database.  I have never updated the version of the Microsoft Access database application that I purchased with Office 2000 back a decade or more ago.  But I thought I would see if the Visual Basic 2010 Express and Visual C# 2010 Express that I have been using would happen to be able to read databases produced by Access 2000.  As it turned out, I used Visual C# 2010 Express.

Since I had previously had to change my Visual Basic interface calls to read and update the Access database with new releases of Visual Basic and Access, I was pleasantly surprised when it turned out that I was able to use Visual C# 2010 Express to interface with Access 2000 of Windows 97.

Visual C# 2010 Express project initial problems

At first I managed to create a Visual C# project using my new 64-bit PC that referenced one of my .mdb files to update new records to its table, but never to read the file.  That is, each new run would detect zero rows in the database but would produce the rows over again and cause the timestamp on the .mdb to be updated.  If the program was modified to create another new row, the .mdb file then contained that row when examined using my old Windows Office 2000 Access application.

Searching the internet I found that a 32-bit program had to be used to interface to Access and that certain dll's had to be present in the Windows System32 folder.  Also, that the Express versions didn't support producing a 32-bit program on my 64-bit Windows 7 computer.

I therefore attempted the program on my 32-bit XP notebook where its version of Visual C# 2010 Express ran and was able to read the existing .mdb file. 

On the notebook a sample program from Microsoft ran and could retrieve the data from the sample's .mdb file.  On the Windows 7 PC it wouldn't run and resulted in an exception when attempting to fill the data set after opening the connection to the database. 

However, in attempting to run my own project on the XP notebook, it continued to ignore the original row or the two rows that were updated by the program.  However, the behavior was somewhat different than on the 64-bit PC.

On the Windows 7 PC the program could output (update) a new row the first time that particular row was created by the program but would always find zero rows when restarted.  After running, the mdb file timestamp would be updated, but it wouldn't have any new rows (unless the program had been modified to create yet another row).

On the XP PC each time the program is run, the two new rows that it created were duplicated in the mdb file so that its number of rows increased each time.  But it didn't recognize these rows the next time it is run.

So the operation is somewhat different on the XP notebook and the Microsoft example sample did recognize the two existing rows of its mdb file.

I noticed that the properties associated with the C# icon on the notebook have a Compatibility tab with a compatibility mode.  On the XP notebook the Compatibility is Windows 97 which happens to be where the Access 2000 came from.  On the Windows 7 PC, the Visual C# install resulted in Windows XP (Service Pack 3) compatibility.  I tried changing it to Windows 97 compatibility but C# projects can't be launched with this setting.

It took awhile to discover how to update the database using the generated database class.  There are various update methods that only update the loaded dataset but not the actual database.  There is just one Insert method to add a new row to the database.  That is, one can update the dataset a row at a time including a new row but, using the C# generated classes, there is only one method to output a new row to the database and it doesn't update the dataset at the same time.  (Note:  Creating a C# project for a particular Access .mdb file generates supporting classes in their own project files to be used by the user's Program while the Microsoft examples use OleDb classes directly for this type of database.)

Also, as I created the project, the generated database classes are such that the original database is copied to both the /bin/Debug and /bin/Release subfolders.  Running the C# debugger or the executable that is in the Release folder causes the original database to be copied to the subfolder being used.  Using the debugger causes this to happen each time it starts running.  Using the Release executable, the copy is made at least every time a new Solution is Built.

Therefore, trying to determine if my code was working caused me some wonderment.  Sometimes the changes would be there and then they would disappear again.  After I figured this out, I could move the executable to the folder of the database selected when building the generated classes and keep the modifications from one execution to the next.

Visual C# 2010 Express project example

The example code provided below has the project Program class Main procedure invoke a ReadUpdateReadwithDataSetDesignerClasses method.  (This naming used since I also plan to produce a similar example using the OleDB classes directly rather then the classes generated by referencing the database when the C# project is created.)

The database name is db1.mdb.  It has one table named HouseholdInventory with seven fields/columns named as described in the example code.  The first column is the CategoryID column.  It is the database key and is AutoNumber.

The example
o Opens a connection to the database,
o Loads the database into the internal dataset, and
o Instantiates a Reader and then displays the current contents of the dataset's table.

Then the example
o Adds a new row to the dataset and Inserts it into the database, and
o Once again creates a Reader and displays the current contents of the dataset's table.

Following this, the example
o Queries for a particular row by the unique value of the CategoryID field/column,
o Modifies a couple of fields in a copy of the queried row and Updates the modified row into the dataset and the database, and
o Creates a Reader once more and displays the updated contents of the dataset.

Lastly, the example closes the connection and waits for the operator to examine the Console output.  Before using ENTER/RETURN the operator can also use Access to check that the database matches the displayed dataset values.

Notes

Since the example attempts to lookup a CategoryID of 3, the original database must have a least three rows which, with AutoNumber will have values of 1, 2, and 3.

As mentioned in the example code, the database, dataset table, and table adapter objects are instantiated from the generated classes as static within the Program class along with an unused table adapter manager object.  They could, of course, be instantiated locally at the beginning of the ReadUpdateReadwithDataSetDesignerClasses method.

To be able to provide all the steps required in this post to select a particular Access database to be associated with a project, I went through all the steps once again.  In doing so, I discovered why I had what I had thought was strange behavior.  See step 16 below.

Steps to Create a C# Access database project

1) Launch Microsoft Visual C# 2010 Express
2) Select File|New Project
3) In New Project form select Console Application
4) With the ConsoleApplication1 (for instance) window opened, select Project|Add Class.
5) Out of the various options, select Local Database.
6) This will display the Data Source Configuration Wizard form to Choose a Database Model.  With Dataset highlighted, do Next>.
7) This will display that the database selected is new.  Do < Previous.
8) This will display whether to display the connection string.  Do < Previous again.
9) This will display the Choose Your Data Collection form with a default of Database1.sdf.  Select New Connection.
10) This will display the Add Connection form.  Select Change.
11) This will display the Change Data Source form.  Select Microsoft Access Database File.
12) This will display .NET Framework Data Provider for OLE DB as the Data provider.  Select OK.
13) This will return to the Add Connection form.  Browse for the .mdb database and Open it.  (Advanced Properties will display that the Provider=Microsoft.Jet.OLEDB.4.0 along with the selected Data Source.)
14) Test Connection will validate that the connection can succeed.  After OKing that, select OK on the Add Connection form.
15) Upon return to the Data Source Configuration Wizard, select Next >.
16) Then there will be whether to copy the data file to your project.  Selecting Yes will result in what I thought was the strange behavior when running the Debug and the Release versions of the project since I hadn't paid enough attention.  Selecting No will always use the selected file.
17) Upon return to the  Data Source Configuration Wizard, Yes will be checked to save the connection string as a particular name.  Select NEXT >.
18) This will display a Choose Your Database Objects form of the Wizard.  Select the + to display the Tables in the database and then the + of the desired tables and then use the checkboxes to select the tables and fields/columns objects wanted.  The Views checkbox can also be used to include this object.  Then select Finish.

The above steps create a Program.cs code file for a Program class with an empty Main procedure along with subfolders of Properties and References. 

Example Code

  static void ReadUpdateReadwithDataSetDesignerClasses()
  {
      // This method reads and displays an existing Access database and
      // then updates the loaded dataset and inserts new rows into the
      // database and then redisplays the dataset.  To do this it uses
      // the Microsoft Visual C# 2010 Express generated DataSet classes
      // that were created at the time the new project was created as
      // well as the Properties folder, etc.
      //
      // The existing Access database that was used to create the
      // generated classes.  This database has one table named
      // HouseholdInventory.  This table has 7 fields/columns where
      // the first column is named CategoryID and has a data type of
      // AutoNumber.  Five of the other 6 columns are Text strings
      // while the DatePurchased column has a data type of Date/Time.
      //
      // New static objects of these generated classes of
      //   static db1DataSet1 db1 = new db1DataSet1();
      //   static db1DataSet1.HouseholdInventoryDataTable table =
      //     new db1DataSet1.HouseholdInventoryDataTable();
      //   static HouseholdInventoryTableAdapter adapter =
      //     new HouseholdInventoryTableAdapter();
      //   static TableAdapterManager manager = new TableAdapterManager();
      // are instantiated in the Program class of which this method is a
      // part and are referenced by this method.

      adapter.Connection.Open(); // Open a connection to the database

      table = adapter.GetData(); // Get/Load the database into the dataset table

      int rows = table.Count;    // Display original number of rows
      Console.WriteLine("original number of table rows {0}", rows.ToString());

      // Display the original data and capture last current CategoryID value.
      DataTableReader reader = table.CreateDataReader();
      while (reader.Read())
      {
          Console.WriteLine("Category ID {0}", reader["CategoryID"].ToString());
          Console.WriteLine("Notes {0}", reader["Notes"].ToString());
          Console.WriteLine("Model Number {0}", reader["ModelNumber"].ToString());
          Console.WriteLine("Date Purchased {0}",
                            reader["DatePurchased"].ToString());
          Console.WriteLine("Description {0}", reader["Description"].ToString());
          Console.WriteLine("Item Name {0}", reader["ItemName"].ToString());
          Console.WriteLine("Item Type {0}", reader["ItemType"].ToString());
      }
      Int32 CategoryIDValue = Convert.ToInt32(reader["CategoryID"].ToString());
      reader.Close();

      // Add a new row to the table and the data base.
      db1DataSet1.HouseholdInventoryRow row = table.NewHouseholdInventoryRow();
      row.CategoryID = CategoryIDValue + 1;
      row.Description = "new item";
      row.ModelNumber = "45123";
      row.Notes = "new note";
      DateTime dateTime = System.DateTime.Now;
      row.DatePurchased = dateTime;
      row.ItemName = "hammer";
      row.ItemType = "tool";
      UpdateInsert(row);

      // Display the updated data table.
      rows = table.Count;    // Display updated number of rows
      Console.WriteLine(" ");
      Console.WriteLine("number of table rows after add {0}", rows.ToString());
      reader = table.CreateDataReader();
      while (reader.Read())
      {
          Console.WriteLine("Category ID {0}", reader["CategoryID"].ToString());
          Console.WriteLine("Notes {0}", reader["Notes"].ToString());
          Console.WriteLine("Model Number {0}", reader["ModelNumber"].ToString());
          Console.WriteLine("Date Purchased {0}",
                            reader["DatePurchased"].ToString());
          Console.WriteLine("Description {0}", reader["Description"].ToString());
          Console.WriteLine("Item Name {0}", reader["ItemName"].ToString());
          Console.WriteLine("Item Type {0}", reader["ItemType"].ToString());
      }
      reader.Close();

      // Query for row with CategoryID of 3 and display values.
      row = table.FindByCategoryID(3);
      Console.WriteLine(" ");
      Console.WriteLine("Table row found");
      Console.WriteLine("Category ID {0}", row.CategoryID.ToString());
      Console.WriteLine("Notes {0}", row.Notes.ToString());
      Console.WriteLine("Model Number {0}", row.ModelNumber.ToString());
      Console.WriteLine("Date Purchased {0}", row.DatePurchased.ToString());
      Console.WriteLine("Description {0}", row.Description.ToString());
      Console.WriteLine("Item Name {0}", row.ItemName.ToString());
      Console.WriteLine("Item Type {0}", row.ItemType.ToString());

      // Modify the Notes and Date Purchased values and Update.
      db1DataSet1.HouseholdInventoryRow newRow = table.NewHouseholdInventoryRow();
      newRow = row;
      newRow.Notes = "modified note to show that updated";
      dateTime = System.DateTime.Now;
      newRow.DatePurchased = dateTime;
      Update(newRow,row);

      // Display data once again.
      Console.WriteLine(" ");
      Console.WriteLine("Table rows after update of row with CategoryID of 3");
      reader = table.CreateDataReader();
      while (reader.Read())
      {
          Console.WriteLine("Category ID {0}", reader["CategoryID"].ToString());
          Console.WriteLine("Notes {0}", reader["Notes"].ToString());
          Console.WriteLine("Model Number {0}", reader["ModelNumber"].ToString());
          Console.WriteLine("Date Purchased {0}",
                            reader["DatePurchased"].ToString());
          Console.WriteLine("Description {0}", reader["Description"].ToString());
          Console.WriteLine("Item Name {0}", reader["ItemName"].ToString());
          Console.WriteLine("Item Type {0}", reader["ItemType"].ToString());
      }
      reader.Close();

      // Close the connection.
      adapter.Connection.Close();

      // Allow viewing of console output.
      Console.WriteLine(" ");
      Console.Write("Use ENTER/RETURN when finished viewing output: ");
      Console.Read();

  } // end method ReadUpdateReadwithDataSetDesignerClasses

  private static void Update(db1DataSet1.HouseholdInventoryRow newRow,
                             db1DataSet1.HouseholdInventoryRow oldRow)
  {
      // This method Updates a current row to the dataset and to the
      // database.

      adapter.Update(newRow);
      adapter.Update(newRow.Notes, newRow.ModelNumber, newRow.DatePurchased,
                     newRow.Description, newRow.ItemName, newRow.ItemType,
                     oldRow.CategoryID, oldRow.ModelNumber,
                     oldRow.DatePurchased, oldRow.Description,
                     oldRow.ItemName, oldRow.ItemType);
  }

  private static void UpdateInsert(db1DataSet1.HouseholdInventoryRow row)
  {
      // This method Updates a new row to the dataset and Inserts the row
      // into the database.

      table.AddHouseholdInventoryRow(row);
      adapter.Insert(row.Notes, row.ModelNumber, row.DatePurchased,
                     row.Description, row.ItemName, row.ItemType);
  }




Thursday, January 12, 2012

Launch of User Apps by Display App


Launch of User Apps by Display App

I have been thinking about having the first launched application launch the others in the configuration.  The Visual C# interface to Windows functions seems to allow the launch of both local and remote applications – that is, applications on the same PC as the already running application/process and applications that are on another PC.  The older Win32Ada interface only seems to have a function to launch another process on the same PC as the invoking application/process.

I have just modified the Visual C# display application to start the user applications of the local PC.  It first checks if the application is already running.  Then, if not, it starts the application process.  This turned out to be very easy.

Nothing has been done as yet about checking whether the application is running on a remote computer or to start an application on a remote computer as can be specified in the configuration.  The change was only made to the display application since the user applications would need access to recent Windows interface functions.  Therefore, to use this feature the operator needs to start by launching the display application.

There is the possibility of race conditions.  That is, the currently running processes are obtained and then a check is made for each user application in the configuration to determine if it is in the list of running processes.  If not, it is launched.  Therefore, there is the possibility that the user application could be independently launched in the interval between obtaining the list of running processes and programmatically launching it due to not being in the list.  This is highly unlikely to happen since a single operator would be running the set of applications (that is, Windows processes).

This change required three modifications to the display application.  First, an additional configuration field for user applications was extracted – the application executable path and name.  (The PC of the application will also need to be extracted and stored when also do remote PC applications.) 

Second, this application path and name was stored in the internal table (C# struct) of the instance of the Program class as executable as well as adding additional entries for whether the application is running (opened) and the process name (processName) portion of the executable path.

Third, the Remote class Initialize method was changed to invoke a new runRemoteApplications method.  This new method was implemented as

private void runRemoteApplications()
{ // Launch the other applications of the configuration

    // Get the list of process identifiers.
    System.Diagnostics.Process[] aProccesses =
      System.Diagnostics.Process.GetProcesses();

    string processName = "";
    for (int r = 0; r < Program.userAppConfiguration.count; r++)
    {
        if (!Program.userAppConfiguration.opened[r])
         { // check if the application is running
            for (int i = 0; i < aProccesses.Length - 1; i++)
            {
                if (aProccesses[i].ProcessName ==
                    Program.userAppConfiguration.processName[r])
                {
                    processName = Program.userAppConfiguration.processName[r];
                    // application is running via manual start
                    Program.userAppConfiguration.opened[r] = true;
                    break; // exit inner loop
                }
            } // end for
        }

        if (!Program.userAppConfiguration.opened[r])
        { // Start the application
            try
            {
                Program.userAppConfiguration.userProcess[r] =
                  System.Diagnostics.Process.Start(
                    Program.userAppConfiguration.executable[r]);
                Program.userAppConfiguration.opened[r] = true;
            }
            catch { }
        }

    } // end remote user app of configuration search

} // end method runRemoteApplications

This worked on the first attempt, whether or not a user app had already been launched.  So I give myself a pat on the back.

Monday, January 9, 2012

Addition of Handshake to Display Protocol


Addition of Handshake to Display Protocol

Recently I changed the display widgets / windows controls to be disabled in the Definition File (DF) so disabled when the default forms are initially displayed.  Then, when the connection between the user app and the display app occurs, the user app enables the form and hence any controls that were enabled in the Definition File. 

The DF was changed so that widgets/controls that only need the user app that interfaces with the display layer remained initially enabled so that as soon as the parent form was enabled these widgets became active and usable.  However, widgets/controls that had user code that required communication with another user app to fully determine the command to be transmitted to the display were disabled in the DF.  Then, a command to enable them was sent when the user app to user app connection was established.

With these changes, the controls couldn't clicked, etc until the user application(s) could treat the event.

At times, after these changes were made, the form would never be enabled or the widgets that required connection another user app would never be enabled.  This seemed to occur, although perhaps not in every instance, when the form was hidden under the other form.

I have not discovered whether the display layer failed to receive the command or whether Windows failed to change the status of the control or whether Windows changed the status of the control but failed to update the displayed form and its widgets.  Therefore, I have been adding a handshake to the Display Protocol for communication between a user app and its display app layer.

In the display app, when the command has been received and acted upon to update the display, an acknowledge "event" is sent to the sender of the command with the sequence number of the received extended header as data to identify the received command.

In the user app, each command that is to be transmitted is added to a list of sent commands along with the current value of a timeout counter.  Each acknowledge that is received removes the command with the corresponding sequence number from the sent commands list.  A periodic timeout subcomponent was added to the framework remote component to increment the timeout counter in a thread safe manner.  (That is, the periodic timeout thread only updates the timer and then sends an event to wakeup the remote component main thread.  All other processing is down in the remote component main thread so that the two threads are not both using the same data except for the main thread making a copy of the current value of the timeout counter.)

When the remote main thread receives notification that the timeout counter has been updated, it checks the sent command list for old entries.  Any such entries have their sequence number updated to the next value along with the current timeout counter and the command message is resent to the display.  This should take care of any problems due to the display app missing the command.

In case Windows fails to change the enabled status of a form or widget under some circumstances, I will next modify the display app to check that the Windows interface reports the same status for a control after the display app attempted to change it as the command message specified.  Only if all the widgets specified in the command have their updated status will the acknowledge "event" message be sent to the user application.  Therefore, if Windows doesn't report back the new status the user app will resend the command.

After this second change, if the display ever doesn't match the commanded state then I think the problem would be that Windows updated the state but failed to update the displayed form.