获取 DataTable 列字符表示的最大长度 [英] Get maximum length of DataTable column character representation

问题描述

我在 Access 数据库中有一个表，我正在尝试使用 C# 来获取列名和每列的字符串表示的最大长度.也就是说，如果表格如下所示:

I have a table in an Access database and I'm trying to use C# to get the column names and the maximum length of the string representations of each column. That is, if the table looks like this:

Name     ID  SysBP
-------------------
Jerry  1234  108.1
Tim     123  140.6
Marge     6   99.0

其中 ID 和 SysBP 列是数字列，我想要一个包含以下信息的 DataTable 对象:

Where the ID and SysBP columns are numeric columns, I want a DataTable object that contains the following information:

ColumnName  MaxCharLen
----------------------
Name        5
ID          4
SysBP       4

我有一个到数据库的 OLEDB 连接和两个 DataTable 对象，一个用于表架构，一个用于实际表.

I have an OLEDB connection to the database and two DataTable objects, one for the table schema and one for the actual table.

public DataTable GetMetadata(string tableName)
{
    // At this point the _oleConnection object exists and is open...
    OleDbCommand selectTable = new OleDbCommand("SELECT * FROM [" + tableName + "]",
        _oleConnection);

    OleDbDataReader oleReader = selectTable.ExecuteReader();

    // Column names from table schema
    DataTable schemaTable = oleReader.GetSchemaTable();
    schemaTables.Columns.Add("MaxCharLen", typeof(int));

    // Import full Access table as DataTable
    DataTable tableRecords = new DataTable();
    tableRecords.Load(oleReader);

    // Get maximum length of string representations by column
    // Populate MaxCharLen with that information

    ...???
}

谁能提供有关如何计算该字段的任何见解?

Can anyone provide any insight on how to go about calculating that field?

推荐答案

这就是我最终的做法.对我来说最有意义.在获取长度之前，数值被转换为字符串.感谢@Brad 的回答.

This is how I ended up doing it. Made the most sense to me. The numeric values are cast to strings before getting the lengths. Thanks @Brad for your answer.

public static T ConvertFromDBVal<T>(object obj)
{
    if (obj == null || Convert.IsDBNull(obj))
        return default(T);
    else
        return (T)obj;
}

public DataTable GetMetadata(string tableName)
{
    // Again, connection open at this point

    OleDbCommand selectTable = new OleDbCommand("SELECT * FROM [" +
        tableName + "]", _oleConnection);

    OleDbDataReader oleReader = selectTable.ExecuteReader();

    DataTable schemaTable = oleReader.GetSchemaTable().Copy();
    schemaTable.Columns.Add("_maxCharLength", typeof(int));

    foreach (DataRow schemaRow in schemaTable.Rows)
    {
        OleDbCommand getMax = new OleDbCommand();
        getMax.Connection = _oleConnection;

        if (schemaRow.Field<Type>("DataType") == typeof(string))
            getMax.CommandText = "SELECT MAX(LEN(" +
                schemaRow.Field<string>("ColumnName") + ")) FROM " +
                tableName;
        else
            getMax.CommandText = "SELECT MAX(LEN(STR(" +
                schemaRow.Field<string>("ColumnName") + "))) FROM " +
                tableName;

        int maxCharLength = ConvertFromDBVal<int>(getMax.ExecuteScalar());

        schemaRow.SetField("_maxCharLength", maxCharLength);

        getMax.Dispose();
        getMax = null;
    }

    ...

    return schemaTable;
}

这篇关于获取 DataTable 列字符表示的最大长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！