Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

The LinqDataSource control gives your applications native .NET object mapping to back-end data stores, unifying data access and reducing the need for store-specific coding.




Application Security Testing: An Integral Part of DevOps

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.

Thiru Thangarathinam works at Intel Corporation in Chandler, Arizona. He's a Microsoft MVP who specializes in architecting, designing, and developing distributed enterprise-class applications using .NET-related technologies. He is the author of the books "Professional ASP.NET 2.0 XML" and "Professional ASP.NET 2.0 Databases" from Wrox press and has coauthored a number of books on .NET-related technologies. He is a frequent contributor to leading technology-related online publications.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date