DataTable in C# – Usage And 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? Let’s weather the storm together and emerge as DataTable champions!

Introduction: What Is DataTable

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 created a new DataTable!

Delving Into the Creation of a DataTable

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

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

DataTable table = new DataTable("Customers");

We’ve cooked up a new table called “Customers”. Who said programming can’t be fun!

Add Column to DataTable

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

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

Adding DataRow to DataTable

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

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

With our nifty hands, 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

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

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

What’s better than a victory lap? A lap around your DataTable!

Comparing DataTable’s Column Values

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

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

Abracadabra! We have the rows with Order Amount greater than 100.

Sorting DataTable

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

Just like magic, all our data is sorted in descending order of Order Amount!

Filtering and List Conversion

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

Filtering DataTable

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

DataRow[] result = table.Select("OrderAmount > 100 AND OrderAmount < 200");

We’ve just caught all rows with Order Amount between 100 and 200!

List DataTable

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

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

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

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

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

Hocus pocus, our DataTable is now in Excel!

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

Abracadabra! 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);

Boom! We just turned a DataReader into a DataTable!

Using AsEnumerable and Select with DataTable

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

DataTable AsEnumerable Select

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 organize 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!