LINQ To SQL Tutorial

July 31st, 2008 | 59 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 52803 views, 177 so far today |

Tags: LINQ

Follow Discussion

59 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
Joey Says:
July 8th, 2009 at 8:40 am
Hello:

I’m new to LINQ. If I had two tables “Customer” and “CustomerDetails”, how (using LINQ) can I insert to both of these tables? Do I need to create a “Customer” object as well as a “CustomerDetails” object?

Thanks for your patience.
Joey
Karthick Says:
July 8th, 2009 at 11:40 pm
This is a very useful “Get Started with DLINQ” tutorial. Thanks very much.
Majid Says:
July 22nd, 2009 at 12:11 am
Thanks Rohan, the best article. I learned a lot.
Priya Says:
August 19th, 2009 at 4:08 pm
HI,

Very nice article..Easy to understand..I want Dot net updates in my mail.Could u please give me a link..
tareq Says:
August 21st, 2009 at 7:57 pm
Thanks for nice article.
This is very helpful to learn linq.
Deepak Says:
August 21st, 2009 at 8:39 pm
Tareq,

Thanks for your motivating comment.
balu Says:
August 23rd, 2009 at 1:49 am
Nice article.
Dipty Says:
September 10th, 2009 at 6:36 pm
Hi its a nice article.
great job!!!
nadeem Says:
September 10th, 2009 at 7:41 pm
You have written very good tutorials.Please provide more tutorials on linq.
Rajat Kapoor Says:
September 15th, 2009 at 2:09 pm
Excellent demonstration which made me LINQ concept clear.

Regards
Rajat K
Deepak Says:
September 15th, 2009 at 2:17 pm
Thank you Rajat.
edgar allan Says:
September 19th, 2009 at 7:38 pm
hey, i don’t believe this bloody LINQ and you know why? with LINQ you are writing your sql code in the client side which is not advisable because when you submit it to the server it needs to be compiled before being executed where you are hurting the server performance. so stick with stored procedure if you already in, its pre-compiled,less server loads, fast and efficient.
Rohan Dave Says:
September 24th, 2009 at 10:26 pm
Hi guys

First I would say sorry because it’s not related to Linq but it’s really urgent for me and need your help to fix this issue in my work.

I have one XML file having XML Schemas. I have generated it using DataTable.WriteXML().The structure of data table is little different like,I have one column inside datatable say “Rows” which is again type of DataTable. So the cell under “Rows” column for each rows contain another data table having some amount of rows. So the generated XML is having multiple schemas.

Now when I am trying to read the XML file using DataTable.ReadXML(),it’s throwing me error like “The supplied xml instance is a schema or contains an inline schema. This class cannot infer a schema for a schema.”

can you guys please help me out ?

Thanks,
Rohan Dave
Deepak Says:
September 25th, 2009 at 9:43 am
Rohan,

First thing to check is that your xml is well formed. Next just try to read it through XMLDocument or LINQ To XML and see if you get similar error.
Rohan Dave Says:
September 26th, 2009 at 4:26 pm
I have made some changes,now i am writing XMl using DataSet.WriteXml(filepath,XmlWriteMode.WriteSchema). now when i am trying to read it using DataSet.ReadXml(XmlReader,
XmlReadMode.ReadSchema)and it’s now throwign error”No parameterless constructor defined for this object.” and sometimes it;s giving me error “DataTable does not match to any DataTable in source.”. I say it’s weird:-). I believe there is XML is generating correctly somthing is wrong inside XML.the xml is looking like below :

false
1
Test
Rohan Dave Says:
October 1st, 2009 at 7:55 pm
Hi Guys

do anyone of you have any idea about converting HTML to XML ? i mean how to convert HTML to XML file using C# asp.net. I have searched on internet but didn’t find valuable information. also in .Net i don;t think , there is any class or function which will directly convert HTML to XML file.

can you guys please help me ?

