C# Datatable to List (How it Works For Developers)

Converting DataTable to List in C#

Often, in the realm of programming with C#, there arises a need to convert a DataTable into a list. While many novices stumble upon this task, they are often met with answers that are just not comprehensive enough. This tutorial aims to bridge that gap and provide a clear guide on how to convert a DataTable to a list in C#.

What is a DataTable?

Before diving into the conversion process, it's crucial to understand what a DataTable is. In C#, a DataTable object is a representation of an in-memory database table with rows and columns. It's part of the System.Data namespace.

For the sake of this tutorial, let's use a sample DataTable named dt. This can be visualized as shown below:

using System.Data;

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Category", typeof(string));
dt.Rows.Add(1, "Electronics");
dt.Rows.Add(2, "Books");

using System.Data;

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Category", typeof(string));
dt.Rows.Add(1, "Electronics");
dt.Rows.Add(2, "Books");

Imports System.Data

Private dt As New DataTable()
dt.Columns.Add("ID", GetType(Integer))
dt.Columns.Add("Category", GetType(String))
dt.Rows.Add(1, "Electronics")
dt.Rows.Add(2, "Books")

Getting Started with Conversion

So, you've got your DataTable dt, and now you're staring at it thinking, "How do I convert this?". Don't worry; that's a question that shows research effort. There are primarily two methods to convert DataTable to a list:

1. Using LINQ (Language Integrated Query)
2. Using the classic foreach loop

Conversion Using LINQ

The LINQ method is a powerful tool in C# that provides a way to query collections in a declarative manner. Let's take a look at how this can be done.

Define a method as follows:

using System.Linq;
using System.Collections.Generic;

private static List<dynamic> LinqMethod(DataTable dt)
{
    return dt.AsEnumerable().Select(row =>
        new 
        {
            ID = row.Field<int>("ID"),
            Category = row.Field<string>("Category")
        }).ToList();
}

using System.Linq;
using System.Collections.Generic;

private static List<dynamic> LinqMethod(DataTable dt)
{
    return dt.AsEnumerable().Select(row =>
        new 
        {
            ID = row.Field<int>("ID"),
            Category = row.Field<string>("Category")
        }).ToList();
}

'INSTANT VB NOTE: 'Option Strict Off' is used here since dynamic typing is used:
Option Strict Off

Imports System.Linq
Imports System.Collections.Generic

'INSTANT VB NOTE: In the following line, Instant VB substituted 'Object' for 'dynamic' - this will work in VB with Option Strict Off:
Private Shared Function LinqMethod(ByVal dt As DataTable) As List(Of Object)
	Return dt.AsEnumerable().Select(Function(row) New With {
		Key .ID = row.Field(Of Integer)("ID"),
		Key .Category = row.Field(Of String)("Category")
	}).ToList()
End Function

In the above code, the extension method AsEnumerable() is called on the DataTable dt. This allows us to use LINQ on each DataRow in the DataTable. The method creates a list of dynamic objects, each representing a row from the DataTable.

Conversion Using foreach Loop

The foreach loop is a tried and tested way to iterate over collections in C#. This method might seem a tad longer, but it's easy to understand and implement.

Here's how it works:

private static List<Category> ForeachMethod(DataTable dt)
{
    List<Category> list = new List<Category>();

    // Iterates through each row within the data table
    foreach (DataRow row in dt.Rows)
    {
        var category = new Category
        {
            ID = Convert.ToInt32(row["ID"]),
            Name = row["Category"].ToString()
        };
        list.Add(category);
    }
    return list;
}

public class Category
{
    public int ID { get; set; }
    public string Name { get; set; }
}

private static List<Category> ForeachMethod(DataTable dt)
{
    List<Category> list = new List<Category>();

    // Iterates through each row within the data table
    foreach (DataRow row in dt.Rows)
    {
        var category = new Category
        {
            ID = Convert.ToInt32(row["ID"]),
            Name = row["Category"].ToString()
        };
        list.Add(category);
    }
    return list;
}

public class Category
{
    public int ID { get; set; }
    public string Name { get; set; }
}

Private Shared Function ForeachMethod(ByVal dt As DataTable) As List(Of Category)
	Dim list As New List(Of Category)()

	' Iterates through each row within the data table
	For Each row As DataRow In dt.Rows
		Dim category As New Category With {
			.ID = Convert.ToInt32(row("ID")),
			.Name = row("Category").ToString()
		}
		list.Add(category)
	Next row
	Return list
