Article Areas

Wise Words

"We are what we repeatedly do. Excellence, then, is not an act, but a habit."

- Aristotle

Programmatically create data tables

I seldom recommend using raw Data Tables or Data Sets for data binding purposes - I always say create custom business classes and collections and move your data set data over to them instead.

It will make your code easier to read, understand and use (safe access to properties instead of data table["colname"]). Also it's easier to extend - meaning adding custom business functions such as calculations etc.

I do however confess that a data table has some features a regular class can't have: it support dynamic fields - and while it might sound strange to need such a thing I recently encountered such an scenario where I had to get some old data from historic databases and "merge" with their live database:

Simplified example - imagine the live database holds a table CUSTOMER with the following fields:

Customer_id Customer_Name Loan_Amount

Now there are a lot of historic databases which also has the same table and what you want to present is a data grid where each customer is a row and each databases Loan_Amount is presented as their own column.

Here's how I did it - the problem was that I didn't know on forehand which historic databases was to be used - i.e. the user could select which (meaning they decide the columns I need to show = the columns need to be dynamic).

So forget the GUI stuff - somehow I create an array of strings containing the selected databases and that's what I send into the RunReport function:

public void RunReport(string []SelectedDatabases)
{
DataTable oTab3 = new DataTable();
oTab3.Columns.Add("Customer_id", System.Type.GetType("System.Int32") );
oTab3.Columns.Add("Customer_Name", System.Type.GetType("System.String"));
//For each selected database
foreach( string sDBName in SelectedDatabases )
{
RunDatabase( sDBName, oTab3);
}
dlGrid.DataSource = oTab3;
dlGrid.DataBind();
}

Nothing magical here - we create a DataTable, add some columns to it and lastly use that as data source for a grid. Lets look into the RunDatabase - which is called once for each database:

private void RunDatabase( string sDatabase, DataTable dt )
{
//Fake function - not explained but it receives the date from
//when the database backup was taken
DateTime dtWhen = GetDateFromDatabase(sDatabase);

//construct a unique name for new new column we are about to add
//for example LoanAmount051218
string sColumn = "Loan_Amount" + dtWhen.ToString("yyMMdd");

//Add the column
dt.Columns.Add(sColumn, System.Type.GetType("System.Double"));

//Get all customers from this database
DataSet dsCustomersInThisDB = DBHelper.Run( sDatabase, "select * from customer");
foreach(DataRow rowCust in dsCustomersInThisDB.Tables[0].Rows )
{
//Does it already exist such a row?
DataRow oThisRow = null;
foreach(DataRow rowExisting in dt.Rows )
{
if ( rowExisting["customer_id"].ToString() == rowCust["customer_id"].ToString() )
{
oThisRow = rowExisting;
break;
}
}
if ( rowExisting == null )
{
//Not existing need to create row
oThisRow = dt.NewRow();
//and insert base data as well
oThisRow["customer_id"] = Convert.ToInt32(rowCust["customer_id"]);
oThisRow["customer_name"] = rowCust["customer_name"].ToString();
}
//Update the special column
oThisRow[sColumn] = Convert.ToInt32(rowCust["loan_amount"]);
}
}

Here we call a special function GetDateFromDatabase which I wont get into but I used it to retrieve the date for this specific database. We use that date to create a new column in the resulting data table - and I name the columns ending with the date. This means that in the end we will end up with a data table with columns like :

customer_id
customer_name
LoanAmount_060512
LoanAmount_060212
LoanAmount_051211

So to sum it up - this was an example to show you both HOW to create a datatable using code - but also to show you one way of how you can use it to kind turn your data from rows into columns.