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















Click to Enlarge


Step 2. Create table in the database.





Click to Enlarge


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







Click to Enlarge


Step 4: Run the Application





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

    }
}





Click to Enlarge


Posted By  -  Karan Gupta
 
Posted On  -  Thursday, March 20, 2014

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250