DataTable in C# — Usage + Examples 🧪

DataTables are an essential part of data handling in C#, but they can be quite tricky for beginners as well as those who haven’t used them often. So, ready to unravel the mysteries of C# DataTables?

Introduction: What Is DataTable in C#

DataTable, in C#, is like a mini-database living in your computer’s memory, representing a single table of in-memory data. Isn’t it exciting to imagine a whole database in such a small space?

Understanding C# DataTable and Its Core Functionality

DataTable is part of ADO.NET, the data access model from Microsoft used in .NET framework. It represents a single table of data with a collection of columns and rows. DataTable can be standalone or part of a DataSet, which can hold multiple DataTable objects.

// Create a new DataTable
DataTable dt = new DataTable();

Look, we just birthed a new DataTable! Pretty simple, eh?

Delving Into the Creation of a DataTable in C#

Let’s take a deeper dive into the creation process of a DataTable. It’s just like baking, but instead of flour and eggs, we’re using columns and rows.

How to Create a DataTable using C#

Creating a DataTable involves declaring a new instance of the DataTable class.

DataTable table = new DataTable("Customers");

Add Column to DataTable in C#

Having a table is great but what’s a table without its columns?

table.Columns.Add("CustomerID", typeof(int));
table.Columns.Add("CustomerName", typeof(string));

Like a proud chef, we’ve just added two columns to our Customers table!

Populating Your DataTable in C#

Creating a table and adding columns is just the beginning. We’ll now add the lifeblood of a table — data!

Adding DataRow to DataTable in C#

DataRow represents a single row in the DataTable. Here’s how to add them in C#:

DataRow row = table.NewRow();
row["CustomerID"] = 1;
row["CustomerName"] = "John Doe";
table.Rows.Add(row);

With these few lines of code, our Customer table has its first row of data!

DataTable and Row Addition: Building a C# DataTable from Scratch

Building a DataTable from scratch gives us an empty canvas to work with. Create the table, define the columns, and add rows to it. It’s like giving birth to a new table, isn’t it?

DataTable table = new DataTable("Orders");
table.Columns.Add("OrderID", typeof(int));
table.Columns.Add("OrderAmount", typeof(decimal));
DataRow row = table.NewRow();
row["OrderID"] = 101;
row["OrderAmount"] = 150.75m;
table.Rows.Add(row);

We’ve made a new Orders table with one row of data! Isn’t C# amazing?

Learn to Manipulate DataTable in C#: From Simple to Complex Operations

Now that our DataTable is alive with data, let’s learn to play with it. A skillful magician never reveals his secrets, but here, we break the rules!

Getting Column Value from DataTable in C#

Accessing column values from a DataRow is similar to accessing values from an array or a dictionary. See this neat trick:

DataRow row = table.Rows[0];
int orderId = row.Field<int>("OrderID");
decimal amount = row.Field<decimal>("OrderAmount");

You know the feeling when you crack the secret code? This is it!

Iterating through DataTable in C#

Running around the DataTable in circles, or in programming terms, iterating over the DataTable, is like a fun sport!

foreach (DataRow row in table.Rows){
  int orderId = row.Field<int>("OrderID");
  decimal amount = row.Field<decimal>("OrderAmount");
}

Comparing DataTable’s Column Values in C#

Often, you’ll need to compare DataTable column values. It’s like a little detective game, where variables are our clues.

Comparing Two DataTable Column Values in C#: A Practical Guide

Let’s find out how to compare column values from two different DataTables.

bool areEquals = dataTable1.AsEnumerable()
                    .SequenceEqual(dataTable2.AsEnumerable(), DataRowComparer.Default);

Ah, the joy of finding the missing piece of the puzzle!

Select and Sort Operations in C#

The ability to select or sort data is like a powerful magic spell that every C# programmer must learn!

C# Select from DataTable

Select operation is similar to a SQL SELECT query.

DataRow[] selectedRows = table.Select("OrderAmount > 100");

We have the rows with Order Amount greater than 100.

Sorting DataTable in C#

Sorting is significant when dealing with a large amount of data. It’s like arranging your stuff in order.

table.DefaultView.Sort = "OrderAmount DESC";
table = table.DefaultView.ToTable();

All our data is sorted in descending order of Order Amount!

Filtering and List Conversion in C#

Enough chit-chat, let’s dive into the serious stuff — filtering data and converting DataTable to lists!

