GridView Paging and Sorting with EntityDataSource

There are many different possible configurations and combinations that can be created using the GridView and any of the many available data source controls.  The EntityDataSource control can be used along with the GridView to provide the paging and sorting functionality required for many situations.  Below is a basic example and I hope to provide more as time allows.

<asp:GridView ID="GridView1" runat="server"
    AllowPaging="True" AllowSorting="True" PageSize="10" AutoGenerateColumns="False"
    DataSourceID="CustomersDataSource" DataKeyNames="CustomerID">
    <Columns>
        <asp:BoundField DataField="CompanyName" HeaderText="Company" SortExpression="CompanyName" />
        <asp:BoundField DataField="ContactTitle" HeaderText="Title" SortExpression="ContactTitle" />
        <asp:BoundField DataField="ContactName" HeaderText="Contact" SortExpression="ContactName" />
        <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
        <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region" />
        <asp:BoundField DataField="PostalCode" HeaderText="Postal Code" SortExpression="PostalCode" />
    </Columns>
</asp:GridView>

<data:EntityDataSource ID="CustomersDataSource" runat="server"
    ProviderName="CustomersProvider"
    EntityTypeName="Northwind.BLL.Customers, Northwind.BLL"
    SelectMethod="GetPaged"
    EnablePaging="True"
    EnableSorting="True"
>
    <Parameters>
        <data:CustomParameter Name="WhereClause" Value="Country = 'USA'" ConvertEmptyStringToNull="false" />
        <data:CustomParameter Name="OrderByClause" Value="CompanyName ASC" ConvertEmptyStringToNull="false" />
        <asp:ControlParameter Name="PageIndex" ControlID="GridView1" PropertyName="PageIndex" Type="Int32" />
        <asp:ControlParameter Name="PageSize" ControlID="GridView1" PropertyName="PageSize" Type="Int32" />
        <data:CustomParameter Name="RecordCount" Value="0" Type="Int32" />
    </Parameters>
</data:EntityDataSource>

Michael (mike123) gave a great tip over in the General Support forum and I wanted to include it as part of the documentation.  The following code snippet shows how you can set the value of a CustomParameter object at runtime:


using Northwind.Web.Data;

...

CustomParameter p = CustomersDataSource.Parameters["WhereClause"] as CustomParameter;
p.Value = "Country = 'USA'";

Hi Bobby,

First of all great piece of work.

I think stumbled across a bug in the EntityDataSource.When the method GetEntityList is called and _entityList != null than the arguments.TotalRowCount is not properly set. This may cause a problem when using controls who need this RowCount like the Telerik Grid Control.

To solve this problem I defined a private field _entityCount and used this to remember the rowcount for me. In GetEntityList arguments.TotalRowCount will be set to _entityCount when the entitylist exists.

Kind Regards,

Niels Schneider

Niels,

Good catch!  I am working on a couple of bug fixes and some enhancements for the EntityDataSource as well as some of the other classes in the Web Library, and I will be sure to include this in my next update.

Thank you.

Hi Bobby!

I am in the process of chossing a good O/R-Mapping tool for a project, and .netTiers retained my attention. I have to tell you that working with n-Tiers architecture is new to me, and that I understand OO programming concepts, but I am no OOP guru. Now, there is one thing with .netTiers that is still obscure to me: How do you handle queries that require a JOIN operation? For example, how would you rewrite the code in your example if the contacts (ContactTitle, ContactName, etc.) would be stored in their own table? Let's say, just for the sake of my question that the tables in Northwind would be defined as follows:

[Customers]
    CustomerID
    CompanyName
    ContactID
    Address
    City
    Region
    PostalCode
    Country
    Phone
    Fax

[Contacts]
    ContactID
    ContactName
    ContactTitle

From what I have read up to now, I understand that it would be possible to use a Stored Procedure or a View? Are these the only solutions? I am asking because sometimes, I write code to build my SQL queries dynamically and to include only the tables I need in JOIN operations. This would not be possible with a Stored Proc or a View. I mainly use this technic with Search Forms where the user can specify a variety of parameters to perform the search. It allows me to build a SELECT query that matches the parameters the user specified.

There is a second part to my question. Let's say now that you want to design an Edit form to modify a Customer. How would you handle this with the table schema I gave you above? Or in more generic terms, how do you handle CRUD operations with .netTiers on objects that span over more than one table?

Best regards,

Jean-François Beauchamp
IT Consultant

Hey velum.

I'm also reasonable new to NetTiers so I was pondering something similar.

For your example, you would set up a foreign key on ContactID obviously.
Then NetTiers would pick up that key and allow you to do the following:

Customers MyCustomer = DataRepository.CustomersProvider.GetByID(1);

//Populate the ContactIDSource
MyCustomer.ContactIDSource = DataRepository.ContactsProvider.GetByID(MyCustomer.ContactID);

