How can i round the amount field to 2 decimals while inserting to Mysql table

How can to round the value to 2 decimals before it is inserting it to mysql database. Suppose if i have my amount field as 800 while inserting to database i would like to round it to 800.00 and also if i enter 234.56 i would like to insert this as it is, if 345开发者_运维技巧.5655 means i would like to insert as 345.56.

---

Try to formate string like this

Console.WriteLine("{0:0.##}", value);

---

If you are using parameterized/prepared statements and the MySQL Connector/NET provider, you would just use the normal rounding functions (Math.Round()) and use the result of that as the value of the parameter executing the query:

using(var command = connection.CreateCommand()) 
{
  command.Text = "INSERT INTO MyTable (Col1, Col2) VALUES (@v1, @v2)";
  var parameter1 = command.CreateParameter();
  parameter1.Name = "@v1";
  parameter1.Value = Math.Round(myValue, 2);
  command.Parameters.Add(parameter1);
  // ... 
  command.ExecuteNonQuery();
}

---

Assuming that you store the value in a DECIMAL column.

Decimal value = 345.5655M;
Decimal roundedValue = Math.Round(value, 2);

Then insert the roundedValue into the database.

If you want to control the rounding you can use an overload where you specify a MidpointRounding value.

If you don't round the value before inserting it into the database (assuming that the precision on the database is 2) you run the risk of truncating (instead of rounding) the value:

When such a [DECIMAL] column is assigned a value with more digits following the decimal point than are permitted by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the permissible number of digits.)

A Decimal stores the precision that was used when it was created meaning that 3.9M.ToString() returns 3.9 while 3.90M.ToString() returns 3.90 (even though the numbers are equal). However, using Math.Round will truncate any trailing zero decimals. If you want to round 3.9M to 3.90M you need to parse the number from a string:

var roundedValue = Decimal.Parse(
   value.ToString("F2", CultureInfo.InvariantCulture),
   CultureInfo.InvariantCulture
);

However, I don't know if the trailing zero decimals will be retained by MySQL.