# Dynamic Data Table

## Dynamic?

Dyanmic Data Tables work similiarly to DataTables. The key difference being they are very loosely defined until the data is solved. 

* This allows for rows to be added anywhere without keeping up if that row is currently present. 
* Headers are automatically detected within the data set
* Column types are solved, and support solving mesh types within a column.
* Metrics are stored and calculated about each row and column.
* They can easily be converted to fully structurally defined DataTable's at any point.

### The example table

Let's take a look at a basic table with 3 columns, and 3 rows. <br>

| ID | Name  | Age |
| -- | ----- | --- |
| 1  | John  | 25  |
| 2  | Smith | 30  |
| 3  | Jane  | 29  |

To create this the standardized way using DataTable in the shortest possible way, here's how to recreate this table:

```csharp
DataTable DT = new DataTable();
DT.Columns.Add("ID", typeof(int));
DT.Columns.Add("Name", typeof(string));
DT.Columns.Add("Age", typeof(int));

DT.Rows.Add(1, "John", 25);
DT.Rows.Add(2, "Smith", 30);
DT.Rows.Add(3, "Jane", 29); 
```

Here is how you would do the same thing with a dynamic data table:

```csharp
DataTable DTDyn = new DynamicDataTable()
.AddRowValues(0u, "ID", "Name", "Age")
.AddRowValues(1, 1, "John", 25)
.AddRowValues(2, 1, "Smith", 30)
.AddRowValues(2, 1, "Jane", 29)
.ToDataTable();
```

### Data Typing

In the above example, notice what's different between the two? The Data Types are not present! This is one of the key differences and the reason behind the **Dyanmic** in the class name.&#x20;

We also do not have to worry about what rows we've added, or not added. They keep up with themselves.&#x20;

<mark style="color:blue;">**`DynamicDataTable`**</mark> supports several additional "smart" features that allow it to process and solve the data while processing it. This is a huge advantage when loading data that is not a pristine export.&#x20;

If you were to change row 3 in the first example you would generate an **`ArgumentException`**:

```csharp
DT.Rows.Add(3, "Jane", "29"); <-- ArgumentException
```

Changing row 3 in a <mark style="color:blue;">**`DynamicDataTable`**</mark> will convert that column into a string:

```csharp
.AddRowValues(2, 1, "Jane", "NA")
```

Data types will be auto solved after N(default 1000) rows are added and will then be readjusted as rows are added.&#x20;

{% hint style="success" %}
This is the secret sauce behind the [Excel Reader](https://docs.perigee.software/core-modules/file-formats/excel), it uses a <mark style="color:blue;">**`DynamicDataTable`**</mark> to solve and strip header content before load.
{% endhint %}

### Header solve

What happens when your data is not on the first row? This is another big problem when dealing with a client file. There may be rows above the actual table and we don't need that information.

```csharp
DynamicDataTable DTDyn = new DynamicDataTable()
.AddRowValues(0u, "My awesome file")
.AddRowValues(1, "Data as of:", "6/5/1920")
.AddRowValues(2, "ID", "Name", "Age")
.AddRowValues(3, 1, "John", 25)
.AddRowValues(4, 1, "Smith", 30)
.AddRowValues(5, 1, "Jane", "NA");

DTDyn.FinishDataLoad(); //Auto called on ToDataTable

uint HeaderRowIsAt = DTDyn.HeaderRow; //Value 2 (0 based)
```

You can see it automatically solved the header row after calling `.FinishDataLoad()`. The header, column data types, fill rates, data lengths and data type counts will all be solved and available.&#x20;

### Data Table methods

There are two primary methods for converting the dynamic table into a standard DataTable.&#x20;

The `.ToDataTable_ColumnsOnly()` version does not convert any data, only the schema of the table (the columns).

The `.ToDataTable()` version has two optional parameters for splicing the table. If left at default values, the whole table is converted and returned.

```csharp
DynamicDataTable.ToDataTable_ColumnsOnly()
DynamicDataTable.ToDataTable(inclusiveFromRow = -1, InclusiveToRow = -1)
```

### CSV

You can easily create a CSV out of the data by calling `.ToCSV()`. This is a handy method for quickly exporting data or debugging.

```csharp
DynamicDataTable DTDyn = new DynamicDataTable()
.AddRowValues(0u, "My awesome file")
.AddRowValues(1, "Data as of:", "6/5/1920")
.AddRowValues(2, "ID", "Name", "Age")
.AddRowValues(3, 1, "John", 25)
.AddRowValues(4, 1, "Smith", 30)
.AddRowValues(5, 1, "Jane", "NA");

var csv = DTDyn.ToCSV();

/*
ID,Name,Age
1,John,25
1,Smith,30
1,Jane,NA
*/
```

### Static Values

You may add static values to the data table, they will be appended to the end of the columns load set.

```csharp
DynamicDataTable DTDyn = new DynamicDataTable()
.AddStatic("BatchID", 1)

.AddRowValues(0u, "My awesome file")
.AddRowValues(1, "Data as of:", "6/5/1920")
.AddRowValues(2, "ID", "Name", "Age")
.AddRowValues(3, 1, "John", 25)
.AddRowValues(4, 1, "Smith", 30)
.AddRowValues(5, 1, "Jane", "NA");

var csv = DTDyn.ToCSV();

/*
ID,Name,Age,BatchID
1,John,25,1
1,Smith,30,1
1,Jane,NA,1
*/
```

### "Special" Columns&#x20;

The dynamic table comes with a few included "Special" columns that can be automatically added during the load.&#x20;

```csharp
DynamicDataTable DTDyn = new DynamicDataTable()
.SetSpecialColumns(DDTSpecialColumns.RowIndex)

.AddRowValues(0u, "My awesome file")
.AddRowValues(1, "Data as of:", "6/5/1920")
.AddRowValues(2, "ID", "Name", "Age")
.AddRowValues(3, 1, "John", 25)
.AddRowValues(4, 1, "Smith", 30)
.AddRowValues(5, 1, "Jane", "NA");

var csv = DTDyn.ToCSV();

/*
ID,Name,Age,DDT_RowIndex
1,John,25,3
1,Smith,30,4
1,Jane,NA,5
*/
```

### Data Metrics

To retrieve data statistics after the call to `.FinishDataLoad()`, you can easily use `.GetStatistics()` to retrieve a data statistics class with information like:

* Column names,
* Types
* Fill rates
* Unpopulated row counts
* Jagged counts
* Row counts

This is a helpful method for post load data analysis.

```csharp
DynamicDataTable DTDyn = new DynamicDataTable()
.AddRowValues(0u, "My awesome file")
.AddRowValues(1, "Data as of:", "6/5/1920")
.AddRowValues(2, "ID", "Name", "Age", "Notes")
.AddRowValues(3, 1, "John", "", "")
.AddRowValues(4, 1, "Smith", 30, "", "what?")
.AddRowValues(5, 1, "Jane", "", "");

DTDyn.FinishDataLoad();
var stats = DTDyn.GetStatistics();
foreach (var namekvp in stats.ColumnNames)
{
    Console.WriteLine($"[{namekvp.Key}] {namekvp.Value}({stats.ColumnTypes[namekvp.Key].Name}) Fill: {(stats.FillRate[namekvp.Key]*100.0m):N2}%");
}
```