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);
  }