//Use the ContactIDSource
MessageBox.Show(MyCustomer.ContactIDSource.ContactName);

//Edit the ContactIDSource
MyCustomer.ContactIDSource.ContactName = "John Doh";

//Save the changes
DataRepository.ContactsProvider.Save(MyCustomer.ContactIDSource);

 

What I don't know how to do, is to display the Contact Name in a datagrid next to the company name.  I tried setting a Bound Column to be "ContactIDSource.ContactName".  That didn't work.

Well...

Hope this helps...

and it would be great if someone could tell me how to display the CompanyName 'joined' to the ContactName in a DataGrid.

Thanks
Daryl

 

bdiaz wrote:

Michael (mike123) gave a great tip over in the General Support forum and I wanted to include it as part of the documentation.  The following code snippet shows how you can set the value of a CustomParameter object at runtime:


using Northwind.Web.Data;

...

CustomParameter p = CustomersDataSource.Parameters["WhereClause"] as CustomParameter;
p.Value = "Country = 'USA'";

I am currently attempting to write a filter form above my gridview control.  I have a DropDownList for field name and a DropDownList for asc/desc.

I am looking to pass these values into my ClientsDataSourceObject via a button onClick event.

I tried to use the above code but with no luck, can someone please post a working example of the above code in action as this simple problem has given me a headache all weekend.

Yours

David Lawton

To get the ContactName to display in the grid, you will need to use a TempalteColumn.

<asp:TemplateColumn HeaderText="Contact">
    <ItemTemplate>
       <%# Eval("ContactIDSource.ContactName") %>
    </ItemTemplate>
</asp:TemplateColumn>

Hope that helps.

David,

Can you post some sample code for your GridView and data source control so that I can give you a meaningful example?

Thanks.

bdiaz wrote:

David,

Can you post some sample code for your GridView and data source control so that I can give you a meaningful example?

Thanks.

 

Will do, codes at home and i am at work right now. will post tonight or tomorrow morning.

Thanks

I've just been playing with this and it seems to me that the 'OrderByClause' should actually be 'OrderBy'; i.e.

<data:CustomParameter Name="OrderBy" Value="CompanyName ASC" ConvertEmptyStringToNull="false" />

I can't get the sort to work without this amendment.

More work on sorting seems to indicate that I need to change this:

<data:CustomParameter Name="OrderByClause" Value="CompanyName ASC" ConvertEmptyStringToNull="false" />

To this:

<asp:ControlParameter Name="OrderBy" ControlID="GridView1" PropertyName="SortExpression" Type="String"/>

But this doesn't allow control of the sort direction.

These changes give me a sortable GridView but I feel I might be missing something here... ?

Hi, 

I’m trying to get a GridView to sort on a foreign key column using NetTiers. This is not supported on the EntityGridView on HyperLinkField Columns as well. To explain a bit further on what I’m trying to achieve I’ll take the above listed example.

 Table Structure

[Customers]
    CustomerID
    CompanyName
    ContactID
    Address
    City
    Region
    PostalCode
    Country
    Phone
    Fax

[Contacts]
    ContactID
    ContactName
    ContactTitle

 In my aspx page in my GridView I have the following <asp:TemplateColumn HeaderText="Contact">
    <ItemTemplate>
       <%# Eval("ContactIDSource.ContactName") %>
    </ItemTemplate>
</asp:TemplateColumn> Everything in the grid works fine, except that I cannot sort by the Contact [ContactName to be more precise. I can get it to sort by ContactID but that is not the requirement]. If someone could tell me if this is possible using NetTiers or any ideas or pointers would be much appreciated. Thanks,Jay.

 

Jayawewa,

Try this (not an elegant solution though ...)

                <data:HyperLinkField HeaderText="Contact" DataContainer="ContactIDSource" DataTextField="ContactName"
                 SortExpression="(SELECT c.ContactName FROM Contacts c WHERE Customers.ContactID = c.ContactID)"
                />

or this way:

                <asp:TemplateField HeaderText="Contact" SortExpression="(SELECT c.ContactName FROM Contacts c WHERE Customers.ContactID = c.ContactID)">
                    <ItemTemplate>
                       <%# Eval("ContactIDSource.ContactName")%>
                    </ItemTemplate>
                </asp:TemplateField>    

Hi Mike,Thanks for the prompt response. I tried your solution out. I agree that it is not the most elegant, but it should work for small tables but for tables with large numbers of rows the performance hit will be large. Unfortunately for me the table I was hoping to use this one is a large table. I feel this is one of the basic requirements that most times us developers are asked to do so other tips or tricks that could solve this problem would be greatly appreciated. Another thing I noticed was when I added a sort expression to my GridView, like the one you sent, when I sorted this column the GridView disappears [no page errors or anything, it just disappears]. Any idea what might be causing this since this is the first time I have come across this type of issue. Thanks,Jay.