Jon's Blog

.NET Development & More

LinqDataSource: Efficient Custom Paging with LINQ

NOTE: If you set your LinqDataSource's AutoPage property to true and are using SQL Server (2005+ I believe) then it will automatically use Skip() and Take() as seen below. However, if you want to do it manually or wonder how it works then read on.

Creating a GridView (or other data control) with efficient paging is very easy with LINQ.  You can thank the Take and Skip operators for this; they allow you to only pull back the records you need.  In the simple example below we are using a LinqDataSource and handling its onselecting method to create our LINQ query and do our paging.  Set AutoPage to false since we are writing code to handle paging ourselves.  Also the PageSize property of the GridView control is being populated from a constant in the code-behind class.

ASPX:

<asp:LinqDataSource ID="linqDS" runat="server" AutoPage="false" 
    ContextTypeName="Namespace.MyDataContext" 
    onselecting="linqDS_Selecting" />
        
<asp:GridView ID="myGV" runat="server" DataSourceID="linqDS"
    AllowPaging="true" PageSize="<%# PAGE_SIZE %>"
    AutoGenerateColumns="false">
    <Columns>
        <!--Removed for simplicity -->
    </Columns>
</asp:GridView>

 

Code-behind:

// Const declared at top of code-behind
public const int PAGE_SIZE = 100;

protected void linqDS_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
    // LINQ query
    var query = from c in myDC.Class
                select c;

    // Do advanced query logic here (dynamically build WHERE clause, etc.)     
    
    // Set the total count     
    // so GridView knows how many pages to create    
    e.Arguments.TotalRowCount = query.Count();

    // Get only the rows we need for the page requested
    query = query.Skip(myGV.PageIndex * PAGE_SIZE).Take(PAGE_SIZE);

    e.Result = query;
}

 

GridView EmptyDataTemplate: Get Rid of That Annoying Border!

If you are like me then you may have a CSS table style for your GridView with a specific border.  When you use the the EmptyDataTemplate you may then have a border around your message.  I create a CSS class and then use the EmptyDataRowStyle and set its CssClass.  Note, in the GridView I am also setting the default border to 0.  Example:

CSS

table.myGridClass .empty td
{
border-style: none;
border-width: 0px;
background-color: #ffffdd;
}

 

ASPX

<asp:GridView ID="myGridView" runat="server" CssClass="myGridClass" BorderWidth="0">
<HeaderStyle CssClass="myHeaderStyle" />
<RowStyle CssClass="myRowStyle" />
<EmptyDataRowStyle CssClass="empty" />
<EmptyDataTemplate>Your message here.</EmptyDataTemplate>

ASP.NET AJAX Web Service Call

So I was working on a project where I needed to have some checkboxes in a GridView.  When checked they needed to update the underlying business object.  My first idea was the just wrap the whole thing in an UpdatePanel, but that would of course require a partial postback with the full contents of the grid being posted back to the server.  The better solution is to call a web service method to do the update.

First, add a new Web Service class to your project.  You need to add the [ScriptService] attribute to this class.  This attribute is from the System.Web.Script.Services namespace.  Then create your WebMethod, including whatever parameters you will need to pass in.

[WebMethod]
public void UpdateFlag(int id, bool isChecked)
{
// Logic to update business object
}

Next you need to add a ServiceReference that points to the web service you just created.  You can add this to your ScriptManager instance.  In my scenario the ScriptManager was in the Master page and I just wanted to include the ServiceReference in this one ASPX page.  Here you can use the ScripManagerProxy object like so (assuming the WebService file is in the same directory as the ASPX file):

<asp:ScriptManagerProxy ID="scriptManProxy" runat="server">
    <Services>
        <asp:ServiceReference Path="MyWebService.asmx" />
    </Services>
</asp:ScriptManagerProxy>

 

Then add the following code to your GridView's RowDataBound event.  This will set the onclick events for the individual checkboxes you are binding to the GridView.

if (e.Row.RowType == DataControlRowType.DataRow)
{
    BusinessObject obj = (BusinessObject)e.Row.DataItem;
    CheckBox myCB = (CheckBox)e.Row.FindControl("myCB");

    string eventText = string.Format("myCB_Checked({0}, this);", obj.SomeID);
    myCB.Attributes["onclick"] = eventText;
}

 

Now you need to actually write the JavaScript method that will call your web service.  Here it is:

<script type="text/javascript">
    function myCB_Checked(id, ctrl) {
        var isChecked = ctrl.checked;
        Namespace.MyWebService.UpdateFlag(id, isChecked);
    }
</script>

 

Make sure that you use the fully qualified name of your class here.