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.
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)
Using the Microsoft Enterprise Library (or some other OO way of accessing a database), how do I retrieve a column's precision?
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.
' 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
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)