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

Paging In Datagrid In Lightswitch When Binding From Stored Procedure






Objective Of This Tutorial






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




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

Steps Involved


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

Step 1. Create Projects


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


Picture showing the project structure in the solution explorer

Click to Enlarge


Step 2. Create Table In The Database.




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




CreateProc GetClasses As 
SELECT 
  [ClassId], 
  [ClassCode], 
  [ClassDisplayName] 
FROM 
  [dbo].[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();
		}
	}
}






Picture showing adding the Classes entity in the project

Click to Enlarge


Step 4: Run The Application




Picture showing the editable grid screen with data

Click to Enlarge



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




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();
		}

	}
}




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/FeedbackCharacters remaining 250