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

  • Article

You can use the SqlDataSource control to connect to any database that supports Open Database Connectivity (ODBC) by storing connection string information in the Web application's Web.config file and then referencing the connection string from your SqlDataSource control. This topic shows you how to connect a SqlDataSource control to any ODBC data source.

To configure a connection string for ODBC 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 by the database you are connecting to, setting the appropriate driver, server name, and authentication information. Each ODBC database uses different connecting string values. For information about which connection string values you must use, contact your database administrator.

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

    The connection string configuration will be similar to the following example. In this example, the connection string values are used to connect to a database by using an ODBC provider. The connection string used in the example is an invented string for an example database.

    <configuration>
  <connectionStrings>
    <add       name="ODBCDataConnectionString"       connectionString="Driver=ODBCDriver;server=ODBCServer;"      providerName="System.Data.Odbc"    />
  </connectionStrings>
</configuration>

  4. Save the Web.config file and close it.

To reference the ODBC connection string from a SqlDataSource control

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

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

    • SelectCommand   Set to the query that you want to execute for this data control. The syntax of the query depends on the data source you are accessing.

    • 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 for the corresponding connection string.

      NoteNote

      You can also define UpdateCommand, InsertCommand, and DeleteCommand properties. The SqlDataSource control supports commands and functionality not covered here. For details see SqlDataSource.

    The following example shows a SqlDataSource control configured to access an ODBC data source. In the example, the SelectCommand property is set to a SQL query.

    <asp:SqlDataSource 
  ID="SqlDataSource1" 
  Runat="server" 
  SelectCommand="Select * From Products"
  ConnectionString="<%$ ConnectionStrings:ODBCDataConnectionString %>"
  ProviderName="<%$ ConnectionStrings:ODBCDataConnectionString.ProviderName %>" />

    You can now bind other controls, such as the GridView control, to the SqlDataSource control.

See Also

Concepts

Data Source Controls Overview