Dynamic columns when using SFDataManager

[George Logue]

I need to be able to dynamically generate the columns base on the data returned from my custom adapter. For example certain columns should not be rendered. The issue is I don't know how to get the data when using the custom adapter.

                            
                                                       
                            
                                
                                @{
                                    var primary = false;

                                    if(Rows != null)
                                    {
// How do I get the data in order to go through each column here?
                                        foreach (var c in Rows?.First())
                                        {
                                            if (c.Key.StartsWith("#"))
                                            {
                                                if (c.Key == "#ID")
                                                {
                                                    primary = true;
                                                }
                                                else
                                                {
                                                    continue;
                                                }
                                            }

                                            var field = c.Key;
                                            
                                        }
                                    }
                                }
                            
                        

To implement the custom adaptor that gets the data:

public class CustomAdaptor : DataAdaptor
{
    public ITruckDB db { get; set; }


    public CustomAdaptor() { }


    public CustomAdaptor(ITruckDB database)
    {
        db = database;
    }


    public Dictionary Records { get; set; } = new Dictionary();


    public override async Task<object> ReadAsync(DataManagerRequest dataManagerRequest, string key = null)
    {
        DataTable dt;
        var totalItemCount = 250;


        dt = await db.GetData();


        var ed = dt.ToEnumerableDictionary();


        var data = ed.Select(x => x.ToDictionary(
            x => x.Key,
            x =>
            {
                if (x.Value == DBNull.Value)
                {
                    return null;
                }


                return x.Value;
            })).ToList();


        return dataManagerRequest.RequiresCounts ? new DataResult() { Result = data, Count = totalItemCount } : (object)data;
    }
}


[Sarveswaran Palani]

Hi George,

Greetings from Syncfusion support.

From your query, we suspect that you want to render columns dynamically using custom adaptor. When data binding externally, then the TValue must be provided explicitly in the datagrid component. We prepared sample based on your requirement. Kindly refer the attached sample for your reference.

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

If you have any further queries, please get back to us.

Regards,

Sarvesh

Attachment: SfGridDynamicColumn_c932857a.zip

[George Logue]

I took a look at the example and added some code to Index.razor with comments to show what I am trying to do.  I attached that same project here.

To sum up what I am trying to do

I am using the Custom Adaptor so that I can override ReadAsync to create and run a query based on page, pagesize and what is being filtered and sorted.

This query returns a DataTable which is then converted to List of Dictionary<string, object> so the grid can use it.

The Grid TValue is Dictionary<string, object>.

The original example you provided shows to iterate through the properties of an existing Type but the properties of my object are dynamic based on the query results, that is why I am using a Dictionary<string, object> so that I can dynamically create columns based on the "Name" of the column and the Type of the object.

Normally I would just get the first row of the grids data source but I do not know how to access it.

Is there a way to access the current data source of the grid?

Do I have to use ExpandoObject? Is that possible with a custom adapter so that I can create the queries myself?

Attachment: SfGridAdaptorModified_5b40d905.zip

[Sarveswaran Palani]

Hi George,

Based on the shared code snippet, it seems that you have used Dictionary<string, object> Tvalue in the grid. Please note that our Grid component requires strongly typed class or POCO object-type data for binding, meaning 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

Kindly get back to us if you have further queries or after following up on the above suggestion, if the issue persists.

Regards,
Sarvesh

[George Logue]

I am evaluating the controls as we usually use Telerik but want to try the sync fusion controls. Normally how I bind the grids is this method here:

https://demos.telerik.com/blazor-ui/grid/data-table

I am able to set a DataTable locally and then access it when binding grids. I don't see a way to do this because the SFGrid binds through the SFDataManager, rather than the actual grid. How can I share information between the SFDataManager and the actual grid?

[Sarveswaran Palani]

Hi George,

We have developed a sample based on your requirements, which demonstrates data binding to a data table using the ExpandoObject. To provide you with a better understanding, we have attached a code snippet and a sample for your reference. Please review them to see how the data binding is implemented.

<SfGrid DataSource="@DataSourceList" TValue="System.Dynamic.ExpandoObject" AllowPaging="true" AllowFiltering="true" AllowSorting="true">

    <GridEvents RowSelected="@GetSelectedRecord" TValue="System.Dynamic.ExpandoObject"></GridEvents>

    <GridPageSettings PageCount="5"></GridPageSettings>

    <GridColumns>

        @foreach (DataColumn col in DataSourceTable.Columns)

        {

            <GridColumn Field[email protected] />

        }

    </GridColumns>

