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.

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:

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:

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.

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

DynamicDataTable 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.

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

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

Changing row 3 in a DynamicDataTable will convert that column into a string:

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

This is the secret sauce behind the Excel Reader, it uses a DynamicDataTable to solve and strip header content before load.

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.

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.

Data Table methods

There are two primary methods for converting the dynamic table into a standard DataTable.

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.

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.

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.

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

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

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.

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