Reading XML with numbers formatted with opposite decimal character (period/comma) in .NET

Question

My VB.NET app is importing a XML file generated by a 3rd-party website into a SQL Server table. The website (and my computer) use the period character for decimals (e.g. 42.015) and everything works great. But a European user reported that numbers imported were being multiplied by a factor of 1000 or 10000. It turns out that his computer is looking for a comma decimal (e.g. 42,015) and when it sees the XML input it converts it to 42015.00.

I'm using DataSet.ReadXML and SqlBulkCopy.WriteToServer and I'm not sure where I can step in to tell the program to expect period decimals. My code is below:

    Dim ds As New DataSet
    Try
        ds.ReadXml(tempfile, 0)
    Catch ex As XmlException
        Log($"Error reading XML: {ex.Message} with {ex.Data}")
        Exit Sub
    End Try
    Dim columnMap As New Dictionary(Of String, String) From {
        {"LOTID", "InventoryID"},
        {"ITEMTYPE", "ItemType"},
        {"ITEMID", "ItemNum"},
        {"COLOR", "ColorID"},
        {"CONDITION", "Cond"},
        {"REMARKS", "LocationName"},
        {"QTY", "Qty"},
        {"DESCRIPTION", "Description"},
        {"SUBCONDITION", "Completeness"},
        {"SALE", "Discount"},
        {"STOCKROOM", "Stockroom"},
        {"BULK", "Bulk"},
        {"BUYERUSERNAME", "Reserve"},
        {"PRICE", "Price"}
    }
    Using conn = New SqlConnection(GetDBConnectionString)
        Using sbc As New SqlBulkCopy(conn)
            conn.Open()
            DoSql(conn, "TRUNCATE TABLE dbo.Online_Inventories;")
            For Each column As DataColumn In ds.Tables("ITEM").Columns
                If columnMap.ContainsKey(column.ColumnName) Then
                      sbc.ColumnMappings.Add(column.ColumnName, columnMap(column.ColumnName))
                End If
            Next
            sbc.DestinationTableName = "Online_Inventories"
            sbc.WriteToServer(ds.Tables("ITEM"))
            conn.Close()
        End Using
    End Using

The XML imported looks like this:

   <ITEM>
      <LOTID>217770136</LOTID>
      <DATEADDED>9/20/2020 3:02:00 PM</DATEADDED>
      <CATEGORY>771</CATEGORY>
      <COLOR>0</COLOR>
      <PRICE>11.7563</PRICE>
      <QTY>1</QTY>
      <BULK>1</BULK>
      <IMAGE></IMAGE>
      <DESCRIPTION></DESCRIPTION>
      <CONDITION>U</CONDITION>
      <SUBCONDITION>I</SUBCONDITION>
      <ITEMTYPE>S</ITEMTYPE>
      <ITEMID>41110-1</ITEMID>
      <SALE>0</SALE>
      <REMARKS></REMARKS>
      <STOCKROOM>Y</STOCKROOM>
      <MYWEIGHT>0</MYWEIGHT>
      <ITEMWEIGHT>0</ITEMWEIGHT>
      <DATELASTSOLD></DATELASTSOLD>
      <BASECURRENCYCODE>USD</BASECURRENCYCODE>
   </ITEM>

So in this example, after the third line (ds.ReadXml), ds("Price")="11.7563", a string After the line sbc.WriteToServer, the value of dbo.Online_Inventories.Price is 117563.0 (actually an error in this case because Price is a NUMERIC(9,4))

How do I get .net to read periods as decimals when the user's home culture uses commas as decimals? Thanks!

The dataset pulls them in as strings and the SqlBulkCopy attempts to convert it into numbers to match the table definition. — Scott
Try doing the conversion before, put real numbers in the dataset, parsing them taking into account the format in the XML file. — Alejandro
For a simple hack, I believe that you should be able to resolve this by: 1) store the current thread CultureInfo before the code, 2) change the thread culture to InvariantCulture (Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture), 3) after the code, restore the thread culture to the stored value. This way the default parsing will use the InvariantCulture decimal mark (a period). — TnTinMn

TnTinMn TnTinMn · Accepted Answer · 2020-10-08T23:31:34

The default thread CultureInfo is based on the running machine's set culture. Default string parsing will use the default CultureInfo. You can change the thread CultureInfo to use the InvariantCulture (basically en-US) while executing the code you posted. The InvariantCulture uses a period(.) for the decimal mark.

Dim currentCulture As CultureInfo = Threading.Thread.CurrentThread.CurrentCulture
Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture
' ***
' insert your code here
' ***
Threading.Thread.CurrentThread.CurrentCulture = currentCulture

Reading XML with numbers formatted with opposite decimal character (period/comma) in .NET

1 Answers