</SfGrid>

@code {

    [Parameter] public DataTable DataSourceTable { get; set; }

    [Parameter] public List<System.Dynamic.ExpandoObject> DataSourceList { get; set; }

    public void GetSelectedRecord(RowSelectEventArgs<System.Dynamic.ExpandoObject> args)

    {

        var data = args.Data as dynamic;

        Console.WriteLine(data.CustomerID);

    }

}

We have discussed a similar issue on the forum. Kindly check the mentioned thread for your information.

If you have any further queries, please get back to us.

Regards,
Sarvesh

Attachment: SfGridExpandOobject_6aff5038.zip

[George Logue]

Your sample does not use SfDataManager with custom adaptor. I need to use it to customize the query on each action.

[sadie]

I am able to set a DataTable locally and then access it when binding grids.

[George Logue]

But you can't set it from within a custom data adapter, because it is not in scope.

[Sarveswaran Palani]

Hi George,

From your shared code snippet, it appears that you have assigned TValue as Dictionary<string, object>. As we mentioned before, our Grid component requires data binding to be based on a strongly typed class or POCO object-type data. This means that data binding must be done using a specific model type (class). This behavior is inherent to the default functionality of the grid. However, if the model type is unknown, we recommend considering the use of either ExpandoObjectBinding or DynamicObjectBinding. To demonstrate this, we have prepared a sample that uses a DataTable to bind values to the grid using SfDataManager with a custom adapter on ExpandoObject. This approach allows for flexible data binding even when the model type is not predefined.

<SfGrid TValue="ExpandoObject" AllowPaging="true" AllowGrouping="true" AllowSorting="true">

    <SfDataManager AdaptorInstance="@typeof(CustomAdaptor)" Adaptor="Syncfusion.Blazor.Adaptors.CustomAdaptor"></SfDataManager>

    <GridEditSettings AllowAdding="true" AllowDeleting="true" AllowEditing="true"></GridEditSettings>

    <GridColumns>

        <GridColumn Field="Image_ID" HeaderText="Image ID" IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>

        <GridColumn Field="Site_ID" HeaderText="Site Name" Width="120"></GridColumn>

        <GridColumn Field="Name" HeaderText="Name" Width="120"></GridColumn>

        <GridColumn Field="Short_Name" HeaderText="Short Name"  Width="120"></GridColumn>

        <GridColumn Field="Image_Binary" HeaderText=" Order Date" Width="130" Type="ColumnType.Date"></GridColumn>

        <GridColumn Field="Available" HeaderText="Active" DisplayAsCheckBox="true" Width="150"></GridColumn>

    </GridColumns>

</SfGrid>

 

