Creating a new filtered dataSet (new tableadapter) from an exisitng tableAdapter in the XSD

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dbuchanan - 11 Jul 2008 06:44 GMT

This is in a web environment and the data that is to be used in a
ReportViewer report in local mode.

What I am asking is how I can filter the result-set returned from a
tableAdapter into a new tableAdapter that I can then use as the
OjectDataSource for a ReportViewer report?

I want to do this because the original query may take a long time to
generate and subsequent data is simply filtered views of the original data
that is returned.

If my concept is not feasible but there are alternative ways to achieve this
please describe thta approach.

Thanks,

Doug

Reply to this Message

Charles Wang [MSFT] - 11 Jul 2008 11:12 GMT

Hi Doug,
Regarding this requirement, I think that I had replied you under your other
post "Question about navigation for click through report". You can use
DataTable.Select method to filter out the rows and then merge the rows into
a new dataset and then you can set the dataset to a new ReportDataSource
and then add the datasource to your local report. You may refer to some
code snippets like the following:
ReportDataSource rds = new ReportDataSource();
DataSet1 ds = new DataSet1();
ds.Merge(retDataSet.Table.Select("xxx"));
rds.Name = "The dataset name in your report";
rds.Value = ds.Tables[0];
this.ReportViewer1.LocalReport.DataSources.Add(rds);
this.ReportViewer1.LocalReport.Refresh();

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

dbuchanan - 14 Jul 2008 04:52 GMT

Charles,

I have wired Report

Charles,

I have wired reportviewers to SqlDataSources and wired to ObjectDataSources
when using an XSD. This however is unfamiliar to me so I ask for a little
more assistance.

You said

> You may refer to some code snippets like the following:
> ReportDataSource rds = new ReportDataSource();
[quoted text clipped - 4 lines]
> this.ReportViewer1.LocalReport.DataSources.Add(rds);
> this.ReportViewer1.LocalReport.Refresh();

Here is my attempt at the code:

========================

protected void btn_Fill_TempReportData_Click(object sender, EventArgs e)

{

// move to the view with the ReportViewer

setView(Views.MyView10);

// instantiate a report data source

ReportDataSource rptds = new ReportDataSource();

// Create a new data set to contain filtered data

DataSet ds = new DataSet();

// This is the XSD code under the App_Code where the tableAdapter
that is configured for the stored procedure resides

DataSet1 ds1 = new DataSet1();

// How do I fill this? The XSD table is configure with a 'Fill' and
a 'GetData' ??

//ds1.TempReportData.Load();

//ds1.TempReportData.GetData();

ds1.TempReportData.Fill();

// Filter expression

string expression = "Lv = 1";

//// Merge the data

ds.Merge(ds1.TempReportData.Select(expression));

// Name the dataset ~ is this optional?? What do you mean by "The
dataset name in your report".

// RDLC has no associated dataset. Do you mean DataSource

// Do You mean the ReportViewer?

// The ReportViewer cannot be associated with a SqlDataSource or
ObjectDataSource because this code

// must give it the DataSouce / DataSet n't ??? Please clarify

rptds.Name = "ReportData"; //??

// Transfer the data to the ReportDataSource

rptds.Value = ds.Tables[0];

// Hand off the DataSoucre to the ReportViewer.

this.ReportViewer2.LocalReport.DataSources.Add(rptds);

this.ReportViewer2.LocalReport.Refresh();

}

When trying to build this I receive the following error:

"DataSet1.TempReportDataDataTable' does not contain a definition for 'Fill'"

How do I fill this table so I can merge from it? And will it still be
available later in the session so that the same user can filter from it
again.

Thank you

Doug

Reply to this Message

Charles Wang [MSFT] - 14 Jul 2008 11:04 GMT

Hi Doug,
Was ds1 full of your required data after the Fill method was called? As far
as I know, a dataset object itself does not provide such function. To fill
a dataset from your database, you need to use some method like
SqlDataAdapter.Fill(). An empty dataset object can directly merge a table,
so if you only had a SqlDataSource, you can directly get the DataView
object by using the following code:
DataView dv = sqlDatasource.Select(DataSourceSelectArguments.Empty) as
DataView;
DataSet1 ds = new DataSet1();
if (dv != null && dv.Count > 0)
{
ds.TempReportData.Merge(dv.Table);
rptds.Name = "DataSet1_TempReportData"; //check your dataset name within
your report
rptds.Value = ds.TempReportData;
this.reportViewer1.LocalReport.DataSources.Add(rptds);
this.reportViewer1.LocalReport.Refresh();
}

You said that you had got a full set of data, so I thought that there must
be an existing dataset in your application. If so, you can directly merge
part of your original dataset.

Regarding the dataset name, a RDLC must have a dataset, otherwise how did
you design your report? You can open your .rdlc file with XML Editor and
then click Ctrl+F to find "DataSet", and then you can find the dataset name
for your .RDLC. After that set it to rptds.Name.

