Using LINQ in ASP.NET (Part 1)

Introduction

Language INtegrated Query or LINQ changes the way you write your data driven applications. Previously developers used to think and code differently to access different data stores such as SQL server, XML files and in-memory collections. The new LINQ based programming can take away the hassles involved while developing such applications. In this multi part series I am going to explain how LINQ capabilities can be used ASP.NET applications.

Example

The LINQ way of development is best learnt with example. We will develop a simple data entry web form that performs INSERT, UPDATE, DELETE and SELECT operations on the Employees table of the Northwind database. All the operations happen via LINQ to SQL queries. Our development involves the following steps:

• Create an entity class for Employees table
• Create a strongly typed data context to expose the underlying tables
• Develop a web form that makes use of LINQ to SQL capabilities to perform the required operations

Assemblies and Namespaces

In this example you will be using LINQ to SQL features and hence you need to refer System.Data.LINQ assembly in your web site. This assembly provides two important namespaces:

• System.Data.Linq
• System.Data.Linq.Mapping

The former namespace provides classes that allow you to interact with database whereas the later namespace contains classes and attributes that are used to generate entity classes representing the tables.

Create an entity class for Employees table

Just like anything else in .NET framework LINQ is an object oriented framework. That means you must represent your database objects such as tables as .NET classes. This LINQ to SQL object model is designed with the help of certain attributes. Have a careful look at the class below:

[Table(Name="Employees")]
public class Employee
{
 [Column(IsDbGenerated=true,IsPrimaryKey=true)]
 public int EmployeeID { get; set; }

 [Column(Name="FirstName",DbType="varchar(20)")]
 public string FirstName { get; set; }

 [Column(Name = "LastName", DbType = "varchar(20)")]
 public string LastName { get; set; }

}

The public class Employee consists of three public properties viz. EmployeeID, FirstName and LastName. What makes this class special is the [Table] and the [Column] attributes. The [Table] attribute marked on the Employee class indicates that the underlying class represents an RDBMS table. The Name property of the [Table] attribute specifies the name of the database table. If your class name and table name are same you can very well skip the Name property.

The property level [Column] attribute specifies that the underlying property is a table column. The [Column] attribute has several properties. SOme of the important ones are listed below:

• Name: Name of the table column
• DbType: Data type of the table column (not .NET data type!)
• IsPrimaryKey: Whether the column represents the primary key of the table
• IsDbGenerated: Whether the column values are generated by the RDBMS automatically (e.g. Identity columns)
• CanBeNull: Whether the column can be null
• Storage: The name of the class field that stores the column data

Notice how in our example the EmployeeID is marked with IsDbGenerated and IsPrimaryKey properties. Also, observe how the Name and DbType properties are used.

This completes our Employee class. We used only three columns from the Employees table. You can add extra columns as per the requirement.

Create a strongly typed data context

Any data driven application has some data source from which data is fed into the system. Your application needs a way to talk to this data source. In LINQ to SQL terminology a Data Context does the job of this communication. Programmatically a data context is a class that derives from DataContext base class.

In order to create a strongly typed data context you need to derive a class from DataContext base class as shown below:

public class NorthwindDb:DataContext
{
 public NorthwindDb(string connectionString):base(connectionString)
 {
  //nothing here
 }
 public Table<Employee> Employee;
}

The NorthwindDb class inherits from DataContext base class. The custom data context class must provide a constructor with one parameter. The constructor accepts a database connection string and simply passes it to the base class. More interesting is, however, the Table of Employee objects. This is how you expose the previously mapped LINQ to SQL objects to the external world.

That's all you have in NorthwindDb class. Now we are ready to consume these classes from our web form. You might be wondering - where is the logic to INSERT, UPDATE, DELETE and SELECT data? That's what LINQ to SQL does for us. All such tasks are taken care internally by LINQ framework.

Developing a Web Form

Design the default web form as shown below:

The web form consists of a textbox to filter records based on first name or last name. The GridView displays the selected employee records. Upon selecting an employee record its details are populated in a DetailsView for editing.

Once you design the web form switch to its code view. We will now write two helper methods - BindGridView() and BindDetailsView(). The BindGridView() method binds the GridView with the required records and is shown below:

private void BindGridView(string criteria)
{
string strConn = ConfigurationManager.ConnectionStrings
["connstr"].ConnectionString;
NorthwindDb db = new NorthwindDb(strConn);

IEnumerable<Employee> results;

if (criteria == string.Empty)
{
results=db.Employee.ToArray();
}
else
{
results = (from c in db.Employee
           where c.FirstName.Contains(criteria)
           select c).ToArray();
}
GridView1.DataSource = results;
GridView1.DataBind();
}

