How to use multiple datasets in rdlc c# report

Question

Firstly I need to create reports that use a select from database through TextBox input. How can I get textbox value and generate a report based in this TextBox? And if I need to use multiple DataSets for fill some tables with informations in my report, how to do it? Note: I use a WPF to get TextBoxes values and Winforms to create reportViewer.

private void Report_Load(object sender, EventArgs e)
{

      DataSet dsr = new DataSet();
      _con = new SqlConnection(_strCon);
      _adp = new SqlDataAdapter("Select * from tbl_cad",_con);
      _adp.Fill(dsr,dsr.Tables[0].TableName);

      ReportDataSource rds = new ReportDataSource("tbl_cad",dsr.Tables[0]);
      this.reportViewer.LocalReport.DataSources.Clear();
      this.reportViewer.LocalReport.DataSources.Add(rds);
      this.reportViewer.LocalReport.Refresh();

      this.reportViewer.RefreshReport();
}

Solution 1

You might need to clarify multiple DataSets??? or Multiple tables that are WITHIN a single DataSet.

Using a SQL Data Adapter, you can run fill on a single DataTable instead of a DataSet, then add the table to a main dataset, and finally provide THAT dataset to the report.

Just as an example...

DataSet dsr = new DataSet();
_con = new SqlConnection(_strCon);

_adp = new SqlDataAdapter("Select * from tbl_cad",_con);
DataTable tbl1 = new DataTable();
tbl1.TableName = "TableNameForReport";
_adp.Fill( tbl1 );

_adp = new SqlDataAdapter("Select * from OtherTable",_con);
DataTable tbl2 = new DataTable();
tbl2.TableName = "AnotherTableNameForReport";
_adp.Fill( tbl2 );

dsr.Tables.Add( tbl1 );
dsr.Tables.Add( tbl2 );

Now, you can obviously change whatever queries you need to get data from the source database, but then put them all into a single DataSet and they should be available for your report to run through.

Solution 2

I'm using multiple dataSources for my reports. I embed a ReportViewer on a winform and add buttons/textBox/comboBox to pass the report parameters. Don't mind example is in VB.NET, but it's doing the exact same thing. I would pass the text box value as a parameter to your query after running sanity checks to ensure you're not trying to pass a NULL or invalid result(See below where I declare my ComboBox Value as an Integer and perform a conversion just to be sure).

Generally I load ComboBox's with database values, and have some dateTimePickers, and then a button to execute the report once parameters are selected.

Private Sub auditYearButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles auditYearButton.Click
Dim year As Integer = CInt(yearComboBox.Text)
weeklyReportViewer.Clear()
weeklyReportViewer.Reset()
weeklyReportViewer.LocalReport.DataSources.Clear()

Dim eYear As New Microsoft.Reporting.WinForms.ReportParameter("year", CStr(year))

Dim itm As New Microsoft.Reporting.WinForms.ReportDataSource
Dim itm2 As New Microsoft.Reporting.WinForms.ReportDataSource
Dim itm3 As New Microsoft.Reporting.WinForms.ReportDataSource
Dim ta As New DsFSCTableAdapters.v_ConvertTableAdapter
Dim tb As New DsFSCTableAdapters.AllocationTableAdapter
Dim tc As New DsFSCTableAdapters.v_ConvertCommercialTableAdapter


weeklyReportViewer.LocalReport.ReportEmbeddedResource = "MERP.AuditYearAllocationReport.rdlc"
Me.weeklyReportViewer.LocalReport.SetParameters(New Microsoft.Reporting.WinForms.ReportParameter() {eYear})

tb.FillByYear(DsFSC.Allocation, year)
itm2.Name = "DsFSC_Allocation"
itm2.Value = AllocationBindingSource


ta.Fill(DsFSC.v_Convert)
itm.Name = "DsFSC_v_Convert"
itm.Value = v_ConvertBindingSource

tc.Fill(DsFSC.v_ConvertCommercial)
itm3.Name = "DsFSC_v_ConvertCommercial"
itm3.Value = v_ConvertCommercialBindingSource

weeklyReportViewer.LocalReport.DataSources.Add(itm)
weeklyReportViewer.LocalReport.DataSources.Add(itm2)
weeklyReportViewer.LocalReport.DataSources.Add(itm3)
Me.weeklyReportViewer.RefreshReport()
End Sub

Let me know if you need this clarified.