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

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 DataTable to 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 DataTable dt. DataTable dt can be visualized as below code example:

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");

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");

Dim 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 like below code example:

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

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

Private Shared Sub LinqMethod(ByVal dt As DataTable)
	Dim list = dt.AsEnumerable().Select(Function(row) New With {
		Key .ID = row.Field(Of Integer)("ID"),
		Key .Category = row.Field(Of String)("Category")
	}).ToList()
End Sub

Note the use of var list, var row, and the linqmethod. In the above code, the extension method AsEnumerable() is called on the DataTable dt. This allows us to use LINQ on each DataRow row in the DataTable.

Conversion using Foreach Loop

The foreach loop is the 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.

Let's start:

private static void 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();
        category.ID = Convert.ToInt32(row ["ID"]);
        category.Name = row ["Category"].ToString();
        list.Add(category);
    }
}

private static void 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();
        category.ID = Convert.ToInt32(row ["ID"]);
        category.Name = row ["Category"].ToString();
        list.Add(category);
    }
}

Private Shared Sub ForeachMethod(ByVal dt As DataTable)
	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()
		category.ID = Convert.ToInt32(row ("ID"))
		category.Name = row ("Category").ToString()
		list.Add(category)
	Next row
End Sub

In the private static void ForeachMethod() method, the DataTable is iterated over using a foreach loop. For each datarow row, a new var category object is instantiated and added to the var list.

To represent the Category, you'll need a class:

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

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

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

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.

One of the limitations of the previously discussed methods is that they're specific to our Category class. What if you could write a method to convert any DataTable to a list of generic objects?

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

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;
}

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;
}

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 private static method employs reflection, iterating over each DataRow row and column in the DataTable dt. For each column, it searches for a matching property (var prop) 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.

Iron Suite is a comprehensive suite of tools designed to make .NET developers' lives easier. From PDF operations and Excel manipulations to Optical Character Recognition (OCR) and barcode reading, Iron Suite offers a myriad of capabilities. Let's break down the components of Iron Suite and see how they can complement our DataTable operations.

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.