Two or Multiple Table Data Adapters in ASP.NET Report

Question

User-698989805 posted

Hello friends! I am just trying to do a simple thing in a report. But seems like it isn't that simple. I like to add two or multiple table data adapter in a report (ReportViewer) something as follows: In a DataSet, two data adapters

Right now, I've one data set with one data table adapter in a report using the following code (Same as the image but only one data adapter):

 public DataTable GetSPResult()
 {
    int m = Convert.ToInt32(ddlStoreID.SelectedValue);
    int k = Convert.ToInt32(DropDownList2.SelectedValue);

    DataTable ResultsTable = new DataTable();

    var context = new DemoEntities();
    var con = context.Database.Connection;

    try
    {
       using (context)
       {
          con.Open();

          using (var cmd = con.CreateCommand())
          {
             cmd.CommandText = "MonthlyConsumption"; //Here is the SP - MonthlyConsumption
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.Add(new SqlParameter("@StoreID", SqlDbType.Int));
             cmd.Parameters["@StoreID"].Value = m;
             cmd.Parameters.Add(new SqlParameter("@Year", SqlDbType.Int));
             cmd.Parameters["@Year"].Value = k;

             using (var reader = cmd.ExecuteReader())
             {
                ResultsTable.Load(reader);
             }
          }
       }
    }

    catch (Exception ex)
    {
      throw ex;
    }

     finally
     {
        if (con != null)
        {
          con.Close();
        }
     }

    return ResultsTable;
}

protected void Button1_Click(object sender, EventArgs e)
{
  using (var context = new DemoEntities())
  {
     string h = Session["EmployeeID"].ToString();
     int j = Convert.ToInt32(h);

     var con = (from c in context.EmployeeDetails
                where c.EmployeeID == (j)
                select c).ToList();

     foreach (EmployeeDetails c in con)
     {
         if (c.StoreID == Convert.ToInt32(ddlStoreID.SelectedValue))
         {
            ReportViewer1.Visible = true;
            ReportViewer1.LocalReport.ReportPath = Server.MapPath("Report9.rdlc");
            ReportViewer1.LocalReport.DataSources.Clear();
            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet16", dt));
         }
         else
         {
            Label1.Text = "You don't have access for other Stores";
         }
      }
   }
}

My requirement is to show store details and the products of it in a single report individually in tabular format (Store details and store products). So I thought, using two data adapters, one for store details and another store products would do. But I am not sure if it's the right way to do. Is there any way where I can implement two data adapters in an ASP.NET reporting system?