How to use Linq with ASP.Net GridView

Linq is one of the best things that has happened in recent past in .Net framework world. It has made data manipulation so much easy. Yes, Linq has its own set of limitations but for most part it does the job. For those special cases you can always go back to classic ways. In this post, I will describe use of Linq for Sql and provide some simple answers to questions like:

• How to connect to database using Linq?
• How to query data from a table using Linq?
• How to use Linq with GridView, DataGrid etc.?

Namespace and reference to use Linq with Sql

The classes that you need to use Linq with Sql live in System.Data.Linq namespace. So in your project you will have to add the following line at top of your source code file.

This namespace lives in System.Data.Linq assembly. That means that you will have to add reference to this assembly in your project. Now you are all set to us Linq.

How to connect to the databae?

You have been using ADO.Net for a while now and first thing we all do is have a connection object that will be used to connect to database, open it and later on close it. Well when you are using, all that plumbing is taken care of for you by Linq frameework. The entry point to all actions in Linq for Sql is DataContext object. This object takes care of establishing connection with the database. You can provider either a connection string or connection object to create instance of DataConext object. Rest will get taken care of for you.

var dataContext = 
new DataContext(ConfigurationManager.ConnectionStrings["blogengine"].ConnectionString);

Query data using Linq

Now you have set up DataContext object, it is time to get some data from the database. You will notice that DataContext object provides methods like GetTable, ExecuteQuery, ExecuteCommand etc. All the methods that return collection of data, expect another parameter which is Type of an object that represent the data returned from query or command. In otherwords, the method is looking for a mapping between table in the database to an object. Here is an object definition that I used in my code to map to fields from a datatable that I wanted to fetch.

[Table(Name="be_Posts")]
public class BlogPost
{
  [Column(IsPrimaryKey=true)]
  public Guid PostID
  {get;set;}

  [Column]
  public string Title
  {get;set;}

  [Column]
  public string MiniUrl
  {get;set;}
}

There are few attributes on this class that are to be notices. First, there is TableAttribute on the class itself. By default Linq assumes that name of the class or object is same as table in the database. But if your table name does not match with the class name, then you can use this attribute to provide the name of the table that maps witht this obejct. For example in my case table name be_Posts is to be mapped to BlogPosts object which is my ViewModel. Next attribute is ColumnAttribute. You will assign this attribute to properties or fields that needs to be queried. If your column name does match with name of the property or field, you can provide that mapping as well. There are more values you can set in the column attribute. I will discuss those in subsequent posts. For now this simple definition of .Net object will work for our simple query purposes.

Bind Linq To GridView

Now we have our Linq query set to go, rest is just setting this collection to our GridView object on ASP.Net page and rest is all taken care of for us. Following code snippet shows how in few lines we are able to connect to database, query the data and bind it to a GridView.

void BindGrid()
{
 var dataContext = 
  new DataContext(ConfigurationManager.ConnectionStrings["blogengine"].ConnectionString);
 var posts = dataContext.GetTable<BlogPost>();
 postsGridView.DataSource = posts;
 postsGridView.DataBind();
}

It is as simple as these 4 lines of code to connect to database, query the table and bind to a gridview using Linq to Sql.