Limit datagrid items in asp.net c#

Question

I am using DataGrid in ASP.NET C# . I want to limit and control the number of items displayed in a DataGrid (I am forced to use this controller).

I know we can control using

1. AllowPaging = "TRUE" per page basis
2. Limit DataSource using sql query and then binding (DataBind()) it.

But I don't want either. I want to run sql query once and get all data, then I must be able to display first 10 or 20 items depending on the requirements.

It's preferable if I could do it

1. in code behind.
2. without page reload, if I increase the item size to 30 from 10

Any kind of heads up in right path appreciated

Answer 1

Here are two options to consider. The first uses a DataTable and Linq to get the top x rows.

DataTable dt = yourDataTableSource;
GridView1.DataSource = dt.AsEnumerable().Take(5).CopyToDataTable();
//Linq example with sorting added
//GridView1.DataSource = dt.AsEnumerable().OrderBy(x => x["columnName"]).Take(5).CopyToDataTable();
GridView1.DataBind();

Or you could use the same attributes that are normally found on the .aspx page. Only these are set in code behind now and you can still use your current datasource for that.

GridView1.PageSize = 5;
GridView1.AllowPaging = true;
GridView1.PagerSettings.Visible = false;
GridView1.DataBind();

or for a DataGrid

DataGrid1.PageSize = 10;
DataGrid1.AllowPaging = true;
DataGrid1.PagerStyle.Visible = false;
DataGrid1.DataBind();

And to complete your question, here is a little example to get you started of how get the change in the items displayed without a page refresh.

    <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
    <!-- a ScriptManager is required when working with UpdatePanels -->

    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>

            <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            <br />
            <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
                <asp:ListItem Text="5 rows" Value="5"></asp:ListItem>
                <asp:ListItem Text="10 rows" Value="10"></asp:ListItem>
                <asp:ListItem Text="15 rows" Value="15"></asp:ListItem>
            </asp:DropDownList>

        </ContentTemplate>
    </asp:UpdatePanel>

Code behind:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            DataTable dt = yourDataTableSource;
            GridView1.DataSource = dt;
            GridView1.PageSize = 10;
            GridView1.AllowPaging = true;
            GridView1.PagerSettings.Visible = false;
            GridView1.DataBind();

            ViewState["GridView1Content"] = dt;
        }
    }

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        int pageSize = 10;

        //always do validation with user input...
        try
        {
            pageSize = Convert.ToInt32(DropDownList1.SelectedValue);
        }
        catch
        {
        }

        GridView1.PageSize = pageSize;
        GridView1.DataSource = ViewState["GridView1Content"] as DataTable;
        GridView1.DataBind();
    }

Answer 2

However, we have to add a list (could be any kind of list) to datasource when we are using DataGrid control.

All what we want to do is, limit the number of items in this list and then bind your data using DataBind().

To limit number of data

var firstFiveItems = myList.Take(5);

or

var secondFiveItems = myList.Skip(5).Take(5);

Answer 3

have you tried Datatable Plugin It has functionality to display record as per our requirement.. See this link

Apply Datatable Plugin For Your Grid To display data and you can change this option by Changing aLengthMenu and iDisplayLength properties as per your requirement.. this is in built functionality

$(document).ready(function() {
    $('#example').dataTable({
        "aLengthMenu": [[10, 20, 30, -1], [10, 20, 30, "All"]],// set as per your requirement
        "iDisplayLength": 10 // dont forget to change as per alengthmenu


    });
} );

Answer 4

I came something like this but i was using gridview and when page come too loaded o migrated to Datatables with serverside page processing.

It´s a generic layer. Modify it to ur need!

Here´s the parser i got:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Web;

namespace iMax_WeSite.app.Objects.DataTables
{
    public class DataTable
    {
        public DataTable()
        {
        }
        public int sEcho { get; set; }
        public int iTotalRecords { get; set; }
        public int iTotalDisplayRecords { get; set; }
        public string iTotalValue { get; set; } //Somatoria total do valor
        public string iTotalFilteredValue { get; set; } //Somatoria parcial do valor
        public List<List<string>> aaData { get; set; }
        public string sColumns { get; set; }

        public void Import(string[] properties)
        {
            sColumns = string.Empty;
            for (int i = 0; i < properties.Length; i++)
            {
                sColumns += properties[i];
                if (i < properties.Length - 1)
                    sColumns += ",";
            }
        }
    }
    public class DataTableParser<T>
    {
        private const string INDIVIDUAL_SEARCH_KEY_PREFIX = "sSearch_";
        private const string INDIVIDUAL_SORT_KEY_PREFIX = "iSortCol_";
        private const string INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX = "sSortDir_";
        private const string DISPLAY_START = "iDisplayStart";
        private const string DISPLAY_LENGTH = "iDisplayLength";
        private const string ECHO = "sEcho";
        private const string SEARCH = "sSearch";
        private const string ASCENDING_SORT = "asc";
        private IQueryable<T> _queriable;
        private readonly Dictionary<string, object> _tableParams;
        private readonly Type _type;
        private readonly System.Reflection.PropertyInfo[] _properties;
        public DataTableParser(Dictionary<string, object> tableParams, IQueryable<T> queriable)
        {
            _queriable = queriable;
            _tableParams = tableParams;
            _type = typeof(T);
            _properties = _type.GetProperties();
        }

