Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

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