LINQ To SQL Tutorial

July 31st, 2008 | 28 Comments | Posted in Programming

Introduction

With .NET Framework 3.5 Microsoft released Language Integrated Query aka LINQ. LINQ enables developers to query data sources using a query like syntax with both C# and VB.NET. These data sources can be collections, SQL Server databases, XML, DataSets etc. Other than what is supplied by Microsoft, LINQ is also extensible. This means that you can query data sources beyond what Microsoft ships. Examples of such implementations are LINQ To Flickr, LINQ To Amazon, LINQ to Google etc. In this article I will show you how you can use LINQ To SQL to perform CRUD operations on a SQL Server database. I will use Northwind database and build an ASP.NET application to demonstrated the capabilities of LINQ To SQL. You can download Northwind database here.

Toolset for this article

Visual Studio 2008
.NET Framework 3.5 (This is already installed if you have Visual Studio 2008)
SQL Server 2005 (You can also work with SQL Server Express)

Solution Structure

For this article we will need two projects. One is a data layer (created as a Class Library)which we will generate and the other is an ASP.NET Web Application. The solutions structure looks like this in Solution Explorer.

LINQ

Creating Data Layer

Before we generate our data layer we must create a new connection in Server Explorer which points to Northwind database.

LINQ

We will now generate our data layer using LINQ To SQL. To do this you need to add a new item to the data layer project of type LINQ to SQL Classes. We will name it Northwind as shown below.

LINQ

After adding a LINQ to SQL Class we are presented with a designer surface. Here we can simply drag the tables which will become part of our data layer. For this article we will drag all tables on the designer by selecting them all in one go. Our designer should look like this after dragging all tables on it.

We should now build our solution to make sure everything is okay. And that’s it. We have successfully generated our data layer. In Solution Explorer we can see that we have two new files namely Northwind.dbml.layout and Northwind.designer.cs. We can also see that references required to compile and run our code have been added by Visual Studio.

LINQ

The .cs file contains the code for our data layer. Let’s examine the code that has been generated for us. We will look at the Region class.

[Table(Name="dbo.Region")]

public partial class Region : INotifyPropertyChanging, INotifyPropertyChanged

The class itself is decorated with Table attribute and the Name property has been assigned the actual table name we have in our database. Region class also implements INotifyPropertyChanging and INotifyPropertyChanged interfaces. These interfaces are used for databinding. Region class also contains one property per column. Let’s look at the RegionDescription property.

[Column(Storage="_RegionDescription", DbType="NChar(50) NOT NULL",

CanBeNull=false)]

public string RegionDescription

get

    return this._RegionDescription;

set

    if ((this._RegionDescription != value))

      this.OnRegionDescriptionChanging(value);

      this.SendPropertyChanging();

      this._RegionDescription = value;

      this.SendPropertyChanged("RegionDescription");

      this.OnRegionDescriptionChanged();

Columns are decorated with Column attribute and values are passed in for Storage, DbType and CanBeNull which indicates if the column can be null or not.

Using Data Layer

Now that we have generated our data layer. We will work on ASP.NET web application where we will use our data layer. To keep things simple we will create a web forms to search for customers and display search results. We will also create a web form to insert new customers. Let’s start by creating our web form for customer search. For this we will use the Default.aspx page. We will place few controls on the web form. These controls will give us search parameters and a button which will do the search and display results when clicked. This is what the form will look like after placing our controls.

We will also place a GridView control on our form to display search results. We will now put in some code in our button’s click event handler to do the search and display results in GridView. Make sure that we have a reference to Data Layer project, System.Data.Linq and appropriate using statement. Here is what our button click event handler will contain.

protected void buttonSearch_Click(object sender, EventArgs e)

  using (NorthwindDataContext context = new NorthwindDataContext())

    var customers =

      from c in context.Customers

      select c;

    gridViewCustomers.DataSource = customers;

    gridViewCustomers.DataBind();

This code will query the customers table in northwind database and will return all customers. We will now modify it slightly to accept customer name and company name as parameters for our query. After modification our event handler looks like this.

protected void buttonSearch_Click(object sender, EventArgs e)

  using (NorthwindDataContext context = new NorthwindDataContext())

    var customers =

      from c in context.Customers

      where (

        c.ContactName.Contains(textBoxCustomerName.Text.Trim())

&&

        c.CompanyName.Contains(textBoxCompanyName.Text.Trim()))

      select c;

      gridViewCustomers.DataSource = customers;

      gridViewCustomers.DataBind();

Our search results will now be filtered.

Let us now created a data entry form for customers. We will insert a new web form in our ASP.NET project and call it CustomerEntry. To start with we will make sure that our form contains fields required to insert a customer. Our form after completion will look like this.

LINQ

We expect a new row to be inserted into customers table when Save Customer button is clicked. This code achieves data insertion into customers table for us.

protected void buttonSave_Click(object sender, EventArgs e)

  using (NorthwindDataContext context = new NorthwindDataContext())

    Customer customer = new Customer

      CustomerID = textBoxCustomerID.Text,

      CompanyName = textBoxCompanyName.Text,

      ContactName = textBoxCustomerName.Text,

      ContactTitle = textBoxTitle.Text,

      Address = textBoxAddress.Text,

      City = textBoxCity.Text,

      Region = textBoxRegion.Text,

      PostalCode = textBoxPostalCode.Text,

      Country = textBoxCountry.Text,

      Phone = textBoxPhone.Text,

      Fax = textBoxFax.Text

};

    context.Customers.InsertOnSubmit(customer);

    context.SubmitChanges();

Similarly an existing row in database can be updated by first retrieving the data and then submitting it via DataContext.

Conclusion

