Binding Grid View Control using LINQ

Self-Innovator
Posted by in LINQ category on for Beginner level | Points: 250 | Views : 3743 red flag

In this article we will see the uses of LINQ in .NET Applications. The abbreviation for LINQ is Language Integrated Query primarily works with objects in an application.

Introduction

In this article we will see the uses of LINQ in .NET applications. LINQ provides a variety of features in terms of performance of an application. The objective of LINQ is primarily uses an objects to handle the data in a multiple format in application and improves the performance of application by using LINQ rather than using SQL queries.

Background

Language Integrated Query provides a flexibility on writing the SQL queries to retrieve a data from a database efficiently, LINQ uses an objects to perform to operations and it supports any kind of data sources to deal with.

Using the code

In this article we will try see how to retrieve a data from a database and bind it to a grid view control using LINQ query and later you will find a big difference on comparing to LINQ vs Normal SQL queries while binding with grid view which is being so simple with LINQ.

Before creating application, we need to create table design for our application, where we will be using the Two tables names (Teacher,Course) with primary key and foreign key relationships between these tables.

Create table Teacher
(
ID int Identity primary key,
Name nvarchar(40),
Location nvarchar(40)
)

GO

Insert into Teacher(Name,Location)
Select 'Aaisha','Chicago'
union all
Select 'Zohra','Boston'
union all
Select 'Nahida','Massachusetts'
union all
Select 'Izza Mariyam','Florida'

GO

Create table Course
(
ID int identity primary key,
CourseName nvarchar(40),
CourseCredits decimal(10,2),
Duration nvarchar(40),
TeacherId int foreign key references Teacher(ID)
)

GO

Insert into Course(CourseName,CourseCredits,Duration,TeacherId)
Select 'Asp.Net MVC 5','2','1 Year',1
union all
Select 'Database Designs','4','2 Year',3
union all
Select 'Designing with HTML 5','3','1 Year',4
union all
Select 'Object Oriented Analysis & Design','2','3 Year',2
union all
Select 'C# .Net Programming','1','2 Year',1

In the above table structure primary key and foreign key relationships is mandatory to achieve the results accurately using LINQ

The first step is to create an empty web application project using VS Studio 2013 or which ever version which should supports LINQ and right click in solution explores -> choose new Item -> Select Data tab-> and select LINQ to SQL Classes and name it as Sample.dbml then click add as shown below.



The next step is to drag & drop the tables Teacher & Course from server explorer which will automatically generate the .Net classes and creates an connection to DB in the configuration file it will create an association link between the two tables which is due to Primary key and foreign key relationships as below screen




The Next step is to add an web form to the application and add an Grid view Control to populate the data from LINQ query.One important thing need to know about the .Net classes which has got created in SampleDBDesigner.cs file which will have a SampleDBContext class which is an entry point for a database. By creating an instance of SampleDBContext Class provides an access to DB.

In the below code snippet we have used an Linq query to populate all the Teachers in a gridview control using SampleDBContext object.
protected void Page_Load(object sender, EventArgs e)
        {
            SampleDBDataContext db = new SampleDBDataContext();
            grd1.DataSource = db.Teachers.ToList();
            grd1.DataBind();
        }

In the below code snippet we are retrieving the records where teachers who are all residing in the City (Florida). 
 protected void Page_Load(object sender, EventArgs e)
        {
            SampleDBDataContext db = new SampleDBDataContext();
            grd1.DataSource = from t in db.Teachers
                              where t.Location == "Florida"
                              orderby t.Name descending
                              select t;
            grd1.DataBind();
        }



In the below code snippet we are retrieving the records of the teachers who teaches the specific courses by joining the two tables Teachers & Courses
  SampleDBDataContext db = new SampleDBDataContext();
            grd1.DataSource = from t in db.Teachers
                              join c in db.Courses
                              on t.ID equals s.TeacherId
                              orderby t.ID ascending
                              select new {t.ID, t.Name,c.CourseName,c.CourseCredits,c.Duration,t.Location };
            grd1.DataBind();
    



Conclusion

In this article we tried to bind the grid view control with the database tables using LINQ mechanism which makes more efficient way for writing a simple LINQ queries instead of SQL Statements, we also tried how to join the tables in LINQ and retrieve the data from multiple tables. 

Reference

http://tempuri.org/tempuri.html

Page copy protected against web site content infringement by Copyscape

About the Author

Self-Innovator
Full Name: Sayeed Ahmed
Member Level: Bronze
Member Status: Member
Member Since: 12/22/2011 7:45:35 AM
Country: India
Join Hands Change lives Thanks & Regards Straight Edge Society


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)