Simple GridView Binding using LINQ to SQL

• Download source - 285.21 KB

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

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:

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:

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

• 28^th February, 2009: Initial post