Using toString() in C# get exponential value for big Integer

Question

I have script task transformation on my SSIS package. Basically i write data(sql query) to txt file using scrip task.

this piece of code sw.Write(dr(i).ToString()) couldn't convert this value 11398069 properly. I think i need to use bigInt.ToString() but does it work with any value. 11398069 -- after export txt shows this value as 1.13981e+007 instead of 11398069 Let me know.

Here is the code below.

sw.Write(sw.NewLine)
 For Each dr As DataRow In dt.Rows
  sw.Write("5|")
   For i As Integer = 0 To iColCount - 1
    If Not Convert.IsDBNull(dr(i)) Then
     **sw.Write(dr(i).ToString())**
    End If
    If i < iColCount - 1 Then
     sw.Write("|")
    End If
  Next
 sw.Write(sw.NewLine)
 Next

Values could be really anything. For instance(Sample values). 120 120.09290 1.23443 ABC ZZZZZZZ 11398069 -- after export txt shows this value as 1.13981e+007 instead of 11398069

Let me know how to handle this

Did you get any error message? If yes could you post that message? — Steve, Feb 26 '14 at 17:51
No error, didn't get the correct values as i mentioned. I see this value/data "1.13981e+007" instead of correct data(11398069). — user1810575, Feb 26 '14 at 17:57

Olivier Jacot-Descombes · Answer 1 · 2014-02-27T14:58:14.407

The SQL Server bigint type maps to the .NET System.Int64 or VB Long type. This should do it if the table column is really a bigint:

DirectCast(dr(i), Long).ToString()

You also say that the values could be anything. As an example you write "ABC". Is your table column really typed as bigint or is it a VARCHAR or NVARCHAR column? In the latter case it could just contain the value formatted as 1.13981e+007.

Another question is, what is sw? Could it be that the Write command performs some kind of unwanted formatting?

If dr(i) returns a Double then it could be formaatted in the exponeial format as well. This would help:

DirectCast(dr(i), Double).ToString("0")

UPDATE

You might have to do something like

If Not dr.IsDBNull(i) Then
    Dim s As String
    If dr.GetFieldType(i) Is GetType(Double) Then
        s = dr.GetDouble(i).ToString("0")
    Else
        s = dr(i).ToString()
    End If
    sw.Write(s)
End If

It really depends on the type of the very column that is wrongly formatted. The fact that the number is formatted with the exponential format is a strong hint that the number's type (SQL type and especially the VB type it is mapped to) is in fact not an integer type (Integer, Long) but some decimal or floating type (Single, Double, Decimal). The very number stored might have no decimal fractions and be integer but this does not make it an integer number type.

Set a breakpoint on the sw.Write(dr(i).ToString()) line and inspect the runtime type returned by dr(i). Or insert the line

System.Diagnostics.Debug.WriteLine( _
                               String.Format("col({0}) = {1}", i, dr.GetFieldType(i)))

And display the Ouput Window (menu Debug > Windows > Output).

1. SW is Stream Writer Dim sw As New StreamWriter(Path, False). — user1810575, Feb 26 '14 at 18:53
1. SW is Stream Writer Dim sw As New StreamWriter(Path, False). 2. It's not about what's DB column. I run Select SQL query and write data to txt file. I need to add header/trailer/detail records to text so loop thru the data using above code. There will be variety of data coming from different columns. Everything works fine expect for big Int values. I'm not sure how to use directCast in relation to Stream Writer. — user1810575, Feb 26 '14 at 19:01
It really depends on the type of the column that gets formatted the wrong way. Please see my updated answer. — Olivier Jacot-Descombes, Feb 26 '14 at 22:08
@user1810575 If none of Olivier's 3 options work, then... is it clear what SQL data type the *query* is giving the column? If that question is giving trouble... you could temporarily change your query to a `SELECT INTO ...`, which would save the query results into SQL table ``, and then look at that table to see the SQL data-type of the column-in-question... so at least you know what *SQL* data type is being mapped. — Doug_Ivison, Feb 27 '14 at 04:07

score 1 · Answer 2 · answered Feb 26 '14 at 21:36

1

The core problem here is that you are just calling ToString() on the default type returned from the datareader, which is Object. You need to give the compiler more help eg cast to double then use ToString("R") or, use one of the Get methods etc etc

answered Feb 26 '14 at 21:36

peterG

1,651
3
14
23

Using toString() in C# get exponential value for big Integer

2 Answers2