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



































Steps Involved











Step 1. Create projects














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