The EntitySpaces Community

Share and learn about the EntitySpaces Architecture.

Welcome to The EntitySpaces Community Sign in | Join | Help

The EntitySpaces Community » EntitySpaces API » ASP.NET - Runtime Data Binding » Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Last post 07-29-2008, 9:05 AM by Scott.Schecter. 16 replies.

Page 1 of 2 (17 items) 1 2 Next >
	Sort Posts: Previous Next

07-18-2008, 4:37 AM 10285

LBowers
Joined on 01-29-2007
United Kingdom
Posts 184

Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

I currently have a ListView control that is used to render a page of data. A DataPager control is attached to this, and is set to page 10 records at a time. An esDataSource control is used to source the data via the (ListView's DataSourceID), and AutoPaging is set to true.

Everything appears to work fine. However... What I'm finding (via SQL Server Profiler) is that there's an additional, unexpected, hit to the database when paging through data. For example:

Viewing rows 1 - 10.
I click on the "Next" button in the paging control.
esDataSource's esSelect event fires and retrieves rows 1 - 10 again (!?)
esDataSource then obtains a total record count.
esDataSource then retrieves rows 11 - 20.

I click on the "Next" button in the paging control.
esDataSource's esSelect event fires and retrieves rows 1 - 10 again (!?)
esDataSource then obtains a total record count.
esDataSource then retrieves rows 21 - 30.

Why is the additional database hit happening? Why are rows 1 - 10 retrieved regardless of what page of data is requested?

Thanks

Leigh

Report abuse

07-18-2008, 4:46 AM 10286 in reply to 10285

Scott.Schecter
Joined on 01-19-2007
Posts 583

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Are these all standard Microsoft controls?

Regards,

Scott Schecter
EntitySpaces | My Site

Report abuse

07-18-2008, 5:06 AM 10287 in reply to 10286

LBowers
Joined on 01-29-2007
United Kingdom
Posts 184

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Hi Scott.

Yes - ListView and DataPager come with .Net 3.5, as standard.

Leigh

Report abuse

07-18-2008, 5:12 AM 10288 in reply to 10287

Scott.Schecter
Joined on 01-19-2007
Posts 583

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Can you post a code sample for us to try and reproduce.

Regards,

Scott Schecter
EntitySpaces | My Site

Report abuse

07-18-2008, 5:25 AM 10289 in reply to 10288

LBowers
Joined on 01-29-2007
United Kingdom
Posts 184

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Sure thing. I've trimmed out any unnecessary markup/code.

Code:

<asp:ListView runat="server" ID="lvForumThreads">
    <LayoutTemplate>
        <table class="Forum" cellpadding="0" cellspacing="1">
            <thead>
                <th>
                </th>
            </thead>
            <tbody>
                <tr runat="server" id="itemPlaceHolder">
                </tr>
            </tbody>
        </table>
    </LayoutTemplate>
    <ItemTemplate>
        <tr>
            <td>
            </td>
        </tr>
    </ItemTemplate>
</asp:ListView>
<es:esDataSource ID="esDataSource" runat="server" AutoPaging="True" 
    onesselect="esDataSource_esSelect" />
<asp:DataPager ID="dataPager" runat="server" QueryStringField="pg" PagedControlID="lvForumThreads"
    PageSize="10">
    <Fields>
        <asp:NextPreviousPagerField ButtonType="Link" ShowFirstPageButton="True" ShowLastPageButton="True" />
    </Fields>
</asp:DataPager>

Code:

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                lvForumThreads.DataSourceID = "esDataSource";
            }
        }

        protected void esDataSource_esSelect(object sender, EntitySpaces.Web.esDataSourceSelectEventArgs e)
        {
            VwForumThreadQuery query = new VwForumThreadQuery();

            query.Where(query.ForumId == 1);
            query.OrderBy(query.DateLastPost, esOrderByDirection.Descending);

            e.Collection = new VwForumThreadCollection();
            e.Query = query;
        }

Leigh

Report abuse

07-18-2008, 6:32 AM 10290 in reply to 10289

LBowers
Joined on 01-29-2007
United Kingdom
Posts 184

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Sorry - I should add that I'm using ES2008.

Leigh

Report abuse

07-18-2008, 6:35 AM 10291 in reply to 10290

Scott.Schecter
Joined on 01-19-2007
Posts 583

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

