Click here to Skip to main content
Click here to Skip to main content

Simple GridView Binding using LINQ to SQL

, 28 Feb 2009
Rate this:
Please Sign up or sign in to vote.
GridView binding using LINQ to SQL implementing Lambda Expression or Stored Procedure
screen1.jpg

Introduction

This article will be very useful to all users who are working with ASP.NET 3.5 GridView control and want to data bind the control using LINQ to SQL, utilizing either Lambda expression or precompiled stored procedure.

Background

dbml.jpg - Click to enlarge image

Before creating the UI illustrated above, it is necessary to create LINQ to SQL environment. The first thing you should do is to create a connection to your database using Server Explorer. Then, you need to add a LINQ to SQL class to your project by right clicking on the website inside Solution Explorer, select Add New Item...., and from Templates select LINQ to SQL Classes. Visual Studio will then create a *.dbml, *.dbml.layout and *.designer.cs files. These files are basically a graphical representation, HTML and a code behind of the DataContext class you have just created.

Now just drag and drop a selected table from database in Server Explorer, which you want a GridView to display onto the *.dbml file. Now the DataContext class will create a mapping code that is basically a partial class of the table you have just selected.

 public System.Data.Linq.Table<User> Users
 {
  get
  {
   return this.GetTable<User>();
  }
 }
[Table(Name="dbo.Users")]
public partial class User : INotifyPropertyChanging, INotifyPropertyChanged
{ 
 private static PropertyChangingEventArgs emptyChangingEventArgs = 
				new PropertyChangingEventArgs(String.Empty);
 
 private System.Guid _UserID;
 
 private System.Data.Linq.Binary _Sid;
 
 private int _UserType;
 
 private int _AuthType;
 
 private string _UserName;
 
    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnUserIDChanging(System.Guid value);
    partial void OnUserIDChanged();
    partial void OnSidChanging(System.Data.Linq.Binary value);
    partial void OnSidChanged();
    partial void OnUserTypeChanging(int value);
    partial void OnUserTypeChanged();
    partial void OnAuthTypeChanging(int value);
    partial void OnAuthTypeChanged();
    partial void OnUserNameChanging(string value);
    partial void OnUserNameChanged();
    #endregion
 
 public User()
 {
  OnCreated();
 }
 
 [Column(Storage="_UserID", DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true)]
 public System.Guid UserID
 {
  get
  {
   return this._UserID;
  }
  set
  {
   if ((this._UserID != value))
   {
    this.OnUserIDChanging(value);
    this.SendPropertyChanging();
    this._UserID = value;
    this.SendPropertyChanged("UserID");
    this.OnUserIDChanged();
   }
  }
 }
 
 [Column(Storage="_Sid", DbType="VarBinary(85)", UpdateCheck=UpdateCheck.Never)]
 public System.Data.Linq.Binary Sid
 {
  get
  {
   return this._Sid;
  }
  set
  {
   if ((this._Sid != value))
   {
    this.OnSidChanging(value);
    this.SendPropertyChanging();
    this._Sid = value;
    this.SendPropertyChanged("Sid");
    this.OnSidChanged();
   }
  }
 }
 
 [Column(Storage="_UserType", DbType="Int NOT NULL")]
 public int UserType
 {
  get
  {
   return this._UserType;
  }
  set
  {
   if ((this._UserType != value))
   {
    this.OnUserTypeChanging(value);
    this.SendPropertyChanging();
    this._UserType = value;
    this.SendPropertyChanged("UserType");
    this.OnUserTypeChanged();
   }
  }
 }
 
 [Column(Storage="_AuthType", DbType="Int NOT NULL")]
 public int AuthType
 {
  get
  {
   return this._AuthType;
  }
  set
  {
   if ((this._AuthType != value))
   {
    this.OnAuthTypeChanging(value);
    this.SendPropertyChanging();
    this._AuthType = value;
    this.SendPropertyChanged("AuthType");
    this.OnAuthTypeChanged();
   }
  }
 }
 
 [Column(Storage="_UserName", DbType="NVarChar(260)")]
 public string UserName
 {
  get
  {
   return this._UserName;
  }
  set
  {
   if ((this._UserName != value))
   {
    this.OnUserNameChanging(value);
    this.SendPropertyChanging();
    this._UserName = value;
    this.SendPropertyChanged("UserName");
    this.OnUserNameChanged();
   }
  }
 }

Soon we will use this table to bind GridView to display all the records, or use a lambda expression to select just one using "Where" clause. As an example, we will also use a stored procedure for the same purpose. I have created one in my database as an example:

CREATE PROCEDURE [dbo].[spGetUser]
@UserName nvarchar(260)

AS

BEGIN

