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
-
It is useless tutorial
Well written article to get a good introduction to LINQ.
Hi
Thanks for nice explanation .great job :)
Thanks. its very helpful to new learners.
Thanks and this is good for new learner
I have installed SQL server 2008 express, where can I get a copy of the Northwind Database?
Rage,
You can download it here:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23654
Even though the page says that Northwind database is for SQL 2000, it will equally work well on SQL Server 2008 express.
Thanks Deepak, you are awesome!
Most welcome Rage. You made my day with your comment :)
You done a good job. It is really nice.
Usually I don’t comment and don’t let the people know how I deeply thank them for providing information but this time, it is so useful that I just couldn’t stand. This is exactly what I was searching for. So simple and comprehensible unlike the other examples given in other sites. After searching hours for this, it just won’t be enough to thank you over and over again. Just thank you. You’re awesome !
Omer, wow! I’m short of words here. Thanks man for your very encouraging comment.
Special Code , thanks … it’s very easy to learn
thnksss baby
Thanks deepak for nice and clean article, it really helps
what about update query? how we can update in table????
Hi
I am trying to insert a record in my table using the following code. and i am getting the error
“unable to update the entity set because it has a defining query and no element exists in the elemnet to support the current operation.
There is no any column in my table which is primary.Follwoing is the code i written
CDRegistration cdReg = new CDRegistration();
cdReg.Date = DateTime.Now;
cdReg.Serial_Number = objIdentityXML.SelectSingleNode(“//SerialNo”).InnerText;
cdReg.Install_Code = objIdentityXML.SelectSingleNode(“//RequestCode”).InnerText;
cdReg.Registration_Code = a_sActivationCode;
cdReg.Operator = objIdentityXML.SelectSingleNode(“//UserName”).InnerText;
ctx.AddToCDRegistrations(cdReg);
ctx.SaveChanges();
following is the table structure
CREATE TABLE [dbo].[CDRegistrations](
[Date] [datetime] NOT NULL,
[Serial Number] [nvarchar](50) NOT NULL,
[Install Code] [nvarchar](50) NULL,
[Registration Code] [nvarchar](80) NULL,
[Comments] [nvarchar](max) NULL,
[Registration Returned] [bit] NULL,
[CD Binding] [bit] NULL,
[Subgroup] [int] NULL,
[AUEnabled] [bit] NULL,
[WDEnabled] [bit] NULL,
[Operator] [nvarchar](10) NULL,
[Renewal] [bit] NULL,
[incd] [bit] NULL
) ON [PRIMARY]
Please help me where i am doing wrong.
Thanks in Advance
ctx.AddToCDRegistrations(cdReg);
replace above line with
ctx.CDRegistration.InsertOnSubmit(cdReg);
ctx.SubmitChanges();
hope so this wiil help u !!!!
Happy Coding!!!
It is very helpful.You have described it in very eazygoing way.
Thanx..
Hi,
it,s useful….
Raja
Thank you Raja, K Mohan.
Thanks a lot .. it’s very easy to learn…..
wow beautifull simple and easy tutorial to get me running ;) .. thanks !
Thanks Parvin and Pedro for your kind comment.
Thanks for this – great.
very useful for beginners and very nice
awesome article it helped me a lot.thanx
Harj, Santosh, Rohit,
You are most welcome and thank you for your kind comments.
This is realy a good tutorial to understand linq to sql
Really it is very useful to understand the practical
approach of Linq in application. Nice Job!!
thnx It is very useful to al..
awesome one…great job man
how to write this code in c# using linq to sql
‘j_’ + (CHAR(64+DATEPART(MONTH,getdate()))
+CONVERT(VARCHAR,DATEPART(day,getdate()))
+CHAR(65+DATEPART(HOUR,getdate()))
+CONVERT(VARCHAR,DATEPART(MINUTE,GETDATE()))
+CONVERT(VARCHAR,DATEPART(SECOND,GETDATE())))
nice article for bigginer in LINQ
Thanks just started at a new job and I needed to understand LINQ and this was just perfect very simple!! thank you man.
Most welcome and all the best at your new job.
Nice introduction of LINQ for learner.
Very easy n Simple way of writing articles… it helps alot..
but where is Update and Delete Query in LinQ to sql.
Hi Waseem,
There is no update or delete query. Essentially you modify your entity after you have retrieved it and then call submit changes for an update. For delete you mark your retrieved entity as with DeleteOnSubmit() and then call submit changes. Here is a link which explains more: http://msdn.microsoft.com/en-us/library/bb386925.aspx
thanks for such a nice article..
private void LoadButton()
{
int x = 30;
int y = 12;
int j = 0;
var q = from a in data.Tables
select a;
var count = q.Count();
for (var i = 0; i <= count; i++)
{
j = i + 1;
Button bt = new Button();
bt.Size = new Size(200, 74);
bt.Top = x;
bt.Left = y;
bt.Name = q.First().ID.ToString(); <- wrong
bt.Text = q.First().Name.ToString(); <- wrong
x += 173;
if (j % 6 == 0)
{
x = 30;
y += 84;
}
pnButton.Controls.Add(bt);
}
}
select data of row in database but it erow, can you fix it for me,Thanks
Dim str As String = ” select nom_quart,count(b.id_bien)’nbr_bien’,q.id_quartier from bien b ” & _
‘” inner join type_annonce ta on b.id_typ_annon=ta.id_typ_annon ” & _
‘” inner join sous_type_bien stb on b.id_sou_typ=stb.id_sou_typ ” & _
‘” inner join type_bien tb on stb.id_typ_bien=tb.id_typ_bien ” & _
‘” inner join quartier q on b.id_quartier=q.id_quartier ” & _
‘” inner join commune c on q.id_commun=c.id_commun ” & _
‘” inner join ville v on v.ID_VILLE=c.ID_VILLE ” & _
‘” where v.ville =’” & ViewState(“ville”) & “‘” & _
‘” and typ_annon =’” & ViewState(“type_annon”) & “‘” & _
‘” and type_bien=’” & ViewState(“type_bien”) & “‘” & _
‘” and visible_ann=’Annonce visible’ group by nom_quart,q.id_quartier”
on linq svp
very useful. and easy to understand. great job Deepak.. Thank u..
It is very useful,thank you.