DataSet defined by ReadXmlSchema: why do column datatypes change after filling?

Question

I have a process that exports the results of a database call to a CSV file. The data types and file format must match a specific format so the file can be uploaded to an external system. This process has been running for maybe 7 or 8 years on an ASP.NET (.NET 2.0) webform and suddenly (sometime over the last 6-18 months) it's not working the way it used to. Perhaps after .NET 4.0 was installed on the client server, or maybe after some other framework update(?) or windows update(?) or provider update(?). Our DLL has not changed in a few years. I'd like to fix this legacy process with the least amount of hacking and slashing possible.

There are three data types being exported: integer, string, and decimal. The problem is that all the integer columns are being exported as decimals now. The CSV export library looks at the datatypes of the columns to determine the correct output format, so I use a XSD file to define my DataSet before filling it. Here is a simplified example of the XSD file:

<?xml version="1.0" standalone="yes"?>
<xs:schema id="FDSR" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="FDSR" msdata:IsDataSet="true" msdata:Locale="en-CA">
    <xs:complexType>
      <xs:choice maxOccurs="unbounded">
        <xs:element name="TBLEXPORT">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="INTCOLUMN1" type="xs:integer" minOccurs="0" />
              <xs:element name="STRCOLUMN2" type="xs:string" minOccurs="0" />
              <xs:element name="DBLCOLUMN3" type="xs:decimal" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

The data types defined for each column used to persist after the data was loaded, but now they are reset by the data load. For example:

Dim ds as New DataSet
ds.ReadXmlSchema("MyFile.xsd")

' Breakpoint here: 
' ds.Tables(0).Columns(0).DataType shows: {Name = "Int64" FullName = "System.Int64"}

Dim db as New DatabaseCall("my db call...")    
ds = db.ReturnData()

' Breakpoint here: 
' ds.Tables(0).Columns(0).DataType now shows: {Name = "Decimal" FullName = "System.Decimal"}

GenerateCSVOutput(ds)

How can I force the integer column to remain an integer after the database call? Or how can I change the datatype after populating the dataset?

This code has been simplified for posting, but basically the db.ReturnData() is calling an Oracle stored procedure to do some processing and return the data using System.Data.OracleClient.OracleDataAdapter.Fill(dataset) to populate the DataSet. There is no integer column in Oracle so the source table has the column defined as NUMBER(1,0). It's definitely outputting the correct precision, I just don't understand why the column type in the DataSet is suddenly changing when it is explicitly defined as an integer. Unfortunately the CSV file needs to be uploaded to an external government system that will not accept 1.0 for 1...

score 1 · Accepted Answer · edited May 23 '17 at 12:18

Workaround: Clone the DataSet, change the data type, copy the data.

Dim dsExport as New DataSet
'dsExport.ReadXmlSchema("MyFile.xsd") ' Don't bother, this no longer works

Dim db as New DatabaseCall("my db call...")    
dsExport = db.ReturnData()

' Clone the structure of dsExport, while empty change the datatype(s) as required, then copy the data in
Dim dsClone As DataSet = dsExport.Clone
dsClone.Tables("tblExport").Columns("INTCOLUMN1").DataType = System.Type.GetType("System.Int32")
For Each row As DataRow In dsExport.Tables("tblExport").Rows
    dsClone.Tables("tblExport").ImportRow(row)
Next

GenerateCSVOutput(dsClone)

DataSet defined by ReadXmlSchema: why do column datatypes change after filling?

1 Answers1