Wednesday, January 9, 2013

How to format, add or delete (remove) a column in a DataTable

Hack the Gibson

A project I am working on required me to generate a dataset that was displayed in a webpage and then export that to an Excel spreadsheet. The webpage used a GridView and I simply performed the DataFormatString there. I was totally nauseous about the thought of writing an SP that pulled the exact same data from a SQL Server that went to the webpage but format it to fit the needs.  What to do?
Take heart, there is an answer my fellow geekonians!

Let us discuss what a DataTable is.. Without all the geek speak mumbo jumbo, it is basically one table of a DataSet.  So a DataSet is a collection of DataTables (not literally, but essentially).   This is why we can fetch a DataTable from a DataSet, like so:

Public Sub datatableExample()
Dim ds As DataSet = ADO.Net_Method_To_Populate_DataSet()
Dim dt As DataTable = ds.Tables(0)
End Sub




Due to the DataSet being a collection (basically an array) of DataTables, we refer to the first table in the set as 0 (zero).  Obviously, if you name the table during the DataSet.Fill() method, you can refer to it as such.  This being the case, you have perform a tSQL query that will return multiple result sets and thus create multiple DataTables in the DataSet.  So now that we know what a DataTable is (essentially), how does one get at the data?

Obviously, we would perform this at the BLL (Business Logic Layer) -


We need to create a new column for each column we want to format and then format the data, finally delete the original column.  Complicated you say? Nay! In 10 lines of code we can accomplish the task. 
Now that we have a DataSet and populate our DataTable from said DataSet, we need to go a couple of layers lower.  The columns in a DataTable are of DataColumn type (Yes, you guessed right, rows in a DataTable are of DataRow type).  So our first task is to create the DataColumn and give THE VALUES inside the DataColumn a specific type.  Since we are trying to format our result set into a money and number representation and then put that in a cell for Excel, we’ll use the String type.  You will see why here shortly.  Now the neat trick here is we have a chance to rename the column if we so choose, using our example above it would look like:




Public Sub datatableExample()
Dim ds As DataSet = ADO.Net_Method_To_Populate_DataSet
Dim dt As DataTable = ds.Tables(0)

Dim dcCost As DataColumn = New _
DataColumn("Cost", Type.GetType("System.String"))
dt.Columns.Add(dcCost)
Dim dcQuantity As DataColumn = New _
DataColumn("Quantity", Type.GetType("System.String"))
dt.Columns.Add(dcQuantity)
End Sub




So here, we get our data and populate the DataTable.  Then we create a new DataColumn called dcCost.  This new column has a header of “Cost”.  We then have to add the column to the DataTable.  We perform the same logic for the Quantity column.

Now we need to loop through the rows of the DataTable to populate the new column.  While populating the new column, we can format the value as we see fit.  In this example, I am going to use a For..Next Loop.  If you choose to use a For Each loop, it will require you to either directly reference the Rows.Count or create another variable that is a representation of the number of rows (Remember that it starts at 0 and not 1).  Using my For..Next Loop, it would look like:




Public Sub datatableExample()
Dim ds As DataSet = ADO.Net_Method_To_Populate_DataSet
Dim dt As DataTable = ds.Tables(0)

Dim dcCost As DataColumn = New _
DataColumn("Cost", Type.GetType("System.String"))
dt.Columns.Add(dcCost)
Dim dcQuantity As DataColumn = New _
DataColumn("Quantity", Type.GetType("System.String"))
dt.Columns.Add(dcQuantity)

'Format New Columns
For i = 0 To (dt.Rows.Count - 1)
dt.Rows(i)("Cost") = String.Format("{0:C}", _
dt.Rows(i)("cost_ea_unit"))
dt.Rows(i)("Quantity") = String.Format("{0:N3}", _
dt.Rows(i)("quantity_on_hand"))
Next
End Sub




So here is the For..Next Loop.  You can directly reference a specific column value by giving the row number and column name.  Here we are using i  as our counter, but it also represents the row number.  Notice that we are going to dt.Rows.Count – 1.  This is because the Count() Method starts at 1 instead of zero.  If you do not put the – 1, you will get an OutOfBounds error.  This is because there might be 24 objects in the column (thus dt.Rows.Count() = 24) but the last place in the collection (array) will be #23.  This is because we start at 0.

The cost_ea_unit and quantity_on_hand are the original column names (which came from SQL).  We set the new column in the DataTable to the String.Format value of the old column.


The first parameter for String.Format is the format as a string value (hence that it is encased in quotes).  You then have the curly bracket and a 0 (zero).  The zero references the value of the object that follows the comma (in our case the value of dt.Rows(i)(“cost_ea_unit”)).  You the have a separator colon, followed by the formatter.  The uppercase c (C) denotes to format it as money.  In the second column the N3 denotes a number (thus add commas where appropriate) plus have 3 decimal places.



Note that this does not CONVERT the value to money or a number.  The object is still of a String type when it is rendered.  Hence the name of the method is FORMAT.



Finally, we need to delete the original columns so that they are not presented from the DataTable in the Presentation Layer.  We accomplish this with the DataTable.Columns.Remove method, like so:



dt.Columns.Remove("cost_ea_unit")
dt.Columns.Remove("quantity_on_hand")



So all together, our code would look like:



Public Sub datatableExample()
Dim ds As DataSet = ADO.Net_Method_To_Populate_DataSet
Dim dt As DataTable = ds.Tables(0)

Dim dcCost As DataColumn = New _
DataColumn("Cost", Type.GetType("System.String"))
dt.Columns.Add(dcCost)
Dim dcQuantity As DataColumn = New _
DataColumn("Quantity", Type.GetType("System.String"))
dt.Columns.Add(dcQuantity)

'Format New Columns
For i = 0 To (dt.Rows.Count - 1)
dt.Rows(i)("Cost") = String.Format("{0:C}", _
dt.Rows(i)("cost_ea_unit"))
dt.Rows(i)("Quantity") = String.Format("{0:N3}", _
dt.Rows(i)("quantity_on_hand"))
Next

dt.Columns.Remove("cost_ea_unit")
dt.Columns.Remove("quantity_on_hand")
End Sub



 



So here we learned how to Format a specific (in our case, 2 specific) columns in a DataTable.  We can then either present it to another object to render to Excel or we can bind it to a Grid or such. 
And that’s all there is to it.



Håþþ¥ .ñꆆïñg…

No comments:

Post a Comment