I will try and reproduce this over the weekend. I will post back with my findings when I do.

Regards,

Scott Schecter
EntitySpaces | My Site

Report abuse

07-18-2008, 6:36 AM 10292 in reply to 10289

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 2,869

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Are you using ES2008? Also, have you set a break point in the esSelect event?

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

07-18-2008, 6:49 AM 10294 in reply to 10292

LBowers
Joined on 01-29-2007
United Kingdom
Posts 184

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Thanks Scott. I appreciate the help.

@Mike:

I added the ES 2008 info in a post a bit further up - Sorry it was missing initially.

I have set a breakpoint in the esSelect event, and it is firing twice.

Leigh

Report abuse

07-18-2008, 8:42 AM 10297 in reply to 10294

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 2,869

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

I will test this out in our esDataSource sample and get back to you, thanx for the post.

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

07-19-2008, 7:46 AM 10300 in reply to 10297

Scott.Schecter
Joined on 01-19-2007
Posts 583

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

I think you have your DataPager setup incorrectly for use with the ListView. Look at my sample, which appears to function correctly, and esSelect is only called once. This is using Employees from Northwind.

Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Sandbox.Web._Default" %>

<%@ Register Assembly="EntitySpaces.Web" Namespace="EntitySpaces.Web" TagPrefix="es" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ListView ID="ListView1" runat="server" DataKeyNames="EmployeeID" DataSourceID="esDataSource">
            <AlternatingItemTemplate>
                <tr style="">
                    <td>
                        <asp:Label ID="EmployeeIDLabel" runat="server" Text='<%# Eval("EmployeeID") %>' />
                    </td>
                    <td>
                        <asp:Label ID="LastNameLabel" runat="server" Text='<%# Eval("LastName") %>' />
                    </td>
                    <td>
                        <asp:Label ID="FirstNameLabel" runat="server" Text='<%# Eval("FirstName") %>' />
                    </td>
                </tr>
            </AlternatingItemTemplate>
            <LayoutTemplate>
                <table runat="server">
                    <tr runat="server">
                        <td runat="server">
                            <table id="itemPlaceholderContainer" runat="server" border="0" style="">
                                <tr runat="server" style="">
                                    <th runat="server">
                                        EmployeeID
                                    </th>
                                    <th runat="server">
                                        LastName
                                    </th>
                                    <th runat="server">
                                        FirstName
                                    </th>
                                </tr>
                                <tr id="itemPlaceholder" runat="server">
                                </tr>
                            </table>
                        </td>
                    </tr>
                    <tr runat="server">
                        <td runat="server" style="">
                            <asp:DataPager ID="DataPager1" runat="server" PageSize="2">
                                <Fields>
                                    <asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True" ShowLastPageButton="True" />
                                </Fields>
                            </asp:DataPager>
                        </td>
                    </tr>
                </table>
            </LayoutTemplate>
            <InsertItemTemplate>
                <tr style="">
                    <td>
                        <asp:Button ID="InsertButton" runat="server" CommandName="Insert" Text="Insert" />
                        <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Clear" />
                    </td>
                    <td>
                         
                    </td>
                    <td>
                        <asp:TextBox ID="LastNameTextBox" runat="server" Text='<%# Bind("LastName") %>' />
                    </td>
                    <td>
                        <asp:TextBox ID="FirstNameTextBox" runat="server" Text='<%# Bind("FirstName") %>' />
                    </td>
                </tr>
            </InsertItemTemplate>
            <SelectedItemTemplate>
                <tr style="">
                    <td>
                        <asp:Label ID="EmployeeIDLabel" runat="server" Text='<%# Eval("EmployeeID") %>' />
                    </td>
                    <td>
                        <asp:Label ID="LastNameLabel" runat="server" Text='<%# Eval("LastName") %>' />
                    </td>
                    <td>
                        <asp:Label ID="FirstNameLabel" runat="server" Text='<%# Eval("FirstName") %>' />
                    </td>
                </tr>
            </SelectedItemTemplate>
            <EmptyDataTemplate>
                <table runat="server" style="">
                    <tr>
                        <td>
                            No data was returned.
                        </td>
                    </tr>
                </table>
            </EmptyDataTemplate>
            <EditItemTemplate>
                <tr style="">
                    <td>
                        <asp:Button ID="UpdateButton" runat="server" CommandName="Update" Text="Update" />
                        <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Cancel" />
                    </td>
                    <td>
                        <asp:Label ID="EmployeeIDLabel1" runat="server" Text='<%# Eval("EmployeeID") %>' />
                    </td>
                    <td>
                        <asp:TextBox ID="LastNameTextBox" runat="server" Text='<%# Bind("LastName") %>' />
                    </td>
                    <td>
                        <asp:TextBox ID="FirstNameTextBox" runat="server" Text='<%# Bind("FirstName") %>' />
                    </td>
                </tr>
            </EditItemTemplate>
            <ItemTemplate>
                <tr style="">
                    <td>
                        <asp:Label ID="EmployeeIDLabel" runat="server" Text='<%# Eval("EmployeeID") %>' />
                    </td>
                    <td>
                        <asp:Label ID="LastNameLabel" runat="server" Text='<%# Eval("LastName") %>' />
                    </td>
                    <td>
                        <asp:Label ID="FirstNameLabel" runat="server" Text='<%# Eval("FirstName") %>' />
                    </td>
                </tr>
            </ItemTemplate>
        </asp:ListView>
        <es:esDataSource ID="esDataSource" runat="server" AutoPaging="True" AutoSorting="True"
            OnesSelect="esDataSource_esSelect" />
    </div>
    </form>
