Connecting to Databases in ASP.NET

  • Article

ASP.NET gives you flexibility in how you connect to databases. A simple way is to use data source controls, which allow you to encapsulate data access in a control that you can configure with connection and query information. Alternatively, you can write code to perform data access yourself using ADO.NET classes.

Using Data Source Controls in an ASP.NET Web Page

To connect to a database using a data source control, you do the following:

  • Determine the type of data source control you need. For example, the ObjectDataSource control works with a middle-tier business object to retrieve and modify data, while the SqlDataSource control allows you to supply a connection to a data source and SQL statements to retrieve and modify data.

  • When using the SqlDataSource control, determine what provider you need. A provider is a class that communicates with a specific type of database. The default is the System.Data.SqlClient provider, which connects to a Microsoft SQL Server database.

  • Add the data source control to the page and set its properties for data access. For example, the ObjectDataSource control requires the type of the middle-tier business object and one or more methods to query or modify data. The SqlDataSource control requires a connection string that includes the information that the provider needs to open a specific database and one or more SQL commands to query or modify data.

    You can specify provider and connection information as individual properties of the SqlDataSource control, or you can define provider and connection string information centrally in the Web application's Web.config file. Storing connection information in the Web.config file allows you to reuse the information with multiple data control instances. It also helps you secure the connection string information using access control lists (ACLs) and encryption. For more information, see "Connection Strings" later in this topic.

Providers

An ADO.NET provider is a class that can communicate with a specific type of database or data store. For example, one provider might be able to communicate with a SQL Server database, and another provider might be able to communicate with an Oracle database. The providers included with the .NET Framework are:

  • The .NET Framework Data Provider for SQL Server in the System.Data.SqlClient namespace. This provider is the default for the SqlDataSource control; if you are connecting to a SQL Server database using the SqlDataSource control, you do not need to explicitly specify a provider.

  • The .NET Framework Data Provider for OLE DB in the System.Data.OleDb namespace.

  • The .NET Framework Data Provider for ODBC in the System.Data.Odbc namespace.

  • The .NET Framework Data Provider for Oracle in the System.Data.OracleClient namespace.

You can specify the provider in Web.config file as part of a connection string, or as a property of an individual data source control on a page.

Connection Strings

A connection string provides the information that a provider needs to communicate with a particular database. You can store a connection string in the Web.config file and reference the configuration entry in a data source control.

Depending on the provider, a connection string usually supplies the server or location of the database server, the particular database to use, and authentication information. As with the provider, you can indicate the connection string within the Web.config file or as a property of an individual data source control on a page.

Connection String Storage

It is good practice to place connection strings in the Web.config file. Within the <configuration> Element element, you can create a child element named <connectionStrings> and place your connection strings there, as shown in this example:

<connectionStrings>
  <add name="NorthindConnectionString" 
   connectionString=" Server=MyDataServer;Integrated Security=SSPI;Database=Northwind;"
   providerName="System.Data.SqlClient" />
</connectionStrings>

In this example, the name and provider are both supplied. Any data source control on any page in the application can reference this connection string entry. An advantage to storing connection string information in the Web.config file is that you can easily change the server name, database, or authentication information without editing individual Web pages. Additionally, you can secure the connection string using encryption. For more information, see How to: Secure Connection Strings When Using Data Source Controls.

Data Source Controls

Data source controls provide data services, including retrieving and modifying data, that can be used by other data-bound Web server controls such as the GridView, FormView, and DetailsView controls. A data source control such as SqlDataSource encapsulates all the elements required to connect to a database (provider, connection string, and query) to retrieve or manipulate data. For example, the following SqlDataSource control is configured to connect to a database and read all records from the Customers table:

<asp:SqlDataSource ID="SqlDataSource1" Runat="server" 
  SelectCommand="Select * from Customers"
  ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" />

In this example, the provider name and connection string are stored in the Web.config file, while the SQL query is configured as a property of the data source control.

Performing Data Access in Code

If it is not practical in your application to use data source controls, you can code data access yourself using ADO.NET classes. You might code data access yourself if you have complex requirements that are not met by using data source controls, or if you want to create a separate component that performs data access outside of your Web pages.

See Also

Tasks

How to: Connect to an Access Database Using the SqlDataSource Control
How to: Connect to an ODBC Database Using the SqlDataSource Control
How to: Bind to Data in a Templated Control

Concepts

ASP.NET Data Access Overview