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

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.



Add 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.



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



Drag and drop the table from the list.



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.



Sample Code

namespace LinqTest_Web
{table
    public partial class _Default : Page
    {
    //Here is the entry point to database

 TestDBDataContext context = new TestDBDataContext();

        protected void Page_Load(object sender, EventArgs e)
        {
            // Linq TO SQL

            var employees = from listempoyees in context.tblEmployees
                           // where listempoyees.Salary > 70000
                            select listempoyees;
            GridView1.DataSource = employees;
            GridView1.DataBind();
       }
    }
}

Output Page



Explanation

Here we can see the LINQ Query that is fetching the data from the SQL Server database.

var employees = from listempoyees in context.tblEmployees
                             where listempoyees.Salary > 70000
                            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.



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.



Database Operation Using LINQ

The following describes the Insert Operation.

Using LINQ, data can be saved directly into a table using the TestDBDataContext instance.

protected void Button1_Click(object sender, EventArgs e)
{
    using (TestDBDataContext context = new TestDBDataContext())
    {

    tblEmployee emp = new tblEmployee();
    emp.EmployeeName = TextBox1.Text;
    emp.Location = TextBox2.Text;
    emp.Salary =float.Parse(TextBox3.Text);

    context.tblEmployees.InsertOnSubmit(emp);
    context.SubmitChanges();

    GetEmploees();

    };

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.

USE [School]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetCustomers]    Script Date: 12/31/2014 14:08:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Abhishek>
-- Description: <Get Employees Data,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetEmployees]

AS
BEGIN
        SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * from tblEmployees
END

GO

Expand the data connection from the Server Explorer and drag and drop the Stored Procedure to the TESTDB.dbml.

private void GetEmploeesusingSP()
{
    using (TestDBDataContext context = new TestDBDataContext())
    {
        GridView1.DataSource = context.sp_GetEmployees();
        GridView1.DataBind();
    };
}

When the definition of it is checked we will be navigated to the location.

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.sp_GetEmployees")]
        public ISingleResult<sp_GetEmployeesResult> sp_GetEmployees()
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
            return ((ISingleResult<sp_GetEmployeesResult>)(result.ReturnValue));
        }

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

Again go to SQL profiler and run the trace.



We will get this in the results of the trace.

declare @p3 int
set @p3=0
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[sp_GetEmployees] ',N'@RETURN_VALUE int output',@RETURN_VALUE=@p3 output
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).