End Function

Public Class Category
	Public Property ID() As Integer
	Public Property Name() As String
End Class

In the ForeachMethod method, the DataTable is iterated over using a foreach loop. For each DataRow, a new Category object is instantiated and added to the list.

Expanding on Advanced Conversion Techniques

After mastering the basics of converting a DataTable to a list in C#, there are several advanced techniques and considerations that can optimize this process and adapt it to more complex scenarios. Let's delve deeper into some of these techniques.

Using Reflection to Convert DataTable to List

Reflection is a powerful tool in C# that allows you to inspect the metadata of types at runtime. Let's harness its power:

using System.Reflection;

private static List<T> ConvertDataTableToList<T>(DataTable dt) where T : new()
{
    List<T> list = new List<T>();

    foreach (DataRow row in dt.Rows)
    {
        T obj = new T();
        foreach (DataColumn col in dt.Columns)
        {
            var prop = obj.GetType().GetProperty(col.ColumnName);
            if (prop != null && row[col] != DBNull.Value)
                prop.SetValue(obj, row[col]);
        }
        list.Add(obj);
    }
    return list;
}

using System.Reflection;

private static List<T> ConvertDataTableToList<T>(DataTable dt) where T : new()
{
    List<T> list = new List<T>();

    foreach (DataRow row in dt.Rows)
    {
        T obj = new T();
        foreach (DataColumn col in dt.Columns)
        {
            var prop = obj.GetType().GetProperty(col.ColumnName);
            if (prop != null && row[col] != DBNull.Value)
                prop.SetValue(obj, row[col]);
        }
        list.Add(obj);
    }
    return list;
}

Imports System.Reflection

Private Shared Function ConvertDataTableToList(Of T As New)(ByVal dt As DataTable) As List(Of T)
	Dim list As New List(Of T)()

	For Each row As DataRow In dt.Rows
		Dim obj As New T()
		For Each col As DataColumn In dt.Columns
			Dim prop = obj.GetType().GetProperty(col.ColumnName)
			If prop IsNot Nothing AndAlso row(col) IsNot DBNull.Value Then
				prop.SetValue(obj, row(col))
			End If
		Next col
		list.Add(obj)
	Next row
	Return list
End Function

This ConvertDataTableToList method employs reflection, iterating over each DataRow and column in the DataTable. For each column, it searches for a matching property in the generic object and sets its value. This approach allows for a highly reusable method that can convert any DataTable to a list of generic objects.

Usage

To use the above code, simply call the method by specifying the type:

List<Category> categories = ConvertDataTableToList<Category>(dt);

List<Category> categories = ConvertDataTableToList<Category>(dt);

Dim categories As List(Of Category) = ConvertDataTableToList(Of Category)(dt)

With this method, you're no longer confined to converting specific data tables to specific object types. Instead, you have a versatile tool at your disposal that can handle a variety of data scenarios.

Performance Considerations

While the reflection method is mighty, it's worth noting that it can be slower, especially with large data tables. It's always crucial to measure performance and weigh it against the benefits of code reusability and maintainability.

Iron Suite Toolkit for .NET Developers

While we've delved into the intricacies of converting DataTable to lists in C#, sometimes, relying on external tools can simplify our development process, especially when it comes to more complex operations. That's where Iron Suite comes into play.

IronPDF: The PDF Powerhouse

When it comes to working with PDFs in C#, IronPDF is a game-changer. Imagine having converted your DataTable to a list and then needing to generate a PDF report from it. IronPDF can effortlessly create, edit, and extract data from PDF documents, streamlining the process of translating your data table-derived information into professional-looking reports.

IronPDF’s main feature is its HTML to PDF functionality, ensuring layouts and styles are preserved. It generates PDFs from web content, suitable for reports, invoices, and documentation. You can convert HTML files, URLs, and HTML strings to PDF files effortlessly.

using IronPdf;

