ASP.NET Data Tables

In ASP.NET Data Tables provide APIs for sorting, filtering, performing aggregation and computation on tabular data. You can also display the data on pages and views. In this tutorial we’ll use Razor pages, however the technique is applicable to Razor Pages in MVC too. Razor Pages is a lightweight framework for building web applications in ASP.NET Core. It’s designed to make it easy to build simple web pages with minimal boilerplate code. One of the features of Razor Pages is its integration with the DataTable class, which allows you to work with tabular data in a fast and efficient way. By the way, did you know that we offer a unique online course that boosts your C# career? Check it out here!

Setting the Project Up

To create a razor page do the following:

Create an empty Razor web application
Replace the Pages/Index.cshtml codes with the following:
- ```
@page
<h1>Hello Razor Pages</h1>
```
Run the project, navigate to the `http://localhost:5175/` (Where `5175` is your app’s port) and you should see this page:

Adding Data Tables

To get started with DataTables in ASP.NET, you first need to create a new DataTable and add some columns to it. This can be done in the OnGet method of your page model:

using System.Data;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace DataTableInRazorPages.Pages;

public class IndexModel : PageModel
{
    public DataTable MyTable { get; set; } = new();

    public void OnGet()
    {
        MyTable = new DataTable();
        MyTable.Columns.Add("ID", typeof(int));
        MyTable.Columns.Add("Name", typeof(string));
        MyTable.Columns.Add("Age", typeof(int));

        MyTable.Rows.Add(1, "John", 25);
        MyTable.Rows.Add(2, "Mary", 30);
        MyTable.Rows.Add(3, "Bob", 35);
    }
}

In this example, we’re creating a new DataTable and adding three columns to it: ID, Name, and Age. We’re also adding three rows of data to the table. The OnGet method is called when the page is loaded, and it sets the MyTable property of the page model to the new DataTable.

Displaying the Data

You can use the DataTable as a data source for controls in your Razor Pages. For example, you can use the DataTable as a data source for a table in your Index.cshtml file:

@page
@using System.Data;     
@model IndexModel
@{
    ViewData["Title"] = "Home page";
}

<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Age</th>
        </tr>
    </thead>
    <tbody>
        @foreach (DataRow row in Model.MyTable.Rows)
        {
            <tr>
                <td>@row["ID"]</td>
                <td>@row["Name"]</td>
                <td>@row["Age"]</td>
            </tr>
        }
    </tbody>
</table>

In this example, we’re using the @foreach directive to iterate over the rows in the DataTable and generate HTML markup for a table. We’re using the @row["ColumnName"] syntax to access the values in each column of the DataTable.

Run the app and you should see the following result:

Once you have a DataTable, you can easily filter and sort the data using the Select and DefaultView properties.

Sorting

Sorting the data in a DataTable is just as easy. You can use the DefaultView property to sort the data by one or more columns:

public void OnGet()
{
    // ...

    MyTable.DefaultView.Sort = "Name DESC";
  
    MyTable = MyTable.DefaultView.ToTable();
}

In this example, we’re using the DefaultView property to sort the data by the Name column in descending order. We’re then using the ToTable method to create a new DataTable that contains the sorted data.

Filtering

To filter the rows where Age is greater than 30, for example, you can add the following code to your page model:

public void OnGet()
{
    // ...

    DataRow[] results = MyTable.Select("Age > 30");

    MyTable = results.CopyToDataTable();
}

In this example, we’re using the Select method to filter the rows in the DataTable based on a set of criteria. We’re then using the CopyToDataTable method to create a new DataTable that contains only the filtered rows.

Overall, the DataTable class is a powerful and versatile tool for working with tabular data. Whether you need to filter, sort, or manipulate the data, the DataTable class provides an easy-to-use API that allows you to work with data in a way that is both intuitive and efficient.

Calculations

In addition to the basic operations covered in this article, the DataTable class provides many other features that make it a great choice for working with tabular data. For example, you can use the Compute method to perform aggregate calculations on the data, such as computing the sum or average of a column.

Here’s an example of how you can use the Compute method in your page model:

public void OnGet()
{
    //...

    object result = MyTable.Compute("SUM(Age)", "");

    int sum = Convert.ToInt32(result);
}

In this example, we’re using the Compute method to compute the sum of the Age column in the DataTable. We’re passing an empty string as the second argument to indicate that we want to compute the sum for all rows in the table. We’re then converting the result to an integer value using the Convert.ToInt32 method. We can add the sum as a new table row as well.

public void OnGet()
{
     //...

     object result = MyTable.Compute("SUM(Age)", "");
     int sum = Convert.ToInt32(result);

     MyTable.Rows.Add(0, "Sum", sum);

}

If you want to skyrocket your C# career, check out our powerful ASP.NET full-stack web development course that also covers test-driven development.