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