MS SQL Server Decimal datatype Vs .NET’s System.Decimal datatype
In my applications, some users want money rounded to two places of decimal (e.g 5,692.25) at times they want no decimal place at all (e.g 5,692). It occurred to me that one day, some one might request for four places of decimal. Thus I needed a way to automatically format DataGridView among other controls at run time.
I did write some code such as
''' <summary>
''' All the grid columns of a supplied datatype will be formated to the supplied style
''' </summary>
''' <param name="grid"></param>
''' <param name="style"></param>
''' <param name="dataType"></param>
''' <remarks></remarks>
Public Sub FormatGridColumn(ByVal grid As DataGridView, ByVal style As DataGridViewCellStyle, ByVal dataType As Type)
Dim columnStyle As New DataGridViewCellStyle()
Dim columnType As Type
Try
columnStyle.Alignment = DataGridViewContentAlignment.MiddleRight
columnStyle.Format = "#,##0.00"
columnType = GetType(Decimal)
If IsNothing(dataType) = False Then columnType = dataType
If IsNothing(style) = False Then columnStyle = style
For Each column As DataGridViewColumn In grid.Columns
If column.ValueType Is Nothing Then Continue For
If column.ValueType.FullName.Equals(columnType.FullName) Then
grid.Columns(column.Name).DefaultCellStyle.ApplyStyle(columnStyle)
End If
Next
Catch ex As Exception
End Try
End Sub
On inspecting corresponding datatypes of a databound DataGridView, found out that both Currency and Decimal datatypes of MS SQL map to System.Decimal. Well we all know that System.Decimal is much more accurate than Double or Single/Float but am not sure if a datatype such as Decimal (5, 2)(SQL) should map to Decimal (VB).
Why?
For a column that stores an hourly wage, a DECIMAL with a precision of 5 and scale of 2 i.e. Decimal (5, 2) is probably a better choice than MONEY or SMALLMONEY though I’d use the latter types for wage. Both of the latter types have far too much precision and scale to be useful for a value that is almost always going to be less than $60.00
Precision is the number of decimal places that the column can take; scale is the number of decimal places allowed to the right of the decimal point. So, if you need to store percentages (e.g. 28.67%) you would use DECIMAL (5, 2). Assuming you needed to cover the 100.00% case. If you wanted more accuracy, you could use DECIMAL (8, 5), which would allow a value like 38.96523%.
Now if you asked me as what VB should do to such a datatype, I’d say inspect precision and scale and there after convert accordingly to Single or Double or Decimal but not always.
Wilson Kutegeka | Microsoft MVP - Visual Basic | C#
Developer | Promoter | ClinicMaster Software
web www.clinicmaster.net