I have a common functions class (cfc) project i tend to fill with all functions i believe can be applied to anything and will likely be reused.
I was making some adjustments to a few handy functions and I thought it might be useful to publish the following functions which will help convert datatables to various output formats.
Visual Studio references the following libraries within my cfc project, im not entirely sure which ones are required for these functions so i will list them all.
System
System.Core
System.Data
System.DirectoryServices
System.Drawing
System.Runtime.Serialization
System.ServiceModel.Web
System.Web
System.Web.Extensions
System.Xml
We have 6 functions here
1. ConvertDtToXML
2. ConvertDtToTDF
3. ConvertDtToCSV
4. ConvertDtToJSON
5. ConvertDtToHTML
6. RenderControl
Lets talk about each one briefly.
1. ConvertDtToXML
This function is making use of .NETs Dataset object built in method GetXml, the one simple rule here is that the table must be named in order for the xml to be generated. So we simply declare a dataset, check for a table name, if none then give a standard name, add the table to the dataset and output using GetXml.
|
1 2 3 4 5 6 7 |
'Converts a DataTable to XML Public Shared Function ConvertDtToXML(ByVal dt As DataTable) As String Dim dSet As DataSet = New DataSet If String.IsNullOrEmpty(dt.TableName) Then dt.TableName = "TableXML" dSet.Tables.Add(dt) ConvertDtToXML = dSet.GetXml End Function |
2. ConvertDtToTDF
3 things are going on here, the first for loop we get the column headings. Second we begin a for loop on all of the rows, and third we for loop against each column getting the data this time. Each loop builds up the TAB delimited string we end up with.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
'Converts a DataTable to Tab Delimited Format Public Shared Function ConvertDtToTDF(ByVal dt As DataTable) As String Dim Str As New System.Text.StringBuilder() Dim dr As DataRow, ary() As Object, i As Integer Dim iCol As Integer 'Output Column Headers For iCol = 0 To dt.Columns.Count - 1 Str.Append(dt.Columns(iCol).ToString & vbTab) Next Str.Append(vbCrLf) 'Output Data For Each dr In dt.Rows ary = dr.ItemArray For i = 0 To UBound(ary) Str.Append(ary(i).ToString & vbTab) Next Str.Append(vbCrLf) Next Return Str.ToString End Function |
3. ConvertDtToCSV
A very similar approach to the tab delimited has been taken here with the CSV, its the text used to build the sting that differs.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
'Converts a DataTable to CSV Public Shared Function ConvertDtToCSV(ByVal dt As DataTable) As String Dim Str As New System.Text.StringBuilder() Dim dr As DataRow, ary() As Object, i As Integer Dim iCol As Integer 'Output Column Headers For iCol = 0 To dt.Columns.Count - 1 Str.Append("""" & dt.Columns(iCol).ToString.Replace("""", "'") & """" & ",") Next Str.Append(vbCrLf) 'Output Data For Each dr In dt.Rows ary = dr.ItemArray For i = 0 To UBound(ary) Str.Append("""" & ary(i).ToString.Replace("""", "'") & """" & ",") Next Str.Append(vbCrLf) Next Return Str.ToString End Function |
4. ConvertDtToJSON
We make use of .NETs Json serializer to convert our datatable to a json string
|
1 2 3 4 5 6 7 8 9 10 11 |
'Converts a DataTable to JSON Public Shared Function ConvertDtToJSON(ByVal dt As DataTable) As String If String.IsNullOrEmpty(dt.TableName) Then dt.TableName = "TableAsJason" Dim json As String = String.Empty Dim ser As New Runtime.Serialization.Json.DataContractJsonSerializer(dt.[GetType]()) Using ms As New IO.MemoryStream() ser.WriteObject(ms, dt) json = Text.Encoding.[Default].GetString(ms.ToArray()) End Using Return json End Function |
5. ConvertDtToHTML and 6. RenderControl
This final converter function ConvertDtToHTML needs to use last function RenderControl.
Load the datatable into a gridview, bind it, then extract the rendered HTML from it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
'Converts a DataTable to unformatted HTML table Public Shared Function ConvertDtToHTML(ByVal dt As DataTable) As String Dim View As New System.Web.UI.WebControls.GridView View.DataSource = dt View.AllowPaging = False View.DataBind() Return RenderControl(View) End Function 'usefull function to turn a web ui control into rendered html Private Shared Function RenderControl(ByVal ctrl As System.Web.UI.Control) As String Dim sb As System.Text.StringBuilder = New Text.StringBuilder() Dim tw As IO.StringWriter = New IO.StringWriter(sb) Dim hw As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(tw) ctrl.RenderControl(hw) Return sb.ToString() & String.Empty End Function |