How to: Connect to an Oracle Database Using the SqlDataSource Control

  • Article

You can use the SqlDataSource control to connect to an Oracle database by storing connection string information in the Web application's Web.config file and then referencing the connection string from your SqlDataSource control.

NoteNote

N:System.Data.OracleClient managed provider requires Oracle client software version 8.1.7 or higher to be installed on the computer where the ASP.NET Web page runs.

To configure a connection string for Oracle in the Web.config file

  1. Open the Web.config file in the root directory of your ASP.NET application. If you do not have a Web.config file, create one.

  2. In the Configuration element, add a ConnectionStrings element if one does not already exist.

  3. Create an add element as a child of the ConnectionStrings element, defining the following attributes:

    • name   Set the value to the name that you want to use to reference the connection string.

    • connectionString   Assign the connection string that is required to connect to the Oracle database. For information about the connection string values you must use, contact your database administrator.

    • providerName   Assign the value "System.Data.OracleClient", which specifies that ASP.NET should use the ADO.NET System.Data.OracleClient provider when making a connection with this connection string.

      Your completed add element might look like the following:

      <add name="OracleConnectionString"
  connectionString="Data Source=OracleServer1;Persist 
    Security Info=True;Password="******";User ID=User1"
  providerName="System.Data.OracleClient" />
      NoteNote

      If you include explicit authentication information in a connection string, you should encrypt the ConnectionStrings section of the Web.config file. For details, see Encrypting Configuration Information Using Protected Configuration.

  4. Save the Web.config file.

To reference the Oracle connection string from a SqlDataSource control

  1. In the page in which you want to connect to the Oracle database, add a SqlDataSource control.

  2. In the SqlDataSource control, specify the following properties:

    • SelectCommand   Set to the SQL select statement for retrieving data, as in the following example:

      SelectCommand="select * from customers"

    • ConnectionString   Set to the name of the connection string that you created in the Web.config file.

    • ProviderName   Set to the name of the provider that you specified in the Web.config file.

      NoteNote

      You can also define UpdateCommand, DeleteCommand, and InsertCommand properties. For more information, see SqlDataSource control.

    The SqlDataSource control declaration will be similar to the following:

    <asp:SqlDataSource 
    ID="SqlDataSource1" Runat="server" 
    SelectCommand="select * from products"
    ConnectionString="<%$ ConnectionStrings:OracleConnectionString%>"
    ProviderName="<%$ ConnectionStrings:AppConnectionString1.ProviderName %>" />

See Also

Concepts

Data Source Controls Overview

Other Resources

Using the .NET Framework Data Provider for Oracle