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.
Creating Data Layer
Before we generate our data layer we must create a new connection in Server Explorer which points to Northwind database.
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.
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.
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
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();
}
}
}
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();
}
}
{
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();
}
}
{
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.
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();
}
}
{
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.
193 Responses to LINQ To SQL Tutorial
Leave a Reply
Connect with me
-
Thanks Bipin.
Thanks
your document is very useful
Hello Deepak, great article, i follwoed your example, however, when I insert data, I get a Insert statemnet confilt,
The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Vendors_GLAccounts”. The conflict occurred in database “Payables”, table “dbo.GLAccounts”, column ‘AccountNo’.
The statement has been terminated.
How do I solve this problem when I am inserting data.
Hi Naveed,
I assume that you are setting the AccountNo column in your Payables object. Is AccountNo the name of column? If yes then try setting the Account object or whatever it’s called in your schema.
Thank you, Deepak, I am trying to insert an identity column from the UI, the Linq to sql is not inserting the PK autogeneretd field.
VendorId is the primarykey. How do I insert auto generated field when I am inserting data?
Naveed,
Have a look at the definition of VendorId. Does it have DbType=”Int NOT NULL IDENTITY” in the Column attribute? If not then that’s your problem. Double check that the column is marked as Identity in your table and re-generate your entities.
Hope this helps.
Thank you Deepak, you are awesome.
Most welcome Naveed.
Good work Deepak also add delete and update using LINQ….
hi..this is a nice article.but i want select only specific columns as we select in the query like
“select a,b from table1″..how can do this in link to sql..
Jiuadheen,
You can select individual columns by using anonymous type. Here is an example
from c in context.Customers
select new { Name = c.name, Age = c.Age}
suppose i have display data in the gridview but how to read gridview data by linq.
[...] post is to mark a little milestone for ThereforeSystems. My second post on this site was a LINQ To SQL Tutorial which was a basic tutorial to get the reader started with LINQ To SQL. This post has also been one [...]
Well Done Deepak
It was really nice way to teach beginners like me.
Write a tutorial about crystal reports. So that we get an
idea to work with it.
Best Regards,
Aftab
Deepak..
This Example is absolutely Helpfull for me!
Need more examples using stored procedures deepak..
Keep on posting good articles like this..
Thank you Deepak..This is a good article for beginners…
Thanks Satish and Ashok.
very nice artical friend…
Thanks for encouragement Vishal.
Simple and very good article.Any body can understand linq by reading this article.good work,keep it up.
I am making online test….
but problem is i am getting first question from database but not getting more question from database on next button so plz help me….
hey Bro… nice example
it really helped me understanding LINQ to SQL..
keep posting…
can u post something about .net generics??
Thanks Shyam.
Hi,
Its nice one for beginners.its biggest use full for me.keep post like this.take care.
Thanks so much!
Its really awesome Deepak. Please keep posting many articles in Linq. Thanks.
Thanks NIthya and Sauchem.
It is very helpful.You have described it in very eazygoing way.
Thanx..
Thanks, this has been really helpful to me.
hi
Deepak
this artical was veryy help full to beginers ,and please post insert ,update delete within the datagridview only not enter into text boxes , i need this one
thanks
Good evening Deepak
this is also deepak
pls
give me reply to my question
q is
how to insert a row in table if it is consists identity column and timestamp using “linq to sql”
Hi Deepak,
Take a look at UpdateCheck parameter of Column attribute for what you are trying to achieve.
Great article, thank you for posting!
Hi Deepak,
I really have to appreciate you for the valuble time you have invested in producing this article, to teach people like me. Please keep up your good work and we are hungry for more. ye dil maange more.
Thanks a lot again
I Read Tutorial . It quite Useful for Developer to aware with linq Topic .Its Quality Standard is maintain. Use Must Read Tutorial to be Benefited.
Ashok Verma
Software Engineer
Om-Nano-Tect Pvt Ltd
Hi Deepak,
It is very helpful for beginners. With in a short duration, i got quick idea about linq
Thanks
Nice article to start with….
Great article. You gotta be making a lot of $$$. You deserve it buddy.
Haha! I wish :)
Unless I missed something, you totally skipped over the DataContext. Where is the NorthwindDataContext coming from in the Search button’s Click event? I’m using VS2010.
I found the context. It’s the partial class created under the .dbml file that inherits from DataContext. Sorry about that…
No worries Kirk. All the best with what you are doing.
Very good Deepak.
This is the one explaination where one ‘gets it’ right away…
Thanks Mac for your encouraging comment.
Customer customer = new Customer
what is Customer here?????
Hi Vivek,
Customer class is generated by Visual Studio for you. Put your mouse over Customer and view definition. You’ll see that the class is present in the .cs file accompanying dbml.
Very nice
hey thanx man.. its awsome tutorial.. thank you very much
Thanks Hasmukh, Pankaj :)