Introduction
Now what is this new thing? As we all know that every day we have something new coming up. It is the new way to access data from the SQL. LINQ to SQL got introduced in VS 2008, .Net Framework 3.5. Hence update your visual studio to this version before you can work on LINQ to SQL.
First thing before we start, Please forget everything about ADO.Net concepts so that you don't try to relate any of its concepts with LINQ to SQL internals.
Overview
LINQ to SQL uses an Entity-based Framework. It uses a DataContext that contains one or more entity classes. These classes contain public properties that map to each column of the database table that they map to. When we execute LINQ to SQL queries and retrieve data from database, instances of these entity objects get created for each row in that SQL table. You do not have to do all this work, the whole process is internally taken care by the DataContext object.
If you are new to LINQ to SQL, you may not understand some of the above mentioned
terminologies. In short, DataContext is required which helps retrieve data from the
database and also submits our changes back to the data to the database.
Lets see a step-by-step example and relate to what we have read.
Example
a. Lets say we have a Countries table in SQL as below. We will use LINQ to SQL query to retrieve the countryName values from Countries table.
Here we will work on a simple console application mainly to display the data that we retrieve using LINQ to SQL.
b. Add new item to the project called 'LINQ to SQL Classes'. This creates a .dbml file. Select the .dbml file to load the Object Relational Designer (ORD). Drag and drop the Countries table from Server Explorer to the ORD.
As we dropped the Countries table, it creates the Countries DataContext underneath (can be viewed in Countries.designer.cs file). Using the instance of this DataContext object we can read and update data to and from the database.
As we see above, the Country Entity class gets created and it has public properties that map directly to the columns of Countries table. In our code we would use this entity class that stores the data retrieved from SQL table using the CountriesDataContext.
c. Sourcecode:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace linqtosql
{
class Program
{
static void Main(string[] args)
{
GetCountries();
}
Public static void GetCountries()
{
//Create instance of the Countries DataContext class.
CountriesDataContext _countriesDataContext = new CountriesDataContext();
try
{
//Prepare the query to retrieve the data from Countries table
var resCountries = from c in _countriesDataContext.Countries
select c;
//Execute the SQL Query and print the CountryName values
//Also here the CountryDataContext creates
//instances of Country class with data values for each row of data
//in the Countries table.
foreach (var resCountry in resCountries)
{
Console.WriteLine(resCountry.CountryName);
}
Console.Read();
}
catch (Exception ex)
{
Console.Write(ex.Message);
Console.Read();
}
}
}
}
Optional
This is one way of creating DataContext and the Entity Classes. You can also create them on your own, without any tool. Refer http://msdn.microsoft.com/en-us/library/bb386940.aspx for more information about creating manually DataContext & Entity Classes, map them to the database table and query to get the data in the similar way as above.
In the next article we will see some of the common SQL-like operations using LINQ to SQL like how to insert, update, delete, and read using filters, sorting, grouping.
References: http://msdn.microsoft.com/en-us/library/bb386976.aspx
Good article!