Filtering DataTable in C#

Filtering is essential when working with massive datasets. It’s like going fishing with a good net.

List DataTable in C#

Listing a DataTable allows us to handle and manipulate the data efficiently.

List<DataRow> list = table.AsEnumerable().ToList();

It’s like neatly stacking your records in separate drawers.

Convert DataTable to List in C#

Converting a DataTable to a List gives us more methods and features for manipulating our data.

var list = table.AsEnumerable().Select(row => new Customer {
  CustomerId = row.Field<int>("CustomerID"),
  CustomerName = row.Field<string>("CustomerName")
}).ToList();

Our DataTable is now a List of Customers.

Creating a DataTable from C# List

Creating a DataTable from a List is like reverse engineering!

DataTable newDt = new DataTable();
newDt.Columns.AddRange(new DataColumn[2] {
                   new DataColumn("CustomerId", typeof(int)),
                   new DataColumn("Name", typeof(string))});

foreach (var item in list)
{
    var row = newDt.NewRow();
    row["CustomerId"] = item.CustomerId;
    row["Name"] = item.Name;
    newDt.Rows.Add(row);
}

Surprise! We’ve turned our Customers List back into a DataTable!

Exporting DataTable into Different Formats in C#

Let’s find out how to export our DataTable into various formats. Imagine you’re a magician, and your DataTable is your magic hat!

DataTable to CSV Conversion in C#

Converting a DataTable to a CSV file is like translating your thoughts into another language.

StringBuilder sb = new StringBuilder();
string[] columnNames = dt.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray();
sb.AppendLine(string.Join(",", columnNames));
foreach (DataRow row in dt.Rows)
{
    string[] fields = row.ItemArray.Select(field => field.ToString()).ToArray();
    sb.AppendLine(string.Join(",", fields));
}
File.WriteAllText("path_to_your_csv_file.csv", sb.ToString());

Viola! Your data can now speak CSV!

How to Export C# DataTable to Excel

Exporting DataTable to Excel can be easily achieved using libraries such as EPPlus or NPOI.

using (var excelFile = new ExcelPackage())
{
  var worksheet = excelFile.Workbook.Worksheets.Add("Sheet1");
  worksheet.Cells["A1"].LoadFromDataTable(table, true);
  excelFile.SaveAs(new FileInfo("path_to_excel_file.xlsx"));
}

C# DataTable to PDF

The conversion of DataTable to PDF requires third-party libraries like iTextSharp or SelectPdf.

Document document = new Document();
PdfWriter writer = PdfWriter.GetInstance(document, new FileStream("data.pdf", FileMode.Create));
document.Open();
PdfPTable pdfTable = new PdfPTable(dt.Columns.Count);
for (int i = 0; i < dt.Columns.Count; i++) {
  pdfTable.AddCell(new Phrase(dt.Columns[i].ColumnName));
}
for (int i = 0; i < dt.Rows.Count; i++) {
  for (int j = 0; j < dt.Columns.Count; j++) {
    pdfTable.AddCell(new Phrase(dt.Rows[i][j].ToString()));
  }
}
document.Add(pdfTable);
document.Close();

Our DataTable is now a PDF!

DataReader to DataTable Conversion

Converting a data reader to a DataTable comes handy when we need to manipulate object data using DataTables.

C#: From DataReader to DataTable with Minimum Effort

A DataReader provides a forward-only cursor for reading rows from a SQL Server database, DataTable makes the data manipulation easier.

SqlDataReader reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);

We just turned a DataReader into a DataTable!

Using AsEnumerable and Select with DataTable in C#

DataTable’s AsEnumerable extension method provides a powerful way to query data using LINQ, similar to querying a collection of entities.

DataTable AsEnumerable Select in C#

Let’s understand how AsEnumerable and Select all tie up.

var result = from row in table.AsEnumerable()
  where row.Field<int>("CustomerID") == 1
  select row;

No more messing around, eh? We’ve just selected all rows where CustomerID equals 1!

In a nutshell, DataTable allows you to organise and manipulate data in a tabular format, be it creating DataTable, adding rows, fetching column values, sorting, selecting or even exporting to various formats like CSV, Excel or PDF. I hope that this thorough guide to DataTable in C# has advanced your skills in C#. Don’t forget, practice makes perfect. Happy coding!