C# .NET String to Decimal with currency formatting then to PostgreSQL Numeric while keeping the formatting.

See more:

C#

.NET

PostgreSQL

Connection is with npgsql, to PostgreSQL 9.2.

Currency value is taken in as string from a TextBox, which is then converted to Decimal, with formatting to have/allow decimal delimiter and group pointer for thousands.
The SQL table variable is of type numeric.

This is where I get the string and put it into the decimal variable:

decimal payIn;
if(!decimal.TryParse(textBoxPaymentIn.Text.Trim(), 
    NumberStyles.AllowThousands | NumberStyles.AllowDecimalPoint, CultureInfo.CurrentCulture, out payIn))
{
    //code to display message that txt_price doesn't have a valid value.
    MessageBox.Show("This is not a valid Incasari value! The format has to be" + "#,##0.00 or #.##0,00 according to global regional settings.");
    return;
}

This is where I would put the decimal variable's value as is into the database table's numeric type variable:

//Insert records
public void InsertRecord(NpgsqlTimeStamp docDate, NpgsqlTimeStamp regDate, string docNumber, string docType, string regDesc, string partnerCodeName, string journalType, decimal payIn, decimal payOut)
{
    try
    {
        using (NpgsqlConnection npgsqlConn = new NpgsqlConnection(connString))
        {
            // Open the PgSQL Connection.
            npgsqlConn.Open();

            insertCommand = String.Format( "INSERT INTO main.transaction  (documentdate,registrationdate,documentnumber," +                    

"documenttype,description,partnercodename,journaltype,paymentin,paymentout)" + 

"VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');",

docDate,regDate,docNumber,docType,regDesc,partnerCodeName,journalType,payIn,payOut
            );
    
            using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(insertCommand, npgsqlConn))
            {
                npgsqlCommand.ExecuteNonQuery();
            }                    
        }
    }
    catch (NpgsqlException ex)
    {
        throw ex;
    }
    catch (Exception ex)
    {
        //MessageBox.Show(ex.ToString());
        throw ex;
    }
}

The SQL line for the table column is:

paymentin numeric NOT NULL DEFAULT 0

But whatever I have tried, with comma as group pointer and dot as decimal delimiter, in the database for example 11,111,111.11 was always registered as 11111111.11 .

If I understand right, because of the

CultureInfo.CurrentCulture

the user is supposed to be able to use the dot and comma when entering the number according to what is set in the operating system's regional settings' format options for digit group pointer and decimal delimiter. So depending on what is set, either 1,111.11 or 1.111,11 could be valid.

But the database accepts only this #,###.## format.

How could the database numeric column get what is given from the C# formatted decimal? Or more precisely if the user enters a number with this #.###,## format then how can I make the database accept that?

Or should I store them as just varchar? But then how to perform monetary calculations without using a dataTable?