DataTable in C# – Usage And Examples 🧪
Tags: datatable, csharp, promptengineering, tutorial
Published: 2024-06-18 10:01:49

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.

```csharp
// 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.

```csharp
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?

```csharp
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:

```csharp
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?

```csharp
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:

```csharp
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!

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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!

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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!