Jasinski Technical Wiki

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

PoweredBy

Using Microsoft Access with ASP.NET

RSS
Modified on Thu, Jan 15, 2009, 2:07 PM by Administrator Categorized as ASP·NET Web Forms
{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;
}


ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.