netnr 2021-04-07 09:47:43 2021-05-12 21:45:48 👁1K 💬0

Microsoft.Data.Sqlite.Core 读取 DataReader 结果集,同一列将获得不同的类型

var ds = new DataSet();
var reader = cmd.ExecuteReader();
table.Load(reader); // ERROR
PRAGMA table_info('SysUser'); PRAGMA table_info('SysRole')

-- 该脚本为查询表信息
-- 设置字段的默认值时,`dflt_value` 列的返回类型不同
<PackageReference Include="Microsoft.Data.Sqlite.Core" Version="5.0.0" />
// 报错,返回的列类型不同

<PackageReference Include="System.Data.SQLite.Core" Version="1.0.113.6" />
// OK

Microsoft.Data.Sqlite.Core 存在该问题,System.Data.SQLite.Core 返回为 object ,给出的回复是不处理该问题:https://github.com/dotnet/efcore/issues/23490

兼容的方法,手动读取表,并指定列为 object 可解决该问题

/// <summary>
/// 查询返回数据集
/// </summary>
/// <param name="dbCommand"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(this DbCommand dbCommand)
{
    var ds = new DataSet();
    var reader = dbCommand.ExecuteReader();

    var isSQLite = dbCommand.Connection.GetType().FullName.ToLower().Contains("sqlite");
    // https://github.com/dotnet/efcore/issues/23490
    if (isSQLite)
    {
        do
        {
            var table = new DataTable
            {
                TableName = "table" + (ds.Tables.Count + 1).ToString()
            };

            var ctype = typeof(object);

            while (reader.Read())
            {
                if (table.Columns.Count == 0)
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        table.Columns.Add(new DataColumn(reader.GetName(i), ctype));
                    }
                }

                var dr = table.NewRow();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    dr[i] = reader.GetValue(i);
                }
                table.Rows.Add(dr.ItemArray);
            }
            ds.Tables.Add(table);

        } while (reader.NextResult());
    }
    else
    {
        do
        {
            var table = new DataTable
            {
                TableName = "table" + (ds.Tables.Count + 1).ToString()
            };
            table.Load(reader);
            ds.Tables.Add(table);
        } while (!reader.IsClosed);
    }

    return ds;
}

链接