Click here to Skip to main content
Click here to Skip to main content
Go to top

Connecting to Oracle or Access from ASP.NET 2.0

, 16 Aug 2007
Rate this:
Please Sign up or sign in to vote.
Wrox authors Devin Rader and Bill Evjen show the ASP.NET 2.0 SqlDataSource and AccessDataSource controls

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.

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
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
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.

Bill Evjen, Scott Hanselman, Devin Rader, Farhan Muhammad, Srinivasa Sivakumar: Professional ASP.NET 2.0 Special Edition

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Wrox Publishing

United States United States
Wrox Press, established in 1992 to publish books for computer programmers, is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems. Wrox's unique author-editorial process delivers the best and most useful information you need in the timeliest manner.
 
Remember, Programmer to Programmer is not just our slogan; it's the way we write our books.
 
Wrox Press is proudly part of the John Wiley & Sons stable of educational and reference books.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 16 Aug 2007
Article Copyright 2007 by Wrox Publishing
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid