Using decimal or double to save data in database

Question

1.00/5 (1 vote)

See more:

Hello,

I have a simple program with a specific culture, set to "ro-RO".

For example:

en-EN is 1,000.20
ro-RO is 1.000,20

those number ar the same

I have some math calculation with result is a DECIMAL, if my pc has set the regional to en-EN, the data is save oK in my database, if my pc has regional set to ro-RO, when i save the decimal in database is 100,020.00.

If i convert the number to Double the number is save proper.

Any suggestion what is the best solution?

Some code:

I retrive some data from database like this:

C#

DateTime[] UltimaInregistrareGr = new DateTime[CategorieProdus.Rows.Count];
                   decimal[] SoldGramaj = new decimal[CategorieProdus.Rows.Count];
                   int[] IdGramaj = new int[CategorieProdus.Rows.Count];

                   for (int i = 0; i < CategorieProdus.Rows.Count; ++i)
                   {
                       int NrCatCategorie = Convert.ToInt32(CategorieProdus.Rows[i]["id_categorie"].ToString());
                       Program.Connection.CommandText = "SELECT regGramaj.regGramaj_id, regGramaj.regGramaj_date, regGramaj.regGramaj_soldinitial, regGramaj.regGramaj_soldfinal, IIF(regGramaj.regGramaj_categorie Is Null, 0, regGramaj.regGramaj_categorie) AS RegRaportCategorie " +
                                                       "FROM regGramaj, [SELECT MAX(regGramaj.regGramaj_date)  FROM regGramaj]. AS maxdate " +
                                                       "WHERE regGramaj.regGramaj_categorie=@regGramaj_categorie";
                       Program.Connection.AddParameter("@regGramaj_categorie", NrCatCategorie);
                       DataTable TableSold = new DataTable();
                       Program.Connection.FillDataTable(TableSold, true);

                       if (TableSold.Rows.Count != 0)
                       {
                           for (int j = 0; j < TableSold.Rows.Count; ++j)
                           {
                               UltimaInregistrareGr[i] = Convert.ToDateTime(TableSold.Rows[j]["regGramaj_date"].ToString());
                               if (UltimaInregistrareGr[i] == DateTime.Today)
                                   SoldGramaj[i] = Convert.ToDecimal(TableSold.Rows[j]["regGramaj_soldinitial"].ToString());
                               else
                                   SoldGramaj[i] = Convert.ToDecimal(TableSold.Rows[j]["regGramaj_soldfinal"].ToString());
                               IdGramaj[i] = Convert.ToInt32(TableSold.Rows[j]["regGramaj_id"].ToString());
                           }
                       }
                       else
                       {
                           SoldGramaj[i] = 0;
                           IdGramaj[i] = 0;
                       }
                   }

and after sone math calculation i save the result like this:

C#

Program.Connection.CommandText = "INSERT INTO regGramaj (regGramaj_date, regGramaj_soldinitial, regGramaj_intrari, regGramaj_vanzare, regGramaj_soldfinal, regGramaj_categorie) values (@regGramaj_date, @regGramaj_soldinitial, @regGramaj_intrari, @regGramaj_vanzare, @regGramaj_soldfinal, @regGramaj_categorie)";
                                Program.Connection.AddParameter("@regGramaj_date", DateTime.Today);
                                Program.Connection.AddParameter("@regGramaj_soldinitial", SoldGramaj[i]);
                                Program.Connection.AddParameter("@regGramaj_intrari", IntrariGramaj[i]);
                                Program.Connection.AddParameter("@regGramaj_vanzare", IesireGramaj[i]);
                                Program.Connection.AddParameter("@regGramaj_soldfinal", GramajFinal);
                                Program.Connection.AddParameter("@regGramaj_categorie", CatProdusNr[i]);
                                Program.Connection.ExecuteNonQuery();
                                Program.Connection.CommitTranzaction();

Posted 27-Oct-14 21:23pm

aciobanita constantin

Updated 29-Oct-14 6:22am

v2

Add a Solution

Comments

Maciej Los 28-Oct-14 4:56am

Show us the way you do that (post sample code).

aciobanita constantin 29-Oct-14 12:22pm

i have add some code to my question.

Richard Deeming 29-Oct-14 13:11pm

Where did the Program.Connection class come from? That doesn't look like any of the built-in ADO.NET objects.

aciobanita constantin 29-Oct-14 13:39pm

no, i have created that class for retrive, update and save information in database.

Richard Deeming 29-Oct-14 13:41pm

So where's the code for that class? We can't diagnose a problem with your code if you don't post it. :)

aciobanita constantin 29-Oct-14 13:55pm

ok,
public static DataConnection Connection;
[STAThread]
static void Main()
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("ro-RO");
Thread.CurrentThread.CurrentUICulture = new CultureInfo("ro-RO");

.......

here is the DataConection class:

