custom paging w/ dynamic fields

[=?Utf-8?B?SmVmZiBCZWVt?=]

The default paging behavior of the gridview doesn't work well with very large
sets of data which means we have to implement some sort of custom paging.
The examples I have seen (4guysfromrolla, etc.) suggest using an
ObjectDataSource which has built-in paging functionality that, when used in
conjunction with certain SQL 2005 functionality, only works with the records
to be displayed on the page rather than the entire set. The problem with
using the objectdatasource, for us, is that our users have the ability to
select the fields they want to see, which means we don't know what fields
will be present at design time.

Does anyone have a solution for when the grid fields will be dynamic?

Thanks in advance

[Alexey Smirnov]

"Jeff Beem" <Je******@discussions.microsoft.comwrote in message
news:BA**********************************@microsof t.com...

The default paging behavior of the gridview doesn't work well with very
large
sets of data which means we have to implement some sort of custom paging.
The examples I have seen (4guysfromrolla, etc.) suggest using an
ObjectDataSource which has built-in paging functionality that, when used
in
conjunction with certain SQL 2005 functionality, only works with the
records
to be displayed on the page rather than the entire set. The problem with
using the objectdatasource, for us, is that our users have the ability to
select the fields they want to see, which means we don't know what fields
will be present at design time.

Does anyone have a solution for when the grid fields will be dynamic?

Thanks in advance

Hi Jeff

I think, you can try to do the data paging on the server using SQL Server
2005's new ROW_NUMBER() feature (in a stored procedure). If you have not
that many fields, you can return all of the fields for each page and bind to
a datagrid selected fields only.

With MyDocs AS (
SELECT *
ROW_NUMBER() OVER (order by 'sortcolumn') as RowNumber
FROM AllDocs )
select *
from MyDocs
Where RowNumber Between 1 and 10

There are many articles on this topic available in the Internet
http://www.google.com/search?hl=en&q...paging+asp.net

Hope it helps

[=?Utf-8?B?SmVmZiBCZWVt?=]

Hello Alexey,

This is the approach mentioned in several of the articles I have seen. The
problem is that you still have to somehow tell the gridview the
virtualrecordcount so that it creates the correct number of page links. This
is why they're using the ObjectDataSource, as it supports the
virtualrecordcount, in those articles. Problem with binding to objects,
though, is that the fields we need to bind to are decided at runtime. We
already have the stored procedures that will return the correct records based
on the current page, etc. The problem is getting the paging to show on the
grid. If we don't use the ObjectDataSource all we get is one page, because
there appears to be no way to set the virtualrecordcount.

Thanks,

Jeff

"Alexey Smirnov" wrote:

>
"Jeff Beem" <Je******@discussions.microsoft.comwrote in message
news:BA**********************************@microsof t.com...

The default paging behavior of the gridview doesn't work well with very
large
sets of data which means we have to implement some sort of custom paging.
The examples I have seen (4guysfromrolla, etc.) suggest using an
ObjectDataSource which has built-in paging functionality that, when used
in
conjunction with certain SQL 2005 functionality, only works with the
records
to be displayed on the page rather than the entire set. The problem with
using the objectdatasource, for us, is that our users have the ability to
select the fields they want to see, which means we don't know what fields
will be present at design time.

Does anyone have a solution for when the grid fields will be dynamic?

Thanks in advance

Hi Jeff

I think, you can try to do the data paging on the server using SQL Server
2005's new ROW_NUMBER() feature (in a stored procedure). If you have not
that many fields, you can return all of the fields for each page and bind to
a datagrid selected fields only.

With MyDocs AS (
SELECT *
ROW_NUMBER() OVER (order by 'sortcolumn') as RowNumber
FROM AllDocs )
select *
from MyDocs
Where RowNumber Between 1 and 10

There are many articles on this topic available in the Internet
http://www.google.com/search?hl=en&q...paging+asp.net

Hope it helps

[Alexey Smirnov]

