How to Convert a DataTable to a List of Objects in C#

Step 1. Define the Conversion Method

The generic method will accept a DataTable and convert it into a List<T>, where T represents any object type.

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;

public static class DataTableExtensions
{
    public static List<T> ToListOfObjects<T>(this DataTable dataTable) where T : new()
    {
        List<T> resultList = new List<T>();

        try
        {
            // Serialize DataTable to JSON and then Deserialize to List<T>
            var json = JsonConvert.SerializeObject(dataTable, Formatting.Indented, 
                        new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });
            resultList = JsonConvert.DeserializeObject<List<T>>(json);
        }
        catch (Exception ex)
        {
            // Handle errors
            Console.WriteLine("Error during DataTable to List conversion: " + ex.Message);
        }

        return resultList;
    }
}

The conversion method will allow you to convert any DataTable into a List<T>, where T can be any object type.

In this example:

• Serialization and Deserialization:

  • We first serialize the DataTable into JSON format.
  • Then, we deserialize the JSON into a List<T>, where T is the object type we wish to use.

• Error Handling:

  • The conversion process is wrapped in a try-catch block to ensure we handle any issues that might arise during the serialization or deserialization process. If an error occurs, it logs the exception and returns an empty list.

• Return Value:

  • The method returns a List<T> containing objects of type T, each representing a row in the original DataTable.

Step 2. Using the Conversion Method

For example, let's assume we have a Product class, and we want to convert our DataTable to a list of Product objects.

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public decimal Price { get; set; }
}

public List<Product> GetProducts()
{
    string sql = "SELECT * FROM Products"; // Your SQL query
    DataTable dataTable = GetDataTable(sql); // Assume GetDataTable() gets a DataTable from your database

    // Convert DataTable to List<Product>
    List<Product> products = dataTable.ToListOfObjects<Product>();
    return products;
}

public DataTable GetDataTable(string sqlQuery)
{
    // Example code to retrieve DataTable from a database (ADO.NET)
    DataTable dataTable = new DataTable();

    using (SqlConnection connection = new SqlConnection("YourConnectionString"))
    {
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connection);
        adapter.Fill(dataTable);
    }

    return dataTable;
}

Practical Considerations

1. Null Handling:

   • The NullValueHandling.Ignore setting in JsonConvert.SerializeObject ensures that null values in the DataTable are ignored, resulting in cleaner conversion.

2. Performance:

   • While JSON serialization is a convenient method, it can be slower for very large DataTable objects. In such cases, consider using alternative mapping techniques that might provide better performance.

3. Schema Matching:

   • Ensure that the column names in the DataTable match the property names in the target object class (T). If they don’t match, the JSON conversion might fail or produce incorrect results.

This method provides a straightforward and flexible way to convert a DataTable into a list of objects in C#. It’s particularly useful for developers working with ADO.NET or those who frequently need to map data from a database into custom classes.