Introduction to LINQ to SQL – A Beginners Guide

What is LINQ TO SQL ?

LINQ to SQL is new component that is released with .NetFramework 3.5. LINQ to SQL provides a way where a developer can manage a relational database object as a .net object. In simple terms, it is an ORM (Object-relational mapping) that is used to manage a back end object as a front end object in object oriented way i.e. relational to object oriented type system.

We can query, insert, update and delete the underlying database object with LINQ to SQL classes. When we query or update or delete these .net objects, LINQ to SQL will automatically take care of the operations in underlying relational data objects.

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();

Conclusion

Thus, we have understood a new ORM that is shipped with .Netframework 3.5. Even though, we can call LINQ to SQL an ORM there are still areas of improvements and it is limited with features for a full ORM. Microsoft also released another infrastructure called LINQ to Entities which overcomes and adds more feature in terms of a full fledged ORM. We will see more about this in coming days!! Stay tuned!!