Binding to Databases

  • Article

Web applications commonly display data that comes from a relational database such as Microsoft SQL Server, Microsoft Access, Oracle, or an OLEDB or ODBC data store. To simplify the task of binding a control to data from a database, ASP.NET provides the SqlDataSource control.

The SqlDataSource Control

The SqlDataSource control represents a direct connection to a database in a Web application. Data-bound controls such as the GridView, DetailsView, and FormView controls can use the SqlDataSource control to retrieve and modify data automatically. You specify commands to select, insert, update, and delete data as part of the SqlDataSource control, and the control performs these operations automatically. You do not need to write code (for example, ADO.NET code that uses classes in the System.Data namespace) to create a connection and specify commands to query and update a database.

The following code example shows a GridView control bound to a SqlDataSource control to retrieve, update, and delete data.

<%@ Page language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>GridView Edit Example</title>
</head>
<body>
    <form id="form1" runat="server">
        
      <h3>GridView Edit Example</h3>

      <!-- The GridView control automatically sets the columns     -->
      <!-- specified in the datakeynames property as read-only.    -->
      <!-- No input controls are rendered for these columns in     -->
      <!-- edit mode.                                              -->
      <asp:gridview id="CustomersGridView" 
        datasourceid="CustomersSqlDataSource" 
        autogeneratecolumns="true"
        autogeneratedeletebutton="true"
        autogenerateeditbutton="true"
        datakeynames="CustomerID"  
        runat="server">
      </asp:gridview>
            
      <!-- This example uses Microsoft SQL Server and connects  -->
      <!-- to the Northwind sample database. Use an ASP.NET     -->
      <!-- expression to retrieve the connection string value   -->
      <!-- from the Web.config file.                            -->
      <asp:sqldatasource id="CustomersSqlDataSource"  
        selectcommand="Select [CustomerID], [CompanyName], [Address], [City], [PostalCode], [Country] From [Customers]"
        updatecommand="Update Customers SET CompanyName=@CompanyName, Address=@Address, City=@City, PostalCode=@PostalCode, Country=@Country WHERE (CustomerID = @CustomerID)"
        deletecommand="Delete from Customers where CustomerID = @CustomerID"
        connectionstring="<%$ ConnectionStrings:NorthWindConnectionString%>"
        runat="server">
      </asp:sqldatasource>
            
    </form>
  </body>
</html>

<%@ Page language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>GridView Edit Example</title>
</head>
<body>
    <form id="form1" runat="server">
        
      <h3>GridView Edit Example</h3>

      <!-- The GridView control automatically sets the columns     -->
      <!-- specified in the datakeynames property as read-only.    -->
      <!-- No input controls are rendered for these columns in     -->
      <!-- edit mode.                                              -->
      <asp:gridview id="CustomersGridView" 
        datasourceid="CustomersSqlDataSource" 
        autogeneratecolumns="true"
        autogeneratedeletebutton="true"
        autogenerateeditbutton="true"
        datakeynames="CustomerID"  
        runat="server">
      </asp:gridview>
            
      <!-- This example uses Microsoft SQL Server and connects  -->
      <!-- to the Northwind sample database. Use an ASP.NET     -->
      <!-- expression to retrieve the connection string value   -->
      <!-- from the Web.config file.                            -->
      <asp:sqldatasource id="CustomersSqlDataSource"  
        selectcommand="Select [CustomerID], [CompanyName], [Address], [City], [PostalCode], [Country] From [Customers]"
        updatecommand="Update Customers SET CompanyName=@CompanyName, Address=@Address, City=@City, PostalCode=@PostalCode, Country=@Country WHERE (CustomerID = @CustomerID)"
        deletecommand="Delete from Customers where CustomerID = @CustomerID"
        connectionstring="<%$ ConnectionStrings:NorthWindConnectionString%>"
        runat="server">
      </asp:sqldatasource>
            
    </form>
  </body>
</html>

The SqlDataSource control directly connects to a database and therefore implements a two-tiered data model. If you need to bind to a middle-tier business object which performs data retrieval and updates, you can use the ObjectDataSource control. For details, see Binding to Business Objects.

For more information about the SqlDataSource control, see the SqlDataSource Web Server Control Overview.

Binding to a Microsoft Access Database

ASP.NET provides an AccessDataSource control that simplifies the task of connecting to a Microsoft Access database file (.mdb file). The AccessDataSource class inherits from the SqlDataSource class and automatically connects to an .mdb file using the System.Data.OleDb .NET Framework data provider and the Microsoft.Jet.OLEDB.4.0 OLE DB provider. To connect to an Access database, you supply a file path as the DataFile property. Other than the difference in how the AccessDataSource control connects to a Microsoft Access database, the control works exactly like the SqlDataSource control. For more information, see Retrieving Data Using the AccessDataSource Web Server Control.

See Also

Concepts

ASP.NET Data Access Overview
SqlDataSource Web Server Control Overview
Retrieving Data Using the AccessDataSource Web Server Control