Learning LINQ Made Easy (LINQ to SQL): Tutorial 2

This article will cover LINQ to SQL basics for beginners to understand the framework and the underlying workings of the LINQ to SQL.

Introduction

This article is the next article in LINQ learning tutorial series. This article will cover LINQ to SQL basics for beginners to understand the framework and the underlying workings of the LINQ to SQL.

Background

LINQ to SQL is nothing but a ORM framework for converting LINQ queries into Transact SQL that can be supported by SQL Server.

MSDN says, LINQ to SQL is a component of .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects.

I have explained the underlying providers, one of them is LINQ to SQL and is also there.

Tutorial1 link: Learning LINQ Made Easy: Tutorial 1.

Basically it will create a strongly typed .Net class based on the database table that are further used for the queries. Using LINQ technology to access SQL databases is similar to accessing an in-memory collection.

LINQ to SQL support transactions, views and Stored Procedures and will work only with SQL Server databases. That can be considered to be a limitation.

Since it is strongly typed, the ORM Framework has compile type error checking and intelligence.

Getting started with LINQ to SQL: step-by-step


Open Visual Studio 2012 and select New Project -> Web-> Web Form Application.

Web Form Application

Add LINQ to SQL classes:

 LINQ to SQL classes

Go to View -> Server Explorer.

Open Data Connections and create a new connection by supplying the required server and database details.

Server Explorer

In the data connections you will be able to see the Database objects, like table, views, Stored Procedures and so on.

Stored Procedures

Drag and drop the table from the list.

Drag and drop

This will create an entity and classes automatically. The file with the name TestDBDataContext will be the entry point to the database from the application.

TestDBDataContext

Sample Code

  1. namespace LinqTest_Web  
  2. {table  
  3.     public partial class _Default : Page  
  4.     {  
  5.     //Here is the entry point to database  
  6.   
  7.  TestDBDataContext context = new TestDBDataContext();  
  8.   
  9.         protected void Page_Load(object sender, EventArgs e)  
  10.         {  
  11.             // Linq TO SQL  
  12.   
  13.             var employees = from listempoyees in context.tblEmployees  
  14.                            // where listempoyees.Salary > 70000  
  15.                             select listempoyees;  
  16.             GridView1.DataSource = employees;  
  17.             GridView1.DataBind();  
  18.        }  
  19.     }  
  20. }  
Output Page

Output Page

Explanation

Here we can see the LINQ Query that is fetching the data from the SQL Server database.
  1. var employees = from listempoyees in context.tblEmployees  
  2.                              where listempoyees.Salary > 70000  
  3.                             select listempoyees;  
What exactly has happened underneath is, the LINQ to SQL provider has done an analysis and converted the query into Transact SQL that has been executed by SQL Server internally and returned the list of Employees from the database table.

Open SQL Server Management Studio and select Tools -> SQL Profiler.

SQL Profiler

Run the trace and start the application. After the data display on the web page stop the trace and you will be able to see the trace log as below that is the SQL query that will understand by the SQL Server framework and it will provide the required output to the page.

required output to the page

Database Operation Using LINQ

The following describes the Insert Operation.

Using LINQ, data can be saved directly into a table using the TestDBDataContext instance.
  1. protected void Button1_Click(object sender, EventArgs e)  
  2. {  
  3.     using (TestDBDataContext context = new TestDBDataContext())  
  4.     {  
  5.   
  6.     tblEmployee emp = new tblEmployee();  
  7.     emp.EmployeeName = TextBox1.Text;  
  8.     emp.Location = TextBox2.Text;  
  9.     emp.Salary =float.Parse(TextBox3.Text);  
  10.   
  11.     context.tblEmployees.InsertOnSubmit(emp);  
  12.     context.SubmitChanges();  
  13.   
  14.     GetEmploees();  
  15.   
  16.     };  
table

Here in the preceding you have seen how to retrieve data from the database and save the data from the UI. Similarly other operations like update and delete can be done using LINQ to SQL.

This is the stand alone query that is running using LINQ to SQL for the various operations. We can use a Stored Procedure to do the same.

LINQ to SQL with Stored Procedure

First create a Stored Procedure to retrieve the Employee's data from the table.
  1. USE [School]  
  2. GO  
  3.   
  4. /****** Object:  StoredProcedure [dbo].[sp_GetCustomers]    Script Date: 12/31/2014 14:08:14 ******/  
  5. SET ANSI_NULLS ON  
  6. GO  
  7.   
  8. SET QUOTED_IDENTIFIER ON  
  9. GO  
  10.   
  11. -- =============================================  
  12. -- Author:      <Abhishek>  
  13. -- Description: <Get Employees Data,,>  
  14. -- =============================================  
  15. CREATE PROCEDURE [dbo].[sp_GetEmployees]  
  16.       
  17. AS  
  18. BEGIN  
  19.         SET NOCOUNT ON;  
  20.   
  21.     -- Insert statements for procedure here  
  22.     SELECT * from tblEmployees  
  23. END  
  24.   
  25. GO  
Expand the data connection from the Server Explorer and drag and drop the Stored Procedure to the TESTDB.dbml.

TESTDB
  1. private void GetEmploeesusingSP()  
  2. {  
  3.     using (TestDBDataContext context = new TestDBDataContext())  
  4.     {  
  5.         GridView1.DataSource = context.sp_GetEmployees();  
  6.         GridView1.DataBind();  
  7.     };  
  8. }  
When the definition of it is checked we will be navigated to the location.
  1. [global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.sp_GetEmployees")]  
  2.         public ISingleResult<sp_GetEmployeesResult> sp_GetEmployees()  
  3.         {  
  4.             IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));  
  5.             return ((ISingleResult<sp_GetEmployeesResult>)(result.ReturnValue));  
  6.         }  
get navigated to the location

We can the check the trace and see what is happening internally.

Again go to SQL profiler and run the trace.

run the trace

We will get this in the results of the trace.
  1. declare @p3 int  
  2. set @p3=0  
  3. exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[sp_GetEmployees] ',N'@RETURN_VALUE int output',@RETURN_VALUE=@p3 output  
  4. select @p3  
Basically we will get the Stored Procedure called using the LINQ to SQL Provider internally.

So we are either running a stand alone query or a Stored Procedure. Internally SQL Server is working using the LINQ to SQL Server provider that is a layer between the generated typed classes and is responsible for the underlying operation for the required result with LINQ.

Conclusion

LINQ to SQL is a component for working between the UI and the database layer to control the operations and provide a magical framework to write SQL queries and does database operations easily with less code.

More tutorials on LINQ will be coming shortly.

Keep smiling and keep learning.

References: Learning by Walkthroughs (LINQ to SQL).