greetings -
I am trying to set up a custom parameters page in an ASP .NET application -
I am in the process of choosing between keeping the values (information I
need to list the parameters for the reports, as well as the available, valid
and default values for any given report) in a database or access all I need
through reporting services (web service).
Does anyone have any advice for setting up a Web App to run rs reports - can
you get all you need from the rs web services to create a custom parameter
page, or is it better to retrieve the values you need from a database?
I can dynamically build the custom parm page using values from a database
(dynamically list the parms for the report, fire off the sp to return valid
values for the parm and set the default value) - I would like to get this
going using the web services but am stuck at the point of trying to fill the
valid parm values - I do have the name of the stored procedure to run to
retrieve the valid values for the parm, but can't seem to figure out how to
either a)get the name of the sp to run, or b)just do a databind some how to
my dynamic control (what ever that may be - a textbox, a list box, a drop
down list, etc.)
Any ideas - ?
ThanksI have done exactly this, but the code is at work and it is a (very) long
weekend.
You can certainly use the web services to retrieve everything - the trick to
getting the values you have set up from datasets that populate the possible
values etc is to that when you run the GetReportParameters method, make sure
you set the ForRendering option to true - then everything you need comes
back.
Have a go and I'll post my code on Tuesday
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> greetings -
> I am trying to set up a custom parameters page in an ASP .NET
> application -
> I am in the process of choosing between keeping the values (information I
> need to list the parameters for the reports, as well as the available,
> valid
> and default values for any given report) in a database or access all I
> need
> through reporting services (web service).
> Does anyone have any advice for setting up a Web App to run rs reports -
> can
> you get all you need from the rs web services to create a custom parameter
> page, or is it better to retrieve the values you need from a database?
> I can dynamically build the custom parm page using values from a database
> (dynamically list the parms for the report, fire off the sp to return
> valid
> values for the parm and set the default value) - I would like to get this
> going using the web services but am stuck at the point of trying to fill
> the
> valid parm values - I do have the name of the stored procedure to run to
> retrieve the valid values for the parm, but can't seem to figure out how
> to
> either a)get the name of the sp to run, or b)just do a databind some how
> to
> my dynamic control (what ever that may be - a textbox, a list box, a drop
> down list, etc.)
> Any ideas - ?
>
> Thanks
>
>|||Thank you for the reply Mary - I may need a little help yet,
I went back and read my question and I was so involved I think I mis-stated
my question! Do the web services expose the names of the stored procedures
used to fill the valid values for the parameters - from your response it
sounds as if you just use the GetReportParameters method and that returns
everything (all of the datasets for all of the parameters) - if that's the
case how do you separate or sort through it all...HELP!
It was a very long weekend - and I still didn't get anything done!
Thanks,
"Mary Bray [MVP]" wrote:
> I have done exactly this, but the code is at work and it is a (very) long
> weekend.
> You can certainly use the web services to retrieve everything - the trick to
> getting the values you have set up from datasets that populate the possible
> values etc is to that when you run the GetReportParameters method, make sure
> you set the ForRendering option to true - then everything you need comes
> back.
> Have a go and I'll post my code on Tuesday
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > greetings -
> >
> > I am trying to set up a custom parameters page in an ASP .NET
> > application -
> > I am in the process of choosing between keeping the values (information I
> > need to list the parameters for the reports, as well as the available,
> > valid
> > and default values for any given report) in a database or access all I
> > need
> > through reporting services (web service).
> >
> > Does anyone have any advice for setting up a Web App to run rs reports -
> > can
> > you get all you need from the rs web services to create a custom parameter
> > page, or is it better to retrieve the values you need from a database?
> >
> > I can dynamically build the custom parm page using values from a database
> > (dynamically list the parms for the report, fire off the sp to return
> > valid
> > values for the parm and set the default value) - I would like to get this
> > going using the web services but am stuck at the point of trying to fill
> > the
> > valid parm values - I do have the name of the stored procedure to run to
> > retrieve the valid values for the parm, but can't seem to figure out how
> > to
> > either a)get the name of the sp to run, or b)just do a databind some how
> > to
> > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > down list, etc.)
> >
> > Any ideas - ?
> >
> >
> > Thanks
> >
> >
> >
> >
>
>|||OK - now I'm at work so here are some code snippets that may help (in C#):
They are used to build drop down lists in a web page with the parameter
values. This way RS takes care of the security for the parameter data. To
find out the names of the stored procs you need to get the DataSetDefinition
object and query it. I haven't yet worked out how to get it back though...
sorry. I think i tried this first and gave up, so used the parameters
collection to build the lookup data.
ReportingService rs=new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
bool forRendering = true;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(ReportPath, historyID, forRendering,
values, credentials);
if (parameters != null)
{
foreach (ReportParameter rp in parameters)
if((rp.ValidValues!=null)||(rp.ValidValuesQueryBased))
{
DropDownList lst=new DropDownList();
foreach(ValidValue vv in rp.ValidValues)
{
ListItem item=new ListItem();
item.Text=vv.Label;
item.Value=vv.Value;
lst.Items.Add(item);
}
"Myles" wrote:
> Thank you for the reply Mary - I may need a little help yet,
> I went back and read my question and I was so involved I think I mis-stated
> my question! Do the web services expose the names of the stored procedures
> used to fill the valid values for the parameters - from your response it
> sounds as if you just use the GetReportParameters method and that returns
> everything (all of the datasets for all of the parameters) - if that's the
> case how do you separate or sort through it all...HELP!
> It was a very long weekend - and I still didn't get anything done!
>
> Thanks,
>
> "Mary Bray [MVP]" wrote:
> > I have done exactly this, but the code is at work and it is a (very) long
> > weekend.
> > You can certainly use the web services to retrieve everything - the trick to
> > getting the values you have set up from datasets that populate the possible
> > values etc is to that when you run the GetReportParameters method, make sure
> > you set the ForRendering option to true - then everything you need comes
> > back.
> >
> > Have a go and I'll post my code on Tuesday
> > --
> >
> > Mary Bray [SQL Server MVP]
> > Please reply only to newsgroups
> >
> > "Myles" <Myles@.discussions.microsoft.com> wrote in message
> > news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > > greetings -
> > >
> > > I am trying to set up a custom parameters page in an ASP .NET
> > > application -
> > > I am in the process of choosing between keeping the values (information I
> > > need to list the parameters for the reports, as well as the available,
> > > valid
> > > and default values for any given report) in a database or access all I
> > > need
> > > through reporting services (web service).
> > >
> > > Does anyone have any advice for setting up a Web App to run rs reports -
> > > can
> > > you get all you need from the rs web services to create a custom parameter
> > > page, or is it better to retrieve the values you need from a database?
> > >
> > > I can dynamically build the custom parm page using values from a database
> > > (dynamically list the parms for the report, fire off the sp to return
> > > valid
> > > values for the parm and set the default value) - I would like to get this
> > > going using the web services but am stuck at the point of trying to fill
> > > the
> > > valid parm values - I do have the name of the stored procedure to run to
> > > retrieve the valid values for the parm, but can't seem to figure out how
> > > to
> > > either a)get the name of the sp to run, or b)just do a databind some how
> > > to
> > > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > > down list, etc.)
> > >
> > > Any ideas - ?
> > >
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> >
> >
> >|||beautiful - thank you very much Mary - this will be a tremendous help. If I
figure out how to actually get the sp names I will post back ~
Hope you had a great weekend!
Thanks,
Pete
"Mary Bray [SQL Server MVP]" wrote:
> OK - now I'm at work so here are some code snippets that may help (in C#):
> They are used to build drop down lists in a web page with the parameter
> values. This way RS takes care of the security for the parameter data. To
> find out the names of the stored procs you need to get the DataSetDefinition
> object and query it. I haven't yet worked out how to get it back though...
> sorry. I think i tried this first and gave up, so used the parameters
> collection to build the lookup data.
> ReportingService rs=new ReportingService();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> bool forRendering = true;
> string historyID = null;
> ParameterValue[] values = null;
> DataSourceCredentials[] credentials = null;
> ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(ReportPath, historyID, forRendering,
> values, credentials);
> if (parameters != null)
> {
> foreach (ReportParameter rp in parameters)
> if((rp.ValidValues!=null)||(rp.ValidValuesQueryBased))
> {
> DropDownList lst=new DropDownList();
> foreach(ValidValue vv in rp.ValidValues)
> {
> ListItem item=new ListItem();
> item.Text=vv.Label;
> item.Value=vv.Value;
> lst.Items.Add(item);
> }
>
>
> "Myles" wrote:
> > Thank you for the reply Mary - I may need a little help yet,
> >
> > I went back and read my question and I was so involved I think I mis-stated
> > my question! Do the web services expose the names of the stored procedures
> > used to fill the valid values for the parameters - from your response it
> > sounds as if you just use the GetReportParameters method and that returns
> > everything (all of the datasets for all of the parameters) - if that's the
> > case how do you separate or sort through it all...HELP!
> >
> > It was a very long weekend - and I still didn't get anything done!
> >
> >
> > Thanks,
> >
> >
> >
> > "Mary Bray [MVP]" wrote:
> >
> > > I have done exactly this, but the code is at work and it is a (very) long
> > > weekend.
> > > You can certainly use the web services to retrieve everything - the trick to
> > > getting the values you have set up from datasets that populate the possible
> > > values etc is to that when you run the GetReportParameters method, make sure
> > > you set the ForRendering option to true - then everything you need comes
> > > back.
> > >
> > > Have a go and I'll post my code on Tuesday
> > > --
> > >
> > > Mary Bray [SQL Server MVP]
> > > Please reply only to newsgroups
> > >
> > > "Myles" <Myles@.discussions.microsoft.com> wrote in message
> > > news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > > > greetings -
> > > >
> > > > I am trying to set up a custom parameters page in an ASP .NET
> > > > application -
> > > > I am in the process of choosing between keeping the values (information I
> > > > need to list the parameters for the reports, as well as the available,
> > > > valid
> > > > and default values for any given report) in a database or access all I
> > > > need
> > > > through reporting services (web service).
> > > >
> > > > Does anyone have any advice for setting up a Web App to run rs reports -
> > > > can
> > > > you get all you need from the rs web services to create a custom parameter
> > > > page, or is it better to retrieve the values you need from a database?
> > > >
> > > > I can dynamically build the custom parm page using values from a database
> > > > (dynamically list the parms for the report, fire off the sp to return
> > > > valid
> > > > values for the parm and set the default value) - I would like to get this
> > > > going using the web services but am stuck at the point of trying to fill
> > > > the
> > > > valid parm values - I do have the name of the stored procedure to run to
> > > > retrieve the valid values for the parm, but can't seem to figure out how
> > > > to
> > > > either a)get the name of the sp to run, or b)just do a databind some how
> > > > to
> > > > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > > > down list, etc.)
> > > >
> > > > Any ideas - ?
> > > >
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
Showing posts with label asp. Show all posts
Showing posts with label asp. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Paramaters
Hi, we run an ASP.NET portal that has fairly extensive reporting needs (it's
a government portal...) We're thinking of switching to reporting services
from a custom xml/xsl solution. My questions are:
1. interfacing with RS from our existing .net web app. We have screens set
up to let the user do things like select paramaters and stuff. Can this be
integrated? Currently for things that require multiple selections (for
instance, selecting a set of cities to report on), we build a string of city
ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which uses
the string to select info on each city (using IN(1,3,45,60,61)). Is this
possible in RS, and can we use our existing frontend? If we use our existing
frontend, can we still have access to the toolbar, for things like exporting
to different formats?
2. I know dynamic fields aren't possible. But can fields be left out /
included based on the presence of data to fill it?
3. Is it possible to use XML as a datasource to a report?
If anyone has any insight into these, I'd really appreciate it. Thanks in
advance
Peter LRS has two ways to integrate an existing app. You can either use web
services or you can use URL integration. To be able to have the toolbar you
need to use the URL integration. What you want to do with #1 is very easy to
do using URL integration. For question number 2, whether something is
visible can be set with an expression. The expression can decide not to show
it based on whatever you choose. #3 is possible but a little more difficult.
It is possible to create your own data extension. I suggest first seeing if
you really need to do this or if you can just use the stored procedures
and/or queries instead.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:CF96A1DF-84CA-4136-BCF4-9AF3BBC345CF@.microsoft.com...
> Hi, we run an ASP.NET portal that has fairly extensive reporting needs
> (it's
> a government portal...) We're thinking of switching to reporting services
> from a custom xml/xsl solution. My questions are:
> 1. interfacing with RS from our existing .net web app. We have screens set
> up to let the user do things like select paramaters and stuff. Can this be
> integrated? Currently for things that require multiple selections (for
> instance, selecting a set of cities to report on), we build a string of
> city
> ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which
> uses
> the string to select info on each city (using IN(1,3,45,60,61)). Is this
> possible in RS, and can we use our existing frontend? If we use our
> existing
> frontend, can we still have access to the toolbar, for things like
> exporting
> to different formats?
> 2. I know dynamic fields aren't possible. But can fields be left out /
> included based on the presence of data to fill it?
> 3. Is it possible to use XML as a datasource to a report?
>
> If anyone has any insight into these, I'd really appreciate it. Thanks in
> advance
> Peter L
a government portal...) We're thinking of switching to reporting services
from a custom xml/xsl solution. My questions are:
1. interfacing with RS from our existing .net web app. We have screens set
up to let the user do things like select paramaters and stuff. Can this be
integrated? Currently for things that require multiple selections (for
instance, selecting a set of cities to report on), we build a string of city
ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which uses
the string to select info on each city (using IN(1,3,45,60,61)). Is this
possible in RS, and can we use our existing frontend? If we use our existing
frontend, can we still have access to the toolbar, for things like exporting
to different formats?
2. I know dynamic fields aren't possible. But can fields be left out /
included based on the presence of data to fill it?
3. Is it possible to use XML as a datasource to a report?
If anyone has any insight into these, I'd really appreciate it. Thanks in
advance
Peter LRS has two ways to integrate an existing app. You can either use web
services or you can use URL integration. To be able to have the toolbar you
need to use the URL integration. What you want to do with #1 is very easy to
do using URL integration. For question number 2, whether something is
visible can be set with an expression. The expression can decide not to show
it based on whatever you choose. #3 is possible but a little more difficult.
It is possible to create your own data extension. I suggest first seeing if
you really need to do this or if you can just use the stored procedures
and/or queries instead.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:CF96A1DF-84CA-4136-BCF4-9AF3BBC345CF@.microsoft.com...
> Hi, we run an ASP.NET portal that has fairly extensive reporting needs
> (it's
> a government portal...) We're thinking of switching to reporting services
> from a custom xml/xsl solution. My questions are:
> 1. interfacing with RS from our existing .net web app. We have screens set
> up to let the user do things like select paramaters and stuff. Can this be
> integrated? Currently for things that require multiple selections (for
> instance, selecting a set of cities to report on), we build a string of
> city
> ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which
> uses
> the string to select info on each city (using IN(1,3,45,60,61)). Is this
> possible in RS, and can we use our existing frontend? If we use our
> existing
> frontend, can we still have access to the toolbar, for things like
> exporting
> to different formats?
> 2. I know dynamic fields aren't possible. But can fields be left out /
> included based on the presence of data to fill it?
> 3. Is it possible to use XML as a datasource to a report?
>
> If anyone has any insight into these, I'd really appreciate it. Thanks in
> advance
> Peter L
Monday, March 12, 2012
Paging of Large Results Using Server Cursors
I'm writing an ASP.NET application that uses a SQL Server 2000 database. The application searches in large tables with 500, 000+ Records and then displays the search results, the search results could be easily 20,000 or 30,000 results. Ofcourse i need to use paging to show like 10 or 20 results per page. Unfortunetly ADO.NET doesn't support the paging functions that were found in ADO (like PageSize or AbsolutePosition) so i have to implement the paging myself.
I've read many articles that talk about how paging could be implemented. Most of them suggest doing the paging through SQL Server using Server Cursors. I know that cursors are resource intensive and should be avoided whenever possible but it seems that this is the only solution that fits. I just want you to notice that the cursor will just loop through 20 or 30 entries no more (Page Size) So is this a problem?
I will be using code that looks similar to this:
--
DECLARE @.PK /* PK Type */
DECLARE @.tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @.PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
BEGIN
INSERT @.tblPK(PK) VALUES(@.PK)
FETCH NEXT FROM PagingCursor INTO @.PK
SET @.PageSize = @.PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
Another method was suggested also that uses RowCount but it doesn't work for some technical reasons discussed in the article above.
So what do you think should i move on or what?
Regards,
Mohamed Salah
Please take a look at Aaron's article on this:http://aspfaq.com/show.asp?id=2120
Paging of Large Results Using Server Cursors
I'm writing an ASP.NET application that uses a SQL Server 2000 database. The
application searches in large tables with 500, 000+ Records and then display
s
the search results, the search results could be easily 20,000 or 30,000
results. Ofcourse i need to use paging to show like 10 or 20 results per
page. Unfortunetly ADO.NET doesn't support the paging functions that were
found in ADO (like PageSize or AbsolutePosition) so i have to implement the
paging myself.
I've read many articles that talk about how paging could be implemented.
Most of them suggest doing the paging through SQL Server using Server
Cursors. I know that cursors are resource intensive and should be avoided
whenever possible but it seems that this is the only solution that fits. I
just want you to notice that the cursor will just loop through 20 or 30
entries no more (Page Size) So is this a problem?
I will be using code that looks similar to this:
---
DECLARE @.PK /* PK Type */
DECLARE @.tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @.PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
BEGIN
INSERT @.tblPK(PK) VALUES(@.PK)
FETCH NEXT FROM PagingCursor INTO @.PK
SET @.PageSize = @.PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
----
I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
Another method was suggested also that uses RowCount but it doesn't work for
some technical reasons discussed in the article above.
So what do you think should i move on or what?
Regards,
Mohamed SalahLet's assume you are using a stored procedure call to page through a
Customer table and sorting by LastName. All you need to do is maintain in
session state the last offset value of LastName and CustomerID. This should
be fast resource efficeint. For example:
select top 20
LastName,
FirstName,
PhoneNumber
from
Customer
where
LastName > @.PrevLastName and
CustomerID > @.PrevCustomerID
order by
LastName,
CustomerID
"Mohamed Salah" <MohamedSalah@.discussions.microsoft.com> wrote in message
news:1D026C27-4BA2-43F8-B1CF-053D10B127D5@.microsoft.com...
> I'm writing an ASP.NET application that uses a SQL Server 2000 database.
> The
> application searches in large tables with 500, 000+ Records and then
> displays
> the search results, the search results could be easily 20,000 or 30,000
> results. Ofcourse i need to use paging to show like 10 or 20 results per
> page. Unfortunetly ADO.NET doesn't support the paging functions that were
> found in ADO (like PageSize or AbsolutePosition) so i have to implement
> the
> paging myself.
> I've read many articles that talk about how paging could be implemented.
> Most of them suggest doing the paging through SQL Server using Server
> Cursors. I know that cursors are resource intensive and should be avoided
> whenever possible but it seems that this is the only solution that fits. I
> just want you to notice that the cursor will just loop through 20 or 30
> entries no more (Page Size) So is this a problem?
> I will be using code that looks similar to this:
> ---
> DECLARE @.PK /* PK Type */
> DECLARE @.tblPK TABLE (
> PK /* PK Type */ NOT NULL PRIMARY KEY
> )
> DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
> SELECT @.PK FROM Table ORDER BY SortColumn
> OPEN PagingCursor
> FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
> WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
> BEGIN
> INSERT @.tblPK(PK) VALUES(@.PK)
> FETCH NEXT FROM PagingCursor INTO @.PK
> SET @.PageSize = @.PageSize - 1
> END
> CLOSE PagingCursor
> DEALLOCATE PagingCursor
> SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
> ORDER BY SortColumn
> ----
> I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
> Another method was suggested also that uses RowCount but it doesn't work
> for
> some technical reasons discussed in the article above.
> So what do you think should i move on or what?
> Regards,
> Mohamed Salah
>
application searches in large tables with 500, 000+ Records and then display
s
the search results, the search results could be easily 20,000 or 30,000
results. Ofcourse i need to use paging to show like 10 or 20 results per
page. Unfortunetly ADO.NET doesn't support the paging functions that were
found in ADO (like PageSize or AbsolutePosition) so i have to implement the
paging myself.
I've read many articles that talk about how paging could be implemented.
Most of them suggest doing the paging through SQL Server using Server
Cursors. I know that cursors are resource intensive and should be avoided
whenever possible but it seems that this is the only solution that fits. I
just want you to notice that the cursor will just loop through 20 or 30
entries no more (Page Size) So is this a problem?
I will be using code that looks similar to this:
---
DECLARE @.PK /* PK Type */
DECLARE @.tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @.PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
BEGIN
INSERT @.tblPK(PK) VALUES(@.PK)
FETCH NEXT FROM PagingCursor INTO @.PK
SET @.PageSize = @.PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
----
I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
Another method was suggested also that uses RowCount but it doesn't work for
some technical reasons discussed in the article above.
So what do you think should i move on or what?
Regards,
Mohamed SalahLet's assume you are using a stored procedure call to page through a
Customer table and sorting by LastName. All you need to do is maintain in
session state the last offset value of LastName and CustomerID. This should
be fast resource efficeint. For example:
select top 20
LastName,
FirstName,
PhoneNumber
from
Customer
where
LastName > @.PrevLastName and
CustomerID > @.PrevCustomerID
order by
LastName,
CustomerID
"Mohamed Salah" <MohamedSalah@.discussions.microsoft.com> wrote in message
news:1D026C27-4BA2-43F8-B1CF-053D10B127D5@.microsoft.com...
> I'm writing an ASP.NET application that uses a SQL Server 2000 database.
> The
> application searches in large tables with 500, 000+ Records and then
> displays
> the search results, the search results could be easily 20,000 or 30,000
> results. Ofcourse i need to use paging to show like 10 or 20 results per
> page. Unfortunetly ADO.NET doesn't support the paging functions that were
> found in ADO (like PageSize or AbsolutePosition) so i have to implement
> the
> paging myself.
> I've read many articles that talk about how paging could be implemented.
> Most of them suggest doing the paging through SQL Server using Server
> Cursors. I know that cursors are resource intensive and should be avoided
> whenever possible but it seems that this is the only solution that fits. I
> just want you to notice that the cursor will just loop through 20 or 30
> entries no more (Page Size) So is this a problem?
> I will be using code that looks similar to this:
> ---
> DECLARE @.PK /* PK Type */
> DECLARE @.tblPK TABLE (
> PK /* PK Type */ NOT NULL PRIMARY KEY
> )
> DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
> SELECT @.PK FROM Table ORDER BY SortColumn
> OPEN PagingCursor
> FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
> WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
> BEGIN
> INSERT @.tblPK(PK) VALUES(@.PK)
> FETCH NEXT FROM PagingCursor INTO @.PK
> SET @.PageSize = @.PageSize - 1
> END
> CLOSE PagingCursor
> DEALLOCATE PagingCursor
> SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
> ORDER BY SortColumn
> ----
> I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
> Another method was suggested also that uses RowCount but it doesn't work
> for
> some technical reasons discussed in the article above.
> So what do you think should i move on or what?
> Regards,
> Mohamed Salah
>
Paging of Large Results Using Server Cursors
I'm writing an ASP.NET application that uses a SQL Server 2000 database. The application searches in large tables with 500, 000+ Records and then displays the search results, the search results could be easily 20,000 or 30,000 results. Ofcourse i need to use paging to show like 10 or 20 results per page. Unfortunetly ADO.NET doesn't support the paging functions that were found in ADO (like PageSize or AbsolutePosition) so i have to implement the paging myself.
I've read many articles that talk about how paging could be implemented. Most of them suggest doing the paging through SQL Server using Server Cursors. I know that cursors are resource intensive and should be avoided whenever possible but it seems that this is the only solution that fits. I just want you to notice that the cursor will just loop through 20 or 30 entries no more (Page Size) So is this a problem?
I will be using code that looks similar to this:
--
DECLARE @.PK /* PK Type */
DECLARE @.tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @.PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
BEGIN
INSERT @.tblPK(PK) VALUES(@.PK)
FETCH NEXT FROM PagingCursor INTO @.PK
SET @.PageSize = @.PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
Another method was suggested also that uses RowCount but it doesn't work for some technical reasons discussed in the article above.
So what do you think should i move on or what?
Regards,
Mohamed Salah
Please take a look at Aaron's article on this:http://aspfaq.com/show.asp?id=2120
Friday, March 9, 2012
Paging and printing without the toolbar
Is there a way to issue the report print command or paging command from code.
I would like to hide the toolbar and use another button in our ASP.NET
application to kick off printing or tell the reportviewer to go to another
page.
ThanksPerhaps you could use the findstring to search for the literal in a report
ie
http://server/Reportserver?/SampleReports/Product
Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
Search for URL Access in Books on line and you will get all of the URL
parameters, etc... There is also code which shows you how to put and IE
browswer instance in a windows form, and put a report into it...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"NeedToKnow" wrote:
> Is there a way to issue the report print command or paging command from code.
> I would like to hide the toolbar and use another button in our ASP.NET
> application to kick off printing or tell the reportviewer to go to another
> page.
> Thanks|||I do not understand. I have looked through URL Access and why would I want to
find a literal. I want to send the command to reportviewer through a URL
that tells the report viewer to print the current report to the printer.
"Wayne Snyder" wrote:
> Perhaps you could use the findstring to search for the literal in a report
> ie
> http://server/Reportserver?/SampleReports/Product
> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>
> Search for URL Access in Books on line and you will get all of the URL
> parameters, etc... There is also code which shows you how to put and IE
> browswer instance in a windows form, and put a report into it...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "NeedToKnow" wrote:
> > Is there a way to issue the report print command or paging command from code.
> > I would like to hide the toolbar and use another button in our ASP.NET
> > application to kick off printing or tell the reportviewer to go to another
> > page.
> >
> > Thanks|||You asked two question. He answered one of them (the one about going to
another page).
As far as printing, I don't think what you want is possible with the report
viewer control. Are you using the one from VS 2005?
I wanted to do something similar with the winform report viewer control but
found out that until it is rendered I can't request for it to print. So for
Windows I am setting a property to print and then responding to the
rendering event and if their is a request to print, I print and then close
the form. However, I am not familiar with the webform control from VS 2005.
If you are doing this in RS 2000 then I have even less to offer as a
suggestion.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
>I do not understand. I have looked through URL Access and why would I want
>to
> find a literal. I want to send the command to reportviewer through a URL
> that tells the report viewer to print the current report to the printer.
> "Wayne Snyder" wrote:
>> Perhaps you could use the findstring to search for the literal in a
>> report
>> ie
>> http://server/Reportserver?/SampleReports/Product
>> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>>
>> Search for URL Access in Books on line and you will get all of the URL
>> parameters, etc... There is also code which shows you how to put and IE
>> browswer instance in a windows form, and put a report into it...
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "NeedToKnow" wrote:
>> > Is there a way to issue the report print command or paging command from
>> > code.
>> > I would like to hide the toolbar and use another button in our ASP.NET
>> > application to kick off printing or tell the reportviewer to go to
>> > another
>> > page.
>> >
>> > Thanks|||It is vs 2005.
I don't want to print the form, since my report is several pages long. Is
there a way to do this?
"Bruce L-C [MVP]" wrote:
> You asked two question. He answered one of them (the one about going to
> another page).
> As far as printing, I don't think what you want is possible with the report
> viewer control. Are you using the one from VS 2005?
> I wanted to do something similar with the winform report viewer control but
> found out that until it is rendered I can't request for it to print. So for
> Windows I am setting a property to print and then responding to the
> rendering event and if their is a request to print, I print and then close
> the form. However, I am not familiar with the webform control from VS 2005.
> If you are doing this in RS 2000 then I have even less to offer as a
> suggestion.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
> news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
> >I do not understand. I have looked through URL Access and why would I want
> >to
> > find a literal. I want to send the command to reportviewer through a URL
> > that tells the report viewer to print the current report to the printer.
> >
> > "Wayne Snyder" wrote:
> >
> >> Perhaps you could use the findstring to search for the literal in a
> >> report
> >>
> >> ie
> >> http://server/Reportserver?/SampleReports/Product
> >> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
> >>
> >>
> >> Search for URL Access in Books on line and you will get all of the URL
> >> parameters, etc... There is also code which shows you how to put and IE
> >> browswer instance in a windows form, and put a report into it...
> >> --
> >> Wayne Snyder MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >>
> >> I support the Professional Association for SQL Server ( PASS) and it''s
> >> community of SQL Professionals.
> >>
> >>
> >> "NeedToKnow" wrote:
> >>
> >> > Is there a way to issue the report print command or paging command from
> >> > code.
> >> > I would like to hide the toolbar and use another button in our ASP.NET
> >> > application to kick off printing or tell the reportviewer to go to
> >> > another
> >> > page.
> >> >
> >> > Thanks
>
>
I would like to hide the toolbar and use another button in our ASP.NET
application to kick off printing or tell the reportviewer to go to another
page.
ThanksPerhaps you could use the findstring to search for the literal in a report
ie
http://server/Reportserver?/SampleReports/Product
Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
Search for URL Access in Books on line and you will get all of the URL
parameters, etc... There is also code which shows you how to put and IE
browswer instance in a windows form, and put a report into it...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"NeedToKnow" wrote:
> Is there a way to issue the report print command or paging command from code.
> I would like to hide the toolbar and use another button in our ASP.NET
> application to kick off printing or tell the reportviewer to go to another
> page.
> Thanks|||I do not understand. I have looked through URL Access and why would I want to
find a literal. I want to send the command to reportviewer through a URL
that tells the report viewer to print the current report to the printer.
"Wayne Snyder" wrote:
> Perhaps you could use the findstring to search for the literal in a report
> ie
> http://server/Reportserver?/SampleReports/Product
> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>
> Search for URL Access in Books on line and you will get all of the URL
> parameters, etc... There is also code which shows you how to put and IE
> browswer instance in a windows form, and put a report into it...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "NeedToKnow" wrote:
> > Is there a way to issue the report print command or paging command from code.
> > I would like to hide the toolbar and use another button in our ASP.NET
> > application to kick off printing or tell the reportviewer to go to another
> > page.
> >
> > Thanks|||You asked two question. He answered one of them (the one about going to
another page).
As far as printing, I don't think what you want is possible with the report
viewer control. Are you using the one from VS 2005?
I wanted to do something similar with the winform report viewer control but
found out that until it is rendered I can't request for it to print. So for
Windows I am setting a property to print and then responding to the
rendering event and if their is a request to print, I print and then close
the form. However, I am not familiar with the webform control from VS 2005.
If you are doing this in RS 2000 then I have even less to offer as a
suggestion.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
>I do not understand. I have looked through URL Access and why would I want
>to
> find a literal. I want to send the command to reportviewer through a URL
> that tells the report viewer to print the current report to the printer.
> "Wayne Snyder" wrote:
>> Perhaps you could use the findstring to search for the literal in a
>> report
>> ie
>> http://server/Reportserver?/SampleReports/Product
>> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>>
>> Search for URL Access in Books on line and you will get all of the URL
>> parameters, etc... There is also code which shows you how to put and IE
>> browswer instance in a windows form, and put a report into it...
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "NeedToKnow" wrote:
>> > Is there a way to issue the report print command or paging command from
>> > code.
>> > I would like to hide the toolbar and use another button in our ASP.NET
>> > application to kick off printing or tell the reportviewer to go to
>> > another
>> > page.
>> >
>> > Thanks|||It is vs 2005.
I don't want to print the form, since my report is several pages long. Is
there a way to do this?
"Bruce L-C [MVP]" wrote:
> You asked two question. He answered one of them (the one about going to
> another page).
> As far as printing, I don't think what you want is possible with the report
> viewer control. Are you using the one from VS 2005?
> I wanted to do something similar with the winform report viewer control but
> found out that until it is rendered I can't request for it to print. So for
> Windows I am setting a property to print and then responding to the
> rendering event and if their is a request to print, I print and then close
> the form. However, I am not familiar with the webform control from VS 2005.
> If you are doing this in RS 2000 then I have even less to offer as a
> suggestion.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
> news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
> >I do not understand. I have looked through URL Access and why would I want
> >to
> > find a literal. I want to send the command to reportviewer through a URL
> > that tells the report viewer to print the current report to the printer.
> >
> > "Wayne Snyder" wrote:
> >
> >> Perhaps you could use the findstring to search for the literal in a
> >> report
> >>
> >> ie
> >> http://server/Reportserver?/SampleReports/Product
> >> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
> >>
> >>
> >> Search for URL Access in Books on line and you will get all of the URL
> >> parameters, etc... There is also code which shows you how to put and IE
> >> browswer instance in a windows form, and put a report into it...
> >> --
> >> Wayne Snyder MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >>
> >> I support the Professional Association for SQL Server ( PASS) and it''s
> >> community of SQL Professionals.
> >>
> >>
> >> "NeedToKnow" wrote:
> >>
> >> > Is there a way to issue the report print command or paging command from
> >> > code.
> >> > I would like to hide the toolbar and use another button in our ASP.NET
> >> > application to kick off printing or tell the reportviewer to go to
> >> > another
> >> > page.
> >> >
> >> > Thanks
>
>
Paging
Is there any way to implement a paging scheme such that only the required records are transferred from SQL Server to the asp.net app?
The only support I can find such as the DataAdaptor.Fill will bring all the records back from SQL Server and then create the page...
This obviously still takes time and memory based on the entire query, not the page size.
Any ideas?Hi,
You can use a temporary table via stored proc in SQL Server. Seehere andhere.
Another approach is to use SELECT TOP, seehere.
A.|||Thanks... Very useful...
Having a fully dynamic query built up in asp.net, without necessarily being from a single table and having unknown (in advance) columns, without always having a primary key I'm not sure that I will be able to adapt one of these without doing some major work on my system.
Subscribe to:
Posts (Atom)