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.
using System.Data.Linq;
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.
Subscribe