Using LINQ in ASP.NET (Part 3)

Introduction

In the Part 1 and Part 2 of this series we discussed how to use LINQ to SQL features to query and manipulate data. We also learnt to call stored procedures via LINQ to SQL. In the previous examples our approach was manual in that we ourselves created the custom data context and entity classes. Visual Studio comes with an inbuilt designer to perform the same task. This article will teach you how to use the designer and consume the created classes in your application.

Adding LINQ to SQL Classes

The first step in using Visual Studio designer to create LINQ to SQL classes is to add them to your web site. You can do so through "Add New Item..." dialog. Select "LINQ to SQL Classes" template.

It will add three files to your App_Code folder. The one with extension .dbml contains XML markup representing the entity class mappings. The one with extension .layout is intended for the designer and the one with extension .cs contains all the generated code.

Now expand Server Explorer to revel Employees table of the Northwind database. Drag and drop the Employees table on to the surface of DBML designer.

This will automatically create a data context and entity class. You can remove unwanted properties (columns) from the entity class. See below for the designer after creating the entity class named CEmployees.

By default SELECT, INSERT, UPDATE and DELETE queries are generated on the fly. You can, however, tell the designer to use stored procedures for INSERT, UPDATE and DELETE operations. Before you configure that drag and drop Employees_Insert, Employees_Update and Employees_Delete stored procedures on the right pane of the designer.

This will add certain definitions to the designer as shown below. Actually the designer creates methods in the data context class (the way we created in Part 2) that map to the corresponding stored procedures.

To specify INSERT, UPDATE and DELETE operations, right click on the CEmployee class from the designer and choose "Configure Behavior" menu option.

This will open a dialog as shown below:

You can then map a behavior with a custom stored procedure. When you select a stored procedure you also get chance to map its parameters with the properties of the CEmployees class.

Repeat the same procedure for Insert, Update and Delete behaviors. That's it! you are now ready to use the designer generated classes in your web form.

We will modify the same web form of Part 2 to make use of designer generated classes. The BindDetailsView() method now looks as shown below:

private void BindDetailsView()
{
  DataClassesDataContext db = new DataClassesDataContext();
  Table<CEmployee> results= db.GetTable<CEmployee>();
  DetailsView1.DataSource = results;
  DetailsView1.DataBind();
}

Here, we created the instance of DataClassesDataContext class i.e. the designer generated classes. The designer automatically creates an overloaded version of the constructor that picks up connection string gtom web.config file. We then retrieve employee records using GetTable() method. The results are returned as Table of CEmployee objects. The table is then bound with DetailsView.

The insert, update and delete operations are almost identical and are shown below:

protected void DetailsView1_ItemInserting
(object sender, DetailsViewInsertEventArgs e)
{
DataClassesDataContext db = new DataClassesDataContext();
db.Employees_Insert(((TextBox)DetailsView1.Rows[1].
Cells[1].Controls[0]).Text, ((TextBox)DetailsView1.Rows[2].
Cells[1].Controls[0]).Text);
}
protected void DetailsView1_ItemUpdating
(object sender, DetailsViewUpdateEventArgs e)
{
DataClassesDataContext db = new DataClassesDataContext();
db.Employees_Update((int)DetailsView1.SelectedValue, 
((TextBox)DetailsView1.Rows[1].Cells[1].Controls[0]).Text,
 ((TextBox)DetailsView1.Rows[2].Cells[1].Controls[0]).Text);
}
protected void DetailsView1_ItemDeleting
(object sender, DetailsViewDeleteEventArgs e)
{
DataClassesDataContext db = new DataClassesDataContext();
db.Employees_Delete((int)DetailsView1.SelectedValue);
}

In the code above we created instances of DataClassesDataContext class and called appropriate methods viz. Employees_Insert, Employees_Update and Employees_Delete.

You can now run the web form and test the designer generated classes. If you switch to the .cs file created by the DBML designer you will see code similar to what we coded in Part 1 and 2.

In the next part of this series I will explore the new LINQ data source control of ASP.NET. Stay tuned!