In this tutorial we have not written a single SQL statement to retrieve or insert data into a database. This is the beauty of LINQ To SQL. Further our retrieval code while in C# looks a lot like a query. We can already appreciate the benefits of such a streamlined and unified approach in dealing with data.

Technorati Tags: LINQ

Leave a Reply 21652 views, 105 so far today |

Tags: LINQ

Follow Discussion

28 Responses to “LINQ To SQL Tutorial”

ahmad Says:
February 17th, 2009 at 6:16 pm
hi
it’s Wery Good .
thanks
Deepak Says:
February 17th, 2009 at 6:53 pm
Ahmad,
Thanks for kind words.
Mohammed Says:
February 22nd, 2009 at 9:16 pm
Deepak:

Your explanation is awesome, you’ve done a great job in explaining a complex topic with such ease. I’ll be looking forward for more from you.

Thanks!

Mohammed
Deepak Says:
February 23rd, 2009 at 8:39 am
Hi Mohammed,

Thanks for encouraging comment. Comments such as yours are very motivating.

Thanks again.
Sachith Says:
March 16th, 2009 at 4:58 pm
nice article.
Thanks.
Deepak Says:
March 16th, 2009 at 7:56 pm
Thankyou Sachith.
Brent V Says:
March 27th, 2009 at 5:47 am
This was clear and really helped. Thank you!

Brent
Deepak Says:
March 27th, 2009 at 8:29 am
Thanks Brent. I’m glad that you found the article useful.
Rajasekar Says:
April 9th, 2009 at 12:47 am
Thank you. Its much clear!
Deepak Says:
April 9th, 2009 at 6:58 am
Rajasekar, Thanks for kind words.
Rohan Dave Says:
April 13th, 2009 at 4:02 pm
Hi,
It’s really a very nice example.I just want to know something regarding O/R designer.Is there any way to add tables without using O/R designer?
I mean to say after adding LINQ to SQL class, can we add tables form Server Explorer to design surface dynamically? Is there any method exist to do this? like Add() method.
Kindly let me know , I am waiting for your replay.
Deepak Says:
April 13th, 2009 at 7:00 pm
Hi Rohan,

You can use SQLMetal tool to generate LINQ To SQL entities. SQLMetal does not need a designer as it is a command line utility.

SQLMetal is shipped with Windows SDK. Once you have installed the SDK you can find SQLMetal at C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\SqlMetal.exe
M.Nabil Says:
April 14th, 2009 at 9:49 am
This helped me too much.thanks alot for those who published that and I hope to see more..THANKS
Rohan Dave Says:
April 14th, 2009 at 4:51 pm
Hey Deepak,

Thanks for information and quick response :-).Can you please let me know how to use this SQLMetal.exe ? do we need to use this in the C# IDE or something else? Actually I haven’t used this so confused.

Thanks
Deepak Says:
April 15th, 2009 at 11:01 am
Hi Rohan,

You do not need C# IDE to run SQLMetal. Ben Hall wrote an excellent post on SQLMetal which shows you different ways the tool can be used. Here is the link http://blog.benhall.me.uk/2007/08/power-of-sqlmetal.html
Rohan Dave Says:
April 16th, 2009 at 2:41 pm
Hi Deepak,

Thank you very much.I have taken a look on your given web link and it’s really amazing.I can learn lots of things from this.Again thanks a lot for your help.I want to register myself on this onedotnetway site.Does site has this facility ?
Deepak Says:
April 16th, 2009 at 2:55 pm
Hi Rohan,

I’m glad that you found the information helpful. You can subscribe to One .Net Way feed http://feeds2.feedburner.com/OneDotNetWay

Or

Get daily updates by email. Go to “Connect with me” orange box on this page (about half way up) and enter your emaill address and hit subscribe.
Rohan Dave Says:
April 27th, 2009 at 3:09 pm
Hey Deepak,
what’s up? I have one question in my mind regarding LINQ to SQL. is there any properties or method supported by LINQ to SQL which returns SQL query itself during debug the code? I mean to say whether we can retrieve SQL query itslef using LINQ code in the C#.net or not.
Waiting for your replay :-)
hey dude are you in LinkedIn :-)
Deepak Says:
April 27th, 2009 at 4:08 pm
Hi Rohan,

There are few ways to capture the query generated by LINQ To SQL. I wrote a post about it some time ago. Here is the link:

http://www.onedotnetway.com/view-query-generate-by-linq-to-sql/

Yes I am on Linked In. Here are links to me on Social Networks:

Linked In: http://www.linkedin.com/in/kapoordeepak
Facebook: http://www.facebook.com/people/Deepak-Kapoor/625431493
Twitter: http://twitter.com/deepakkapoor

Hope to see on some of them soon.
Sanhita Says:
April 29th, 2009 at 5:33 am
Brilliant……pls cover as much topic as you can…..its really an asset….
Deepak Says:
April 29th, 2009 at 7:06 am
Thank you Sanhita.
Rohan Says:
April 29th, 2009 at 3:09 pm
Thank you very much Deepak. yes you will see me on linkedin soon
thanks again
ihtesham Says:
May 5th, 2009 at 11:58 pm
Hi

Thanks for this info but have you any ideas on how to implement Linq to sql in a 3 layered application model. I am struggling with that.

Thanks in advance
Balaji Birajdar Says:
June 16th, 2009 at 7:11 pm
Brilliant. This post was exremely helpful to me. Thanks a lot. Keep up the good work
Deepak Says:
June 16th, 2009 at 7:40 pm
Balaji,

Thanks for your encouraging comment. I’ll try my best to keep up :)
sandesh Says:
July 2nd, 2009 at 11:36 pm
thanks
its very usefull