Interfacing to an Access Database via Visual C# 2010 Express via OleDb Classes

Interfacing to an Access Database via Visual C# 2010 Express via OleDb Classes

After doing the example of the previous post that used Microsoft Visual C# 2010 Express generated classes to interface to an existing Access 2000 database, I decided to repeat the example using the C# OleDb classes.  Please refer to that post for comments about use of C# to interface to an Access database.

One thing to note however is, that while code to interface with an Access database can't be created with Visual C# 2010 Express on a 64-bit PC, it can be on a 32-bit PC and the executable can then be copied to a 64-bit PC running under Windows 7 and used to interface with an Access database.

In this example, the classes aren't built with a knowledge of the existing database but do interface to the same database as the example of the previous post.

This example has been updated to recognize when a CategoryID of 3 cannot be found to be displayed and updated. 

Also, since it hasn't been created from the actual database, it contains code to do the increment of the CategoryID for the Inserted row.

Example Code

static void ReadUpdateReadwithOleDbClasses()

{
    // 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 OleDb C# classes.
    //
    // 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 the data table key 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.

    // Connection, query, insert strings

    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
        "Data Source=C:\\source\\C#\\JETConsoleTry2\\JETConsoleTry2\\db1.mdb";
    string selectQuery = "SELECT CategoryID, Notes, ModelNumber, " +
        "DatePurchased, Description, ItemName, ItemType FROM HouseholdInventory";

    // General class instantiations

    OleDbConnection dbConn = new OleDbConnection(connString);
    OleDbCommand queryCmd = new OleDbCommand(selectQuery, dbConn);
    OleDbDataAdapter queryAdapter = new OleDbDataAdapter(queryCmd);
    DataSet tableDataSet = new DataSet(connString);
    tableDataSet.DataSetName = "DataSet";

    // Open a connection to the database

    dbConn.Open(); 

    // Get/Fill the database into the dataset table

    queryAdapter.Fill(tableDataSet, "HouseholdInventory");

    // Display the original data and capture last current CategoryID value

    // and the current value of Notes for a particular row so that it can
    // be updated with a different value.
    int rows = tableDataSet.Tables["HouseholdInventory"].Rows.Count;
    Console.WriteLine("original number of table rows {0}", rows.ToString());

    Int32 categoryIDValue = 0;

    string notes3 = "";

    OleDbDataReader queryReader = queryCmd.ExecuteReader();

    try
    {
        while (queryReader.Read())
        {
            Console.WriteLine("Category ID {0}",
                              queryReader["CategoryID"].ToString());
            Console.WriteLine("Notes {0}", queryReader["Notes"].ToString());
            Console.WriteLine("Model Number {0}",
                               queryReader["ModelNumber"].ToString());
            Console.WriteLine("Date Purchased {0}",
                              queryReader["DatePurchased"].ToString());
            Console.WriteLine("Description {0}",
                              queryReader["Description"].ToString());
            Console.WriteLine("Item Name {0}", queryReader["ItemName"].ToString());
            Console.WriteLine("Item Type {0}", queryReader["ItemType"].ToString());
            if (Convert.ToInt32(queryReader["CategoryID"].ToString()) >
                categoryIDValue)
            {
                categoryIDValue =
                    Convert.ToInt32(queryReader["CategoryID"].ToString());
            }
            if (queryReader["CategoryID"].ToString() == "3")
            {
                notes3 = queryReader["Notes"].ToString();
            }
        }
    } // end try
    finally
    {   // always call Close when done reading
        queryReader.Close();
    } // end finally

    // Set new value to be used to update Notes.

    string newNote;
    if (notes3 == "updated note for 3")
    {
        newNote = "Note 3";
    }
    else
    {
        newNote = "updated note for 3";
    }

    // Add a new row to the dataset table and the data base.

    DataRow insertRow = tableDataSet.Tables["HouseholdInventory"].NewRow();
    insertRow["CategoryID"] = categoryIDValue + 1;
    insertRow["Notes"] = "new row note";
    insertRow["ModelNumber"] = "45123";
    insertRow["DatePurchased"] = System.DateTime.Now;
    insertRow["Description"] = "inserted item";
    insertRow["ItemName"] = "hammer";
    insertRow["ItemType"] = "tool";
    UpdateInsertOleDb(dbConn, tableDataSet, insertRow);

    // Display the data following the Insert.

    rows = tableDataSet.Tables["HouseholdInventory"].Rows.Count;
    Console.WriteLine(" ");
    Console.WriteLine("number of table rows after insert {0}", rows.ToString());
    queryReader = queryCmd.ExecuteReader();
    try
    {
        while (queryReader.Read())
        {
            Console.WriteLine("Category ID {0}",
                              queryReader["CategoryID"].ToString());
            Console.WriteLine("Notes {0}", queryReader["Notes"].ToString());
            Console.WriteLine("Model Number {0}",
                              queryReader["ModelNumber"].ToString());
            Console.WriteLine("Date Purchased {0}",
                              queryReader["DatePurchased"].ToString());
            Console.WriteLine("Description {0}",
                              queryReader["Description"].ToString());
            Console.WriteLine("Item Name {0}", queryReader["ItemName"].ToString());
            Console.WriteLine("Item Type {0}", queryReader["ItemType"].ToString());
        }
    } // end try
    finally
    {   // always call Close when done reading
        queryReader.Close();
    } // end finally

    // Query for row with CategoryID of 3 and capture and display values.

    string findQuery = "SELECT CategoryID, Notes, ModelNumber, " +
        "DatePurchased, Description, ItemName, ItemType " +
        "FROM HouseholdInventory " +
        "WHERE CategoryID = 3";
    OleDbCommand findCmd = new OleDbCommand(findQuery, dbConn);
    DataRow row = tableDataSet.Tables["HouseholdInventory"].NewRow();

    Console.WriteLine(" ");

    Console.WriteLine("Lookup for CategoryID of 3");
    bool found = false;
    queryReader = findCmd.ExecuteReader();
    while (queryReader.Read())
    {
        found = true;
        Console.WriteLine("CategoryID of 3 table row found");
        row["CategoryID"] = queryReader["CategoryID"];
        row["Notes"] = queryReader["Notes"];
        row["ModelNumber"] = queryReader["ModelNumber"];
        row["DatePurchased"] = queryReader["DatePurchased"];
        row["Description"] = queryReader["Description"];
        row["ItemName"] = queryReader["ItemName"];
        row["ItemType"] = queryReader["ItemType"];
        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());
    }
    queryReader.Close();
    if (!found)
    {
        Console.WriteLine("CategoryID of 3 table row NOT found");
    }

    // Modify the Notes and Date Purchased values and Update.

    if (found)
    {  
        row["Notes"] = newNote;
        row["DatePurchased"] = System.DateTime.Now;
        UpdateOleDb(dbConn, tableDataSet, row);
    }

    // Display data once again.

    Console.WriteLine(" ");
    Console.WriteLine("number of table rows after update {0}", rows.ToString());
    queryReader = queryCmd.ExecuteReader();
    try
    {
        while (queryReader.Read())
        {
            Console.WriteLine("Category ID {0}",
                              queryReader["CategoryID"].ToString());
            Console.WriteLine("Notes {0}", queryReader["Notes"].ToString());
            Console.WriteLine("Model Number {0}",
                              queryReader["ModelNumber"].ToString());
            Console.WriteLine("Date Purchased {0}",
                              queryReader["DatePurchased"].ToString());
            Console.WriteLine("Description {0}",
                              queryReader["Description"].ToString());
            Console.WriteLine("Item Name {0}", queryReader["ItemName"].ToString());
            Console.WriteLine("Item Type {0}", queryReader["ItemType"].ToString());
        }
    } // end try
    finally
    {   // always call Close when done reading
        queryReader.Close();
    } // end finally

    // Close the connection

    dbConn.Close();

    // Allow viewing of console.

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

} // end method ReadUpdateReadwithOleDbClasses