    	SELECT UserID, UserName
	FROM Users
	WHERE UserName = @UserName

END

To achieve this, you will have to create a stored procedure in the database in the Server Explorer and then drag and drop it onto the *.dbml file. Now the DataContext class will create a method code, as well as a partial class of the stored procedure you have just selected.

[Function(Name="dbo.spGetUser")]
 public ISingleResult<spGetUserResult> spGetUser
	([Parameter(Name="UserName", DbType="NVarChar(260)")] string userName)
 {
  IExecuteResult result = this.ExecuteMethodCall
	(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), userName);
  return ((ISingleResult<spGetUserResult>)(result.ReturnValue));
 }
public partial class spGetUserResult
{ 
 private System.Guid _UserID;
 
 private string _UserName;
 
 public spGetUserResult()
 {
 }
 
 [Column(Storage="_UserID", DbType="UniqueIdentifier NOT NULL")]
 public System.Guid UserID
 {
  get
  {
   return this._UserID;
  }
  set
  {
   if ((this._UserID != value))
   {
    this._UserID = value;
   }
  }
 }
 
 [Column(Storage="_UserName", DbType="NVarChar(260)")]
 public string UserName
 {
  get
  {
   return this._UserName;
  }
  set
  {
   if ((this._UserName != value))
   {
    this._UserName = value;
   }
  }
 }

Using the Code

So now that we have the LINQ to SQL environment ready, let me show how it is really simple to bind the GridView control using the DataContext class we have just created. First, we must instantiate the DataContext class and then we will be able to use its methods and properties. Let's look at the sample code below:

ReportingServicesDataContext rpt = new ReportingServicesDataContext();
    protected void Page_Load(object sender, EventArgs e)
    {      
        if (!IsPostBack)
        {
            GridView1.DataSource = rpt.Users.ToList();
            BindGrid("Binded");
        }
    }

As you can see above, we have instantiated DataContext variable rpt as ReportingServicesDataContext(). In the Page Load event GridView control DataSource property is now using Users table of the rpt object, and ToList() method of the LINQ itself.

Notice that Users table is now a property of the rpt object which represents the mapping of the table, and ToList() method is used to force immediate query evaluation and return a List(T) that contains the query results. Thus, when calling DataBind() method of the GridView control, it will return all the rows of the Users table as shown above.

Now let's take a look at how we can use lambda expression to filter the results of the Users table and bind the result to the GridView as shown below:

lambda.jpg

Here is the sample code to achieve this:

protected void Button1_Click(object sender, EventArgs e)
    {
        GridView1.DataSource = rpt.Users.Where(u => u.UserName=="Everyone").ToList();
        BindGrid("Binded using Lambda");
    }

Here we are filtering the results to just get a UserName that equals to "Everyone". Notice that Where() method of the LINQ is used by injecting a lambda expression as a predicate to achieve such result. And that's all it takes!

Now, let's use the stored procedure we have created earlier to achieve the same result. We should get the result shown below:

StoreProc.jpg

Here is the sample code:

protected void Button2_Click(object sender, EventArgs e)
    {
        GridView1.DataSource = rpt.spGetUser("Everyone").ToList();
        BindGrid("Binded using Stored Procedure");
    }

Remember when your DataContext created a function called [Function(Name="dbo.spGetUser")] and a partial class called spGetUserResult when we dragged and dropped a stored procedure from the Server Explorer onto the *.dbml file? Well, here you see it in action! 

The spGetUser() is a method of rpt object that requires a parameter @UserName. In our case, we use @UserName="Everyone", and rpt object will do the rest for you by calling the stored procedure, passing the parameter, and get the results. Notice, the partial class spGetUserResult will define which columns will be displayed derived from the query. 

This is very cool!

And now a sample code for BindGrid().

private void BindGrid(string label)
    {
        GridView1.DataBind();
        Label2.Text = label;
    }

Nothing special here, just calling DataBind() method of the GridView and displaying label text.

And that's pretty much it for simple GridView binding using LINQ to SQL technology!

Hope you find this article useful, happy coding and have fun!

History

  • 28th February, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Arkady Geltzer
Software Developer (Senior) A & G Web Development and Design
United States United States
Arkady is a web developer and designer with over 10 years experience.
He has breadth as well as depth in ASP, ASP.NET, C#, VB.NET, Microsoft Access, SQL Server, Visual Studio.NET, XML, Web Services, LINQ, AJAX and many other technologies.

Arkady has built everything from simple websites to mission critical windows and web-based applications.
He is proficient at graphics design, programming, database development, and search engines.

Comments and Discussions

 
GeneralMy vote of 5 PinmemberPGunge12-Oct-11 21:24 
QuestionIs .ToList() really useful ? Pinmemberphmphmphm5-Mar-09 5:23 
AnswerRe: Is .ToList() really useful ? PinmemberArkady Geltzer5-Mar-09 7:56 
GeneralRe: Is .ToList() really useful ? Pinmemberphmphmphm5-Mar-09 11:01 
GeneralRe: Is .ToList() really useful ? PinmemberArkady Geltzer5-Mar-09 11:52 
GeneralRe: Is .ToList() really useful ? PinmemberArkady Geltzer5-Mar-09 12:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 28 Feb 2009
Article Copyright 2009 by Arkady Geltzer
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid