C＃.NET字符串到十进制，带有货币格式，然后到PostgreSQL数字，同时保持格式。 [英] C# .NET String to Decimal with currency formatting then to PostgreSQL Numeric while keeping the formatting.

问题描述

与npgsql连接到PostgreSQL 9.2。



货币值从TextBox中作为字符串获取，然后转换为Decimal，格式为/允许数千的小数分隔符和组指针。

SQL表变量的数字类型。



这是我得到字符串的地方并将其放入十进制变量：

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;
    }
}

表格列的SQL行是：

The SQL line for the table column is:

paymentin numeric NOT NULL DEFAULT 0

但无论我尝试过什么，用逗号作为组指针和点作为十进制分隔符，在数据库中例如11,111,111.11总是注册为11111111.11。



如果我理解正确，因为

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

根据操作系统的区域设置中为数字组设置的格式选项输入数字时，用户应该能够使用点和逗号指针和小数分隔符。因此，根据设置的内容，1,111.11或1.111,11可能有效。



但数据库仅接受此＃，###。##格式。



数据库数字列如何从C＃格式化的十进制中得到什么？或者更确切地说，如果用户输入带有＃。###，##格式的数字，那么我该如何让数据库接受呢？



或者我应该存储他们只是varchar？但是如何在不使用dataTable的情况下执行货币计算？

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?

推荐答案

如果使用数字类型，数据库会存储该数字。因此，它具有值并且没有渲染细节。您看到的格式始终是默认格式或您要求的格式。存储的值不会改变。

The database stores the number if you use a numeric type. Thus, it has a value and no rendering details. The format you see, will always be the default or the one you ask for. The value stored does not change.

这篇关于C＃.NET字符串到十进制，带有货币格式，然后到PostgreSQL数字，同时保持格式。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！