C#数据表精确小数

本文关键字：小数 数据表 | 更新日期: 2023-09-27 17:58:10

我有这样的代码来向数据表添加新列：

DataColumn col=新的DataColumn（"column"，typeof（decimal））；col.Caption="列"；神话般的。列。Add（col）；

如何为该列指定小数精度，使值始终为我希望的格式？

C#数据表精确小数

您不能。但是，当您使用String.Format函数从表中检索值时，您可以格式化该值：

String.Format("{0:0.##}", (Decimal) myTable.Rows[rowIndex].Columns[columnIndex]);

我自己也遇到过同样的问题，我在应用程序启动时加载了整个架构，然后根据需要引用架构中的列信息，从而解决了这个问题。

我只有一个Visual Basic的例子，但希望它能很容易地翻译成C#

设置

' in whatever class you do your database communication:
Private _database As SqlDatabase
Private Shared _schema As DataTable
Sub New()
  ' or however you handle the connection string / database creation
  Dim connectionString as String = GetConnectionString()
  _database = New SqlDatabase(connectionString)
  RetrieveSchema()
End Sub

Private Function RetrieveSchema() as DataTable
  If _schema Is Nothing Then
    Using connection As SqlConnection = _database.CreateConnection()
      connection.Open()
      _schema = connection.GetSchema("Columns")
    End Using
  End If
  return _schema
End Function

Public Function GetColumnInformation(tableName As String, columnName As String) as DataRow
  Dim firstMatchingRow as DataRow = (
    From row In _schema.Rows _
    Where (
      row("TABLE_NAME") = tableName AndAlso row("COLUMN_NAME") = columnName)
    )).FirstOrDefault()
  Return firstMatchingRow
End Function

用法

Dim columnInformation As DataRow = Dal.GetColumnInformation(tableName, columnName)
' find the precision
Dim precision = columnInformation("NUMERIC_PRECISION")
Dim scale = columnInformation("NUMERIC_SCALE")
' convert the decimal to the column's format
' e.g.: 2.345 with a scale of 2 would result in 
'       2.35
value = Decimal.Round(value, scale)

这个答案是NullVoxPopuli答案的C#版本。既然我需要这个，我想我应该分享它，为人们节省一些时间。

它将架构加载到DataColumnCollection中，然后将该DataColumnCollection添加到新的DataTable中。如果您想重用列定义，可以将这两个部分拆分为函数。

        var ConnectionString = "bla";
        DataColumnCollection Columns;
        
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            con.Open();
            using(SqlCommand command = new SqlCommand("select top 1 * from trades", con))
            {
                using (var r = command.ExecuteReader())
                {
                    using(var dt = new DataTable())
                    {
                        dt.Load(r);
                        Columns = dt.Columns;
                    }
                }
                
            }
            con.Close();
        }
        
        
        
        DataTable DataTable = new DataTable();
        while(Columns.Count > 0)
        {
            DataColumn c = Columns[0];
            c.Table.Columns.Remove(c);
            DataTable.Columns.Add(c);
        }
        Columns = DataTable.Columns;
        
        // use DataTable;
相关文章：