Blazor DataGrid using DataTable-CodePudding

We currently have a WinForms app where our programmers can execute any query they want across over a hundred databases and tables. We take the query results and stick it in a DataSet of DataTables. Then, we can loop over the rows and columns of the DataTables and display the results in a WinForms DataGrid that has column sorting. Super easy.

I need to convert this project to Blazor WebAssembly app. My issue is trying to figure out how to use a DataSet/DataTable with a DataGrid in Blazor. I've looked into DataGrids by component libraries like MudBlazor or Radzen. If you look at the example code below from MudBlazor, all the examples I've found use Lists/IEnumberables with a specific object types, like Animal or Element.

<MudDataGrid Items="@Elements.Take(4)">
    <Columns>
        <Column T="Element" Field="Number" Title="Nr" />
        <Column T="Element" Field="Sign" />
        <Column T="Element" Field="Name" />
        <Column T="Element" Field="Position" />
        <Column T="Element" Field="Molar" Title="Molar mass" />
    </Columns>
</MudDataGrid>
@code { 
    private IEnumerable<Element> Elements = new List<Element>();

    protected override async Task OnInitializedAsync()
    {
        Elements = await httpClient.GetFromJsonAsync<List<Element>>("webapi/periodictable");
    }
}

I can't setup a DataGrid like this because the results in our DataTables are different every time and we don't have a class subbed out for every query response that I could map to the DataGrid.

I just want to be able to loop over the rows and columns of my DataTable and display them in a basic column sortable DataGrid. I tried doing this using a regular html table as well, but it doesn't have column sorting.

I really need a DataSet/DataTable to work in a sortable DataGrid in Blazor.

CodePudding user response：

I have created a basic Templated component to emphasize some points you've got to take into consideration, but you may improve on it instead of using other DataGrid components from other vendors.

Note that the code shown below was created in .Net 5.0

Create a Blazor Server App with Individual Accounts.
Create a Student class at the root folder of the app:

Student.cs

public class Student
   {
        public int ID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public DateTime EnrollmentDate { get; set; }
   }

Execute the add migration command in the Package Manager Console

add-migration InitialStudent
Execute the update-datatbse to create the database.
Add a couple of records to the Student table
Create an interface in the Data folder IDataService.cs

public interface IDataService { IQueryable Students { get; } }
Create a class service LocalDataService in the Data folder

LocalDataService.cs