</body>
</html>

Code:

using System;
using BusinessObjects;

namespace Sandbox.Web
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        
        }

        protected void esDataSource_esSelect(object sender, EntitySpaces.Web.esDataSourceSelectEventArgs e)
        {
            var query = new EmployeesQuery();
            query.OrderBy(query.LastName.Ascending);

            e.Collection = new EmployeesCollection();
            e.Query = query;
        }
    }
}

Regards,

Scott Schecter
EntitySpaces | My Site

Filed under: esDataSource ListView DataPager 3.5

Report abuse

07-19-2008, 2:02 PM 10302 in reply to 10300

LBowers
Joined on 01-29-2007
United Kingdom
Posts 184

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Thanks for the info Scott.

Because I gave the DataPager's QueryStringField a value, the paging mechanism no-longer used post backs. It was actually requesting the page again, and using the "pg" querystring parameter to control paging (which makes sense). This is why my "if (!Page.IsPostBack)" check was failing; it wasn't a post back! The trouble is, if I use the post back approach, search engines will not be able to follow paging links (as there aren't any - they're all __doPostBack calls).

This won't ever work with data binding (will it?), because the inital bind will always take place when re-entering the page.

Thanks again.

Leigh

Report abuse

07-19-2008, 2:27 PM 10303 in reply to 10302

Scott.Schecter
Joined on 01-19-2007
Posts 583

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

I am not sure how specifying a QueryStringField would prevent a postback? I think your question about SEO is simply a control limitation, and nothing that can be helped from our side.

Regards,

Scott Schecter
EntitySpaces | My Site

Report abuse

07-19-2008, 3:04 PM 10304 in reply to 10303

LBowers
Joined on 01-29-2007
United Kingdom
Posts 184

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

Scott.Schecter:
I am not sure how specifying a QueryStringField would prevent a postback? I think your question about SEO is simply a control limitation, and nothing that can be helped from our side.

When suppling a QueryStringField value, the paging buttons are generated as links (even when a type of "Button" is specified) This is why the page is reloaded when clicking on the paging control, and not simply posted back.

Oh, and I know the SEO issue is a contol limitation. I didn't meant to imply ES had anyhting to do with it. I was merely poiting out the limitation of using this approach.

Leigh

Report abuse

07-19-2008, 3:37 PM 10307 in reply to 10304

Scott.Schecter
Joined on 01-19-2007
Posts 583

Re: Additional database hit when using esDataSource in conjunction with the ListView and DataPager controls

LBowers:
When suppling a QueryStringField value, the paging buttons are generated as links (even when a type of "Button" is specified) This is why the page is reloaded when clicking on the paging control, and not simply posted back.
Oh, and I know the SEO issue is a contol limitation. I didn't meant to imply ES had anyhting to do with it. I was merely poiting out the limitation of using this approach.

Gotcha, I thought you were saying they were not links in your previous post. That would cause the if not post back to evaluate true.

Regards,

Scott Schecter
EntitySpaces | My Site

Report abuse

Page 1 of 2 (17 items) 1 2 Next >