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 LinqDataSource and SqlDataSource controls.

The LinqDataSource Control

The LinqDataSource control enables you to use Language-Integrated Query (LINQ) in an ASP.NET Web page through declarative markup in order to retrieve and modify data from a data object. The control supports automatic generation of select, update, insert, and delete commands. The control also supports sorting, filtering, and paging.

When you use the LinqDataSource control to interact with data from a database, you do not connect the LinqDataSource control directly to the database. Instead, you interact with entity classes that represent the database and the tables. You can generate the entity classes through the Object Relational Designer or by running the SqlMetal.exe utility. For more information, see Object Relational Designer (O/R Designer) and Code Generation Tool (SqlMetal.exe).

The entity classes that you create will typically be located in the App_Code folder of the Web application. The O/R Designer or the SqlMetal.exe utility will generate one class that represents the database and one class for each table in the database.

You connect the LinqDataSource control to a database class by setting the ContextTypeName property to the name of the class that represents the database. You connect the LinqDataSource control to a particular table by setting the TableName property to the name of the class that represents the data table. For example, to connect to the Contacts table in the AdventureWorks database, you set the ContextTypeName property to a class name such as AdventureWorksDataContext (or whatever name that you specify for the database object). You set the TableName property to Contacts.

The following example shows a LinqDataSource control that retrieves data from a table named Products. It automatically generates the commands to support inserting, updating, and deleting data. A DetailsView control displays the data and creates buttons that let users modify the data.

<asp:LinqDataSource 
    ContextTypeName="ExampleDataContext" 
    TableName="Products" 
    EnableUpdate="true"
    EnableInsert="true"
    EnableDelete="true"
    ID="LinqDataSource1" 
    runat="server">
</asp:LinqDataSource>
<asp:DetailsView 
    DataKeyNames="ProductID"
    AutoGenerateEditButton="true"
    AutoGenerateDeleteButton="true"
    AutoGenerateInsertButton="true"
    AllowPaging="true"
    DataSourceID="LinqDataSource1"
    ID="GridView1" 
    runat="server">
</asp:DetailsView>

<asp:LinqDataSource 
    ContextTypeName="ExampleDataContext" 
    TableName="Products" 
    EnableUpdate="true"
    EnableInsert="true"
    EnableDelete="true"
    ID="LinqDataSource1" 
    runat="server">
</asp:LinqDataSource>
<asp:DetailsView 
    DataKeyNames="ProductID"
    AutoGenerateEditButton="true"
    AutoGenerateDeleteButton="true"
    AutoGenerateInsertButton="true"
    AllowPaging="true"
    DataSourceID="LinqDataSource1"
    ID="GridView1" 
    runat="server">
</asp:DetailsView>

For more information, see LinqDataSource Web Server Control Overview.

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.

<asp:LinqDataSource ID="LinqDataSource1" runat="server" 
    ContextTypeName="AdventureWorksLTDataClassesDataContext"
    EnableDelete="True" EnableInsert="True" EnableUpdate="True" 
    TableName="SalesOrderDetails">
</asp:LinqDataSource>

<asp:GridView ID="GridView1" runat="server" 
    AutoGenerateColumns="False" 
    DataKeyNames="SalesOrderID,SalesOrderDetailID"
    DataSourceID="LinqDataSource1">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" 
            ShowEditButton="True" />
        <asp:BoundField DataField="SalesOrderID" 
            HeaderText="SalesOrderID" ReadOnly="True"
            SortExpression="SalesOrderID" />
        <asp:BoundField DataField="SalesOrderDetailID" 
            HeaderText="SalesOrderDetailID" InsertVisible="False"
            ReadOnly="True" SortExpression="SalesOrderDetailID" />
        <asp:BoundField DataField="OrderQty" 
            HeaderText="OrderQty" SortExpression="OrderQty" />
        <asp:BoundField DataField="ProductID" 
            HeaderText="ProductID" SortExpression="ProductID" />
        <asp:BoundField DataField="UnitPrice" 
            HeaderText="UnitPrice" SortExpression="UnitPrice" />
        <asp:BoundField DataField="ModifiedDate" 
            HeaderText="ModifiedDate" SortExpression="ModifiedDate" />
    </Columns>
</asp:GridView>

<asp:LinqDataSource ID="LinqDataSource1" runat="server" 
    ContextTypeName="AdventureWorksLTDataClassesDataContext"
    EnableDelete="True" EnableInsert="True" EnableUpdate="True" 
    TableName="SalesOrderDetails">
</asp:LinqDataSource>

<asp:GridView ID="GridView1" runat="server" 
    AutoGenerateColumns="False" 
    DataKeyNames="SalesOrderID,SalesOrderDetailID"
    DataSourceID="LinqDataSource1">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" 
            ShowEditButton="True" />
        <asp:BoundField DataField="SalesOrderID" 
            HeaderText="SalesOrderID" ReadOnly="True"
            SortExpression="SalesOrderID" />
        <asp:BoundField DataField="SalesOrderDetailID" 
            HeaderText="SalesOrderDetailID" InsertVisible="False"
            ReadOnly="True" SortExpression="SalesOrderDetailID" />
        <asp:BoundField DataField="OrderQty" 
            HeaderText="OrderQty" SortExpression="OrderQty" />
        <asp:BoundField DataField="ProductID" 
            HeaderText="ProductID" SortExpression="ProductID" />
        <asp:BoundField DataField="UnitPrice" 
            HeaderText="UnitPrice" SortExpression="UnitPrice" />
        <asp:BoundField DataField="ModifiedDate" 
            HeaderText="ModifiedDate" SortExpression="ModifiedDate" />
    </Columns>
</asp:GridView>

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

LinqDataSource Web Server Control Overview

SqlDataSource Web Server Control Overview

Retrieving Data Using the AccessDataSource Web Server Control