using System.Linq;
   using Microsoft.EntityFrameworkCore;

   public class LocalDataService : IDataService
   {
        private readonly ApplicationDbContext _dbContext;

        public LocalDataService(ApplicationDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        public IQueryable<Student> Students => _dbContext.Students;
   }

Add services.AddScoped<IDataService, LocalDataService>(); at the end of the Startup.ConfigureServices method
Add a Razor component named TableTemplate

TableTemplate.razor

@typeparam TItem

<table >
    <thead>
        <tr>@TableHeader(Titles)</tr>
    </thead>
    <tbody>
        @foreach (var item in Items)
        {
            <tr>@RowTemplate(item)</tr>
        }
    </tbody>
    <tfoot>
        <tr>@TableFooter</tr>
    </tfoot>
</table>

@code {
    [Parameter]
    public RenderFragment<IReadOnlyCollection<string>> TableHeader { get; set; }

    [Parameter]
    public RenderFragment<Student> RowTemplate { get; set; }

    [Parameter]
    public RenderFragment TableFooter { get; set; }

    [Parameter]
    public IQueryable<Student> Items { get; set; }
    [Parameter]
    public IReadOnlyCollection<string> Titles { get; set; }
}

Add the code snippet to the Index component

@page "/"
@using <Put here the namespace of your app>.Data
@using System.Linq.Expressions
@using System

@inject IDataService DataService

<TableTemplate Items="FilteredStudents" TItem="Student" Titles="titles">
    <TableHeader>
        <th></th>
        <th>@context.ToArray()[0]</th>
        <th><a href="#" @onclick="@(() => Sort(s => s.FirstName))" @onclick:preventDefault>@context.ToArray()[1]</a></th>
        <th><a href="#" @onclick="@(() => Sort(s => s.LastName))" @onclick:preventDefault>@context.ToArray()[2]</a></th>
        <th><a href="#" @onclick="@(() => Sort2(s => s.EnrollmentDate))" @onclick:preventDefault>@context.ToArray()[3]</a></th>
    </TableHeader>
    <RowTemplate Context="student">
        <td></td>
        <td>@student.ID</td>
        <td>@student.FirstName</td>
        <td>@student.LastName</td>
        <td>@student.EnrollmentDate</td>
    </RowTemplate>
</TableTemplate>
<hr />


@code
{
    private IQueryable<Student> FilteredStudents;
    private string[] titles = new[] { "ID", "First Name", "Last Name", "Enrollment Date" };
    private async Task Sort(Expression<Func<Student, string>> func)
    {
        FilteredStudents = FilteredStudents.OrderBy(func) as IQueryable<Student>; 
        await Task.CompletedTask;
    }

    private async Task Sort2(Expression<Func<Student, DateTime>> func)
    {
        FilteredStudents = FilteredStudents.OrderBy(func) as IQueryable<Student>;
        await Task.CompletedTask;
    }

    protected override void OnInitialized()
    {
        FilteredStudents = DataService.Students;
    }

}

Run and test... Click at the headers of the columns to see the rows are sorted according to the column selected

Note that the code is only partially dynamic, as for instance:

<RowTemplate Context="student">
      <td></td>
      <td>@student.ID</td>
      <td>@student.FirstName</td>
      <td>@student.LastName</td>
      <td>@student.EnrollmentDate</td>                
   </RowTemplate>

As you can see, I use 4 HTML elements for the 4 properties of the Student object. But according to your requirements we cannot know before hand what object we are going to view at all. In short, what we need here is to know the type of the object to view and a collection of its fields' names, so that we can iterate this collection and render the content dynamically. In order to provide your Templated component with this information, you'll need to exract it from the DBContext objects. Our LocalDataService returns IQueryable<Student> That's the way to get a collection of entities (Student), but the DbSet object was not created to provide meta data, but rather to provide entities, and to enable actions on them. In order to access meta data, like the names of the fields, data type, and such like, you'll need to use ADO.NET, not EntityFramework.. Do all the work of exraction, etc. in the LocalDataService.

Note that in the RowTemplate above I could remove Context="student", and use the implied object context, like this: <td>@context.ID</td> to make it look generic, but it still has the same limitations mentioned above.

Note that I use an array (titles) for the titles of the columns' headers. This information should also be extracted through ADO.NET (DataTable), etc.

Note: Though QuickGrid is titled "experimental", you should not wory using it. It is being developed by the Blazor team, headed by Steve Sanderson. If you don't trust them, how can you trust what you yourself do. Blazor WebAssembly Apps had been also titled "experimental" for a long time, in the course of which thousnads of developers created websites, and the component libraries many developers use today.

Incidentally, MudDataGrid is also "experimental."

If I had listen to naysayers, I would never have learned C#, I would never have learned Blazor... They were making jokes about Blazor, always reminding us of SilverLight, even making Microsoft traumatized to the extent that all thier new products are titled "experimental", just to be sure...

CodePudding user response：

I was curious myself and I gave it a try. This works:

@using System.Data
@using Microsoft.AspNetCore.Components.QuickGrid
@inject DataService DataService

@if (table.Rows.Count > 0)
{
    <div style="height:25em; overflow:scroll">
        <QuickGrid TGridItem="DataRow" ItemsProvider="provider" Virtualize="true" ItemSize="35">
            @foreach (DataColumn column in table.Columns)
            {
                <PropertyColumn Property="@(c => c[column.ColumnName])" Sortable="true">
                    <HeaderTemplate>
                        @column.ColumnName
                    </HeaderTemplate>
                </PropertyColumn>
            }
        </QuickGrid>
    </div>
}

@code
{
    DataTable table = new();
    //IQueryable<DataRow> = table.AsQueryable();
    GridItemsProvider<DataRow>? provider;

    void Load(int setNum)
    {
        table = DataService.GetDataTable(setNum);   // some testdata
        var rows = table.AsEnumerable().ToList(); 
        var providerResult = GridItemsProviderResult
            .From<DataRow>(rows, rows.Count);
        provider = req => ValueTask.FromResult(providerResult);
    }

    protected override void OnInitialized()
    {
        Load(1);
    }
}

As you can see you will need some wrapping. DataTable is not Linq compatible, DataTable.Rows is an ICollection but not an ICollection<DataRow>.

The Virtualize option makes rendering a snap, even with 100k records.
I didn't look at Sorting yet, shouldn't be too difficult to implement.

Student.cs

Create an interface in the Data folder IDataService.cs

LocalDataService.cs

TableTemplate.razor