"Jeff Beem" <Je******@discussions.microsoft.comwrote in message
news:C8**********************************@microsof t.com...

Hello Alexey,

This is the approach mentioned in several of the articles I have seen.
The
problem is that you still have to somehow tell the gridview the
virtualrecordcount so that it creates the correct number of page links.
This
is why they're using the ObjectDataSource, as it supports the
virtualrecordcount, in those articles. Problem with binding to objects,
though, is that the fields we need to bind to are decided at runtime. We
already have the stored procedures that will return the correct records
based
on the current page, etc. The problem is getting the paging to show on
the
grid. If we don't use the ObjectDataSource all we get is one page,
because
there appears to be no way to set the virtualrecordcount.

In the case of custom paging you cannot use the built-in navigation, you
will need a custom navigation. Your stored procedure must return a total
number of the found rows. This number can be used to calculate how many
pages do you have (~ total \ gridview.Items.Count)

http://dotnetjunkies.com/Tutorial/EA...B871F967F.dcik

How do you select the fields you want to see? Maybe your problem is in just
in the select statement

[=?Utf-8?B?SmVmZiBCZWVt?=]

Hi Alexey,

The fields are returned correctly from the stored procedure, that is not the
problem. The problem is that the gridview doesn't support setting a
virtualrecordcount so that it shows the correct number of pages (for example,
if I return the first 10 records but I knoew I have 10,000, I need to have
the gridview show the page numbers, etc. that indicate there are more pages).
The DataGrid supports this, from what I've been told, and we're considering
changing to use that, but we were hoping to find a solution using the
GridView.

"Alexey Smirnov" wrote:

>
"Jeff Beem" <Je******@discussions.microsoft.comwrote in message
news:C8**********************************@microsof t.com...

Hello Alexey,

This is the approach mentioned in several of the articles I have seen.
The
problem is that you still have to somehow tell the gridview the
virtualrecordcount so that it creates the correct number of page links.
This
is why they're using the ObjectDataSource, as it supports the
virtualrecordcount, in those articles. Problem with binding to objects,
though, is that the fields we need to bind to are decided at runtime. We
already have the stored procedures that will return the correct records
based
on the current page, etc. The problem is getting the paging to show on
the
grid. If we don't use the ObjectDataSource all we get is one page,
because
there appears to be no way to set the virtualrecordcount.

In the case of custom paging you cannot use the built-in navigation, you
will need a custom navigation. Your stored procedure must return a total
number of the found rows. This number can be used to calculate how many
pages do you have (~ total \ gridview.Items.Count)

http://dotnetjunkies.com/Tutorial/EA...B871F967F.dcik

How do you select the fields you want to see? Maybe your problem is in just
in the select statement

[Alexey Smirnov]

"Jeff Beem" <Je******@discussions.microsoft.comwrote in message
news:48**********************************@microsof t.com...

Hi Alexey,

The fields are returned correctly from the stored procedure, that is not
the
problem. The problem is that the gridview doesn't support setting a
virtualrecordcount so that it shows the correct number of pages (for
example,
if I return the first 10 records but I knoew I have 10,000, I need to have
the gridview show the page numbers, etc. that indicate there are more
pages).

The ObjectDataSource has a SelectCountMethod method which tells the gridview
how many rows are expected.

<asp:ObjectDataSource SelectCountMethod="GetTotalNumber"...

public int GetTotalNumber()
{
using (SqlConnection conn =
new SqlConnection(ConnectionString))
{
using (SqlCommand cmd =
conn.CreateCommand())
{
cmd.CommandText = "select count(*) from ...;
conn.Open();
return = (int)cmd.ExecuteScalar();
}
}
}

GridView Examples for ASP.NET 2.0: Paging and Sorting the GridView's Data
http://msdn2.microsoft.com/en-us/library/aa479347.aspx

[=?Utf-8?B?SmVmZiBCZWVt?=]

Hi Alexey,

