Connecting to Oracle or Access from ASP.NET 2.0

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. In this excerpt from Professional ASP.NET 2.0 Special Edition Chapter 11 "Data Binding in ASP.NET 2.0," we'll 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.

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.

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.

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 article is adapted from Professional ASP.NET 2.0 Special Edition by Devin Rader and Bill Evjen (Wrox, 2006, ISBN: 0-7645-7135-4), from Chapter 11, "Data Binding in ASP.NET 2.0."

Copyright 2006 by WROX. All rights reserved. Reproduced here by permission of the publisher.



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Java developers know that testing code changes can be a huge pain, and waiting for an application to redeploy after a code fix can take an eternity. Wouldn't it be great if you could see your code changes immediately, fine-tune, debug, explore and deploy code without waiting for ages? In this white paper, find out how that's possible with a Java plugin that drastically changes the way you develop, test and run Java applications. Discover the advantages of this plugin, and the changes you can expect to see …

  • Live Event Date: September 16, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you starting an on-premise-to-cloud data migration project? Have you thought about how much space you might need for your online platform or how to handle data that might be related to users who no longer exist? If these questions or any other concerns have been plaguing you about your migration project, check out this eSeminar. Join our speakers Betsy Bilhorn, VP, Product Management at Scribe, Mike Virnig, PowerSucess Manager and Michele …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds