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:
- Drag and drop the stored procedure from Server Explorer onto the Object Relational Designer to create a method representation of the stored procedure.
- Write code to intercept the Selecting event of the LinqDataSource, manually create the data context, and execute the stored procedure.
- 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.