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:
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.
Change the DataAdapter
object to be an
OleDbDataAdapter rather than a
SqlDataAdapter:
OleDbDataAdapter DataAdapter = new OleDbDataAdapter (commandString, connectionString);
Replace the System.Data.SqlClient
with the
using
statement for the OleDb
namespace:
using System.Data.OleDb;
This design pattern continues throughout working with the two managed
providers; for every object whose class name begins with
Sql
, there is a corresponding class beginning with
OleDb
. Example 11-4 is the
complete OLE DB version of Example 11-3.
Example 11-4. Using ADO.NET with Access
using System; using System.Collections; using System.ComponentModel; using System.Data;using System.Data.OleDb;
using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; namespace SimpleBugListBoxAccess { /// <summary> /// Summary description for WebForm1. /// </summary> public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.ListBox lbBugs; public WebForm1( ) { Page.Init += new System.EventHandler(Page_Init); } private void Page_Load(object sender, System.EventArgs e) {// connect to the Bugs database
string connectionString =
"provider=Microsoft.JET.OLEDB.4.0; "
+ "data source = c:\simpleBugListAccessDB.mdb";
// get records from the Bugs table string commandString = "Select BugID, Description from dbo_Bugs";// create the dataset command object
// and the DataSet
OleDbDataAdapter dataAdapter =
new OleDbDataAdapter (
commandString, connectionString);
DataSet dataSet = new DataSet( ); // fill the dataset object dataAdapter.Fill(dataSet,"Bugs"); // Get the one table from the DataSet DataTable dataTable = dataSet.Tables[0]; // for each row in the table, display the info foreach (DataRow dataRow in dataTable.Rows) { lbBugs.Items.Add( dataRow["BugID"] + ": " + dataRow["Description"] ); } } private void Page_Init(object sender, EventArgs e) { InitializeComponent( ); } #region Web Form Designer generated code private void InitializeComponent( ) { this.Load += new System.EventHandler(this.Page_Load); } #endregion } }
Before you run this program, edit the description of the first bug to include the word Access; this will help you ensure that you are looking at the correct data. The output, which is shown in Figure 11-7, is identical to that from the previous example (except for the change you’ve made to the description of the first bug).
The OLE DB managed provider is more general than the SQL managed provider and can, in fact, be used to connect to SQL Server as well as to any other OLE DB object. Because the SQL Server provider is optimized for SQL Server, it will be more efficient to use the SQL Server-specific provider when working with SQL Server. In time, there will be any number of specialized managed providers available.
3.144.17.128