Welcome to the CodeSmith Community!

Re: GridView Paging and Sorting with EntityDataSource

.netTiers

A description has not yet been added to this group.

GridView Paging and Sorting with EntityDataSource

  • rated by 0 users
  • This post has 17 Replies |
  • 7 Followers
  • 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>



    Bobby Diaz ------------------------------------------ Member of the .NetTiers team http://www.nettiers.com ------------------------------------------
  • 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'";



    Bobby Diaz ------------------------------------------ Member of the .NetTiers team http://www.nettiers.com ------------------------------------------
  • 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.

    Bobby Diaz ------------------------------------------ Member of the .NetTiers team http://www.nettiers.com ------------------------------------------
  • 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

    David Lawton Hyperion Technologies Ltd (UK)
  • 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.

    Bobby Diaz ------------------------------------------ Member of the .NetTiers team http://www.nettiers.com ------------------------------------------
  • David,

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

    Thanks.

    Bobby Diaz ------------------------------------------ Member of the .NetTiers team http://www.nettiers.com ------------------------------------------
  • 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

    David Lawton Hyperion Technologies Ltd (UK)
  • 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>    

    Mike Shatny
    --------------------------------------------------------------
    Member of the .netTiers team http://www.nettiers.com
    --------------------------------------------------------------

  • 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. 
Page 1 of 2 (18 items) 12