DataSet and DataTable based ad-hoc reporting with ASP.NET Core

In one of my projects I have some ASP.NET Core views that display multiple tables with reporting data. Data comes from SQL Server views and stored procedures and these can be modified in database without deploying application to server again. I came out with very common solution in ASP.NET Core to solve this problem using raw SQL commands and shared views for DataTable and DataSet. Here’s what I did.

Getting DataTable from ORM

It doesn’t matter if you use Entity Framework Core or NHibernate as they both give you access to database connection to execute direct queries. Here is the example for Entity Framework Core from my blog post Execute raw SQL commands in Entity Framework Core. This method can be member of database context or some repository class.

public async Task<DataTable> GetAverageTemperatures(DateTime forDate)
{            
    using(var command = Database.GetDbConnection().CreateCommand())
    { 
        command.CommandText = "sp_AverageTemperaturesReport";
        command.CommandType = CommandType.StoredProcedure;

        var forDateParam = command.CreateParameter();
        forDateParam.ParameterName = "@ForDate";
        forDateParam.DbType = DbType.Date;
        forDateParam.Value = forDate;
        command.Parameters.Add(forDateParam);

        using(var reader = await command.ExecuteReaderAsync())
        { 
            var table = new DataTable();
            table.Load(reader);

            return table;
        }
    }
}

It reads data from custom SQL to DataTable keeping data reader open while data is copied to DataTable. After this data reader is disposed and database connection is “free” again.

Building DataSet

Those who remember early days of .NET should also remember DataSet class. It’s a container holding DataTable objects and relations between them. As I had to show multiple reports in one ASP.NET Core view I decided to go with DataSet. I ask DataTables and build DataSet of them in database context or repository method like shown here.

public async Task<DataSet> GetActiveCustomersReport(DateTime forDate)
{
    var dataSet = new DataSet("Customers report");

    var table = await GetTopGoldCustomersForDate(forDate);
    table.TableName = "Top gold customers";
    dataSet.Tables.Add(table);

    table = await GetTopSilverCustomersForDate(forDate);
    table.TableName = "Top silver customers";
    dataSet.Tables.Add(table);

    table = await GetTopActiveCustomersForDate(forDate);
    table.TableName = "Top active customers";
    dataSet.Tables.Add(table);

    return dataSet;
}

Using this method I get DataSet with all tables I need to show on report.

Common view for DataTable

For a moment I head back to my post Simple view to display contents of DataTable just to grab a common ASP.NET MVC DataTable view I proposed there. For your convenience the markup of view is here. I keep this view in shared views folder.

@model System.Data.DataTable
@using System.Data;

<h2>@Model.TableName</h2>

<table>
    <thead>
        <tr>
            @foreach (DataColumn col in Model.Columns)
            {
                <th>@col.ColumnName</th>
            }
        </tr>
    </thead>
    <tbody>
        @foreach (DataRow row in Model.Rows)
        {
            <tr>
                @foreach (DataColumn col in Model.Columns)
                {
                    <td>@row[col.ColumnName]</td>
                }
            </tr>
        }
    </tbody>
\</table\>

If you need to display DataTable you can use this view from controller and provide DataTable as a model.

Common view for DataSet

To display DataSet we need another common view. To keep it minimal I will re-use DataTable view shown above. This view goes also to shared views folder of ASP.NET Core application.

@model System.Data.DataSet

<h1>@Model.DataSetName</h1>

@foreach(var table in Model.Tables)
{
    <partial name="DataTable" model="table" />
}

This view shows DataSet name and iterates through tables collection calling DataTable view for every table.

Using common DataSet view

There’s one final piece left to build – common view for DataSet. As it re-uses shared DataTable view it is small. Here it is.

@model System.Data.DataSet

<h1>@Model.DataSetName</h1>

@foreach(var table in Model.Tables)
{
    <partial name="DataTable" model="table" \>
}

Here is the controller action that calls GetActiveCustomersReport() shown method above and uses DataSet view to show results.

public async Task<IActionResult> CustomersReport()
{
    var report = await _dbContext.GetActiveCustomersReport(DateTime.Now.Date);

    return View("DataSet", report);
}

After applying some styling to views here’s the result – simple customers and orders report generated by SQL Server views and stored procedures and shown by commong views for DataSets and DataTables.

Be warned! It can be tempting to use this solution as a base for more advanced reporting and after first steps you are probably impressed how much it is possible to do with those common views. But be warned – this solution doesn’t replace any advanced reporting service or reporting components. It’s perfect when you just need to pump out some tabular reports to display data from database server but again – it is not base for advanced reporting.

Wrapping up

If we need to show simple dynamic reports in ASP.NET Core application we can use raw SQL queries and common views for DataSet and DataTable. It’s easy ad-hoc style reporting where changes to application are not needed when queries or stored procedures are tweaked. I have used this approach successfully in practice from ASP.NET MVC times. It’s easy to extend this solution but don’t go too far – if you need advanced reporting then better go with professional reporting components or services.

The post DataSet and DataTable based ad-hoc reporting with ASP.NET Core appeared first on Gunnar Peipman - Programming Blog.