c# - SQL Server Database store number data as exponential -

i have simple console application reads excel file (source.xlsx) using openxml , sql insert using ado.net save data sql server database.

i found problem when read decimal number 0.00015, in after saving sql server, saved "1.5 e-2" instead of 0.00015.

this problem occurs when try run in production environment, not occur when try run in staging environment (in staging environment, saved 0.00015).

as far differences between environment, haven't found useful, far settings looks same (i missing important).

what root cause of data stored way , how prevent it?

one cause if creating sql insert statement using this:

float value = 0.00015f; string sql = "insert [mytable] ([fieldname]) values (" + value.tostring() + ")"; 

or

string sql = string.format("insert [mytable] ([fieldname]) values ({0})", value); 

both of these rely on .net's string formatting produce valid sql, not safe. default string formatting produce "the more compact of either fixed-point or scientific notation." scientific notation (1.5e-5), , fixed point formatting (0.00015). may have different cultures on server , development environments affect format more compact.

the correct way use parameters in sql, avoid formatting problems , sql injections:

using (var cmd = dbconnection.createcommand()) {     var prm = cmd.createparameter();     prm.parametername = "value";     prm.dbtype = dbtype.single;     prm.direction = parameterdirection.input;     prm.value = 0.00015f;     cmd.parameters.add(prm);      cmd.commandtext = "insert [mytable] ([fieldname]) values (@value)";     cmd.executenonquery(); }