private static void UpdateOleDb(OleDbConnection conn, DataSet table,

                                DataRow row)
{
    // This method Updates a current row to the dataset and to the
    // database.

    table.Tables["HouseholdInventory"].Rows.Add(row);

    string updateSet = "UPDATE HouseholdInventory SET Notes=\"" + row["Notes"] +

        "\", ModelNumber=\"" + row["ModelNumber"] +
        "\", DatePurchased=\"" + row["DatePurchased"] +
        "\", Description=\"" + row["Description"] +
        "\", ItemName=\"" + row["ItemName"] +
        "\", ItemType=\"" + row["ItemType"] + "\"" +
        " WHERE CategoryID=" + 3;
    OleDbCommand updateCmd = new OleDbCommand(updateSet, conn);
    updateCmd.ExecuteNonQuery();

} // end method UpdateOleDb

private static void UpdateInsertOleDb(OleDbConnection conn, DataSet table,

                                      DataRow row)
{
    // This method Updates a new row to the dataset and Inserts the row
    // into the database.

    table.Tables["HouseholdInventory"].Rows.Add(row);

    string insertInto = "INSERT INTO HouseholdInventory(CategoryID, Notes, " +

       "ModelNumber, DatePurchased, Description, ItemName, ItemType) " +
       "VALUES(?, ?, ?, ?, ?, ?, ?)";
    OleDbCommand insertCmd = new OleDbCommand(insertInto, conn);
    insertCmd.Parameters.Add("CategoryID", OleDbType.Integer).Value =
        row["CategoryID"];
    insertCmd.Parameters.Add("Notes", OleDbType.VarChar).Value = row["Notes"];
    insertCmd.Parameters.Add("ModelNumber", OleDbType.VarChar).Value =
        row["ModelNumber"];
    insertCmd.Parameters.Add("DatePurchased", OleDbType.Date).Value =
        row["DatePurchased"];
    insertCmd.Parameters.Add("Description", OleDbType.VarChar).Value =
        row["Description"];
    insertCmd.Parameters.Add("ItemName", OleDbType.VarChar).Value =
        row["ItemName"];
    insertCmd.Parameters.Add("ItemType", OleDbType.VarChar).Value =
        row["ItemType"];
    insertCmd.ExecuteNonQuery();

} // end method UpdateInsertOleDb