I realize the ObjectDataSource supports this. My question is how do I
create an object that has the dynamic fields? For example, let's say I have
a "Document" object that has a "DocumentId" property. That much is static,
it will always have that property. However, the user can select that they
want to also see "Author", "LastSavedDate", "WordCount", etc. out of a list
of potentially thousands of fields. Now, the stored procedure we have will
return a dataset that has those fields (field1=DocumentId, field2=Author,
field3=LastSavedDate, etc.) But I don't have an object that has those
fields/properties. I agree that the ObjectDataSource is a good solution for
custom paging. What I don't understand is how to get it to work with a set
of fields that I won't know until runtime.

I read an article about using Reflection.Emit and dynamic properties to add
properties to an existing object. However, since I have very little time
and I do not know that the databinding would even recognize these virtual
properties, I don't really want to spend the time investigating.

Any thoughts?

Thanks,

Jeff

"Alexey Smirnov" wrote:

>
"Jeff Beem" <Je******@discussions.microsoft.comwrote in message
news:48**********************************@microsof t.com...

Hi Alexey,

The fields are returned correctly from the stored procedure, that is not
the
problem. The problem is that the gridview doesn't support setting a
virtualrecordcount so that it shows the correct number of pages (for
example,
if I return the first 10 records but I knoew I have 10,000, I need to have
the gridview show the page numbers, etc. that indicate there are more
pages).

The ObjectDataSource has a SelectCountMethod method which tells the gridview
how many rows are expected.

<asp:ObjectDataSource SelectCountMethod="GetTotalNumber"...

public int GetTotalNumber()
{
using (SqlConnection conn =
new SqlConnection(ConnectionString))
{
using (SqlCommand cmd =
conn.CreateCommand())
{
cmd.CommandText = "select count(*) from ...;
conn.Open();
return = (int)cmd.ExecuteScalar();
}
}
}

GridView Examples for ASP.NET 2.0: Paging and Sorting the GridView's Data
http://msdn2.microsoft.com/en-us/library/aa479347.aspx

[Alexey Smirnov]

"Jeff Beem" <Je******@discussions.microsoft.comwrote in message
news:09**********************************@microsof t.com...

Hi Alexey,

I realize the ObjectDataSource supports this. My question is how do I
create an object that has the dynamic fields? For example, let's say I
have
a "Document" object that has a "DocumentId" property. That much is
static,
it will always have that property. However, the user can select that they
want to also see "Author", "LastSavedDate", "WordCount", etc. out of a
list
of potentially thousands of fields. Now, the stored procedure we have
will
return a dataset that has those fields (field1=DocumentId, field2=Author,
field3=LastSavedDate, etc.) But I don't have an object that has those
fields/properties. I agree that the ObjectDataSource is a good solution
for
custom paging. What I don't understand is how to get it to work with a
set
of fields that I won't know until runtime.

Hmm, but there is an ObjectDataSource.SelectMethod property, right?
It used to set the name of the method that selects the data.

<asp:objectdatasource
id="ObjectDataSource1"
runat="server"
selectmethod="GetAllDocuments"

Now, in the GetAllDocuments() you can define which fields you need to get
back from the database.

public static ICollection GetAllDocuments()
throws SqlException
{
string sql = "SELECT DocumentId";

if (user_has_selected == "Author")
sql += ",Author";
if (user_has_selected == "LastSavedDate")
sql += ",LastSavedDate";
...
sql += " FROM Documents";

SqlDataSource sds =
new SqlDataSource(cts.get_ConnectionString(sql),

if you want to use a stored procedure, you have to pass the fields as a
parameter,

for example

string fields = ""

string sql = "";

if (user_has_selected == "Author")
sql += ",Author";
if (user_has_selected == "LastSavedDate")
sql += ",LastSavedDate";

and build a dynamic SQL

CREATE PROC .... (

@fields nvarchar(max)

)

AS

SET @cmd = 'SELECT DocumentId' +

@fields

+ ' FROM...'

EXEC sp_executesql @cmd

go