CODEWITHSUNDEEP
c#sql-serverdecimal
Jonathan Wood
Jonathan Wood

Reputation: 67195

Formatting Decimal Values from SQL Server Database

I'm working with an existing SQL Server database, with a table column defined as decimal(4,2). However, I've been asked to prevent the code from rounding decimal places.

So I tried changing the column type to decimal, which is automatically converted to decimal(18, 0), which promptly wiped out any fractional portion of my existing values. (Nice. The default decimal type is an integer. What's the point?)

So I tried changing the column type to decimal(8,5). Since 5 digits after the decimal is more than needed, this eliminates the rounding issue.

However, whenever I print this value in a C# ASP.NET application, it is always formatted with 5 digits after the decimal (e.g. 12.34000). I don't understand this. When I read the value from the database, I assign it to a regular decimal value. So how does the decimal value "know" there should be 5 digits after the decimal point?

More importantly, how can I display this value without the trailing zeros? I'm guessing there is a way to format the value. However, since it's used in many places it would be much better if it simply didn't automatically append trailing zeros. If needed, I can change the data type in the database.

Upvotes: 2

Views: 11033

Answers (5)

hvaughan3
hvaughan3

Reputation: 11105

Entity Framework uses decimal(18,2) by default. If you do not tell it otherwise, it will not format to fit the SQL Server data type.

To tell EF what the SQL data type should be do this for each property:

public class DbEntities : DbContext {
    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        modelBuilder.Entity<Location>().Property(location => location.Latitude).HasPrecision(9, 6);
    }
}

There are also other ways to do this for all properties: Decimal precision and scale in EF Code First

Upvotes: 0

Jonathan Wood
Jonathan Wood

Reputation: 67195

I changed the database column to float, and set the C# variable type to double.

Problem solved.

Upvotes: 3

Hamid
Hamid

Reputation: 41

You should really think about formatting your currency in the presentation layer, not the data layer. Generally, you want to store currency values in a decimal(10,18) field. you can retrieve the decimal value and format it for presentation in csharp like str = String.Format("{0:C}", myDecimalValue);

Upvotes: 2

Henk Holterman
Henk Holterman

Reputation: 273244

Try this little snippet in a Console app:

decimal d1 = 12.34M, d2 = 12.3400M;

Console.WriteLine("{0} {1}", d1, d2);
Console.WriteLine("{0:0.0###} {1:0.0###}", d1, d2);

If, How and Where you can apply a formatting is up to you.

Upvotes: 1

Raj More
Raj More

Reputation: 48016

Decimal will always come back with trailing zeroes. This is by convention - trailing zeroes are there to show the number of significant digits.

In your case, decimal (8, 5) means that the number has a total of 8 digits, 5 of which are after the decimal point.

Try this Select CAST (1 as decimal (8, 5)) will return back 1.00000

Since this means only 3 digits before the decimal point the following statement Select CAST (1000 as decimal (8, 5)) will give you an error Arithmetic overflow error converting int to data type numeric.

Upvotes: 1

Related Questions