Filtering DataSets for Reporting Application

[Need2CSharp]

Hi All,

I'm working on reporting (web) application for my company that
displays order information. It has approximately 15 - 20 different
filter options so that various departments can extract data they need
to view. I'm using a normalized, relational MSSQL 2005 database,
which contains 37 tables.

I would prefer not to execute query after query against the database.
Instead, I want to select what I need once and store the data in a
DataSet, where each DataTable stores data from a single table in the
database. After I have all my data in memory, I want to apply any
filters that have been selected. If this is the wrong approach, stop
reading here and let me know! If it is the right approach, what is
the best way to get all this data into a DataSet? Do I really need to
specifiy a query for each table (whether I use an untyped sql
statement, or a strongly typed one)? Also, please note that I am not
applying any updates back to the database. I'm simply getting the
data and filtering it in order to bind the object to a GridView
control.

I will be creating a custom Filter class. After I have my DataSet, I
would like to iterate through a collection in my
Filter class and apply the appropriate filter to the DataSet. The
only way I have found to filter is by using the
RowFilter property of the DataView class. This is fine, but I can't
seem to figure out how to update my DataSet with the filtered results
from the DataView. How do I do that? And if the DataTable being
filtered in the DataView is related to another table in my DataSet,
will the related table be updated accordingly?

Please help! As you can see, I have many questions and I'm a bit
lost. Let me know if you need more detail.

Thanks!

 

[William Vaughn]

There are several approaches that come to mind. First, you can create a
temporary "ReportData" table that's unique to each report user (expensive)
or a common "rollup" table that's used by all report users. That's step one.
This can be done from the ASP application by executing a suitable query. No,
you won't be able to use #tempdb tables as those are connection-specific and
in an ASP application it does not make sense to hold on to the connection
between page invocations. So, once the "temporary" rollup table is created,
you can run your report against it to see current rolled up data.

Question: can you simply do this with a DataTable persisted in the
viewstate? Yes, I expect you could but it would also be expensive and slow
down the application (if it's very large at all).

Yes, this is a lot easier with a Windows Forms application as the data can
be cached locally and exposed with the ReportViewer control.

I'll be discussing these issues in my seminars in late November at the
DevTeach conference in Vancouver BC.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------