Thanks,
Rohan
Deepak Says:
October 1st, 2009 at 10:04 pm
Rohan,

Your HTML document is already a xml document especially if it follows the appropriate DOCTYPE. You don’t really need to convert it.
Rohan Dave Says:
October 31st, 2009 at 9:39 pm
hey guys, i need you help. I have two calander control on my page one for StartDate and one for EndDate. also i have two Dropdown one for week(first,second,third and forth)and one for days(Sunday to Saturday). Input is as below:
StartDate(mm/dd/yyyy) = 11/17/2009
EndDate(mm/dd/yyyy) = 11/17/2010
Week = Second
Days = Wednesday
now i want only the second Wednesday between given StartDate and EndDate as output. like (mm/dd/yyyy) :
11/11/2009
12/9/2009
01/13/2010
02/10/2010
03/10/2010
04/14/2010 and so on.
can you please help me out here ?
Thanks
Rohan Dave Says:
November 2nd, 2009 at 8:25 pm
Greetings !! it’s done :-)
Thanks
Lo Says:
November 10th, 2009 at 1:15 am
What about when you a key in one table which relates to a value in another? Such as a Guid which relates to a name in a NAME_Table, how would you pull that with Linq?
Deepak Says:
November 10th, 2009 at 11:50 am
Hi Lo,

In the scenario you described, you can use a join to fetch related data from two tables.
Rohan Dave Says:
November 10th, 2009 at 4:47 pm
hi Lo -
yes you can use join like below:
var queryjoin = from a in testtable
join b in NAME_table on a.[yourGUIDfield] equals b.[yourGUIDField]
select new { list of columns you need };

you just need to make sure when joining the table that the outer expression must be first and then inner sequence.otherwise query will not be complied.
Lakshmi Kumar Manyam Says:
November 15th, 2009 at 7:05 pm
Hi Deepak,

This article is real good and awesome. I dont know how to work with LINQ. Your article has made my learning easy and its damm good. Thanks for your beautiful and explanatory article.

I need a small info like, in your explanation you have explained about getting or inserting values, At times we will need to get values after lot of filtering and conditions that will be handled in StoredProcedures.

How to work with Stored procedures here. Should we write the whole code that we write in stored procedures in the Data Access Layer?

Also can you provide me with a link that explains about the syntax to be used while coding using LINQ.
Deepak Says:
November 15th, 2009 at 7:14 pm
Hi Lakshmni Kumar,

Thanks for kind words. Working with stored procedures in LINQ To SQL is very simple. I recommend this resource on MSDN to gain in-depth understanding of using stored procedures with LINQ To SQL.

http://msdn.microsoft.com/en-us/library/bb386946.aspx

Let me know if you have any specific questions.
Rasmi Says:
November 24th, 2009 at 5:51 am
Very good explanation !!!!! Thanks.
Deepak Says:
November 24th, 2009 at 6:35 am
Thank you Rasmi.
david crossan Says:
December 5th, 2009 at 11:18 am
hi deepak

i need to set up a console application(client) that does crud operatiions to a database which is hosted in a WCF service library template. do you know what type of code i would put in the console application(program c.s) i have allready added the sql linq classes but i dont know the code.

any examples you could give me
Rohan Dave Says:
December 5th, 2009 at 3:27 pm
Deepak – hope your are fine :-)
i have one question regarding SQLMetal. suppose once we have created DataContext using SQLMetal, and in future if we need to add/delete some fields from the SQL table, then this changes automatically reflects in DataContext or do we need to refresh it manually by someway. if so then what’s the way to refresh DataContext ?
Thanks in advance !! Rohan
Deepak Says:
December 7th, 2009 at 8:02 am
Hi David,

I’m not sure if I understand what you are trying to do. You’ve lost me at a database hosted in a WCF Service library template. What is the architecture of your application?
Deepak Says:
December 7th, 2009 at 8:05 am
Rohan,

Data context will keep track of all changes you make to your entities. That’s what it does.