Build a DataTable in Code
There may be times you need to build a DataTable using code instead of retrieving the data from a database.
This post provides the code for building a DataTable using VB or C# code.
In C#:
DataTable dt = new DataTable("Customers");
DataColumn dc;
dc = new DataColumn();
dc.DataType = typeof(int);
dc.ColumnName = "CustomerID";
dt.Columns.Add(dc);
dt.Columns.Add(new DataColumn("LastName"));
dt.Columns.Add(new DataColumn("FirstName"));
// Concatenation of first and last names
dt.Columns.Add(new DataColumn("FullName"));
dt.Columns.Add(new DataColumn("Address"));
dt.Columns.Add(new DataColumn("City"));
dt.Columns.Add(new DataColumn("State"));
dt.Columns.Add(new DataColumn("Zip"));
dt.Columns.Add(new DataColumn("Phone"));
dc = new DataColumn();
dc.DataType = typeof(DateTime);
dc.ColumnName = "LastPurchaseDate";
dt.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = typeof(int);
dc.ColumnName = "CustomerType";
dt.Columns.Add(dc);
// Populate the table
dt.Rows.Add(2, "Baggins", "Bilbo", "Baggins, Bilbo", "Bagshot Row #1", "Hobbiton", "SH", "00001", "555-2222", DateTime.Parse("9/24/2008"), 1);
dt.Rows.Add(1, "Baggins", "Frodo", "Baggins, Frodo", "Bagshot Row #2", "Hobbiton", "SH", "00001", "555-1111", DateTime.Parse("9/14/2008"), 1);
dt.Rows.Add(6, "Bolger", "Fatty", "Bolger, Fatty", "ProudFeet Creek", "Hobbiton", "SH", "00001", "555-1111", DateTime.Parse("9/14/2008"), 1); dt.Rows.Add(4, "Elessar", "Aragorn", "Elessar, Aragorn", "Citadel", "Minas Tirith", "Gondor", "00000", "555-0000", DateTime.Parse("9/14/2008"), 4);
dt.Rows.Add(5, "Evenstar", "Arwin", "Evenstar, Arwin", "Citadel", "Minas Tirith", "Gondor", "00000", "555-0001", DateTime.Parse("9/23/2008"), 4);
dt.Rows.Add(3, "Greyhame", "Gandalf", "Grayhame, Gandalf", DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, 3);
In VB:
Dim dt As New DataTable("Customers")
Dim dc As DataColumn
dc = New DataColumn
With dc
.DataType = GetType(Integer)
.ColumnName = "CustomerID"
End With
dt.Columns.Add(dc)
dt.Columns.Add(New DataColumn("LastName"))
dt.Columns.Add(New DataColumn("FirstName"))
‘ Concatenation of first and last names
dt.Columns.Add(New DataColumn("FullName"))
dt.Columns.Add(New DataColumn("Address"))
dt.Columns.Add(New DataColumn("City"))
dt.Columns.Add(New DataColumn("State"))
dt.Columns.Add(New DataColumn("Zip"))
dt.Columns.Add(New DataColumn("Phone"))
dc = New DataColumn
With dc
.DataType = GetType(Date)
.ColumnName = "LastPurchaseDate"
End With
dt.Columns.Add(dc)
dc = New DataColumn
With dc
.DataType = GetType(Integer)
.ColumnName = "CustomerType"
End With
dt.Columns.Add(dc)
‘ Populate the table
dt.Rows.Add(2, "Baggins", "Bilbo", "Baggins, Bilbo", "Bagshot Row #1", "Hobbiton", "SH", "00001", "555-2222", #9/24/2008#, 1)
dt.Rows.Add(1, "Baggins", "Frodo", "Baggins, Frodo", "Bagshot Row #2", "Hobbiton", "SH", "00001", "555-1111", #9/23/2008#, 1)
dt.Rows.Add(6, "Bolger", "Fatty", "Bolger, Fatty", "ProudFeet Creek", "Hobbiton", "SH", "00001", "555-1111", #9/14/2008#, 1)
dt.Rows.Add(4, "Elessar", "Aragorn", "Elessar, Aragorn", "Citadel", "Minas Tirith", "Gondor", "00000", "555-0000", #9/23/2008#, 4)
dt.Rows.Add(5, "Evenstar", "Arwin", "Evenstar, Arwin", "Citadel", "Minas Tirith", "Gondor", "00000", "555-0001", #9/23/2008#, 4)
dt.Rows.Add(3, "Greyhame", "Gandalf", "Grayhame, Gandalf", DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, 3)
The code starts by creating a new DataTable. This one is called "Customer". The next set of code defines the columns. This example demonstrates how to build integer, string, and date columns.
Finally, the last portion of the code populates several rows in the DataTable.
The result looks like this:
Use this technique any time you need to build a DataTable in code.
Enjoy!
EDITED 4/17/10: Corrected an error in the C# code data.
Rostov — April 6, 2010 @ 4:18 pm
Just a few comments… the first would be the ‘wasteful’ use of DateTime.Parse with an included string. If you did this a few thousand times, say, you’re creating strings for each date when you don’t need them. Why not initialize them with their integer components?
Then, if you had a list of ‘things’ to add — say a ‘Person’ defined as:
public int ID {get; set;}
public string FirstName {get; set;}
public string LastName {get; set;}
public DateTime BirthDate {get; set;}
Why do this manually? While I agree that tons of reflection code can get messy, what you’ve got here is a real good case for a reusable component — especially if you got a list of objects back from a webservice and wanted to create a datatable from them:
public static DataTable ListToDataTable(List items)
{
if (items == null)
return null;
DataTable dt = new DataTable(typeof(T).Name);
PropertyInfo[] pis = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo pi in pis)
{
dt.Columns.Add(pi.Name, pi.PropertyType);
}
foreach (T item in items), pass it into the method, and you get a datatable back. 🙂 (Though your example *IS* good for showing how to clearly create the colums and rows)
{
object[] values = new object[pis.Length];
for (int i = 0; i < pis.Length; i++) { values[i] = pis[i].GetValue(item, null); } dt.Rows.Add(values); } return dt; } Now all you have to do is take a List
DeborahK — April 6, 2010 @ 10:14 pm
Hi Rostov –
Yes, my code was meant to show the technique, not necessarily the most efficient way to build code in a real application.
Thanks for the code you posted. It should be very useful for anyone looking for a more generalized solution.
Tom — April 16, 2010 @ 4:54 pm
Looks like your c# data has a bug for Arwen. She’s got an extraneous false in there.
DeborahK — April 17, 2010 @ 10:07 am
Hi Tom –
You are right. Thanks for catching it. I have corrected it above.
Thanks again!