Notice the code marked in bold letters. The code creates an instance of NorthwindDb class (our strongly typed data context) and passes the database connection string to it.  In order to select all the records from the Employees table you simply use the Employee Table<> from the NorthwindDb class. To fetch records matching some criteria you use C# language keywords (from - where - select) and find all the employees whose FIrstName contains certain text. In both the results are collected in a generic IEnumerable collection.

The BindGridView() method is called from Page_Load event and Button1_Click event as shown below:

protected void Page_Load(object sender, 
EventArgs e)
{
 if (!IsPostBack)
 {
  BindGridView(string.Empty);
  }
}

protected void Button1_Click(object sender, 
EventArgs e)
{
 BindGridView(TextBox1.Text);
}

When you select a row from the GridView, you need to display the selected record in DetailsView for editing. This is done in another helper method - BindDetailsView()

private void BindDetailsView()
{
int employeeID = (int)GridView1.SelectedValue;
string strConn = ConfigurationManager.ConnectionStrings
["connstr"].ConnectionString;
NorthwindDb db = new NorthwindDb(strConn);

var results = from emp in db.Employee
              where emp.EmployeeID == employeeID
              select emp;

DetailsView1.DataSource = results;
DetailsView1.DataBind();
}

The BindDetailsView() method is called from the SelectedIndexChanged event handler of the GridView.

protected void GridView1_SelectedIndexChanged
(object sender, EventArgs e)
{
int employeeID = (int)GridView1.SelectedValue;
string strConn = ConfigurationManager.ConnectionStrings
["connstr"].ConnectionString;
BindDetailsView();
}

Inserting, Updating and Deleting data

Now let's see how data manipulation works in LINQ to SQL. First of all we will discuss data insertion.

protected void DetailsView1_ItemInserting
(object sender, DetailsViewInsertEventArgs e)
{
string strConn = ConfigurationManager.ConnectionStrings
["connstr"].ConnectionString;
NorthwindDb db = new NorthwindDb(strConn);
Employee emp = new Employee();
emp.FirstName = ((TextBox)DetailsView1.Rows[1].
Cells[1].Controls[0]).Text;
emp.LastName= ((TextBox)DetailsView1.Rows[2].
Cells[1].Controls[0]).Text;
db.Employee.InsertOnSubmit(emp);
db.SubmitChanges();
BindGridView(string.Empty);
}

Notice the highlighted code above. We created an instance of Employee class and set its FirstName and LastName properties from the new values entered in the DetailsView. We then call InsertOnSubmit() method on the Employee Table to add a new element to it. Calling InsertOnSubmit() method simply inserts a new element to the Table. To actually add that row to the database we call SubmitChanges() method.

The Update operation is similar to Insert with little change.

protected void DetailsView1_ItemUpdating
(object sender, DetailsViewUpdateEventArgs e)
{
string strConn = ConfigurationManager.ConnectionStrings
["connstr"].ConnectionString;
NorthwindDb db = new NorthwindDb(strConn);
var results = from emp in db.Employee
              where emp.EmployeeID == 
               (int)DetailsView1.SelectedValue
              select emp;
results.First().FirstName = ((TextBox)DetailsView1.
Rows[1].Cells[1].Controls[0]).Text;
results.First().LastName = ((TextBox)DetailsView1.
Rows[2].Cells[1].Controls[0]).Text;
db.SubmitChanges();
BindGridView(string.Empty);
}

Here, we first find the row matching the EmployeeID to be updated. We then set its FirstName and LastName properties. Notice the use of First() method to return just the first element of the results. Finally, SubmitChanges() method is called as before.

The Delete operation takes the same lines as that of insert and is illustrated below:

protected void DetailsView1_ItemDeleting
(object sender, DetailsViewDeleteEventArgs e)
{
string strConn = ConfigurationManager.ConnectionStrings
["connstr"].ConnectionString;
NorthwindDb db = new NorthwindDb(strConn);
var results = db.Employee.Single(emp => emp.EmployeeID
==(int)DetailsView1.SelectedValue);
db.Employee.DeleteOnSubmit(results);
db.SubmitChanges();
BindGridView(string.Empty);
}

Here, we first find the employee record to be deleted. Notice the use of Single() method to fetch a single element. The DeleteOnSubmit() method then deletes the employee object from the Table. Finally, SubmitChanges() method propagates the changes to the database.

That's it! Run the web form and test its functionality.

In this part we (or rather LINQ framework) used raw INSERT, UPDATE and DELETE operations. In many cases you may want to use stored procedures to manipulate data. That is the theme of the next part of this series.