Linq to SQL Tutorial – ObjectDataSource binding with paging and sorting

Update: 31/10/2009 – Added source code

ASP.Net has the LinqDataSource which is a handy control but it embeds the business logic in the page which I don’t like. In this post am I going to use the repository objects I created in my last post to bind a GridView using an ObjectDataSource enabling filtering, sorting, and server side paging.

I have a GridView control with an ObjectDataSource as the object. Below are the properties for my ObjectDataSource:

<asp:ObjectDataSource
    ID="odsStudent"
    runat="server"
    TypeName="Repository.StudentRepository"
    DataObjectTypeName="Entities.Student"
    SelectMethod="GetAll"
    UpdateMethod="Save"
    DeleteMethod="Delete">
</asp:ObjectDataSource>

Here we have the following:

  • TypeName – The object, here this is the repository.
  • DataObjectTypeName – The entity, here this is Student. This tells the ObjectDataSource to pass an instance of Student when inserting, updating or deleting instead of the separate values.
  • SelectMethod – The method to load the data, here this is GetData from the base repository.
  • UpdateMethod – The method to update the data, here this is Save from the base repository.
  • DeleteMethod – The method to delete the data, here this is Delete from the base repository.

Running the application at this point would correctly load the data, but delete would not work and update would insert duplicate records. This is because Linq to SQL doesn’t know which entity to attach. To resolve this I can set the DataKeyNames property of the GridView to include the timestamp column:

DataKeyNames="ID, UpdateDate"

I talk more about timestamp columns in my Linq to SQL with WCF Services post.

Now that this property has been added updates and deletes work fine within the GridView. To add server side paging I need to modify my repository to create an overload for GetAll. In doing this I have also added a protected method in the base repository called PageFilter:

protected List<T> PageFilter(IQueryable<T> entities, int startRow, int maxRows)
{
    return entities.Skip(startRow).Take(maxRows).ToList();
}

This method return a List containing the rows from IQueryable between startRow and maxRows. My overloaded GetAll method accepts startRow and maxRows and uses the PageFilter method:

public List<T> GetAll(int startRow, int maxRows)
{
   return PageFilter(_context.GetTable<T>(), startRow, maxRows);
}

The reason for separating the PageFilter is for reuse within the base class, and derived classes which I will demonstrate later. Now that I have my new GetAll method I need to add some new properties on my ObjectDataSource:

SelectCountMethod="Count"
StartRowIndexParameterName="startRow"
MaximumRowsParameterName="maxRows"
EnablePaging="true"
  • SelectCountMethod – Used to determine the number of pages, here this is Count method in the base repository.
  • StartRowIndexParameterName – The name of the parameter for the start row index, this needs to match the name of the parameter in the overloaded GetAll method in the base repository.
  • MaximumRowsParameterName – The name of the parameter for the maximum rows, this needs to match the name of the parameter in the overloaded GetAll method in the base repository.
  • EnablePaging – This needs to be set to true to enable server side paging, otherwise the paging will still be done at the client.

Note: If you configure the ObjectDataSource through the UI and select the new GetAll method it will add parameters to the datasource. We don’t want this so select the parameterless overload or set up the ObjectDataSource in the markup.

When I run the application it now works using server side paging without really putting in too much work. Next I want to add sorting. To do this I need a new overloaded method for PageFilter and another one for GetAll which accepts the parameters for paging and sorting:


protected List<T> PageFilter(IQueryable<T> entities, int startRow, int maxRows, string orderBy)
{
    if (string.IsNullOrEmpty(orderBy))
    {
        orderBy = "ID";
    }

    return entities.OrderBy(orderBy).Skip(startRow).Take(maxRows).ToList();
}

You may notice I’m using the string variable orderBy as a parameter for the OrderBy method which would not usually be allowed. This is because I have included the Linq Dynamic Library in my project. Scott Guthrie has a post on this here. To use the new dynamic extension methods I have added the Dynamic.cs file to my project and added a reference to System.Linq.Dynamic. I am also checking if orderBy is null or empty and setting a default if so. This is because the dynamic library will throw an exception if it is passed an empty string. ID is fine to use as a default as I know all of my entities will have an ID property.

The new GetAll method looks like this:

public List<T> GetAll(int startRow, int maxRows, string orderBy)
{
    return PageFilter(_context.GetTable<T>(), startRow, maxRows, orderBy);
}

This method takes in the paging and sorting parameters and uses the new PageFilter method. Now the methods have been set up I need to set another property on the ObjectDataSource:

