In this book we have seen how to use LINQ to Objects, SQL, Dataset, XML and XSD. We have also seen a number of examples for different query operators used by LINQ. Now, in this appendix, we will see how we can use LINQ features to make web application development easier. Let's build a simple ASP.NET application with one page having a drop-down, and a grid to show details corresponding to the selection of the drop-down box.
On selecting a particular category, we should be able to get the list of items for the selected category in the grid. Let us see how we can build this web application in Visual Studio using LINQ.
Create a new ASP.NET web application using File | New | Web Site | ASP.NET Web Site, as shown in the following screenshot:
Following are the default references that get added to a project when we create it. To make use of LINQ to SQL, we need to add some additional references to the project, specifically System.Data.Linq and System.Data.
Also add the following namespaces to the project. This will let you use LINQ for querying data from the database.
using System.Data.Linq; using System.Data.Linq.Mapping;
Now we have to create the required database entity objects for our web application. Create the database objects with properties as shown below:
The above database objects can be created using LINQ to SQL queries. For more information on this, please see Chapter 4, LINQ to SQL. Let's consider that we have these database objects ready. Now we have to create classes that map to the above database objects and the database.
[Database(Name = "Deserts")]
public class Deserts : DataContext
{
public Table<Categories> Categories;
public Table<Items> Items;
public Deserts(string connection) : base(connection) { }
}
[Table(Name = "Categories")]
public class Categories
{
[Column(Name = "CategoryID", IsPrimaryKey = true,
IsDbGenerated = true, DbType = "int NOT NULL IDENTITY",
CanBeNull = false)]
public int CategoryID
{
get;
private set;
}
[Column(Name = "Category", DbType = "nvarchar(1000)")]
public string Category
{
get;
set;
}
[Column(Name = "Description", DbType = "nvarchar(1000)",
UpdateCheck = UpdateCheck.Never)]
public string Description
{
get;
set;
}
private EntitySet<Items> _Items;
[Association(Name = "FK_Category_Items", Storage = "_Items",
OtherKey = "CategoryID", IsForeignKey = true)]
public EntitySet<Items> Items
{
get { return this._Items; }
set { this._Items.Assign(value); }
}
public Categories() { this._Items = new EntitySet<Items>(); }
}
[Table(Name = "Items")]
public class Items
{
[Column(Name = "ItemID", IsPrimaryKey = true, IsDbGenerated = true,
LINQASP.NET application, buildingDbType = "int NOT NULL IDENTITY", CanBeNull = false)]
public int ItemID { get; private set; }
[Column(Name = "CategoryID")]
public int CategoryID { get; set; }
[Column(Name = "Name", DbType = "nvarchar(1000)")]
public string Name { get; set; }
[Column(Name = "Ingredients", DbType = "nvarchar(1000)")]
public string Ingredients { get; set; }
[Column(Name = "ServingSize", DbType = "nvarchar(1000)")]
public string ServingSize { get; set; }
[Column(Name = "TotalFat", DbType = "nvarchar(1000)")]
public string TotalFat { get; set; }
[Column(Name = "Cholesterol", DbType = "nvarchar(1000)")]
public string Cholesterol { get; set; }
[Column(Name = "TotalCarbohydrates", DbType = "nvarchar(1000)")]
public string TotalCarbohydrates { get; set; }
[Column(Name = "Protein", DbType = "nvarchar(1000)")]
public string Protein { get; set; }
private EntityRef<Categories> _Categories;
[Association(Name = "FK_Category_Items", Storage = "_Categories",
ThisKey = "CategoryID", IsForeignKey = true)]
public Categories Categories
{
get { return this._Categories.Entity; }
set { this._Categories.Entity = value; }
}
public Items() { this._Categories = new EntityRef<Categories>(); }
}
The above code creates mapping of:
Desertsclass to theDesertsSQL database. This class is of typeDataContext.Categoriesclass to theCategoriesSQL database object in theDesertsdatabase.Itemsclass to theItemsSQL database object in theDesertsdatabase.
We also need to have primary and foreign keys defined for these classes.
Now open the Default.aspx designer and add the following controls to build the web page:
Add the HTML table to design the UI.
Add a Label and a DropDownList to the page for categories selection.
Add a button control to execute LINQ queries, and bind the results to the GridView control.
Add a GridView control to show List of Items for the selected category in the drop down list.
Add a Label and a TextBox control to show Total Items for the selected category.
After adding all the controls to the web page, it would look like this:
The columns shown in GridView correspond to the details that will be fetched for the selected category using LINQ queries. We can also set GridView's properties to automatically generate columns at runtime.
Add the following code to the constructor of the class to create a connection to the Deserts database, and this will also create an object using the Deserts DataContext. The Deserts variable can be declared as public, as it is referred to throughout the application.
public Deserts dataBase;
Deserts database = new Deserts("Data Source=.sqlexpress;Initia
Catalog=Deserts;Integrated Security=true");
Now add the following code to the Page_Init event of the page to load the categories drop-down. We will also make use of a LINQ to SQL query to fetch the list of categories from the database.
protected void Page_Init(object sender, EventArgs e)
{
var icecreams = from cat in dataBase.Categories
select cat.Category;
DropDownList1.DataSource = icecreams;
DropDownList1.DataBind();
lblCount.Visible = false;
lblTotalItems.Visible = false;
}
Now save the application and execute it. We can see the web page with the drop-down list loaded with a list of categories as shown in the following figure:
You will also need to write code for getting items for the selected category that form the drop-down list. Add the following code to the Get Items button control:
protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataSource = from items in dataBase.Items
join categories in dataBase.Categories on items.CategoryID
equals categories.CategoryID
where categories.Category == DropDownList1.SelectedValue.ToString()
select new { items.Name, items.Categories.Category,
items.Protein, items.Ingredients };
GridView1.DataBind();
int iCount = (from items in dataBase.Items
join categories in dataBase.Categories on items.CategoryID
equals categories.CategoryID
where categories.Category == DropDownList1.SelectedValue.ToString()
select new { items.Name, items.Categories.Category,
items.Protein, items.Ingredients }).Count();
if (iCount > 0)
{
lblCount.Visible = true;
lblTotalItems.Visible = true;
lblCount.Text = iCount.ToString();
}
else
{
lblCount.Visible = false;
lblTotalItems.Visible = false;
}
}
In the above code, the source for GridView1 is a LINQ query which fetches all items for the selected category. The same query with the Count operator is used for getting the total number items retrieved for the selected category.
The final output of the web page would be as follows:
