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
attributes for that tag are set to AdventureWorksDatabaseContext
, respectively, so that it can retrieve data from the ProductSubcategory
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
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"
Where = "ProductSubcategoryID = @ProductSubcategoryID"
Select= "new(ProductID, Name, ProductNumber)">
|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
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
SET NOCOUNT ON
SELECT ProductID, [Name], ProductNumber FROM
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#" %>
void productSource_Selecting(object sender,
AdventureWorksDatabaseDataContext context = new
e.Result = context.GetProducts();
<title>Using Custom LINQ Queries</title>
<form id="form1" runat="server">
<asp:GridView runat="server" BackColor="LightGray"
<asp:LinqDataSource runat="server" ID="productSource"
|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
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.