Efficient Methods for Converting DataSet to List in C#

Introduction

In C# development, there is often a need to convert ADO.NET DataSet or DataTable objects to strongly-typed List collections. Traditional approaches using foreach loops are functional but verbose. Modern C# offers more elegant solutions.

Analysis of Traditional Conversion Methods

The original code uses a loop-based approach to create Employee object lists:

Employee objEmp = new Employee();
List<Employee> empList = new List<Employee>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
    empList.Add(new Employee { Name = Convert.ToString(dr["Name"]), Age = Convert.ToInt32(dr["Age"]) });
}

While this method is straightforward, it suffers from code duplication and high maintenance costs.

LINQ Optimization Solution

Using LINQ's AsEnumerable() and Select() methods significantly simplifies the code:

var empList = ds.Tables[0].AsEnumerable()
    .Select(dataRow => new Employee
    {
        Name = dataRow.Field<string>("Name"),
        Age = dataRow.Field<int>("Age")
    }).ToList();

This approach offers several advantages:

• Concise code that completes conversion in one line
• Strongly-typed Field<T> method prevents type conversion errors
• LINQ provides better readability and maintainability

Extension Method Approach

For scenarios requiring frequent conversions, create extension methods:

public static class DataTableExtensions
{
    public static List<T> ToList<T>(this DataTable table) where T : new()
    {
        return table.AsEnumerable()
            .Select(row => 
            {
                var item = new T();
                var properties = typeof(T).GetProperties();
                
                foreach (var prop in properties)
                {
                    if (table.Columns.Contains(prop.Name))
                    {
                        var value = row[prop.Name];
                        if (value != DBNull.Value)
                        {
                            prop.SetValue(item, value);
                        }
                    }
                }
                return item;
            }).ToList();
    }
}

Usage:

List<Employee> empList = ds.Tables[0].ToList<Employee>();

Performance Considerations

Performance tests from reference articles indicate that built-in methods are faster than manual loops. In C#, LINQ methods are optimized and perform well in most scenarios.

Best Practices

• Prefer LINQ methods for cleaner code
• For complex mappings, consider tools like AutoMapper
• Handle null values using nullable versions of Field<T> method
• Ensure database column names match class property names

Conclusion

Using LINQ's AsEnumerable() and Select() methods enables efficient DataSet to List conversion, improving code quality and development efficiency. Extension methods offer better reusability for enterprise-level application development.