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.