How to bind a datatable to the model and then render in the view dynamically?

Question

How to bind a datatable to the model and then render in the view dynamically?

The problem is that at beginning I don't know the table structure until run the code.

The table fileds may like this ,but the structure is not fixed!

ID date_start F1 F2 F5........

I want to convert the data into a List<T> ,and then in the view @model T to render the page.

How to realize it？

Answer 1

Hi @Alick Wang

How to bind a datatable to the model and then render in the view dynamically?

From your description, it seems that you just want to display the datatable data in the view page finally. If that is the case, you can directly use the DataTable as the page model, and then loop through the columns and rows and then display the data. Refer to this link:

ASP.Net Core MVC: Using DataSet (DataTable) as Model in View

Generally, the above method is typically associated with ADO.NET and is more commonly used in traditional ASP.NET Web Forms applications. In ASP.NET Core MVC, it's more common to work with strongly-typed models, which provide better type safety, code readability, and easier maintenance. So, in asp.net core application, you can create a model with a Dictionary<string, object> property, and use it to store the columns and row data. Then use it display the data in the view. Refer to the following samples:

Create a DynamicModel:

    public class DynamicModel
    {
        public Dictionary<string, object> Columns { get; set; }
    }

Then use the following method to convert the datatable to a list of objects.

        public List<DynamicModel> ConvertDataTableToDynamicModel(DataTable dataTable)
        {
            List<DynamicModel> models = new List<DynamicModel>();

            foreach (DataRow row in dataTable.Rows)
            {
                DynamicModel model = new DynamicModel
                {
                    Columns = new Dictionary<string, object>()
                };

                foreach (DataColumn column in dataTable.Columns)
                {
                    model.Columns.Add(column.ColumnName, row[column]);
                }

                models.Add(model);
            }

            return models;
        }

Code in the controller: call the ConvertDataTableToDynamicModelmethod and convert the datatable to list of objects.

        public IActionResult Index2(string tablename)
        {
            DataTable table = new DataTable();
            if (tablename == "table1") {
                table = MakeParentTable(); //generate the datatable
            }
            else
            {
                table = MakeChildTable(); //generate the datatable
            }

            List<DynamicModel> data = ConvertDataTableToDynamicModel(table);

            return View(data);
        }

In the view page: use foreach statement to loop the columns and display the row data:

@model IEnumerable<WebApplication1.Data.DynamicModel>

@{
    ViewData["Title"] = "Index2";
} 
<table class="table">
    <thead>
        <tr>
            @foreach (var columnName in Model.First().Columns.Keys)
            {
                <th>@columnName</th>
            }
        </tr>
    </thead>
    <tbody>
        @foreach (var dynamicModel in Model)
        {
            <tr>
                @foreach (var columnValue in dynamicModel.Columns.Values)
                {
                    <td>@columnValue</td>
                }
            </tr>
        }
    </tbody>
</table>

The output as below:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Best regards,
Dillion

Answer 2

As AgaveJoe states, C# is a strongly-typed language. It is not the simplest of things to work with dynamic properties, although the languag is in fact capable.

What I don't see happening is being able to do @model T. Why? Because the C# compiler needs to generate code for every possible T. If T is never known at design time, you won't be able to get a fully typed model. The best you can have is T = ExpandoObject, or T = IDictionary<string, object> or similar general constructs.

It is currently difficult for me to think about a code sample that would help in your case, as there isn't much information. What I'll provide is a general algorithm in the form of a bulleted list. The algorithm does not use of a DataTable (and potentially a SqlDataAdapter) because it is a performance hit because this way you can get to your List<> faster:

• Read from the database using your ORM of choice. For relational databases, I would use Dapper.
• Assuming a relational database, use IDataRecord.GetName() to obtain the returned field names.
• In the reader loop, read each record and use the field names to create expando objects or dictionary key-value pairs using the field name and the corresponding value.
• Use the reader's IDataRecord.GetFieldType() method to determine the data's data type if necessary.
• Finally, return your collection.

NOTE: The IDataReader interface inherits from IDataRecord, so the same reader object contains the functionality of both interfaces.