Using LinqDataSource With GridView Control
In this tutorial I will be covering how to use LinqDataSource with GridView control to retrieve all the products from NorthWind database. The functionality would able to filter the products based on the category selected from the dropdownlist. You will also have the ability to sort and page through the data in the GridView control. The end result is a simple page with a dropdownlist list that allows you to retrieve all the products or filter the products based on the category selected.
The first step is to add a new NorthWind linq to SQL class and drag Product and Category class on the OR designer and build the project. Note it is important to build the project otherwise the intellisense wont be able to pick up NorthWindContext in the code. Here is how the linq to SQL dbml file looks like.
In the above example all I have simply done is dragged Product and Category class onto the designer which would allow me to run my queries on those tables using NorthWind datacontext. The next step is to work on the aspx which allows you to retrieve the products based on all categories or filtered category. Here is how the aspx page looks like.
1: <%@ Page Language="C#" AutoEventWireup="true"
2: CodeFile="Default.aspx.cs"
3: Inherits="_Default" %>
4:
5: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
6: "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
7:
8: <html xmlns="http://www.w3.org/1999/xhtml">
9: <head runat="server">
10: <title>Untitled Page</title>
11: </head>
12: <body>
13: <form id="form1" runat="server">
14: <div style="padding-left:20px;">
15: <asp:DropDownList
16: ID="categories" DataTextField="CategoryName"
17: DataValueField="CategoryID" AppendDataBoundItems="true"
18: DataSourceID="categorysource" AutoPostBack="true"
19: runat="server" >
20: <asp:ListItem Text="Select" Value="" />
21: </asp:DropDownList>
22: <br /><br />
23:
24: <asp:GridView ID="products" runat="server"
25: AutoGenerateColumns="false"
26: AllowPaging="true" PageSize="20"
27: AllowSorting="true"
28: DataSourceID="productsource">
29: <Columns>
30: <asp:BoundField DataField="ProductName"
31: HeaderText="Name"
32: SortExpression="ProductName" />
33: <asp:BoundField DataField="FormattedPrice"
34: HeaderText="Price"
35: SortExpression="UnitPrice" />
36: </Columns>
37: </asp:GridView>
38:
39: <asp:LinqDataSource ID="productsource"
40: ContextTypeName="NorthWindDataContext"
41: AutoPage="true" AutoSort="true"
42: Where="CategoryID=@categoryid"
43: Select="new(ProductName,UnitPrice,FormattedPrice)"
44: TableName="Products"
45: runat="server" onselecting="productsource_Selecting">
46: <WhereParameters>
47: <asp:ControlParameter ConvertEmptyStringToNull="true"
48: ControlID="categories" Type="Int32"
49: Name="categoryid" PropertyName="SelectedValue" />
50: </WhereParameters>
51: </asp:LinqDataSource>
52:
53: <asp:LinqDataSource id="categorysource" runat="server"
54: ContextTypeName="NorthWindDataContext"
55: TableName="Categories" />
56: </div>
57: </form>
58: </body>
59: </html>
In the above code snippet, I start off with a category dropdown control which is bound to categorysource linqdatasource control. In order to retrieve all the categories from the database, I simply specify the name of the datacontext to use and the table where my categories reside. I am also using appendatabounditem property on the dropdown to add a Select text. What this allows me to do is display all the products the first time page load as shown below.
The next step is configuring the gridview control. Since i am specifying my columns manually i am turning of autogeneration of columns by setting autogenerate columns to false. I am also setting allow sorting and allowpaging to true for the grid to be pagable and sortable. I am setting the DataSourceID to the productsource linqdatasource control. Product linqdatasource control is responsible for not only retrieving and filtering the products but also takes care of paging and sorting. What is neat about this implementation is linq datasource ensures that paging and sorting is done on the database side by sending entire query to the database. Here is how the gridview looks like.
In setting up the product linqdatasource control, I specify my context name. I am also setting AutoPage and AutoSort property to true which allows linq datasource control to grab the page number and column to be sorted on from the gridview control and generate appropriate linq query. Since i do want to retrieve all the columns from the product class, I make use of Select property and specify an anonymous type which consists of ProductName, UnitPrice and FormattedPrice. Notice that in my anonymous class I am retrieving a property called FormattedPrice which is not defined in the database. Infact I have defined a partial class where i have added this property. Since i am also allowing sorting on the gridview, i also retrieving the raw UnitPrice from the database because i cannot sort by custom column defined in my partial class. I have to sort by column defined in the database. Here is how product datasource and partial product class looks like.
In order for the linq datasource to filter the products based on the category selected, I am applying the where clause by using where parameters that gets its value from the category dropdown control. In order to handle the scenario where dropdown contains Select which means i have to retrieve all the products, I register with the selecting event of the linq datasource control. Here is how the code for selecting event looks like.
In the selecting event above, i check to see if the categoryid of the where parameter is null which would be when Select text is selected in the category dropdown, I clear the where clause and where parameter to retrieve all the the products.
In next section, I will cover how to use linq datasource to insert, update and delete using NorthWindDataContext.