How to retrieve millions with decimals from SQL table

Question

I've made a C# ASP.NET with Entity Framework, Repository Pattern and SQL. I want to retrieve two kind of numbers (Price & Fuel).

Price example: I want to retrieve 2,441.095, but it shows as 2441095,000 in SQL and 2441095,00 in ASP.NET. I have used decimal(18, 3).

Fuel example: I want to retrieve 22.0, but it shows as 22.00 in ASP.NET. I use decimal(18, 1) for that.

From the controller class:

// GET: Car/Details/5
        public ActionResult Details(int id)
        {
            var objCar = iCar.GetCarByID(id);
            var Car = new Car();
            Car.Id = id;
            Car.Name = objCar.Name;
            Car.Model = objCar.Model;
            Car.Year = objCar.Year;
            Car.Color = objCar.Color;
            Car.Fuel = objCar.Fuel;
            Car.Price = objCar.Price;
            return View(Car);
        }

From the Repository Pattern:

public Car GetCarByID(int Id)
        {
            return dbContext.Cars.Find(Id);
        }

Razor:

@model IEnumerable<Car_Sales.Models.Car>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Model)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Year)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Color)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Fuel)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Price)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Model)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Year)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Color)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Fuel)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Price)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
            @Html.ActionLink("Details", "Details", new { id=item.Id }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.Id })
        </td>
    </tr>
}

</table>

which is your `CurrentCulture`? Seems like Germany, where a comma would be the decimal-seperator, instead of a dot. — MakePeaceGreatAgain, Sep 30 '19 at 10:08
This seems like an issue in your output formatting - if the values are really stored in the DB as decimal - then the issue is as HimBromBeere suggest - wrong culture — Mario The Spoon, Sep 30 '19 at 10:13
`"da-DK"` (**Da**nish-**D**emar**K**); `CultureInfo.CurrentCulture = CultureInfo.GetCultureInfo("da-DK");` — Dmitry Bychenko, Sep 30 '19 at 10:38
Possible duplicate of [Html.DisplayFor decimal format?](https://stackoverflow.com/questions/12067375/html-displayfor-decimal-format) — Peter B, Sep 30 '19 at 10:51
@PeterB That almost worked. I'm getting the following: 11,80 (fuel) & 2.441,10 (price) I need it like 11,8 (fuel) & 2.441,110 (price) — Zagros, Sep 30 '19 at 11:24
You can tweak the format string to be whatever you need. See e.g. https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings for extensive documentation and examples. If what you need is not possible using that, then drop the `@Html.DisplayFor()`, *write your own format function*, and call it using something like `@MyFormattingClass.MyFormatFunction(Model.Fuel)`. — Peter B, Sep 30 '19 at 12:30

Felice Pollano · Answer 1 · 2019-09-30T10:30:07.150

2

Numbers are stored in binary format, so your question is about how to display a number, not how to retrieve. Formatting a number in C# is affected by the format string ( to specify the number of decimal, for instance), and by the culture. Have a look at this example:

decimal value = -16325.62m;
// Display value using the invariant culture.
Console.WriteLine(value.ToString(CultureInfo.InvariantCulture));
// Display value using the en-GB culture.
Console.WriteLine(value.ToString(CultureInfo.CreateSpecificCulture("en-GB")));
// Display value using the de-DE culture.
Console.WriteLine(value.ToString(CultureInfo.CreateSpecificCulture("de-DE")));
// This example displays the following output to the console:
//       -16325.62
//       -16325.62
//       -16325,62

this came from this page on MSDN

In general I don't trust CurrentCulture in my applications, I think it could be a rule unless you can deal with different behavior on different installation: the reason is you can't force a user to have a specific culture installed on the server.

edited Sep 30 '19 at 10:30

answered Sep 30 '19 at 10:17

Felice Pollano

32,832
9
75
115

I'm using ASP.NET to display my data on a table. How would I do this with your suggestion? – Zagros Sep 30 '19 at 10:19
well the code I posted works on ASP.NET to, maybe show your code in the question – Felice Pollano Sep 30 '19 at 10:20
Done, please let me know if you want me to provide any more code. – Zagros Sep 30 '19 at 10:25
@Zagros here is when you retrieve the code, the problem is when you display to the user – Felice Pollano Sep 30 '19 at 10:29
I'm still lost where I should implement your example into mine project. Is it in the Razor part? – Zagros Sep 30 '19 at 11:21

score 0 · Answer 2 · answered Sep 30 '19 at 11:05

0

you can format the decimal variabal at view side

decimalVar.ToString ("#.##"); // returns "" when decimalVar == 0

or

decimalVar.ToString ("0.##"); // returns "0"  when decimalVar == 0

answered Sep 30 '19 at 11:05

Rajat Khandelwal

1
1

And where I do this in MVC5? – Zagros Sep 30 '19 at 11:56
in the view part – Rajat Khandelwal Oct 03 '19 at 08:30

How to retrieve millions with decimals from SQL table

2 Answers2