ObjectDataSource, GridView and Filtering... How to get row count? : The Official Microsoft ASP.NET Forums

Reply

ObjectDataSource, GridView and Filtering... How to get row count?

Oct 22, 2012 07:50 AM|LINK

I know this has been posted a million of times... However it always trick me...

How can I get the total filtered row count, for a GridView with Paging and Filtering applied?

I have a GridView bound to an ObjectDataSource with Filtering applied.

ASPX code

<asp:GridView ID="grvDati" runat="server" DataSourceID="ODSData" AllowPaging="True"
<asp:ObjectDataSource ID="ODSData" runat="server" OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetRicambiWithDisponibilitaByDesc_WithoutParameters" TypeName="WebSpace.BusinessLayer.BRicambi"
    EnableCaching="true" CacheDuration="6000" CacheKeyDependency="cacheKeyRicambi"
    FilterExpression="(DES_RICAMBIO like '%{0}%' OR COD_RICAMBIO like '%{0}%' OR COD_DEPOSITO like '%{0}%' OR COD_UBICAZIONE like '%{0}%' OR CONVERT(DISPONIBILITA, 'System.String') like '%{0}%' OR CONVERT(GIACENZA_STOCK, 'System.String') like '%{0}%' OR CONVERT(QTA_ASSEGNATA, 'System.String') like '%{0}%' OR CONVERT(DATA_INATTIVO, 'System.String') like '%{0}%') AND (DES_RICAMBIO like '%{1}%' AND DES_RICAMBIO like '%{2}%')"
    OnFiltering="ODSData_Filtering" OnSelecting="ODSData_Selecting" OnSelected="ODSData_Selected">
    <FilterParameters>
        <asp:ControlParameter ControlID="txtFilter" PropertyName="Text" />
        <asp:ControlParameter ControlID="txtFilter" PropertyName="Text" />
        <asp:ControlParameter ControlID="txtFilter" PropertyName="Text" />
    </FilterParameters>
</asp:ObjectDataSource>

C# code

On ODS Selected event I get the row count ... but unfortunately this is only and always the TOTAL ROWS of the table, and not

the filtered rows count!!

protected void ODSData_Selected(object sender, ObjectDataSourceStatusEventArgs e)
        {
            if (e.ReturnValue != null)
            {
                if (((System.Data.DataSet)e.ReturnValue).Tables[0] != null)
                {
                    _recordsCount = ((System.Data.DataSet)e.ReturnValue).Tables[0].Rows.Count;
                }
            }
        }

Then, I show it on the GridView DataBound event, but as I have yet said this is only the TOTAL RECORDS on the table.

However ASP.NET knows the "REAL" row count after FILTERING, in fact it displays the correct number of pages!

So, how can I get it myself?

I was wondering why doesn't exist a FILTERED event but only a FILTERING event?

For me it seems the best spot to get the filtered row count....

PS: I don't want to call another function to retrieve the count from the db, as I should also process the filtering parameters! I'd like to know, if exist, a more direct solution

PPS: I have yet implemented a quite dirty hack, based on my above considerations of ASP.NET knowing the REAL count... But I'm not very satisfied of it!

Experience...is simply the name we give our mistakes.

Edited by TheRed on Oct 22, 2012 07:51 AM
Edited by TheRed on Oct 22, 2012 07:51 AM
Edited by TheRed on Oct 22, 2012 07:59 AM
Edited by TheRed on Oct 23, 2012 12:32 PM
Edited by TheRed on Oct 23, 2012 12:37 PM

Reply

Re: ObjectDataSource, GridView and Filtering... How to get row count?

Oct 24, 2012 02:06 AM|LINK

TheRed
TheRed

Hi,

You can try GridView1.Rows.Count after filtering and after it has been re-databound in a Button's click.

If still fails, you have to use jQuery to get the total Row Count.

Reply

Re: ObjectDataSource, GridView and Filtering... How to get row count?

Oct 24, 2012 06:53 AM|LINK

But the GridView is paginated, so I always get the row count of the current page...

What do you mean by using jQuery? Which function can i use to get the row count of the gridview?

Experience...is simply the name we give our mistakes.

Reply

Re: ObjectDataSource, GridView and Filtering... How to get row count?

Oct 24, 2012 07:07 AM|LINK

TheRed
What do you mean by using jQuery?

I mean this, if you've referred jQuery.

#("tr").length;

Reply

Re: ObjectDataSource, GridView and Filtering... How to get row count?

Oct 26, 2012 07:25 AM|LINK

Try

e.AffectedRows

in your "ODSData_Selected" function

Mark it as Answer, if you feel so. Thanks

codepattern.net/blog ||@AnilAwadh

Reply

Re: ObjectDataSource, GridView and Filtering... How to get row count?

Oct 26, 2012 12:24 PM|LINK

@anil

e.AffectedRows return always -1

(ObjectDataSource has caching enabled...)

@Decker

But paging is enabled, so the table only has 10 (PageSize) tr's

Experience...is simply the name we give our mistakes.

