Solved

I need a generic TableAdapter to dynamically access multiple reports

Posted on 2008-10-08
8
963 Views
Last Modified: 2013-11-07
I have several reports I want to choose from and display on a web page.  I have one working using the following code to the needed variables for a ReportViewer:
        Dim taDS As dsCampaignTableAdapters.CampaignTableAdapter
        taDS = New dsCampaignTableAdapters.CampaignTableAdapter
        Dim uData As dsCampaign = New dsCampaign()
        Dim rds As ReportDataSource = New ReportDataSource("dsCampaign_Campaign", uData.Campaign)
        taDS.Fill(uData.Campaign)
        Dim rptPath As String = "Reports\Campaign List.rdlc"
        Dim rptPar(3) As ReportParameter

What I want to do is make it generic so I can pass appropriate names and parameters retrieved from a database.  I have the following working except for the taDS.Fill function...can anyone tell me how to fill in the commands needed to get this to work?
        Dim taDS As SqlDataAdapter
        taDS = New SqlDataAdapter()
        Dim uData As DataSet = New DataSet("dsCampaign")
        Dim rds As ReportDataSource = New ReportDataSource("dsCampaign_Campaign", uData.Tables("Campaign"))
        taDS.Fill(uData.Tables("Campaign"))
        Dim rptPath As String = "Reports\Campaign List.rdlc"
        Dim rptPar(3) As ReportParameter
0
Comment
Question by:Dalexan
  • 3
  • 3
8 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22730036
Strong-typed and generic don't really play well together.  TableAdapters a strong-typed, so if you need a generic implementation, then I would think that you need something different than a TableAdapter.
0
 

Author Comment

by:Dalexan
ID: 22732503
Given the lack of solutions in my searches on the web, and the slow responses to queries, I am assuming this to be much harder than I originally thought...if no one else has figured this out yet, it should be worth more.  So, I have increased the points to the max here...they will be well-deserved for the one who solves this.

Well, yes, mixing generic and strong-typed is difficult, but doable.  I have come up with a solution that works but is not as concise as I would like.  It will involve a Select Case statement for each report (there are over 50 so far and may be up to 100).  I created a strong-typed DataSet for each and use the Case statements to set up a ReportViewer.

What I would like to do is find a way to put something in a data base which could be used to find/identify/create the necessary pieces...taDS, uData and rds in the attached code snippet...even, perhaps, by storing the name of each in the DB and using that string value to create the assignment; or, if SQLServer allows it, somehow store the DataSets and TableAdapters themselves in the DB (not the preferred way to go).

Anyway, what I am still hoping for is a way to use a DB lookup rather than a long SELECT statement...any thoughts or suggestions would be greatly appreciated.
    Private Sub ShowReport(ByVal idx As Integer, ByVal sdate As Date, ByVal edate As Date, ByVal param As String)

        Dim taDS As Object

        Dim uData As DataSet

        Dim rds As ReportDataSource = New ReportDataSource()

        Dim rptPath As String = "Reports\"

        Dim rptPar() As ReportParameter

        Dim bPar As Boolean = False
 

        ReDim Preserve rptPar(1)

        rptPar(0) = New ReportParameter("dtmFrom", sdate.ToShortDateString())

        rptPar(1) = New ReportParameter("dtmTo", edate.ToShortDateString())
 

        Select Case idx

            Case 0

                taDS = New dsCampaignTableAdapters.CampaignTableAdapter()

                uData = New dsCampaign()

                rds = New ReportDataSource("dsCampaign_Campaign", uData.Tables(0))

                taDS.Fill(uData.Tables(0))

                rptPath += "Campaign_List.rdlc"

            Case 1

                taDS = New dsAgentQ_ProductTableAdapters.AgentQ_ProductTableAdapter()

                uData = New dsAgentQ_Product()

                rds = New ReportDataSource("dsAgentQ_Product_AgentQ_Product", uData.Tables(0))

                taDS.Fill(uData.Tables(0), sdate, edate, Val(param))

                rptPath += "AgentQ_Product.rdlc"

                ReDim Preserve rptPar(2)

                rptPar(2) = New ReportParameter("OrdRepNumber", param)

                bPar = True

        End Select
 

        Try

            rv1.Reset()

            rv1.LocalReport.DataSources.Clear()

            rv1.LocalReport.DataSources.Add(rds)

            rv1.LocalReport.ReportPath = rptPath

            If bPar Then rv1.LocalReport.SetParameters(rptPar)

            rv1.LocalReport.Refresh()

            rv1.Visible = True

        Catch ex As Exception

            '("There was an error generating the report:\r\n{0}", ex.ToString())

        End Try

    End Sub

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22732677
If you think that you need to stick to this concept, you could use Activator.CreateType with a type name, and use the Invoke method on the instance created to fill the DataSet table.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:Dalexan
ID: 22732758
You say "If you think that you need to stick to this concept..." as if you have a better one in mind.  Perhaps you would share your thoughts on a different approach?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 22732904
I would use single SqlConnection, SqlCommand, and SqlDataReader instances, with different stored procedure names and parameters that you can use to build a DataTable instance that you can bind to the report.  You could use an XML configuration file that defines the stored procedure name, parameters, and reporting binding information.  Then, you wouldn't need a huge Select Case statement.
0
 

Author Closing Comment

by:Dalexan
ID: 31504546
While this question is really unanswered "TheLearnedOne" probably has the best approach.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now