Articles → LIGHT SWITCH 2011 → Paging In Datagrid In Lightswitch When Binding From Stored Procedure

Paging In Datagrid In Lightswitch When Binding From Stored Procedure

Paging is a very interesting technique which can be used along with LightSwitch application. It helps in viewing all records in one page. The biggest advantage of paging mechanism is that it allows optimization of large collection of data in search screens and editable grids. This is of great help in browsing records.

Objective Of This Tutorial

This tutorial provides a walkthrough on how to implement paging in datagrid in lightswitch when binding a datagrid from stored procedures.

Software

Visual studio 2010 is installed on your machine.
Visual Studio 2010 service pack 1 is installed on your machine.
Microsoft Visual Studio Light switch 2011 is installed on your machine.
WCF RIA Services Toolkit is installed on your machine.

Prerequisites

In order to understand this tutorial you must have technical knowledge about the following concepts

How to create a project in Visual studio 2010?
How to add new item in the project?
How to add new project in the existing solution?
Understanding of WCF RIA Service
What is a ‘Class Library’ project?
Understanding about lightswitch
Basic knowledge of SQL server
Basics of ado.net(How to call a stored procedure in C# code)

Steps Involved

Create projects
Create table in the database.
Add a stored procedure to get records
Run the Application

Step 1. Create Projects

Please follow the steps mentioned below
Create a new lightswitch project by the name of ‘Paging_using_stored_procedure’.
Create a new class library by the name of ‘MyRIAService’.
From ‘MyRIAService’, delete ‘Class1.cs’
Add a new ‘Domain Service Class’ in ‘MyRIAService’.

The new project can be viewed in the solution explorer as shown below

Picture showing the project structure in the solution explorer

Click to Enlarge

Step 2. Create Table In The Database.

Create a database with a table ‘Class’. See the screen shot below for table schema and data

Picture showing the design mode of the sample table

Click to Enlarge

Picture showing the data in the sample table

Click to Enlarge

Step 3. Add A Stored Procedure To Get Records

Now add a stored procedure as shown below to get classes

CreateProc GetClasses As 
SELECT 
  [ClassId], 
  [ClassCode], 
  [ClassDisplayName] 
FROM 
  [dbo].[Class]

Now add following code in domain service class

namespace MyRIAService {
	using System;
	using System.Collections.Generic;
	using System.ComponentModel.DataAnnotations;
	using System.Linq;
	using System.ServiceModel.DomainServices.Hosting;
	using System.ServiceModel.DomainServices.Server;
	using System.Data.SqlClient;
	using System.Data;

	public class Class { [Key]
		public int ClassId {
			get;
			set;
		}
		public string ClassCode {
			get;
			set;
		}
		public string ClassDisplayName {
			get;
			set;
		}
	}
	// TODO: Create methods containing your application logic.
	[EnableClientAccess()]
	public class MyDomainService: DomainService { [Query(IsDefault = true)]
		public IQueryable < Class > GetClasses() {
			List < Class > lst = new List < Class > ();
			DataSet ds = new DataSet();
			using(SqlConnection connection = new SqlConnection(@"conn_string")) {
				using(SqlCommand cmd = new SqlCommand("GetClasses", connection)) {
					connection.Open();
					cmd.CommandType = System.Data.CommandType.StoredProcedure;
					using(SqlDataAdapter adapter = new SqlDataAdapter(cmd)) {
						adapter.Fill(ds);

						if (ds != null) {
							if (ds.Tables.Count > 0) {
								foreach(DataRow row in ds.Tables[0].Rows) {
									Class cls = new Class();
									cls.ClassId = Convert.ToInt32(row["ClassId"]);
									cls.ClassCode = Convert.ToString(row["ClassCode"]);
									cls.ClassDisplayName = Convert.ToString(row["ClassDisplayName"]);
									lst.Add(cls);
								}
							}
						}
					}
				}
			}
			return lst.AsQueryable();
		}
	}
}

The method ‘GetClasses’ call the stored procedure and get records from the ‘Class’ table. Once this is accomplished we can add data source in lightswitch application

Once we have added the data source we will add a screen of type ‘Editable Grid Screen’. An editable grid screen by the name of ‘EditableClassesGrid’ is then created and associated with the ‘Classes’ entity. The editable grid and the ‘Classes’ entity can be viewed in the illustration below.

Picture showing adding the Classes entity in the project

Click to Enlarge

Step 4: Run The Application

When you run the application the result will be as follows

Picture showing the editable grid screen with data

Click to Enlarge

As you can see in the figure above paging is not enabled. Now go to RIA service and add these lines of code

protected override int Count < T > (IQueryable < T > query) {
	return query.Count();
}

So the whole RIA service code will now look like this:

namespace MyRIAService {
	using System;
	using System.Collections.Generic;
	using System.ComponentModel.DataAnnotations;
	using System.Linq;
	using System.ServiceModel.DomainServices.Hosting;
	using System.ServiceModel.DomainServices.Server;
	using System.Data.SqlClient;
	using System.Data;

	public class Class { [Key]
		public int ClassId {
			get;
			set;
		}
		public string ClassCode {
			get;
			set;
		}
		public string ClassDisplayName {
			get;
			set;
		}
	}
	// TODO: Create methods containing your application logic.
	[EnableClientAccess()]
	public class MyDomainService: DomainService { [Query(IsDefault = true)]
		public IQueryable < Class > GetClasses() {
			List < Class > lst = new List < Class > ();
			DataSet ds = new DataSet();
			using(SqlConnection connection = new SqlConnection(@"Data Source=.\SQLExpress2K8R2;Database=DynamicData;Integrated Security=true;")) {
				using(SqlCommand cmd = new SqlCommand("GetClasses", connection)) {
					connection.Open();
					cmd.CommandType = System.Data.CommandType.StoredProcedure;
					using(SqlDataAdapter adapter = new SqlDataAdapter(cmd)) {
						adapter.Fill(ds);

						if (ds != null) {
							if (ds.Tables.Count > 0) {
								foreach(DataRow row in ds.Tables[0].Rows) {
									Class cls = new Class();
									cls.ClassId = Convert.ToInt32(row["ClassId"]);
									cls.ClassCode = Convert.ToString(row["ClassCode"]);
									cls.ClassDisplayName = Convert.ToString(row["ClassDisplayName"]);
									lst.Add(cls);
								}
							}
						}
					}
				}
			}
			return lst.AsQueryable();
		}

		protected override int Count < T > (IQueryable < T > query) {
			return query.Count();
		}

	}
}

Now update data source in lightswitch and run application again.

Picture showing the editable grid screen with data after updating the data source

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Thursday, March 20, 2014

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**