无法使用OdbcDataReader加载DataTable [英] Cannot load DataTable with OdbcDataReader

问题描述

我有一个通过ODBC查询的C-Tree数据库，如下所示:

I have a query running over ODBC for a C-Tree database which looks like this:

SELECT id, CONCAT(TO_CHAR(first_visit_date, 'YYYY-MM-DD '), 
TO_CHAR('00:00:00', 'HH24')) AS date_first_visit FROM admin.v_patient

返回的结果如下:

1, 2015-03-07 00:00:00  
2, 2016-01-27 00:00:00

当然，完整的查询要大得多，但这是唯一给我带来麻烦的字段.当我尝试使用以下方法加载数据时:

Of course the full query is much larger, but this is the only field giving me trouble. When I try loading the data using this:

using (OdbcConnection conn = new OdbcConnection(connectionString))
{
    conn.Open();

    using (OdbcCommand com = new OdbcCommand(cmdText, conn))
    {
        using (OdbcDataReader reader = com.ExecuteReader())
        {
            var rawData = new DataTable();
            rawData.Load(reader);
        }
    }
}

我收到以下错误:

An exception of type 'System.Data.ConstraintException' occurred in System.Data.dll but was not handled in user code
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

我尝试将查询更新为

SELECT id, '2015-03-07 00:00:00 ' AS date_first_visit 
FROM admin.v_patient

有效...应该是完全一样的东西，除了现在每一行都有相同的日期.对于笑容，我尝试使用以下方法将其转换为varchar:

which works... It should be the exact same thing, except now every row has the same date. For grins I tried converting that to varchar using this:

CONVERT('VARCHAR', TO_CHAR(first_visit_date, \'YYYY-MM-DD 00:00:00\') )

和其他方式，但是真正的麻烦似乎是在加载DataTable，但是我无法弄清楚查询结果与我的示例(仅指定一个字符串)之间的区别.

and other ways, but the real trouble seems to be loading the DataTable, but I can't figure out what the difference would be between the results from the query, or the one in my example where I just specify a string.

我没有在表上定义任何约束，这似乎消除了 null或外键约束的错误，并且因为我可以在每一行中重复使用相同的日期，这似乎使 unique 消失了，这让我想知道到底这是怎么回事.

I haven't defined any constraints on the table, which seems to wipe out the errors of non-null, or foreign-key constraints, and since I can reuse the same date in every row, that seems to wipe out unique, which leaves me wondering what the heck is wrong.

推荐答案

用以下代码替换 rawData.Load(reader)可以解决此问题:

Replacing the rawData.Load(reader) with this code fixes the issue:

DataTable dtSchema = reader.GetSchemaTable();
List<DataColumn> listCols = new List<DataColumn>();
var rawData = new DataTable();
if (dtSchema != null)
{
    foreach (DataRow drow in dtSchema.Rows)
    {
        string columnName = System.Convert.ToString(drow["ColumnName"]);
        DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
        column.Unique = false;
        column.AllowDBNull = true;
        column.AutoIncrement = false;

        listCols.Add(column);
        rawData.Columns.Add(column);
    }
}
while (reader.Read())
{
    DataRow dataRow = rawData.NewRow();
    for (int i = 0; i < listCols.Count; i++)
    {
        dataRow[((DataColumn)listCols[i])] = reader[i];
    }
    rawData.Rows.Add(dataRow);
}

这篇关于无法使用OdbcDataReader加载DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！