class DataConnection
{
public System.Timers.Timer ConnectionDistructor;
public static System.Windows.Forms.TextBox Status = new System.Windows.Forms.TextBox();
public static string DataSource = "";
public static string Database = "";
public static string UserName = "";
public static string Password = "";
public static int ConnectionType = -1;
public static int SQLAuthenticationType;

public static void SaveSettings()
{
StreamWriter SW = new StreamWriter(System.Windows.Forms.Application.StartupPath + "\\Setari.dat");
SW.Write(Converter.Encrypt(DataSource));
SW.Write("\n" + Converter.Encrypt(Database));
SW.Write("\n" + Converter.Encrypt(UserName));
SW.Write("\n" + Converter.Encrypt(Password));
SW.Write("\n" + Converter.Encrypt(ConnectionType.ToString()));
SW.Write("\n" + Converter.Encrypt(SQLAuthenticationType.ToString()));
SW.Close(); SW = null;
(Program.Connection = GetConnection()).Open();
Program.Connection.InitialiseDatabase();
}

public static void GetSettings()
{
StreamReader SR = new StreamReader("Setari.dat");
DataSource = Converter.Decrypt(SR.ReadLine());
Database = Converter.Decrypt(SR.ReadLine());
UserName = Converter.Decrypt(SR.ReadLine());
Password = Converter.Decrypt(SR.ReadLine());
ConnectionType = Convert.ToInt32(Converter.Decrypt(SR.ReadLine()));
SQLAuthenticationType = Convert.ToInt32(Converter.Decrypt(SR.ReadLine()));
SR.Close(); SR = null;
}

public static DataConnection GetConnection()
{
if (((ConnectionTypes)ConnectionType) == ConnectionTypes.AccessConnection)
return new MyAccessConnection();
if (((ConnectionTypes)ConnectionType) == ConnectionTypes.MySQLConnection)
return new MySqlConnection(); return new MsSqlConnection();
}

public virtual void AddParameter(string Name, object Value) { }

public virtual void AddParameter(string Name, object Value, bool Encrypt) { }

public virtual void FillDataTable(DataTable Table, bool Decrypt) { }

public virtual string CommandText { set { } }

public virtual object ExecuteNonQuery()
{
return new object();
}

public virtual void Open() { }

public virtual void Close() { }

public virtual void InitialiseDatabase() { }

public virtual void InceputTranzactie() { }
public virtual void CommitTranzaction() { }
public virtual void RollBackTranzaction() { }
}

aciobanita constantin 29-Oct-14 13:57pm

class MyAccessConnection : DataConnection
{
OleDbConnection Connection;
OleDbCommand Command;
OleDbTransaction OperatieDate;

string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataSource + ";Persist Security Info=True;Jet OLEDB:Database Password=" + Password + ";";
public MyAccessConnection()
{
(Command = new OleDbCommand()).Connection = Connection = new OleDbConnection(ConnectionString);
ConnectionDistructor = new System.Timers.Timer(60000);
GC.KeepAlive(ConnectionDistructor);
}

public override void InceputTranzactie()
{
OperatieDate = Connection.BeginTransaction(IsolationLevel.ReadCommitted);
Command.Connection = Connection;
Command.Transaction = OperatieDate;
}
public override void CommitTranzaction()
{
OperatieDate.Commit();
}
public override void RollBackTranzaction()
{
OperatieDate.Rollback();
}
public override void FillDataTable(DataTable Table, bool Decrypt)
{
ConnectionDistructor.Enabled = false;
if (Connection.State == System.Data.ConnectionState.Closed) Connection.Open();
Status.Text = "1";
try
{
Table.Rows.Clear();
Table.Columns.Clear();
new OleDbDataAdapter(Command).Fill(Table);
//Table.Rows[1].ItemArray;

int StartIndex = 1;
try
{
if (Table.Rows.Count > 0)
Table.Rows[0][0].ToString();
if (Table.Rows.Count > 1)
Table.Rows[1][0].ToString();
}
catch { StartIndex++; }
for (int i = StartIndex; i < Table.Columns.Count; i++) for (int j = 0; j < Table.Rows.Count; j++)
Table.Rows[j][i] = Table.Rows[j][i].ToString();

}
catch (Exception Ex) { throw Ex; }
finally { Command.Parameters.Clear(); ConnectionDistructor.Enabled = true; }
}

public override object ExecuteNonQuery()
{
ConnectionDistructor.Enabled = false;
if (Connection.State == System.Data.ConnectionState.Closed) Connection.Open();
Status.Text = "1";
try
{
return Command.ExecuteNonQuery();
}
catch (Exception Ex) { throw Ex; }
finally { Command.Parameters.Clear(); ConnectionDistructor.Enabled = true; }
}

public override string CommandText
{
set
{
Command.CommandText = value;
}
}

public override void AddParameter(string Name, object Value)
{
AddParameter(Name, Value, true);
}

public override void AddParameter(string Name, object Value, bool Encrypt)
{
Command.Parameters.Add(new OleDbParameter(Name, Value));
}

public override void Open()
{
Connection.Open();
}

public override void Close()
{
Connection.Close();
}

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bernhard Hiller · Answer 1 · 2014-10-27T21:34:00

Solution 1

Use a parameterized query instead of a plain string query - it will handle those formatting quirks automatically (and also save you from SQL injection attacks).

Posted 27-Oct-14 21:34pm

Bernhard Hiller

Comments

Maciej Los 28-Oct-14 4:56am

Bernard, it's access database with its limitations. How parameterized queries can help in this case? If input value will be in wrong format, the output (result set) have to be wrong.

Raul Iloc · Answer 2 · 2014-10-27T22:15:00

1.There are two properties that are managing the current culture used in any .NET application:
i)Thread.CurrentThread.CurrentCulture - determine the default format for dates, times, numbers, currency values, the sorting order of text, casing conventions, and string comparisons.
ii)Thread.CurrentThread.CurrentUICulture - similar with the first one, but used in User Interface for displaying and getting the user inputs into a specific culture;

2.So you should use Thread.CurrentThread.CurrentCulture and set its value in your application to have the same value like the culture used by your database server. Better is to use an associated setting into your application config with default value "en-US".

3.You may find some ideas and details about this topic in my next article: MVC Basic Site: Step 1 – Multilingual Site Skeleton[^]

Using decimal or double to save data in database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us