Programming ASP .NET

Managed Providers

The previous example used one of the two managed providers initially available with ADO.NET: the SQL Managed provider. The SQL Managed provider is optimized for SQL Server, but it is restricted to working with SQL Server databases. The more general solution is the OLE DB managed provider, which will connect to any OLE DB provider, including Access.

You can rewrite Example 11-3 to work with theBugs database using Access rather than SQL Server with just a few small changes. First, of course, you need to create a new Access database. Name the new database SimpleBugListBoxAccessDB. Example 11-4 assumes you will save your database to the root directory on your C drive, but you may save it anywhere else that is convenient for you as long as you adjust the connection string.

Use the File → Import menu option in Access to import the data from the SQL database. This will create tables in Access that reflect the structure and content of the data in the SQL database. Notice that the Bugs database is now named dbo_Bugs in Access.

Create a new ASP web application project named SimpleBugListboxAccess and once again drag a list box onto the form and name it lbBugs. Copy the code from Example 11-3, but make the following changes:

1. Change the connection string to:

   string connectionString = 
       "provider=Microsoft.JET.OLEDB.4.0; "
       + "data source = c:\\simpleBugListAccessDB.mdb";

   This will connect to the database you just created.

2. Change the DataAdapter object to be an OleDbDataAdapter ...