Binding DataTables to Grids - a solution.

[A. Richard]

I have a requirement to get a DataTable and display it nicely on a Grid control.  This is pretty straightforward in Blazor with <tables>, but not trivial when it comes to binding the data to a nice, structured  Grid control.

As a control that is expecting an IEnumerable collection, DataTables don't really meet the criteria for the data binding that the Grid needs.  The DataRows are composed of ItemArrays, which will not bind nicely with the DataGrid in the ways it is expecting.  After days of pushing and prodding on this problem and a number of false starts, I've discovered dynamic objects in C# are a pretty fast and easy way to solve this mess, without having to dive into a whole mess of Reflection.  Essentially, the System.Dynamic.ExpandoObject is a weird hybrid that acts like a class, but can be set using a Dictionary.  So, by taking the columns and rows and copying them to a List of these ExpandoObjects, we're able to display the data on the screen.  This isn't going to be a two-way binding scenario (although I suppose it's possible with a bit of work), but just for displaying the data within a DataTable in a nice grid control is a good start.

Attachment: code_sample_c9996fe7.zip

[Renjith Singh Rajendran]

Hi Richard, 

Thanks for contacting Syncfusion support. 

We have analyzed your query. We suspect that, you are facing difficulties in binding a DataTable to Syncfusion Grid. Based on this requirement, we have prepared a sample to bind a DataTable as DataSource for a Syncfusion Grid. We have used the same codes which you have shared with us. We are attaching the sample for your convenience, please download the sample from the link below, 

Sample : https://www.syncfusion.com/downloads/support/directtrac/general/ze/DataTableS-1533408956 

 

Note : In the above sample we have used latest nuget and script version v.17.2.0.34-beta 

If we have misunderstood your query, or you are still facing the same problem, then please get back to us with the following details for better assistance. 

1. Share the detailed description of your complete requirement.
2. Share the Syncfusion version details.
3. Share the details of the problem you are facing in binding a DataTable to Grid.
4. Share the exact scenario or proper replication procedure.
5. Share with us a video demo showing the problem you are facing.

The provided information will help us analyze the problem, and provide you a solution as early as possible. 

Regards, 

Renjith Singh Rajendran. 

[A. Richard]

Good Morning Renjith,

Thank you for bundling my solution nicely into a full project with data and everything.  Just to give everyone a little more context, this solution is an example of late-binding the data to a SyncFusion Grid control, where the Grid control has no prior knowledge of the DataTable schema.  This can be handy when people have dynamic data that they need to display and not necessarily edit.

Richard

[Vignesh Natarajan]

Hi Richard,  

Thanks for the detailed explanation.  

We are glad to hear that you are able to achieve your requirement using our solution. 

Please get back to us if you have further queries.  

Regards, 

Vignesh Natarajan. 

[Joe]

Could you provide a version of this for the latest SfGrid controls?

[Vignesh Natarajan]

Hi Joe,  

 

Thanks for contacting Syncfusion support.  

 

Query: “Could you provide a version of this for the latest SfGrid controls?” 

 

We have achieved your requirement to bind DataTable to Grid component by converting the datatable to ExpandoObject list and using dynamic column building. Kindly download the sample from below which we have prepared using 18.3.0.52 (Syncfusion.Blazor) Nuget version.  

 

Sample: https://www.syncfusion.com/downloads/support/forum/146109/ze/DataGrid19262303   

 

Refer our UG documentation for your reference 

 

https://blazor.syncfusion.com/documentation/datagrid/columns/#dynamic-column-building  

 

Please get back to us if you have further queries.   

 

Regards, 

Vignesh Natarajan 

[Mark Jay]

This does solve he problem and I can get my DataTable to display in the Blazor Grid. But editing and saving back to the database seems to be a complex matter.

Why can't the Blazor SFGrid just use a DataTable as a DataSource like all other Data Grids?

[Prathap Senthil]

Hi Mark,

Based on your query, we suggest using a custom adapter. We have already documented this topic. Please see the documentation link below. 
https://blazor.syncfusion.com/documentation/datagrid/custom-binding#custom-adaptor-as-component

https://blazor.syncfusion.com/documentation/datagrid/data-binding#sql-server-data-bindingsql-client

Regards,
Prathap S

[Ahmad Mohammad]

Heloo syncfusion teams

It is possible to use datatable with Template column if yes could you help me please

Thanks

[Prathap Senthil]

Hi Ahmad,

We would like to clarify that we don’t have direct support to bind data to the DataGrid from a datatable, so it's not possible to use the datatable with a template column. Please note that our Grid component requires strongly typed objects or POCO object-type data for binding. This means that data binding must be based on a specific model type (class). This behavior is inherent to the default functionality of the grid. If the model type is unknown, we recommend using ExpandoObjectBinding or DynamicObjectBinding. For more information on these binding options, please refer to the following documentation link:
https://blazor.syncfusion.com/documentation/datagrid/columns#expandoobject-complex-data-binding
https://blazor.syncfusion.com/documentation/datagrid/columns#dynamicobject-complex-data-binding

However, you can achieve your requirement by converting the datatable to an ExpandoObject list, allowing you to use the template. We have attached a sample for your reference and code snippet for your reference.

<SfGrid DataSource="@CustomerList" TValue="ExpandoObject" AllowPaging="true" AllowSorting="true" AllowFiltering="true" AllowGrouping="true">

    <GridPageSettings PageSize="5"></GridPageSettings>

    <GridColumns>

        <GridColumn Field="OrderID" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Right"></GridColumn>

        <GridColumn Field="CustomerID" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Right">

            <Template>

                @{

                    var employee = (context as IDictionary<string, object>);

                    var edit = (string)employee["CustomerID"];

 

                    <p>CustomerName:@edit</p>

                }

            </Template>

        </GridColumn>

    </GridColumns>

</SfGrid>

@code {

 

 

    System.Data.DataTable dtOrder;

 

    public List<string> Columns;

    public List<ExpandoObject> CustomerList;

 

    protected override void OnInitialized()

    {

 

 

        CustomerList = CreateOrderRows();

 

 

    }

 

    public List<ExpandoObject> CreateOrderRows()

    {

        dtOrder = new DataTable("orders");

        System.Data.DataColumn dcOrderID = new DataColumn("OrderID", typeof(String));

        System.Data.DataColumn dcCustomerID = new DataColumn("CustomerID", typeof(String));

        dtOrder.Columns.Add(dcOrderID);

        dtOrder.Columns.Add(dcCustomerID);

 

        dtOrder.Rows.Add("Order1", "Customer1");

        dtOrder.Rows.Add("Order1", "Customer2");

        dtOrder.Rows.Add("Order2", "Customer2");

 

 

        var lstRows = new List<ExpandoObject>();

        foreach (System.Data.DataRow row in dtOrder.Rows)

        {

            System.Dynamic.ExpandoObject e = new System.Dynamic.ExpandoObject();

            foreach (System.Data.DataColumn col in dtOrder.Columns)

                e.TryAdd(col.ColumnName, row.ItemArray[col.Ordinal]);

            lstRows.Add(e);

 

        }

        return lstRows;

 

    }

 

}

 

Sample Link: https://blazorplayground.syncfusion.com/embed/LXrUshXPLDWuuezP?appbar=true&editor=true&result=true&errorlist=true&theme=bootstrap5

Regards,
Prathap s