Dalexan
asked on
I need a generic TableAdapter to dynamically access multiple reports
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.Ca mpaignTabl eAdapter
taDS = New dsCampaignTableAdapters.Ca mpaignTabl eAdapter
Dim uData As dsCampaign = New dsCampaign()
Dim rds As ReportDataSource = New ReportDataSource("dsCampai gn_Campaig n", 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("dsCampai gn_Campaig n", uData.Tables("Campaign"))
taDS.Fill(uData.Tables("Ca mpaign"))
Dim rptPath As String = "Reports\Campaign List.rdlc"
Dim rptPar(3) As ReportParameter
Dim taDS As dsCampaignTableAdapters.Ca
taDS = New dsCampaignTableAdapters.Ca
Dim uData As dsCampaign = New dsCampaign()
Dim rds As ReportDataSource = New ReportDataSource("dsCampai
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("dsCampai
taDS.Fill(uData.Tables("Ca
Dim rptPath As String = "Reports\Campaign List.rdlc"
Dim rptPar(3) As ReportParameter
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.
ASKER
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.
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
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
While this question is really unanswered "TheLearnedOne" probably has the best approach.