ny user-centric application you write will require some sort of data integration?at minimum, you’ll need to retrieve some data and display it in the user interface. Often, applications must retrieve data from multiple sources, such as memory collections, relational databases, XML files, and so on. Previous versions of the .NET Framework made getting to this data tedious, and required familiarity with multiple data access technologies and XML APIs.
Even worse, developers had to learn different ways of querying all these data sources: SQL for databases, XQuery for XML, LDAP queries for Active Directory, etc. In short, until now, the data access story has lacked a unified approach to accessing data from disparate data sources. That’s exactly the problem Microsoft intends the LINQ (Language INtegrated Query) family of technologies to solve. LINQ provides a much more unified developer interface for querying data.
You can write LINQ queries and execute them from within your .NET application just by referencing a couple of LINQ assemblies. In addition to invoking LINQ queries programmatically, you can also execute LINQ queries declaratively via a new ASP.NET control?the LinqDataSource control. In this article, you’ll see the steps involved in using this new control to access data as well as some more advanced features of the LinqDataSource control, such as passing parameters to a LINQ query and executing a stored procedure. Although this article focuses on using the LINQDataSource control to query a relational database (SQL Server), future articles will discuss how to use LINQ to manage data in other types of stores.
A LinqDataSource Control Primer
You can bind the output of the LinqDataSource control directly to a databinding-capable control such as a GridView or a ListView. You can also configure the LinqDataSource control to implement advanced scenarios such as inserting, updating, and deleting data?without having to write a single line of SQL code.
The LinqDataSource control also provides events that let you handle customized scenarios, such as executing a stored procedure or a complex query. To execute a LINQ query through the LinqDataSource control, you first need to create entity classes that represent the database and its tables. There are two ways you can generate these classes:
- Using the Object Relational Designer: This WYSIWYG interface lets you drag and drop tables, stored procedures, and functions onto the designer surface to create the corresponding entity classes and their methods.
- Using the SqlMetal.exe utility: This command-line utility generates the LINQ object model based on the schema of a supplied database.
To enable this declarative, codeless binding, the LinqDataSource control exposes a number of properties that you’ll find familiar if you have worked much with SQL (see Table 1).
|ContextTypeName||The name of the type that exposes a property containing a value you want to access|
|DeleteParameters||Parameter collection used during a DELETE operation|
|GroupBy||Properties used to group retrieved data|
|GroupByParameters||Parameter collection used while executing a GROUP BY clause|
|InsertParameters||Parameter collection used to execute an INSERT operation|
|OrderBy||List of fields used to order retrieved data|
|OrderByParameters||Parameter collection for creating the ORDER BY clause|
|OrderGroupsBy||Fields used to order grouped data|
|OrderGroupsByParameters||Parameter collection used to create the ORDER GROUPS BY clause|
|Select||List of properties and values to be included in the retrieved data|
|SelectParameters||Parameter collection used during a SELECT operation|
|TableName||Name of the property or field in the data context object that specifies the data collection to be queried|
|UpdateParameters||Parameter collection used during an UPDATE operation|
|Where||Specifies conditions for a record to be included in the retrieved data|
|WhereParameters||Parameter collection used during the WHERE clause|
With this overview in hand, the next step is to dive right in and build a working example.
LinqDataSource in Action
Here’s a simple example that illustrates the steps involved in using the LinqDataSource control to retrieve and display data from the Products table in the sample AdventureWorks database that ships with SQL Server. To begin, create a new Visual C# web site named LinqDataSourceExample. After creating the web site, right-click the project item in the Solution Explorer, and select Add ? New Item from the menu. In the Add New Item dialog box, select the “LINQ to SQL Classes” template and name it AdventureWorksDatabase.dbml.
|Author’s Note: When you hit “Add” in the “Add New Item” dialog box, Visual Studio presents a prompt asking whether you want to place the new class in the App_Code folder. For the purposes of this article, answer “Yes” to the prompt. Answering “Yes” lets you reference the class from within the project’s ASP.NET pages without having to qualify the name of the class by prefixing it with the namespace.|
After you’ve created the AdventureWorksDatabase LinqDataSource class, Visual Studio opens the Object Relational Designer. You’ll see a design surface on which you can drag and drop tables and stored procedures from the Server Explorer to create corresponding entity classes. The designer surface contains two panes: an Entities pane and a Methods pane. As the name suggests, the Entities pane displays entity classes included in your model, and the Methods pane displays DataContext methods that are mapped to stored procedures and functions.
Now select View ? Server Explorer from the menu, and add a new data connection to the AdventureWorks database. After that, drag and drop the Production.ProductSubcategory and Production.Product tables onto the Object Relational Designer from the Tables node in Server Explorer. Your screen should look somewhat similar to Figure 1.
|Figure 1. Object Relational Designer: The designer surface shows the Entities and Methods panes after dragging the Product and ProductSubcategory tables from Server Explorer.|
The act of dropping the database objects onto the design surface causes the Object Relational Designer to create standard .NET classes that reflect the dropped objects. To see the code, take a look at the file AdventureWorksDatabase.designer.cs file after dropping the tables on the design surface. You’ll see that Visual Studio has generated several classes, including one called AdventureWorksDatabaseDataContext that manages connections, and one class for each of the tables in the designer.
Now that you have created the entity classes, you can create an ASP.NET page and use the LinqDataSource control to consume those classes. Create a new ASP.NET page named SimpleLinqDataSourceExample.aspx, and modify its code as follows:
<%@ Page Language="C#" %>
Using LinqDataSource Control to execute a simple LINQ Query
? Figure 2. Bound Data: Here's the output produced by binding the ListView control to a LinqDataSource control that retrieves data from the Products table.
In the above code, the LinqDataSource control has two important properties named ContextTypeName and TableName. The ContextTypeName property refers to the object that represents the database (the LinqDataSource you created in the Object Relational Designer), and the TableName property refers to the object that represents the database table.
The ListView control’s DataSourceID property is set to the name of the LinqDataSource control (productSource). In the ListView’s
Figure 2 shows the output produced by navigating to the page in the browser.
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:
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
|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 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#" %>
Using Custom LINQ Queries
|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.