LINQ to SQL Sample

Monday, May 7, 2012

LINQ to SQL Sample

LINQ to SQL Sample

In this sample we will use O/R Designer to create the mapping between the Objects and the Relational data model. The O/R Designer is an editor which can be used by developers to create the LINQ to SQL mapping.

Open Visual Studio 2008 and create a new Asp.Net Project
Right click the Project and select Add -> New Item
In the list of Templates select the LINQ to SQL Classes template
Rename the default file name DataClasses1.dbml to an appropriate name, let us name it as EmployeeClasses.dbml
Click Add
This will open EmployeeClasses.dbml with 2 panels, the left side panel displays the objects and the relation between the objects, the right side panel displays the methods associated with the objects.
Open the server explorer and add a connection to the target relational database

This creates an Empty DataContext EmployeeClassesDataContext, we can now add objects to the DataContext by dragging items from the Server explorer.

Now, drag the required tables from the server explorer and drop them on to the left side panel of EmployeeClasses.dbml, the screen should look as follows

The connection details used to create the DataContext are also added t to the web.config file in the connection strings section.

   <connectionStrings>
     <add name="EmployeesConnectionString" connectionString="Data
     Source=D-172956\SQLEXPRESS;Initial
     Catalog=Employees;Integrated Security=True"
         providerName="System.Data.SqlClient" />
   connectionStrings>

Now we are ready to use the Objects defined in the DataContext, create a new Asp.net form and add the following code to the Page_Load event

   EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();
   var emp = from employees in dbContext.Employees select employees;

   grdEmployees.DataSource = emp;
   grdEmployees.DataBind();