Saturday, 3 December 2016

What is LINQ to SQL and how to create LINQ to SQL class in C# with example for beginners

In this article, I will explain you how to add LINQ to SQL class in C# and how to use it with simple example.

What is LINQ to SQL

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

LINQ to SQL is an ORM(Object Relational Mapping) framework that creates .net classes based on the database tables. We can write LINQ queries like select, insert, update and delete using any .net programming language like C#. LINQ to SQL provider converts LINQ expression into SQL query and send to database for its execution.

Advantages of LINQ to SQL

1. Compile time error checking
2. Provide intellisense
3. Provide debugging
The only drawback is, it supports SQL-Server database only.

Related Articles

  1. LINQ to SQL: GridView select, insert, update and delete using C# with asp.net
  2. Insert, update, delete, crud operation in asp.net gridview using stored procedure

How to Use LINQ to SQL?

First of all, create a SQL table tblEmployee and insert some records. Below is the script to create table.

create table tblEmployee
(
Id int primary key identity,
Name varchar(50),
Gender varchar(10),
Salary int
)

insert into tblEmployee values('John','Male',50000)
insert into tblEmployee values('Mary','Female',55000)
insert into tblEmployee values('Rahul','Male',40000)
insert into tblEmployee values('Mathew','Male',80000)

Right click on the project menu and select Add New Item.. from visual studio as show below.

add new item

Now, the new window will open as show below. Go to Data tab and select LINQ to SQL classes and give its name Demo.dbml and click on the add button.

add linq to sql class

Now, go to Server Explorer right click on Data Connections and select Add Connection... and new window will open as shown below. Write server name and select your database from dropdown.

add data connection

Now, drag and drop tblEmployee table from connection to Demo.dbmlsurface.

drag and drop linq to sql class

You can check the all the classes are generated in Demo.designer.cs file. DemoDataContext is the entry point to database as shown below.

entry point to database

In designer file

Now, add a gridview control and add a label control to webform. Below is the designer code.

<asp:GridView ID="gvEmployee" runat="server"></asp:GridView>
<br />
<asp:Label ID="lblStatus" runat="server"></asp:Label>

In code-behind file

Below is the code to bind all employee to gridview and count the number of Male employee.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid();
    }
}

private void BindGrid()
{
    DemoDataContext dbContext = new DemoDataContext();

    // bind all employee to gridview
    gvEmployee.DataSource = dbContext.tblEmployees;
    gvEmployee.DataBind();

    // select only male employee using LINQ
    int Malecount = (from emp in dbContext.tblEmployees
                     where emp.Gender == "Male"
                     select emp).Count();

    lblStatus.Text = "Total number of male Employee: "+Malecount.ToString();
}

Below is the code to bind all male employee to gridview and count the number employee.

private void BindGrid()
{
    DemoDataContext dbContext = new DemoDataContext();

    // bind all male employee to gridview using LINQ
    gvEmployee.DataSource = from emp in dbContext.tblEmployees
                            where emp.Gender == "Male"
                            select emp;
    gvEmployee.DataBind();

    // count total number of employees
    int TotalEmployee = (from emp in dbContext.tblEmployees
                        select emp).Count();
   
    lblStatus.Text = "Total number of Employee: " + TotalEmployee.ToString();
    // or 
    lblStatus.Text = "Total number of Employee: " + dbContext.tblEmployees.Count();          
}
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON