Using Microsoft Access with ASP.NET

{outline||Step <1> - } To use a Microsoft Access 2007 database from an ASP.NET page, use the following procedure.

Configuring the Application

Make the following entries in your web.config file. Obviously, you will use the correct file name for the DatabaseVirtualFile entry, and change the name of the connection string to something more meaningful.

<configuration>
    <appSettings>
        <add key="DatabaseVirtualFile" value="../_database/MyDb.accdb"/>
    </appSettings>
    <connectionStrings>
        <add name="MyConnection_release" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={physical-file}"/>
        <add name="MyConnection_debug" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyDb.accdb"/>
    </connectionStrings>
...
</configuration>

Generating the Connection String

Use the following C# code to generate the connection string to the Access 2007 database.

private string GetConnectionString()
{
    string result = "";

    if (Debugger.IsAttached)
    {
        result = ConfigurationManager.ConnectionStrings["MyConnection_debug"].ConnectionString;
    }
    else
    {
        result = ConfigurationManager.ConnectionStrings["MyConnection_release"].ConnectionString;
        string dbFile = ConfigurationManager.AppSettings["DatabaseVirtualFile"];
        string file = Server.MapPath(dbFile);
        result = result.Replace("{physical-file}", file);
    }
    return result;
}

Calling a Query

Use the following C# code as an example of how to call a query in the database. The key elements are

  1. Use the System.Data.OleDb objects
  2. A MS-Access query object is seen as a stored procedure to the OleDb assembly.

using System.Data.OleDb;

...

public static string GetNodes(int parentId)
{
    //- Inits ---------------------------------------------------------------------------------
    string result = "";
    OleDbConnection conn = new OleDbConnection(_connectionString);
    conn.Open();

    //- Call GetNodes Query -------------------------------------------------------------------
    OleDbCommand cmd = new OleDbCommand("GetNodes", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("ParentId?", parentId);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    NodesDataSet.NodesDataTable t = new NodesDataSet.NodesDataTable();
    da.Fill(t);

    // other code here

    //- Clean Up ------------------------------------------------------------------------------
    cmd.Dispose();
    conn.Close();
    conn.Dispose();
    return result;
}