SortParameterName="orderBy"
  • SortParameterName – The parameter name in the select method that will be used for sorting, in this case orderBy.

Now I have binding, updating, deleting, sorting and server side paging all working nicely with very little code! Next I am going to add some simple filtering to filter the results on Forename and Surname. I have added the following to my page:

<fieldset>
    <legend>Search</legend>
    Forename: <asp:TextBox ID="txtSForename" runat="server" />
    Surname: <asp:TextBox ID="txtSSurname" runat="server" />
    <br />
    <asp:Button ID="btnSearch" Text="Search" runat="server" onclick="btnSearch_Click" />
    <asp:Button ID="btnClear" Text="Clear" runat="server" onclick="btnClear_Click" />
</fieldset>

The search button simply resets the GridView’s PageIndex so that it forces it to page 1 after a search, and the Clear button results the values in the TextBoxes:


protected void btnSearch_Click(object sender, EventArgs e)
{
    gvStudents.PageIndex = 0;
}

protected void btnClear_Click(object sender, EventArgs e)
{
    txtSForename.Text = string.Empty;
    txtSSurname.Text = string.Empty;
}

As the Search is student specific I need to add some new functionality to the student repository to handle the search. Firstly I have added a private method called StudentSearch which actually does the filtering and returns IQueryable<Student> accepting parameters for forename and surname:


private IQueryable<Student> StudentSearch(string forename, string surname)
{
    var query = from s in Context.Students
                select s;

    if (!string.IsNullOrEmpty(forename))
    {
        query = query.Where(s => s.Forename.Equals(forename));
    }

    if (!string.IsNullOrEmpty(surname))
    {
        query = query.Where(s => s.Surname.Equals(surname));
    }

    return query;
}

I have also added three different overloads for a public method called search. These all use the private StudentSearch method to handle filtering with no paging, with paging, and paging with sorting:


public List<Student> Search(string forename, string surname)
{
    return StudentSearch(forename, surname).ToList();
}

public List<Student> Search(string forename, string surname, int startRow, int maxRows)
{
    return PageFilter(StudentSearch(forename, surname), startRow, maxRows);
}

public List<Student> Search(string forename, string surname, int startRow, int maxRows, string orderBy)
{
    return PageFilter(StudentSearch(forename, surname), startRow, maxRows, orderBy);
}

I now need to change my ObjectDataSource’s SelectMethod to Search and add the SelectParameters for Forename and Surname:

<asp:ObjectDataSource
    ID="odsStudent"
    runat="server"
    TypeName="Repository.StudentRepository"
    DataObjectTypeName="Entities.Student"
    SelectMethod="Search"
    UpdateMethod="Save"
    DeleteMethod="Delete"
    SelectCountMethod="Count"
    StartRowIndexParameterName="startRow"
    MaximumRowsParameterName="maxRows"
    SortParameterName="orderBy"
    EnablePaging="true">
    <SelectParameters>
        <asp:ControlParameter ControlID="txtSForename" DbType="String" Name="forename" />
        <asp:ControlParameter ControlID="txtSSurname" DbType="String" Name="surname" />
     </SelectParameters>
</asp:ObjectDataSource>

Running the application now throws the following error:

ObjectDataSource ‘odsStudent’ could not find a non-generic method ‘Count’ that has parameters: forename, surname.

This is because the ObjectDataSource is now expecting a Count method with forename and surname parameters to get the number of filtered records. To overcome this I have added a Count method to the student repository which takes in this arguments and uses the StudentSearch method to return the number of records:

public int Count(string forename, string surname)
{
    return StudentSearch(forename, surname).Count();
}

Now everything works! Updating, deleting, server side paging, ordering and filtering witch very little code at the page level. I am sure I will come up with a more generic way to perform filtering but for now this way is fairly straight forward and doesn’t require too much code to implement.

Posted on by Joe in C#, Linq

5 Responses to Linq to SQL Tutorial – ObjectDataSource binding with paging and sorting

  1. PB

    Exactly what I needed! Thanks 😀

  2. Joe

    Glad it helped.

    Joe

  3. Pingback: forename surname

  4. Hung Doan

    So very very very good, You are really expert

  5. Nitin Kadam

    Hi, I tried everything But Still I am getting error
    “ObjectDataSource ‘ObjectDataSource1’ could not find a non-generic method ‘Count’ that has parameters: RestaurantKey, CategoryName, MenuName, IsAvailable, HiddenForCustomer.”

    But When I remove Select Count Method It Works Proprly But Show only 10 Records

Add a Comment