ASP.NET's New LinqDataSource Control Simplifies Data Access : Page 3

Querying with Parameters
To go beyond using the LinqDataSource control for basic data display, you need to be able to pass parameters to a LINQ query though the LinqDataSource control. Consider a simple scenario where you want to display a list of products that belong to a specific category in a GridView control. Users will select the product category at runtime from a dropdown list. Add a new ASP.NET page, and modify its code as shown in Listing 1, which starts by declaring a LinqDataSource named productSubcategorySource. The ContextTypeName and TableName attributes for that tag are set to AdventureWorksDatabaseContext and ProductSubcategories, respectively, so that it can retrieve data from the ProductSubcategory table:

   <asp:LinqDataSource ID="productSubcategorySource" 
     ContextTypeName="AdventureWorksDatabaseDataContext" 
     runat="server" TableName="ProductSubcategories">
   </asp:LinqDataSource>

Next, the code binds the output of the productSubcategorySource control to a DropDownList control named ddlProductSubcategory by setting the DataSourceID attribute of the DropDownList to productSubcategorySource:

   <asp:DropDownList AutoPostBack="true" 
     DataSourceID="productSubcategorySource" 
     ID="ddlProductSubcategory" 
     DataValueField="ProductSubcategoryID"
     DataTextField="Name" runat="server"/>                    

The code defines a second LinqDataSource control named productSource. The Where property of this LinqDataSource control specifies a parameter to be passed to the query, while the Select property defines the property values to be returned as the output. Finally, a <WhereParameters> template specifies the parameter details for the query:

   <asp:LinqDataSource ID="productSource" runat="server" 
     ContextTypeName="AdventureWorksDatabaseDataContext" 
     TableName="Products"
     Where = "ProductSubcategoryID = @ProductSubcategoryID"
     Select= "new(ProductID, Name, ProductNumber)">
     <WhereParameters>
      <asp:ControlParameter 
       Name="ProductSubcategoryID" 
       DefaultValue="0" 
       ControlID="ddlProductSubcategory" 
       Type="Int32" />
     </WhereParameters>
   </asp:LinqDataSource>

Figure 3. Browsing by Category: Selecting a product category passes a CategoryID as a parameter to a LINQ query that retrieves the products belonging to that product category, displaying them in the bound GridView.

The output of this control is bound directly to a GridView control named gridProducts:

   <asp:GridView runat="server" 
     DataSourceID="productSource" 
    ID="gridProducts" BackColor="LightGray" 
     AllowPaging="true"/>                

Because the Where clause requires the ProductSubcategoryID parameter, you need to retrieve the selected ProductSubcategoryID from the DropDownList and pass it to the query. To do that, set the ControlID attribute of the ControlParameter template to the name of the DropDownList (ddlProductSubcategory in this case).

Finally, the code sets a default value of 0 for the ProductSubcategoryID using the DefaultValue property. If you browse to the page, you'll see a screen that looks similar to Figure 3.

Executing Stored Procedures
Although you can control many aspects of a LINQ query through the LinqDataSource control properties, there are times when you might want to invoke a stored procedure instead. Here are the steps:

1. Drag and drop the stored procedure from Server Explorer onto the Object Relational Designer to create a method representation of the stored procedure.
2. Write code to intercept the Selecting event of the LinqDataSource, manually create the data context, and execute the stored procedure.
3. Assign the results of the stored procedure execution to the LinqDataSourceSelectEventArgs.Result property before exiting the Selecting event.

Here's a short walkthrough example. First, create a simple stored procedure named GetProducts as shown below:

   CREATE PROCEDURE dbo.GetProducts   
   AS
    SET NOCOUNT ON 
    SELECT ProductID, [Name], ProductNumber FROM
    Production.Product

Save the stored procedure and then drag and drop it from Server Explorer onto the Object Relational Designer. That causes the designer to create a GetProducts() method in the AdventureWorksDatabaseDataContext class (which you'll find in the file AdventureWorksDatabase.designer.cs in the project discussed in the preceding section). Add a new ASP.NET page to the web site, and modify its code as follows:

   <%@ Page Language="C#" %>
   <script runat="server">
    void productSource_Selecting(object sender, 
       LinqDataSourceSelectEventArgs e)
    {
       AdventureWorksDatabaseDataContext context = new 
       AdventureWorksDatabaseDataContext();
       e.Result =  context.GetProducts();       
    }    
   </script>
   <html xmlns="http://www.w3.org/1999/xhtml">
   <head runat="server">
    <title>Using Custom LINQ Queries</title>
   </head>
   <body>
    <form id="form1" runat="server">
     <div>
      <asp:GridView runat="server" BackColor="LightGray"
       ID="gridProducts" DataSourceID="productSource"/>
      <asp:LinqDataSource runat="server" ID="productSource"
       OnSelecting="productSource_Selecting" />        
      </div>
     </form>
    </body>
   </html>

Figure 4. Stored Procedure Execution: You can execute stored procedures through the LinqDataSource control almost as easily as a declarative Select operation.

One key point to note in the preceding code is the productSource_Selecting method implementation. That method gets assigned as the event handler for the OnSelecting event of the LinqDataSource control defined near the end of the code. The event handler implementation creates an instance of the data context class, and then invokes its GetProducts() method, which in turn invokes the GetProducts stored procedure in the AdventureWorks database. Finally, the code assigns the query output to the Result property of the LinqDataSourceSelectEventArgs object. Figure 4 shows the output produced by the page.

The entire process to execute a stored procedure with the LinqDataSource control follows the procedure discussed earlier: You intercept the OnSelecting event, call the LinqDataSource control method that executes the query, and return the query results in the LinqDataSourceSelectEventArgs.Result property.

At this point, you've seen some of the major features of the LinqDataSource control along with examples of how to use it to execute LINQ queries using a declarative approach, with both parameterless and parameterized queries. You've also seen how to take advantage of the extensibility features built into this control to perform operations such as executing stored procedures. While the benefits of building a data model and executing queries against it using procedures and a SQL-like syntax may not be completely obvious when you're using only a single type of data source, they become far more obvious when you need to write queries against multiple data sources.

Previous Page