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 and2. 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, ando 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 Project3) 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 rowsConsole.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);
}
