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

Creating a Method to Convert DataTable to a List of Objects

To get started, let’s create a generic method that will take a DataTable and convert it into a List<T>, where T can be any object type. Here’s a step-by-step guide to implementing this solution.

Step 1. Define the Conversion Method

This method will use JSON serialization and deserialization to map DataTable directly into a list of objects. This approach is efficient and easy to maintain.// Generic Method to Convert DataTable to List of Objects

public static List<T> ToConvertList<T>(this DataTable datatable) where T : new()
{
    List<T> tempList = new List<T>();
    try
    {
        // Serialize the DataTable to JSON and then Deserialize to List<T>
        var json = JsonConvert.SerializeObject(datatable, Formatting.Indented,
                    new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });
        tempList = JsonConvert.DeserializeObject<List<T>>(json);
    }
    catch (Exception ex)
    {
        // Log or handle the error if necessary
        Console.WriteLine("Error converting DataTable to List: " + ex.Message);
    }

    return tempList;
}

Explanation of the Code

1. Serialization and Deserialization

   • First, we serialize the DataTable into JSON format. This allows us to retain the structure and data in a format that can be easily converted into any object type.
   • Next, we deserialize the JSON into a List<T>, where T is the type of object we want.

2. Error Handling

   • We wrap the code in a try-catch block to handle any unexpected issues during serialization or deserialization. If there’s an error, it’s logged, and an empty list is returned instead of failing entirely.

3. Return Value

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

Step 2. Using the Conversion Method in Your Code

Let’s say we have a User class, and we want to convert our DataTable to a list of User objects. Here’s how you can use the ToConvertList method:

private List<User> GetUserList()
{
    string sql = @"Your_query_here"; // Replace with your actual SQL query
    DataTable dt = yourService.getDataTable(sql);

    // Convert DataTable to List<User>
    List<User> users = dt.ToConvertList<User>();
    return users;
}

Example of the User Class

public class User
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    // Add other properties as needed
}

Practical Tips and Common Pitfalls

• Null Handling: By using the NullValueHandling.Ignore setting in the JsonConvert.SerializeObject method, we ignore any null values in the DataTable, ensuring clean conversion.
• Performance Considerations: For larger DataTable Objects consider alternative mapping techniques if performance becomes an issue. JSON serialization is convenient but may be slower with extremely large datasets.
• Schema Matching: Ensure that the column names DataTable match the property names in your object class. Otherwise, the JSON conversion may fail or produce unexpected results.

Conclusion

This method provides an easy and flexible way to convert a DataTable to a list of custom objects in C#. It’s particularly useful for developers working with ADO.NET and who often need to map database data to custom classes. Give this method a try in your projects, and see how it simplifies your data handling in C#.