CODEWITHSUNDEEP
.netvb.netenterprise-library
NullVoxPopuli
NullVoxPopuli

Reputation: 65083

.NET saving to DB truncates my decimals instead of rounds them.. what could be possible problems?

I'm not using the entity framework, but I am using the Microsoft Enterprise Library.

I have a value like: 1.125979843654984 that gets truncated to 1.125, instead of rounded to 1.126.

The precision of the column this value is in is Decimal(5,3).

What are some reasons that this could happen?

Is there any other information I could provide to help?

I'm new to .NET.

Update 1

The code that invokes the saving process:

Dim dsCalcGroupByBand As New DataSet
dsCalcGroupByBand.Tables.Add(dtCalcGroupByBand)
dsCalcGroupByBand.Tables(0).TableName = "Table"
Dal.SaveGridDataSet(dsCalcGroupByBand, "Select * from tblLTDCalcGroupByBand where iGroupKY=" & GroupData.GroupNo, False, False)

The method method in the variable Dal:

Public Function SaveGridDataSet(ByVal pDS As DataSet, ByVal pSQL As String, _
    Optional ByVal pCheckIsNewGroup As Boolean = True, _
    Optional ByVal pForceAsNewGroup As Boolean = False, _
    Optional ByVal pSQLDelete As String = "") As Boolean

  Dim DA As New SqlDataAdapter
  Dim commandBuilder As SqlCommandBuilder
  Dim adapter As SqlDataAdapter
  Dim updatedRows As Integer

  Using connection As SqlConnection = _database.CreateConnection()

    Try
      If ((pCheckIsNewGroup = True) And (GroupData.isNewGroup = True)) Or _
      (pForceAsNewGroup = True) Then
        pDS = IsNewGroup(pDS)
      End If
      DA = New SqlDataAdapter(pSQL, connection) '_database.GetDataAdapter
      ' Make the CommandBuilder generate the insert, update, and delete commands.
      commandBuilder = New SqlCommandBuilder(DA)

      ' Save the changes.
      DA.Update(pDS)

    Catch e As InvalidOperationException
      Try
        ' it's horrible to run code, in here, but there need to be tests
        ' implemented before modifying the above code.
        adapter = New SqlDataAdapter(pSQL, connection)
        commandBuilder = New SqlCommandBuilder(adapter)
        'adapter.SelectCommand = commandBuilder.GetUpdateCommand
        updatedRows = adapter.Update(pDS)
      Catch ee As DBConcurrencyException
        ' there was no change (data already exists, no need to update)
        Console.WriteLine("no data written")
      End Try

    Catch e As DBConcurrencyException

      ' Delete the current records using the optional delete pSQLDelete
      If pSQLDelete = "" Then
        pSQLDelete = pSQL.Replace("Select *", "Delete")
      End If
      UpdateSQL(pSQLDelete)

      ' Now Create the dataset as if a new group and try the update again
      DA.Update(IsNewGroup(pDS))
    Catch e As Exception

      Console.WriteLine("Un-mitigated exception")
      Console.WriteLine(e.Message)
    End Try


  End Using


End Function

Using the above decimals for an example: in the dtCalcGroupByBand DataTable, there exists a row a field of value 1.125979843654984 corresponding to a column in the database of datatype Decimal(5,3) -- however, the particular corresponding DataColumn object is of datatype System.Decimal - which could be where the problem is coming from? perhaps?

Anyway, once saved, (after calling Dal.SaveGridDataSet(...)), I look up the value in the table, and it shows 1.125 (truncated) instead of 1.126 (rounded)

Update 2

Using the Microsoft Enterprise Library (or some other OO way of accessing a database), how do I retrieve a column's precision?

Upvotes: 1

Views: 500

Answers (2)

NullVoxPopuli
NullVoxPopuli

Reputation: 65083

I fixed my issue loading the whole schema on app startup, and then referencing the column information from the schema as I needed it.

Hopefully other people don't have to search as long as I did to solve this.

Setup

' in whatever class you do your database communication:
Private _database As SqlDatabase
Private Shared _schema As DataTable

Sub New()
  ' or however you handle the connection string / database creation
  Dim connectionString as String = GetConnectionString()
  _database = New SqlDatabase(connectionString)

  RetrieveSchema()
End Sub


Private Function RetrieveSchema() as DataTable
  If _schema Is Nothing Then
    Using connection As SqlConnection = _database.CreateConnection()
      connection.Open()
      _schema = connection.GetSchema("Columns")
    End Using
  End If

  return _schema
End Function


Public Function GetColumnInformation(tableName As String, columnName As String) as DataRow
  Dim firstMatchingRow as DataRow = (
    From row In _schema.Rows _
    Where (
      row("TABLE_NAME") = tableName AndAlso row("COLUMN_NAME") = columnName)
    )).FirstOrDefault()

  Return firstMatchingRow
End Function

Usage

Dim columnInformation As DataRow = Dal.GetColumnInformation(tableName, columnName)

' find the precision
Dim precision = columnInformation("NUMERIC_PRECISION")
Dim scale = columnInformation("NUMERIC_SCALE")

' convert the decimal to the column's format
' e.g.: 2.345 with a scale of 2 would result in 
'       2.35
value = Decimal.Round(value, scale)

Upvotes: 0

StingyJack
StingyJack

Reputation: 19459

The column is declared as Decimal(5,3), so you will get 3 places of precision to the right of the decimal value, the rest will be truncated.

If you want to round the value, you have to round it in the datatable column before saving it to the database, or in the SQL statement being executed.

Upvotes: 2

Related Questions