How can I return multiple datasets from a Controller?

Question

Returning a single DataTable from a Controller works fine for me:

public ActionResult Index()
{
    DataTable dtable = new DataTable();
    dtable = SQL.ExecuteSQLReturnDataTable(SQL.SelectUnitsQuery, CommandType.Text, null);
    ViewBag.Units = dtable;
    return View(dtable);
}

I'm able to access it from the corresponding View like so:

@using System.Data
@{
    ViewBag.Title = "Platypus Report Scheduler";

    DataTable ds = ViewBag.Units as DataTable;
    var rows = from x in ds.AsEnumerable()
               select new
               {
                   unit = x.Field<string>("unit")
               };
}

But I need to reference multiple datasets; I tried this in the Controller:

public ActionResult Index()
{
    DataTable dtable = new DataTable();
    dtable = SQL.ExecuteSQLReturnDataTable(SQL.SelectUnitsQuery, CommandType.Text, null);
    ViewBag.Units = dtable;

    DataTable rpts = new DataTable();
    rpts = SQL.ExecuteSQLReturnDataTable("select ReportName from ReportsLU", CommandType.Text, null);
    ViewBag.Reports = rpts;

    return View(dtable, rpts);
}

...but it won't compile; I get, "Argument 1: cannot convert from 'System.Data.DataTable' to 'string'" for "dtable" and the same error for arg 2 ("rpts"). Also, "The best overloaded method match for 'System.Web.Mvc.Controller.View(string, string)' has some invalid arguments"

What is the way to work around this? Return a generic list of DataTable from the Controller? Populate the subsequent DataTables directly in the View? Or...???

Answer 1

There are two solution.

The first is to use ViewBag as you already do. The second solution (and in my personal opinion the best) is to create a new model that contains all data that you need to use in the view.

First implementation:

public ActionResult Index()
{
    DataTable dtable = new DataTable();
    dtable = SQL.ExecuteSQLReturnDataTable(SQL.SelectUnitsQuery, CommandType.Text, null);
    ViewBag.Units = dtable;

    DataTable rpts = new DataTable();
    rpts = SQL.ExecuteSQLReturnDataTable("select ReportName from ReportsLU", CommandType.Text, null);
    ViewBag.Reports = rpts;

    return View();
}

You don't need to pass dtable and rpts to View method in this case because values are in ViewBag.

@using System.Data
@{
    ViewBag.Title = "Platypus Report Scheduler";

    DataTable ds = ViewBag.Units as DataTable;
    DataTable ds2 = ViewBag.Reports as DataTable;

    // Some other beautiful things
}

Second implementation:

public class YourModel {
    public DataTable dt1 { get; set; }
    public DataTable dt2 { get; set; }
    public DataTable dt3 { get; set; }
    // Other properties
}

public ActionResult Index()
{
    YourModel model = new YourModel();

    DataTable dtable = new DataTable();
    dtable = SQL.ExecuteSQLReturnDataTable(SQL.SelectUnitsQuery, CommandType.Text, null);
    model.dt1 = dtable;

    DataTable rpts = new DataTable();
    rpts = SQL.ExecuteSQLReturnDataTable("select ReportName from ReportsLU", CommandType.Text, null);
    model.dt2 = rpts;

    return View(model);
}

Now in the view:

@model YourModel
@{
    ViewBag.Title = "Platypus Report Scheduler";

    // Retrive data in this way:
    // Model.dt1
    // Model.dt2

    // Some other beautiful things
}

In the view @model YourModel is fundamental!