        public DataTable Parse()
        {
            var list = new DataTable();
            list.Import(_properties.Select(x => x.Name).ToArray());

            list.sEcho = (int)_tableParams[ECHO];

            list.iTotalRecords = _queriable.Count();

            ApplySort();

            int skip = 0, take = list.iTotalRecords;
            if (_tableParams.ContainsKey(DISPLAY_START))
                skip = (int)_tableParams[DISPLAY_START];
            if (_tableParams.ContainsKey(DISPLAY_LENGTH))
                take = (int)_tableParams[DISPLAY_LENGTH];

            //tenho de segregar para mostrar o filtrado 
            list.aaData = _queriable.Where(ApplyGenericSearch)
                                    .Where(IndividualPropertySearch)
                                    .Skip(skip)
                                    .Take(take)
                                    .Select(SelectProperties)
                                    .ToList();

            //tenho de segregar para mostrar o filtrado geral
            list.iTotalDisplayRecords = _queriable.Where(ApplyGenericSearch)
                                                    .Where(IndividualPropertySearch)
                                                    .Select(SelectProperties)
                                                    .Count();
            return list;
        }
        private void ApplySort()
        {
            foreach (string key in _tableParams.Keys.Where(x => x.StartsWith(INDIVIDUAL_SORT_KEY_PREFIX)))
            {
                int sortcolumn = (int)_tableParams[key];
                if (sortcolumn < 0 || sortcolumn >= _properties.Length)
                    break;

                string sortdir = _tableParams[INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX + key.Replace(INDIVIDUAL_SORT_KEY_PREFIX, string.Empty)].ToString();

                var paramExpr = Expression.Parameter(typeof(T), "val");
                var propertyExpr = Expression.Lambda<Func<T, object>>(Expression.Convert(Expression.Property(paramExpr, _properties[sortcolumn]), typeof(object)), paramExpr);


                if (string.IsNullOrEmpty(sortdir) || sortdir.Equals(ASCENDING_SORT, StringComparison.OrdinalIgnoreCase))
                    _queriable = _queriable.OrderBy(propertyExpr);
                else
                    _queriable = _queriable.OrderByDescending(propertyExpr);
            }
        }

        private Expression<Func<T, List<string>>> SelectProperties
        {
            get
            {
                return value => _properties.Select
                                            (
                                                prop => (prop.GetValue(value, new object[0]) ?? string.Empty).ToString()
                                            )
                                            .ToList();
            }
        }

        private Expression<Func<T, bool>> IndividualPropertySearch
        {
            get
            {
                var paramExpr = Expression.Parameter(typeof(T), "val");
                Expression whereExpr = Expression.Constant(true); // default is val => True
                foreach (string key in _tableParams.Keys.Where(x => x.StartsWith(INDIVIDUAL_SEARCH_KEY_PREFIX)))
                {
                    int property = -1;

                    if (!int.TryParse(key.Replace(INDIVIDUAL_SEARCH_KEY_PREFIX, string.Empty), out property) || property >= _properties.Length || string.IsNullOrEmpty(_tableParams[key].ToString())) 
                        continue; // ignore if the option is invalid

                    string query = _tableParams[key].ToString().ToLower();

                    var toStringCall = Expression.Call(
                                        Expression.Call(
                                            Expression.Property(paramExpr, _properties[property]), "ToString", new Type[0]),
                                                                typeof(string).GetMethod("ToLower", new Type[0]));

                    whereExpr = Expression.And(whereExpr,
                                                Expression.Call(toStringCall,
                                                                typeof(string).GetMethod("Contains", BindingFlags.Public | BindingFlags.Static | BindingFlags.Instance | BindingFlags.IgnoreCase),
                                                                Expression.Constant(query)));

                }
                return Expression.Lambda<Func<T, bool>>(whereExpr, paramExpr);
            }
        }

        private Expression<Func<T, bool>> ApplyGenericSearch
        {
            get
            {
                if (!_tableParams.ContainsKey(SEARCH) || _properties.Length == 0)
                    return x => true;

                string search = _tableParams[SEARCH].ToString();

                if (String.IsNullOrEmpty(search))
                    return x => true;

                var searchExpression = Expression.Constant(search.ToLower());
                var paramExpression = Expression.Parameter(typeof(T), "val");

                var propertyQuery = (from property in _properties
                                        let tostringcall = Expression.Call(
                                                            Expression.Call(
                                                                Expression.Property(paramExpression, property), "ToString", new Type[0]),
                                                                typeof(string).GetMethod("ToLower", new Type[0]))
                                        select Expression.Call(tostringcall, typeof(string).GetMethod("Contains"), searchExpression)).ToArray();

                Expression compoundExpression = propertyQuery[0];

                for (int i = 1; i < propertyQuery.Length; i++)
                    compoundExpression = Expression.Or(compoundExpression, propertyQuery[i]);

                return Expression.Lambda<Func<T, bool>>(compoundExpression, paramExpression);
            }
        }

    }

}

I hope i´ve helped in your problem!