LinQ to SQL

Good day. This is the first of a series of my blogs about one of the new technologies that will ship with Visual Studio 2008 called LinQ or Language Integrated Query.

Linq is a new way to write code to fetch, filter and expose data to your application. It is included in the newest framework (3.5) that will be released with Visual Studio 2008.

To use LinQ you will need to call the System.Data.Linq namespace.

Along with using LinQ, ORMapper (Object Relational Mapper) which is also a new feature in VS2008.

To perform this demo, I will create a simple blank website, with a Gridview and populate it with data from an existing Northwind database in my machine. I will also be using SQl Server 2005 as may database provider.

Here are some simplified steps in creating a LinQ enabled application.

1. Prepare an application. For this, I will use VS2008 and create a new website.

2. Prepare your datasource. Although LinQ can query an XML file and even a collection or any datasource following the IEnumerable type, for this demo I will use SQL Server 2005.

3. Prepare an ORMapper. This can actually be done by .Net for you. All one needs to do is to add a Linq to SQL class in your application and .Net will create a Datacontext object for you where you can add/define your tables as classes. You can create or transform an old class or object to become a Datacontext so you won’t need to create multiple objects. The process involves calling the Mapping namespace and editing the existing class to become a datacontext. I will cover this in another blog.

4. Add a LinQ to SQL Class.

5. Use Linq and other new language features to query your data.

Let’s start by creating a new website using VS2008.

image

Here you will notice another new feature in VS2008. This is a new IDE feature called the Design/Split/Source View. The SPLIT view is actually the new feature where you can see the design and markup of your page at the same time. Whatever changes you make to either pane, will almost instantly reflect in the other pane, which I think is really cool.

Then we add a Gridview.

image

while adding the Gridview, you will notice that adding simple formatting in VS2008 has been improved. For this demo I selected the "Simple" format.

Now, what I will need to do is add a Linq to SQL class so I can have an ORMapper that I can use to map my data and create a Datacontext object. I right-click the solution and select Add New Item from the popup. The Add New Item dialog opens up and I choose to add a LinQ to SQL Class. I change the name to "Northwind" and click Add. VS2008 might ask if you want to place the code in the App_Code folder.

image

image

Then I drag and drop the Products and the Categories tables from my Server Explorer. You will see that the tables are shown in class view complete with their relationship, which was automatically detected by my ORMapper. This shows how VS2008 treats/shows data as objects.

image

Save the project and proceed to edit the code for Default.aspx.cs

Ideally, your LinQ code might end up in either your Data Access Layer or Business Logic Layer another specialized tier that your application will have. But for the purpose of this demo, I will not go into that kind of complexity and just write the code straight on the Page_Load event.

In my Page_Load event, I type this code:

NorthwindDataContext myDB = new NorthwindDataContext();
var queryKO = from p in myDB.Products
              select p;
GridView1.DataSource = queryKO;
GridView1.DataBind();

This is the simplest query expression. Just selecting all the data from one table and binding that data to a grid.

I now run the application and…

image

This is quite cool. Instead of looping (foreach-ing) through a collection, all I needed to do was bind the resulting LinQ query to the grid. But of course not all queries are created equal. What if I need to be selective of the columns I want to display? For this, I change my code to:

var queryKO = from p in myDB.Products
                      where p.CategoryID == 2
                      select new
                      {
                          p.ProductName,
                          p.CategoryID
                      };

Here you will notice a few things:

1. The LinQ syntax is quite similar to a query written in SQL. This was intentional so developers only needs to adjust a little in order to use LinQ.

2. The keyword var is used to declare the variable queryKO. What datatype is queryKO then? This is called Type Inferencing, where the compiler infers the datatype and assigns that to the variable on the fly. This is not of type variant or object but of an unknown type which is still to be inferred. For this query, since we used LinQ, the inferred datatype is IEnumerable.

3. The "==" is a C# syntax and not exclusive to LinQ. As I have mentioned, LinQ enables you to have a query that is fully integrated with your language of choice. Not only the language, but LinQ is also integrated with the .Net namespaces. For example, if you change the line where p.CategoryID == 2 to where p.ProductName.StartsWith("c"), the StartsWith method will work seamlessly with LinQ.

However, I find it incomplete to just display the CategoryID. This for me is unreadable. So we have to edit our LinQ query to fetch the values of each category form the Categories table. We now change our code to this:

var queryKO = from p in myDB.Products
                      where p.CategoryID == 2
                      select new
                      {
                          p.ProductName,
                          p.Category.CategoryName
                      };

Run the application to see the result.

LinQ actually performs the INNER JOIN behind the scenes for you. This makes LinQ a Declarative type of construct, where all you need to do is tell the computer what to do and not how to do the task. We used to write foreach statements and loop through the collection one by one and tell the computer exactly how to do a task. But with LinQ, all you need to do is tell the compiler to fetch the data that you want and it constructs a SQL Statement based on the criteria that you gave it, pushes that to the datasource and then returns with the data you need.

To view the resulting SQL Statement that LinQ constructs, place a debug stop on the line that reads:

Gridview1.Databind();

Run the application and place your mouse pointer to the queryKO object and view the resulting query.

queryKO = {SELECT [t0].[ProductName], [t1].[CategoryName]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t1].[CategoryID] = [t0].[CategoryID]
WHERE [t0].[CategoryID] = @p0
}

  The constructed query is fairly straighforward and looks like what one would expect from a machine. This query composed by LinQ can still be tweaked but I can guarantee that 99% of the time, the LinQ generated will be as good or maybe even more optimized than what we could probably come up with. You will also notice the @p0 in the statement. This shows that the SQL constructed by LinQ is actually a parameterized query and is safe from SQL Injections.

As a final addition, the var keyword declares your datasource as IEnumerable so if you wish to expose the resulting data, you can expose and catch them as IEnumerable.

Another thing I would like to mention is that the query will be executed only at the GridView1.Databind() line and not at the line where the LinQ statment is written. So if for example you have two datagrids, you will need to call the databind method once for each grid. And if, in between these two calls, your data just happens to change, the freshest data is loaded in the second call to the databind method.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to LinQ to SQL

  1. Unknown says:

    Hi,Do you have second hand lcds, used laptop lcds and used LCD displays? Please go here:www.sstar-hk.com(Southern Stars).We are constantly buying re-usable LCD panels.We recycled LCDs.The re-usable panels go through strictly designed process of categorizing, checking, testing, repairing and refurbishing before they are re-used to make remanufactured LCD displays and TV sets.Due to our recent breakthrough in testing and repairing technology of LCD, we can improve the value for your LCD panels.
    website:www.sstar-hk.com[dfccbebbgebdbid]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s