class Program
{
    static void Main(string[] args)
    {
        var renderer = new ChromePdfRenderer();

        // 1. Convert HTML String to PDF
        var htmlContent = "<h1>Hello, IronPDF!</h1><p>This is a PDF from an HTML string.</p>";
        var pdfFromHtmlString = renderer.RenderHtmlAsPdf(htmlContent);
        pdfFromHtmlString.SaveAs("HTMLStringToPDF.pdf");

        // 2. Convert HTML File to PDF
        var htmlFilePath = "path_to_your_html_file.html"; // Specify the path to your HTML file
        var pdfFromHtmlFile = renderer.RenderHtmlFileAsPdf(htmlFilePath);
        pdfFromHtmlFile.SaveAs("HTMLFileToPDF.pdf");

        // 3. Convert URL to PDF
        var url = "http://ironpdf.com"; // Specify the URL
        var pdfFromUrl = renderer.RenderUrlAsPdf(url);
        pdfFromUrl.SaveAs("URLToPDF.pdf");
    }
}

using IronPdf;

class Program
{
    static void Main(string[] args)
    {
        var renderer = new ChromePdfRenderer();

        // 1. Convert HTML String to PDF
        var htmlContent = "<h1>Hello, IronPDF!</h1><p>This is a PDF from an HTML string.</p>";
        var pdfFromHtmlString = renderer.RenderHtmlAsPdf(htmlContent);
        pdfFromHtmlString.SaveAs("HTMLStringToPDF.pdf");

        // 2. Convert HTML File to PDF
        var htmlFilePath = "path_to_your_html_file.html"; // Specify the path to your HTML file
        var pdfFromHtmlFile = renderer.RenderHtmlFileAsPdf(htmlFilePath);
        pdfFromHtmlFile.SaveAs("HTMLFileToPDF.pdf");

        // 3. Convert URL to PDF
        var url = "http://ironpdf.com"; // Specify the URL
        var pdfFromUrl = renderer.RenderUrlAsPdf(url);
        pdfFromUrl.SaveAs("URLToPDF.pdf");
    }
}

Imports IronPdf

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		Dim renderer = New ChromePdfRenderer()

		' 1. Convert HTML String to PDF
		Dim htmlContent = "<h1>Hello, IronPDF!</h1><p>This is a PDF from an HTML string.</p>"
		Dim pdfFromHtmlString = renderer.RenderHtmlAsPdf(htmlContent)
		pdfFromHtmlString.SaveAs("HTMLStringToPDF.pdf")

		' 2. Convert HTML File to PDF
		Dim htmlFilePath = "path_to_your_html_file.html" ' Specify the path to your HTML file
		Dim pdfFromHtmlFile = renderer.RenderHtmlFileAsPdf(htmlFilePath)
		pdfFromHtmlFile.SaveAs("HTMLFileToPDF.pdf")

		' 3. Convert URL to PDF
		Dim url = "http://ironpdf.com" ' Specify the URL
		Dim pdfFromUrl = renderer.RenderUrlAsPdf(url)
		pdfFromUrl.SaveAs("URLToPDF.pdf")
	End Sub
End Class

IronXL: Excelling in Excel Operations

If your DataTable conversion leads to a need for Excel-related tasks, IronXL is the tool to turn to. This product provides seamless operations for reading, editing, and creating Excel spreadsheets. With the data table-to-list conversion in hand, exporting your data to an Excel format becomes incredibly straightforward with IronXL.

IronOCR: Making Text Recognizable

There might be times when your DataTable consists of image-based data, or you need to extract text from images. This is where IronOCR shines. It allows .NET developers to read text from images, making it a complementary tool if your DataTable conversion operations involve images containing textual information.

IronBarcode: Reading Between the Lines

Finally, IronBarcode is the go-to tool for any barcode operations in your applications. Suppose your DataTable or the list you've converted it to consists of product information with barcodes. In that case, IronBarcode provides an efficient mechanism to read and generate barcodes, bridging the gap between raw product data and scannable barcode information.

Conclusion

While the manual methods of manipulating and converting DataTable are crucial for any C# developer, integrating powerful tools like the ones provided by Iron Suite can exponentially enhance your productivity and capabilities. It's noteworthy that each product license starts from $749, and what's even more appealing is that every product offers a free trial. If you're contemplating investing in these tools, there's an enticing offer on the table: you can acquire the entire Iron Suite for the price of just two products. Embracing such comprehensive solutions can undoubtedly elevate the quality and efficiency of your .NET development endeavors.