LINQ to SQL uses LINQ as querying technique to query the database object through the LINQ to SQL.

What is LINQ?

LINQ stands for Language Integrated Query.  LINQ is a data querying methodology which provides querying capabilities to .Net languages with syntax similar to SQL query. LINQ has a set of querying operators that can be used to query in memory object collection, Sql database, XML, etc. LINQ processing engine will then convert the LINQ query to native query specific to the database to execute against the datasource.  Since, the querying feature is integrated with the language; one can build an efficient query based on the language of their choice.  With Visual Studio, we have intellisense support and with language support, we have type safety and compile-time error checks.

There are many advantages when we use ORM in our projects. Below are few,

1.      You can query the database object treating it as a .net object using .net language.

2.      Your data access development will be easier.

3.      It handles the SQL injection attacks automatically.

4.      You will get type safety and compiler advantages since the database objects are managed as .net object.

5.      Using Visual Studio provides you intellisense support and other tools for developing the LINQ to SQL classes

Constructing your first LINQ to class

Visual studio 2008 is packed with tool called LINQ to SQL designer which will help us to build the LINQ to SQL classes very easily.

LINQ to SQL designer

It is a new designer to design relational database object as LINQ to SQL objects.  We can either, drag and drop database object from “Server Explorer” or, we can design the LINQ to SQL object manually using the designer and tools.

To understand better, we will create two simple tables, Employee and Department, will design our LINQ to SQL class.

Open Visual Studio 2008, Click File >Website and choose ASP.Net Website. Choose the language of your choice and name your website according to your need.

In solution explorer, right the project and select “Add New Item”. Select “LINQ to SQL classes” as shown in below figure. I have named it as EmployeeInfo.

This will add an EmployeeInfo.dbml file inside “App_Code” folder.  In Visual Studio, EmployeeInfo.dbml will have 2 panes. The left pane is for deigning the data objects and the right pane can be used for creating methods that operates on the data objects.

The Visual Studio toolbar will contain a new toolbar for designing LINQ to SQL objects. Refer below,

We will see more about using these toolbar to create our own object in future articles. Once we include the LINQ to SQL Class in the project, there will be 3 files added to the App_Code.

EmployeeInfo.dbml

An XML file that contains information about the tables like table name, column name, type, etc

EmployeeInfo.layout

An XML file that specifies how the designer places the objects.

EmployeeInfo.designer.cs

The C# code file that will have code to interact with the underlying database called DataContext object. It also has entity class definition.

Designing the LINQ to SQL classes

Open Server Explorer, Expand the database tables.

Drag Employee and Department into LINQ to SQL designer and click Save. The Objects will be created automatically. Refer the below figure.

This will update the EmployeeInfo.designer.cs file with class definition for Employee and Department. It will also generate the DataContext object for these objects for interacting with the underlying datasource i.e. it is with the help of this DataContext class we can interact with the database objects in our managed environment. In our case, it is EmployeeInfoDataContext.

The arrow between the object is the foreign key relationship between them. This means that the Employee class will have the Department class as a member.

Thus, we have modelled LINQ to SQL classes which has O/R mapping. Moving forward, we will see how we can we use these objects to do our data access.

Using LINQ to SQL classes

The below code will fetch all the employees rows and will populate a GridView control.

EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();

        var LINQQuery = from emp in dbEmp.Employees

                        select emp;

        gvEmployee.DataSource = LINQQuery;

        gvEmployee.DataBind();

Updating Employee Information

The below code uses the LINQ to SQL class to update the employee information whose employee id is fetched from txtEmpID.

EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();       

        string EmpID = txtEmpID.Text;           

        Employee empTemp = (from emp in dbEmp.Employees

                           where emp.EmpID == int.Parse(EmpID)

                           select emp).Single();

         empTemp.EmpName = txtEmpName.Text;       

         empTemp.DeptID = int.Parse(ddlTemp.SelectedValue);      

         empTemp.Age = int.Parse(txtAge.Text);       

         empTemp.Address = txtAddr.Text;

dbEmp.SubmitChanges();

Deleting a Row

EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();      

        string EmpID = null;      

        EmpID = txtEmpID.Text;

        Employee empTemp = (from emp in dbEmp.Employees

                            where emp.EmpID == int.Parse(EmpID)

                            select emp).Single();

        dbEmp.Employees.DeleteOnSubmit(empTemp);

        dbEmp.SubmitChanges();

Inserting a new Row

EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();

        Employee emp = new Employee();      

       emp.EmpName = txtEmpName.Text;     

       emp.DeptID = int.Parse(ddlTemp.SelectedValue);     

       emp.Age = int.Parse(txtAge.Text);     

       emp.Address = txtAddr.Text;

       dbEmp.Employees.InsertOnSubmit(emp);

       dbEmp.SubmitChanges();