Edited by TheRed on Oct 26, 2012 12:27 PM

Reply

Re: ObjectDataSource, GridView and Filtering... How to get row count?

Oct 26, 2012 01:01 PM|LINK

Hi TheRed,

I tested that code and it is working fine. You need to have a small hack as-

protected void ODSData_Selected(object sender, ObjectDataSourceStatusEventArgs e)
        {

            if (e.Exception == null)
            {
                if (e.ReturnValue.GetType() == Type.GetType("System.Int32"))
                   _recordsCount = e.ReturnValue;
            }
        }

Just set the debugger, and you will find the total records in second trip of ODSData_Selected call (this second trip/call to this method is due to selectCount method which is specified in ObjectDataSource). In fact this function get calls twice for your single databind.

Mark it as Answer, if you feel so. Thanks

codepattern.net/blog ||@AnilAwadh

Edited by anil.india on Oct 26, 2012 04:51 PM

Reply

TheRed

Member

196 Points

94 Posts

Re: ObjectDataSource, GridView and Filtering... How to get row count?

Oct 26, 2012 01:35 PM|LINK

I have tried you code, and doesn't work for me, but however I have some doubts...

The ReturnValue in my case is of type DataSet...

The DataLayer method called by the Select operation is like:

public DataSet GetRicambiWithDisponibilitaByDesc_WithoutParameters()
        {
            dataSet = new DataSet("ricambi");
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = "ASP_Get_RICAMBI_WITH_DISP_WITHOUT_PARAMETERS";

            try
            {
                this.OpenConnection();
                sqlCommand.Connection = conConnection;
                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                sqlDataAdapter.Fill(dataSet, "ricambi");
                this.CloseConnection();
            }
            catch (Exception ex)
            {
                AppLogger.Instance.LogError(this.GetType().ToString(), ex);
                dataSet = null;
                this.CloseConnection();
                throw;
            }

            return dataSet;
        }

I could change this method or not? I don't think so otherwise the Databindings would not function anymore...

In any case, that is not the point. The point is that ALL the records in the Database table are retrieved on the first call. Then ALL RECORDS are cached by ASP.NET runtime (or by browser :-)?) After each time the user enters some characters in the TextBox, the filter parameter is updated, the browser applies the Filtering specified by the filtering query, and shows the results in the GridView. So I would appreciate very much if there was, besides the Filtering, a FILTERED event ...

Experience...is simply the name we give our mistakes.

Edited by TheRed on Oct 26, 2012 01:36 PM
Edited by TheRed on Oct 26, 2012 02:22 PM

Reply

anil.india

Contributor

2613 Points

453 Posts

Re: ObjectDataSource, GridView and Filtering... How to get row count?

Oct 26, 2012 04:50 PM|LINK

EnableCaching="true" CacheDuration="6000"

First time your gridview is being binded with all records and since you have enabled caching of datasource, it might be filtering the records from cache.

As I said in my previous post, _selected function get called twice (first time for selecting record and second time for getting record count) and there you can find the total record. But in your case you are not spedifying selectcount function so it might be called only once. And this first time you would be getting just filtered record in dataset. So if you need to get count of total records irrespective of page size of gridview, you have to specify selectCount method.

So without the selectcount method call, I see nothing to get total records irrespective of gridview page size.

Mark it as Answer, if you feel so. Thanks

codepattern.net/blog ||@AnilAwadh

Marked as answer by decker dong - msft on Oct 28, 2012 04:20 AM

Reply

craigbtx

Participant

1702 Points

896 Posts

Re: ObjectDataSource, GridView and Filtering... How to get row count?

Jan 30, 2013 03:51 PM|LINK

This works too.

Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Selected

RowCountFilteredLabel.Text = e.AffectedRows

End Sub

Protected Sub GridView1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.PreRender
Dim dv As System.Data.DataView = DirectCast(SqlDataSource1.[Select](DataSourceSelectArguments.Empty), System.Data.DataView)
RowCountFilteredLabel.Text = dv.Count & " of " & RowCountFilteredLabel.Text

End Sub

Edited by craigbtx on Jan 30, 2013 03:58 PM

ObjectDataSource, GridView and Filtering... How to get row count? RSS

9 replies

TheRed

ObjectDataSource, GridView and Filtering... How to get row count?

Decker Dong ...

Re: ObjectDataSource, GridView and Filtering... How to get row count?

TheRed

TheRed

Re: ObjectDataSource, GridView and Filtering... How to get row count?

Decker Dong ...

Re: ObjectDataSource, GridView and Filtering... How to get row count?

TheRed

anil.india

Re: ObjectDataSource, GridView and Filtering... How to get row count?

TheRed

Re: ObjectDataSource, GridView and Filtering... How to get row count?

anil.india

Re: ObjectDataSource, GridView and Filtering... How to get row count?

TheRed

Re: ObjectDataSource, GridView and Filtering... How to get row count?

anil.india

Re: ObjectDataSource, GridView and Filtering... How to get row count?

craigbtx

Re: ObjectDataSource, GridView and Filtering... How to get row count?

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft