Published November 15, 2023
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:
- Using LINQ (Language Integrated Query)
- 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 SubNote 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 SubIn 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 ClassExpanding 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 FunctionThis 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.
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.
