TECHNOLOGIES: Microsoft Access
ASP.NET and Access: Part I
Connect to Microsoft's "other" database with ASP.NET.
By Wayne S. Freeze
Recently a reader reminded me that SQL Server is not Microsoft's only database system. For the next several articles, I'm going to compare and contrast how Access and SQL Server work with ADO.NET. In this article, you will learn how to connect to an Access database using OLEDB.
Technically, Access is Microsoft's desktop database tool and is part of the Microsoft Office System. Access consists of two main components - the development environment and the database engine. The development environment is used to create desktop applications, and the database engine contains your data. Access supports two different database engines: Jet and MSDE. Jet is the classic Access database engine, dating back to the earliest versions of Access, and you can find it on nearly every Windows computer (even if Office isn't installed). MSDE, on the other hand, is a variation of SQL Server that is geared for the desktop environment.
Because MSDE essentially is SQL Server, all of SQL Server's programming tools (such as the SqlClient namespace) work the same way with MSDE. Because the Jet database is a completely different product, however, you need to use a completely different set of programming tools. Over the next few articles, I'll focus on programming the Jet database.
Access and OleDb
Over the years, Microsoft has created several different technologies to allow an application program to connect to a database engine. Prior to the .NET Framework, the most common were ODBC (Open Data Base Connectivity) and OLEDB (OLE Data Base). Although the .NET Framework introduced a new approach for connecting to SQL Server, it also includes objects that support both ODBC and OLEDB. Because OLEDB generally outperforms ODBC, it is the preferred approach for communicating with an Access database.
You can find the classes needed to access an OLEDB database in the System.Data.OleDb namespace. If you're familiar with the classes used to access SQL Server found in the System.Data.SqlClient namespace, you'll already know most of the information you need to create an ASP.NET program for an Access database. All of the most commonly used classes found in System.Data.SqlClient also are found in System.Data.OleDb and they work the classes in the System.Data namespace in the same way.
To use System.Data.OleDb, you need to import the namespace in your ASP.NET application:
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
Connect to a Jet Database
To communicate with your Access database, you need to create a connection string that identifies the name of the database, the OLEDB database provider to be used, and any security information needed to access the database. For example, you can use this connection string to communicate with the Music database:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\aspnetPRO\Music.mdb
The Provider keyword specifies the name of the OLEDB database provider, which in this case is the Microsoft Jet provider. The Data Source keyword specifies the physical location of the database.
Notice that the location of the Access database file need not be in a Web-accessible directory. In fact, you should avoid putting the database where it could be accessed from a Web browser. Instead, the only critical issue is that the ASPNET account needs read/write access to the physical file.
If your Access database includes security, you will need to include this information in the connection string. Workgroup security information is specified with the User Id and Password keywords:
You also can specify a database password using the Jet OLEDB:Database Password keyword:
Jet OLEDB:Database Password=mypass
In addition to specifying the Database Password, you can specify a number of other parameters that should be used to open the database using the Jet OLEDB: keyword. (For a full list of these parameters, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/pg_ado_providers_5.asp.)
A Simple Test
Figure 1 shows a simple Web page that displays a series of rows from an Access database using a DataGrid. Unless you look at the code, you can't tell whether the data came from an SQL Server database or an Access database.
Figure 1. Without looking at the code, you can't tell whether the data displayed in the DataGrid came from an SQL Server database or an Access database.
The code used to load the DataGrid in this example (see Figure 2) is quite similar to the code you saw run against SQL Server. In fact, there are only two significant differences. First, the references to SqlConnection, SqlCommand, and SqlDataAdapter are replaced with references to OleDbConnection, OleDbCommand, and OleDbDataAdapter. Second, because the Access database doesn't support stored procedures (more about this next time), you need to enter into your program the explicit SQL statement.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim ds As DataSet
Dim ConnectionStr As String = _
Dim adpt As OleDbDataAdapter
Dim conn As OleDbConnection
Dim cmd As OleDbCommand
Dim SqlCmd As String = _
"Select CustomerId, Name, Street, City, " & _
" State, ZipCode, Phone, EMailAddress" & _
" From Customers " & _
" Order By CustomerId"
If Not IsPostBack Then
ds = New DataSet()
conn = New OleDbConnection(ConnectionStr)
cmd = New OleDbCommand(SqlCmd, conn)
cmd.CommandType = CommandType.Text
adpt = New OleDbDataAdapter(cmd)
Session("Customers") = ds.Tables("Customers").DefaultView
DataGrid1.DataSource = Session("Customers")
Figure 2. This code displays a list of customers from an Access database using the OleDb Data Provider.
The Page_Load event begins by declaring some local variables. The ConnectionStr variable is initialized from the web.config file and looks like this:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\aspnetPRO\Music.mdb
The SqlCmd variable contains the SQL Select statement that will retrieve the desired columns from the database.
When the Page_Load event fires initially, a new DataSet object is created along with new OleDbConnection and OleDbCommand objects. Note that the CommandType property is now CommandType.Text to reflect the fact that the explicit SQL statement was used when the command object was created.
Next, a new OleDbDataAdapter object is instantiated and used to fill the DataSet object. Then an object reference to the DataTable's default view is saved as a Session object and bound to the DataGrid control. Finally, the DataBind method is called to generate the DataGrid on the Web page.
The sample code in this article is available for download.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send e-mail to mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.