Tags

, , , , , , ,

Language-Integrated Query is a set of features those are introduced in Visual Studio 2008. It extends query capabilities of languages C# and VB for both windows and web applications.

In this post, I will explain one of its powerful and important components of .Net framework 3.5 LINQ to SQL. It allows modeling a relational database using .NET classes i.e. it provides run-time infrastructure for managing relational database as objects. We can use LINQ to access the SQL database. We can query (insert/update/delete) the database.

Here we will take Northwnd.mdf as a sample database to show how it works.

Step 1: Create connection to the database.

  • Choose Server Explorer from the View
  • Right click on the Database Connections and choose Add Connection.
  • Browse and select copy of mdf.
  • Test Connection to see successful connection.
  • OK.
Add connection to databse

Add connection to databse

It will look like:

LINQ to SQL database

LINQ to SQL database

Step 2: Add New Item -> LINQ to SQL Classes from Visual Studio installed templets and name it. Here we are keeping the default name.

Add LINQ to SQL class

Add LINQ to SQL class

Step 3: Drag and drop the tables in DataClasses1.dbml. We have selected Customer table.

Add table to DataClass DataContext

Add table to DataClass DataContext

You can delete some of the fields from the Customers table.

DataClass DataContext view

DataClass DataContext view

Important notes:

  • When you dragged the Customer table onto the designer an Object Relational Mapping (ORM) was created between the Customer table in the database and a Customer class generated by the SQL Designer and placed in DataClasses1.designer.cs. This object is called an entity class and it allows you to access the data and fields of the Customer tables as if they were an object in your project.
  • A second class, referred to as a DataContext, was also created in DataClasses1.designer.cs. You can use this class to automatically connect to your database and to easily access the data and fields in the Customer table.
  • A file called config was added to your project. It contains an automatically generated connection string for your database.

Now, you can query on Customer table in your .cs file in Visual Studio editor.

var query = from c in db.Customers

where c.City == “London”

select c.ContactName;

Here variable query contains all the Customers who lives in City “London”. You can display them as follows:

foreach (var q in query) {

Console.WriteLine(q);

}

Source: http://blogs.msdn.com/

Advertisements