Connecting to Oracle or Access from ASP.NET 2.0

Connecting to Oracle or Access from ASP.NET 2.0

By Devin Rader and Bill Evjen

The SqlDataSource control is the data source control to use if your data is stored in a SQL Server, SQL Server Express, Oracle Server, ODBC data source, OLE DB data source, or Windows SQL CE Database. The control provides an easy-to-use wizard that walks you through the configuration process, or you can modify the control manually by changing the control attributes directly in Source view. Here, we show you how to use this control to connect to either Oracle and we'll finish with a short introduction to connecting to Access databases with the similar AccessDataSource Control.

Using the SqlDataSource with Oracle

Just as you would use the SqlDataSource control to connect to Microsoft's SQL Server, you can also use this same control to connect to other databases that might be contained within your enterprise. One popular enterprise-level database is one of Oracle's databases, such as the Oracle 10g database.

To use the SqlDataSource control with Oracle, start by dragging and dropping the SqlDataSource control onto your page's design surface. Using the SqlDataSource control's smart tag, you are then able to configure your data source by clicking the Configure Data Source link.

In configuring your data source, you are going to want to create a new connection to your Oracle database. The initial Add Connection dialog is presented here in Figure 1.

By default, the Data Source is configured to work with a SQL Server database. You are able to change this default setting by simply pressing the Change button. This will launch a new dialog that allows you to select Oracle as an option. This dialog is presented here in Figure 2.

Selecting an Oracle database will then modify the Add Connection dialog so that it is more appropriate for the job. This is presented here in Figure 3.

From the Add Connection dialog, you can add the name of the Oracle database that you are connecting to in the Server name text box. The name you place here is the name of the database that is held in the tnsnames.ora file. This file is put into place after you install the Oracle Client on the server that will be making the connection to Oracle. You will find this file typically at C:\Oracle\product\10.1.0\Client_1\NETWORK\ADMIN. An example of a database entry in this .ora file is presented here:

MyDatabase =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = MyDatabase)
      (SERVER = DEDICATED)
    )
  )

After the reference to the database, you can then use your database username and password and then simply use the SqlDataSource control as you would if you were working with SQL Server. Once the configuring of the SqlDataSource is complete, you will then find a new connection to Oracle in your <connectionStrings> section of the web.config (if you chose to save the connection string there through the configuration process). An example of this is presented here:

   <connectionStrings>
 <add name="ConnectionString"
  connectionString="Data Source=MyDatabase;User
     ID=user1;Password=admin1pass;Unicode=True"
  providerName="System.Data.OracleClient" />
</connectionStrings>

AccessDataSource Control

Although you can use the SqlDataSource to connect to Access databases, ASP.NET also provides a special AccessDataSource control. This control gives you specialized access to Access databases using the Jet Data provider, but it still uses SQL commands to perform data retrieval because it is derived from the SqlDataSource.

Despite its relative similarity to the SqlDataSource control, the AccessDataSource control has some specialized parts. First, the control does not require you to set a ConnectionString property. Instead the control uses a DataFile property to allow you to directly specify the Access .mdb file you want to use for data access.

A side effect of not having the ConnectionString property is that the AccessDataSource cannot connect to password-protected databases. If you need to access a password-protected Access database, you can use the SqlDataSource control, which allows you to provide the username and password as part of the connection string.

Additionally, because the AccessDataSource uses the System.Data.OleDb to perform data access, the order of parameters matters. You need to verify that the order of the parameters in any Select, Insert, Update, or Delete parameters collection matches the order of the parameters in the SQL statement.

This excerpt is from Chapter 11, "Data Binding in ASP.NET 2.0," of Professional ASP.NET 2.0 Special Edition, which is the expanded edition of the best-selling ASP.NET 2.0 book, Professional ASP.NET 2.0. This new expanded Special Edition adds approximately 300 pages of new content, plus updates throughout the book. Bill Evjen (St. Louis, MO) is one of the most active proponents of the .NET technologies. He has been involved with .NET since 2000 and has since become the founder and president of the International .NET Association representing nearly 500,000 members worldwide. In addition to working in the .NET world, Bill is a Technical Director serving in the office of the Chief Scientist for the international news and financial services company Reuters. Bill is the lead co-author of Professional ASP.NET 2.0 Special Edition. Devin Rader is an Infragistics Technology Evangelist and is responsible for writing Infragistics reference applications and .NET technology articles, as well as the worldwide delivery of Infragistics' technology demonstrations. Devin helped found the St. Louis .NET Users Group and is a frequent speaker at community events nationwide. Devin writes the monthly ASP. NET Tips & Tricks column for ASP.NET Pro magazine and is a co-author of Professional ASP.NET 2.0 Special Edition. Copyright 2006 Wiley Publishing Inc, reprinted with permission, all rights reserved.