Hope this helps. Please feel free to let me know if you have any other
questions or concerns. Have a nice day~!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

dbuchanan - 14 Jul 2008 13:19 GMT

Charles,

> Was ds1 full of your required data after the Fill method was called?

No it fails with error.

> As far
> as I know, a dataset object itself does not provide such function.

Note that I am trying to run the fill agains the tableAdapter not the
dataSet
ds1.TempReportData.Fill();

In Windows Forms a fill looks like this:
this.taPhase.Fill(this.dsHipAdmin.Phase);
But then in windows Forms there is a componet tray where a tableAdapter is
put which is referencing the TableAdapter in the XSD, ut there is no
corresponding functionality in web forms. Yes youcan open a Component
Designer for the page (aspx) and drag a DataSet and BindingSoucrce to it but
not a TableAdapter.

> To fill
> a dataset from your database, you need to use some method like
> SqlDataAdapter.Fill().

This is the same as my statement
ds1.TempReportData.Fill();
ds1 is where the table adapter is and
TempReportData is my table adapter
This is all contained in my XSD.

Am I missing something?

> An empty dataset object can directly merge a table,
> so if you only had a SqlDataSource, you can directly get the DataView
> object by using the following code:

I am very happy to use a SqlDataSource but I do not know who to fill it.
When I try the SqlDataSource1.Fill it is unrecognized by intelisense and not
contained within the intellisense selection.

> You said that you had got a full set of data,

I had not had to use a fill method. It is when page or the view where the
ReportViewer resides is made active that sometiow the SqlDataSource or the
ObjectDataSource on which it depends is automatically filled. However I
cannont find any specific code which performs that command.

> Regarding the dataset name, a RDLC must have a dataset, otherwise how did
> you design your report?

The report was designed winthin Reporting services and then imported into
the web project. RDL files contain a dataset, but RDLC files do not
(qualifier RDLC files which are made from RDL files do but that part of the
XML is not used.) RDLC files must have a data source assigned to them. The
dataSource is assigned to the RDLC file through the ReportViewer.

If you associate a SqlDataSource with the ReportViewer along with the RDLC
file then the SqlDataSource is configured with a connection string, a table
or a stored procedure with the parameters wired to the source of the
parameters. One does not usually have any direct access to that
SqlDataSource - it is dedicated to the ReportViewerer or what ever data
consumer one is useing.

If you associate an ObjectDataSource with the ReportViewer along with the
RDLC, it is the ObjectDataSource that references the TableAdapter inside the
XSD file.

I don't know of any other way to supply data to the Report than via the
ReportViewer. Why you assign a DataSource to a report thorugh code you are
still doing it thorugh the ReportViewer.

>You can open your .rdlc file with XML Editor and
> then click Ctrl+F to find "DataSet", and then you can find the dataset
> name
> for your .RDLC. After that set it to rptds.Name.

Yes you *may* find a dataset there - only if it began its lilfe as an RDL
file. But as an RDLC that DataSet is not used.

See - http://msdn2.microsoft.com/en-us/library/ms252109(VS.80).aspx about
how the RDLC DataSet is not used.

So it seems the two unanswered questions
1.) If I need a name for the ReportDataSource or if it matters what the name
is
2.) How to fill a SqlDataSource or How to fill a tableAdapter inside my XSD
DataSet.

Thanks,
Doug

Reply to this Message

dbuchanan - 15 Jul 2008 06:57 GMT

Charles,

I was speaking with someone today that told me that the data I am expecting
to filter will not be available between post backs. I said I thought it
could be stored locally. He said that a compete dataset for a large report
would be too much to transmitt and too much to store. He referred to
runat=server and said no it is stored on the server and if too many
individuals store it there it will be too much to store.

Please address the discussion of the dataset not being available after
postback. The web environment is relatively new to me as is the postback
concept..

Thanks,
Doug

Reply to this Message

Charles Wang [MSFT] - 15 Jul 2008 10:10 GMT

Hi Doug,
Yes, he is right. By default, if you directly use the dataset in your
webpage, after a post back, the original dataset will not be there, however
there are many ways can help you store your dataset in your web server,
such as global static variable, or using Session or Cache. In this case,
the data will be stored in your server memory until you explicitly clear
it. You can directly reference it even after many post backs :).
For example:
if(Session["MyReportDataSource"] ==null)
Session["MyReportDataSource"] = theDataSet;

To get the value from the session, you can use the code like the following:
DataSet ds = Session["MyReportDataSource"] as DataSet;

Please note that if you store a large dataset in your server, there would
be much memory consumed. Before you adapt the solution, ensure that you do
not encounter memory pressure.

Hope this helps. Please feel free to let me know if you have any other
questions or concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message