Learning LINQ Made Easy (LINQ to SQL)

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

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