Overview
A year ago, when building my framework, I added some methods to allow me to convert a dataset into XML. This was widely used in several of my applications as many clients requested the ability to use the XML format for their transport mechanisms.
After a few hours of having done searches on the Internet and having analyzed some approaches, I decided to use this small piece of code to convert a dataset into XML:
' Convert a DataSet into an Xml ' This functions make it so the schema is included ' expO1 DataSet Public Function DataSetToXml(ByVal toDataSet As DataSet) As String Dim lcXml As String = "" Dim loMemorySteam As New MemoryStream() toDataSet.WriteXml(loMemorySteam, XmlWriteMode.WriteSchema) loMemorySteam.Seek(0, SeekOrigin.Begin) Dim lcStreamReader As New StreamReader(loMemorySteam) lcXml = lcStreamReader.ReadToEnd() Return lcXml End Function
Dim lcXML As String = "" lcXML = DataSetToXml(loDataSet)
Applying a XSL transformation
Then, a few months later, some of my clients requested the ability to export the data into Excel. So, in order to generate an Excel XLS file in an XML format, I had to apply a XSL transformation. This worked great. I was able to achieve that goal with all the help I obtained from the Universal Thread .NET forum.
The process consisted of calling the DataSetToXml() method to prepare the dataset into a XML file and then use my Export class to export the dataset into an Excel XLS format by the use of the XSL transformation. This has worked ok for a few months. A few days ago, I received an email from a client who told me that the Excel file looked a little bit weird because some columns were offset.
I started to look at the Excel file. As also mentioned by the client, I rapidly observed that the columns which were offset have obtained that effect where some rows of the dataset contained some null values. This was the result of a dataset which has been created with some LEFT JOIN where null values were present, when the join was not available.
Handling null values
Once I saw that the problem was originally related to the null values, I could easily observed this reaction inside the XML data file itself. Basically, if each record branch of the XML data file are not the same, when the XSL transformation occurs, this will create an Excel file with a weird result as some columns will be offset on specific rows only.
In order to resolve the issue, I needed to make sure that each record branch of the XML data file would be the same. That was the only way to assure that the XSL transformation would generate the expected and proper result. The resolution of the problem had to be done between the XML data file and the XSL transformation. I did some research on the Internet to obtain an example of scanning a dataset and replacing all null values with an empty value. The original idea led me to define this method:
' Adjust a dataset to avoid null values ' expO1 Dataset Public Function AdjustDataSetToAvoidNullValue(ByVal toDataSet As DataSet) As DataSet Dim loColumn As DataColumn Dim loRow As DataRow Dim loTable As DataTable For Each loTable In toDataSet.Tables For Each loRow In loTable.Rows For Each loColumn In loTable.Columns If IsDBNull(loRow.Item(loColumn.ColumnName)) Then Select Case loColumn.DataType.ToString Case "System.DateTime" ' Get the proper definition as per the current scope If oProcess Is Nothing Then loRow.Item(loColumn.ColumnName) = oProcess.oApp.GetEmptyDate() Else loRow.Item(loColumn.ColumnName) = oApp.GetEmptyDate() End If Case "System.Integer" loRow.Item(loColumn.ColumnName) = 0 Case Else loRow.Item(loColumn.ColumnName) = "" End Select End If Next Next Next Return toDataSet End Function
So, from the DataSetToXml() method, I can then apply a small change to call the AdjustDataSetToAvoidNullValue() method before returning the XML:
' Convert a DataSet into an Xml ' This functions make it so the schema is included ' expO1 DataSet Public Function DataSetToXml(ByVal toDataSet As DataSet) As String Dim lcXml As String = "" Dim loMemorySteam As New MemoryStream() ' Adjust the dataset to avoid null values toDataSet = AdjustDataSetToAvoidNullValue(toDataSet) toDataSet.WriteXml(loMemorySteam, XmlWriteMode.WriteSchema) loMemorySteam.Seek(0, SeekOrigin.Begin) Dim lcStreamReader As New StreamReader(loMemorySteam) lcXml = lcStreamReader.ReadToEnd() Return lcXml End Function
Conclusion
I have been searching in various ways to resolve that issue. At first, I thought this was related to the XSL transformation. But, I was able to locate the source of the problem in the middle level and apply a small change its basis. Sometimes, a problem can easily be resolved by simply going back to the basis.
Thanks to those who provided help on this topic.