@code {

    public static List<ExpandoObject> Orders { get; set; } = new List<ExpandoObject>();

    private List<string> ToolbarItems = new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel" };

    private DataTable DTable { get; set; } = new DataTable();

    private DataTable? dtFlat;

 

    protected override async Task OnInitializedAsync()

    {

 

        if (DTable.Columns.Count == 0)

        {

            if (Orders.Count == 0)

            {

                dtFlat = Test.GetImagesDataTableBySiteIdAsync();

 

                foreach (DataRow row in dtFlat.Rows)

                {

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

                    foreach (DataColumn col in dtFlat.Columns)

                    {

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

 

                    }

                    Orders.Add(e);

                }

            }

        }

    }

    public class Test

    {

     public static DataTable GetImagesDataTableBySiteIdAsync()

      {

        DataTable dataTable = new DataTable();

 

        // Add columns to the DataTable

        dataTable.Columns.Add("Image_ID", typeof(int));

        dataTable.Columns.Add("Site_ID", typeof(Guid));

        dataTable.Columns.Add("Name", typeof(string));

        dataTable.Columns.Add("Short_Name", typeof(string));

        dataTable.Columns.Add("Image_Binary", typeof(byte[]));

        dataTable.Columns.Add("Available", typeof(bool));

 

        // Populate the DataTable with raw data

        DataRow row1 = dataTable.NewRow();

        row1["Image_ID"] = 1;

        row1["Site_ID"] = Guid.NewGuid();

        row1["Name"] = "Image 1";

        row1["Short_Name"] = "Img1";

        row1["Image_Binary"] = new byte[] { 0x01, 0x02, 0x03 };

        row1["Available"] = true;

        dataTable.Rows.Add(row1);

 

        DataRow row2 = dataTable.NewRow();

        row2["Image_ID"] = 2;

        row2["Site_ID"] = Guid.NewGuid();

        row2["Name"] = "Image 2";

        row2["Short_Name"] = "Img2";

        row2["Image_Binary"] = new byte[] { 0x04, 0x05, 0x06 };

        row2["Available"] = false;

        dataTable.Rows.Add(row2);

 

        return dataTable;

    }

}

If you have any further queries, please get back to us.

Regards,
Sarvesh

Attachment: SfGridCustomAdaptorExpandoObject_c07597e9.zip

[George Logue]

In the attached example it looks like you are getting around the issue of being unable to access the object from within the CustomAdaptor by making it static.

When using server-side blazor this is unacceptable as static objects are shared across all sessions and users.

See: https://github.com/dotnet/aspnetcore/issues/15451

The Telerik control does it like this where you can set the OnRead method on the page instead of in a custom adaptor. This allows your OnRead method to access everything the grid has access to on the page without making it static.

[Sarveswaran Palani]

Hi George,

Based on your requirement, we prepared sample access the object from the custom adaptor dynamically. We have attached sample for your reference.

<SfGrid TValue="ExpandoObject" ID="Grid" AllowSorting="true" AllowGrouping="true" AllowPaging="true">

    <SfDataManager Adaptor="Adaptors.CustomAdaptor">

        <Counter></Counter>

    </SfDataManager>

    <GridPageSettings PageSize="8"></GridPageSettings>

    <GridColumns>

        <GridColumn Field="Image_ID" HeaderText="Image ID" IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>

        <GridColumn Field="Site_ID" HeaderText="Site Name" Width="120"></GridColumn>

        <GridColumn Field="Name" HeaderText="Name" Width="120"></GridColumn>

        <GridColumn Field="Short_Name" HeaderText="Short Name" Width="120"></GridColumn>

        <GridColumn Field="Available" HeaderText="Active" DisplayAsCheckBox="true" Width="150"></GridColumn>

    </GridColumns>

</SfGrid>

@code{

    public static List<ExpandoObject> Orders { get; set; } = new List<ExpandoObject>();

 

    private DataTable DTable { get; set; } = new DataTable();

    private DataTable? dtFlat;

 

    protected override async Task OnInitializedAsync()

    {

 

        if (DTable.Columns.Count == 0)

        {

            if (Orders.Count == 0)

            {

                dtFlat = Test.GetImagesDataTableBySiteIdAsync();

 

                foreach (DataRow row in dtFlat.Rows)

                {

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

                    foreach (DataColumn col in dtFlat.Columns)

                    {

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

 

                    }

                    Orders.Add(e);

                }

            }

        }

    }

 

    public class Test

    {

       public static DataTable GetImagesDataTableBySiteIdAsync()

        {

            DataTable dataTable = new DataTable();

 

            // Add columns to the DataTable

            dataTable.Columns.Add("Image_ID", typeof(int));

            dataTable.Columns.Add("Site_ID", typeof(Guid));

            dataTable.Columns.Add("Name", typeof(string));

            dataTable.Columns.Add("Short_Name", typeof(string));

            dataTable.Columns.Add("Image_Binary", typeof(byte[]));

            dataTable.Columns.Add("Available", typeof(bool));

 

            // Populate the DataTable with raw data

            DataRow row1 = dataTable.NewRow();

            row1["Image_ID"] = 1;

            row1["Site_ID"] = Guid.NewGuid();

            row1["Name"] = "Image 1";

            row1["Short_Name"] = "Img1";

            row1["Image_Binary"] = new byte[] { 0x01, 0x02, 0x03 };

            row1["Available"] = true;

            dataTable.Rows.Add(row1);

 

            DataRow row2 = dataTable.NewRow();

            row2["Image_ID"] = 2;

            row2["Site_ID"] = Guid.NewGuid();

            row2["Name"] = "Image 2";

            row2["Short_Name"] = "Img2";

            row2["Image_Binary"] = new byte[] { 0x04, 0x05, 0x06 };

            row2["Available"] = false;

            dataTable.Rows.Add(row2);

 

            // ... add more rows as needed ...

 

            return dataTable;

        }

    }

}

If you have any further queries, please get back to us.

Regards,
Sarvesh

Attachment: SfGrid_Custom_Component_c95d36e3.zip

[Craxe Dunt]

I am able to set a DataTable locally

[Prathap Senthil]

Hi Craxe,

It's great to hear that you've successfully set up a DataTable locally. Please get back to us If you have any questions.

Regards,

Prathap S