检查对数据库记录映射空值 [英] Checking for Nulls on DB Record Mapping

问题描述

我怎样才能在附带的代码检查分贝空值？请理解我是新的C＃转换...

How can I check for db null values in the attached code? Please understand I am a new C# convert...

这段代码确实是需要一个IDataReader的对象，转换和映射到对象的强类型列表。但我发现它是完全错误出来的时候有空列在读者返回。

What this code does is takes a IDataReader object and converts and maps it to a strongly-typed list of objects. But what I am finding is it completely errors out when there are null columns returned in the reader.

internal class Converter<T> where T : new()
{
    // Declare our _converter delegate
    readonly Func<IDataReader, T> _converter;
    // Declare our internal dataReader
    readonly IDataReader dataReader;

    // Build our mapping based on the properties in the class/type we've passed in to the class
    private Func<IDataReader, T> GetMapFunc()
    {
        // declare our field count
        int _fc = dataReader.FieldCount;
        // declare our expression list
        List<Expression> exps = new List<Expression>();
        // build our parameters for the expression tree
        ParameterExpression paramExp = Expression.Parameter(typeof(IDataRecord), "o7thDR");
        ParameterExpression targetExp = Expression.Variable(typeof(T));
        // Add our expression tree assignment to the exp list
        exps.Add(Expression.Assign(targetExp, Expression.New(targetExp.Type)));
        //does int based lookup
        PropertyInfo indexerInfo = typeof(IDataRecord).GetProperty("Item", new[] { typeof(int) });
        // grab a collection of column names from our data reader
        var columnNames = Enumerable.Range(0, _fc).Select(i => new { i, name = dataReader.GetName(i) }).AsParallel();
        // loop through all our columns and map them properly
        foreach (var column in columnNames)
        {
            // grab our column property
            PropertyInfo property = targetExp.Type.GetProperty(column.name, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
            // check if it's null or not
            if (property != null)
            {
                // build our expression tree to map the column to the T
                ConstantExpression columnNameExp = Expression.Constant(column.i);
                IndexExpression propertyExp = Expression.MakeIndex(paramExp, indexerInfo, new[] { columnNameExp });
                UnaryExpression convertExp = Expression.Convert(propertyExp, property.PropertyType);
                BinaryExpression bindExp = Expression.Assign(Expression.Property(targetExp, property), convertExp);
                // add it to our expression list
                exps.Add(bindExp);
            }
        }
        // add the originating map to our expression list
        exps.Add(targetExp);
        // return a compiled cached map
        return Expression.Lambda<Func<IDataReader, T>>(Expression.Block(new[] { targetExp }, exps), paramExp).Compile();
    }

    // initialize
    internal Converter(IDataReader dataReader)
    {
        // initialize the internal datareader
        this.dataReader = dataReader;
        // build our map
        _converter = GetMapFunc();
    }

    // create and map each column to it's respective object
    internal T CreateItemFromRow()
    {
        return _converter(dataReader);
    }
}

映射

Mapper

    private static IList<T> Map<T>(DbDataReader dr) where T : new()
    {
        try
        {
            // initialize our returnable list
            List<T> list = new List<T>();
            // fire up the lamda mapping
            var converter = new Converter<T>(dr);
            while (dr.Read())
            {
                // read in each row, and properly map it to our T object
                var obj = converter.CreateItemFromRow();
                // add it to our list
                list.Add(obj);
            }
            // reutrn it
            return list;
        }
        catch (Exception ex)
        {
            // make sure this method returns a default List
            return default(List<T>);
        }
    }

我只是不太明白其中列类型的对象发生在这里，所以我想尝试做我自己......但我就是不;知道它在哪里。

I just don't quite understand where the column to typed object happens in here, so I'd try to do it myself... but I just don;t know where it is.

我知道这可能赢得 ŧ太大的帮助，但我得到的错误是：

I know this probably won't help much, but the error I am getting is:

无法投类型的对象'System.DBNull'输入'System.String

和它发生在

internal T CreateItemFromRow()
    {
        return _converter(dataReader); //<-- Here
    }

注意

这不，如果我换在查询本身的列与ISNULL发生（列，''），但我相信你能明白，这肯定不是一个解决方案。

Note

This does not happen if I wrap the columns in the query itself with an ISNULL(column, ''), but I am sure you can understand that this is surely not a solution

推荐答案

问题在于行 convertExp = Expression.Convert（propertyExp，property.PropertyType） 。你不能指望的DBNull 值转换为等值的框架类型。这是特别讨厌当你的类型是值类型。一种选择是，以检查是否从数据库读取值 DbNull.Value 和的情况下，是的，你需要给自己找一个兼容的值。在部分的情况下，人们都OK在C＃中这些类型的默认值。如果你必须这样做。

The problem lies in the line convertExp = Expression.Convert(propertyExp, property.PropertyType). You can't expect to convert DbNull value to its equivalent in framework type. This is especially nasty when your type is a value type. One option is to check if the read value from db is DbNull.Value and in case yes, you need to find a compatible value yourself. In some cases people are ok with default values of those types in C#. If you have to do this

property = value == DBNull.Value ? default(T): value;

一个通用的实施将看起来像（至于的foreach 在转换器类云）：

foreach (var column in columns)
{
    var property = targetExp.Type.GetProperty(
        column.name,
        BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
    if (property == null)
        continue;

    var columnIndexExp = Expression.Constant(column.i);
    var propertyExp = Expression.MakeIndex(
        paramExp, indexerInfo, new[] { columnIndexExp });
    var convertExp = Expression.Condition(
        Expression.Equal(
            propertyExp, 
            Expression.Constant(DBNull.Value)), 
        Expression.Default(property.PropertyType), 
        Expression.Convert(propertyExp, property.PropertyType));
    var bindExp = Expression.Assign(
        Expression.Property(targetExp, property), convertExp);
    exps.Add(bindExp);
}

现在这样做的。

property = reader[index] == DBNull.Value ? default(T): reader[index];

您可以通过将其分配给一个变量，并在使用它的值避免读者的双重查找有条件的检查。所以这应该是略好，但律'更复杂的：

You could avoid the double lookup of the reader by assigning it to a variable and using its value in the conditional check. So this should be marginally better, but a lil' more complex:

foreach (var column in columns)
{
    var property = targetExp.Type.GetProperty(
        column.name,
        BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
    if (property == null)
        continue;

    var columnIndexExp = Expression.Constant(column.i);
    var cellExp = Expression.MakeIndex(
        paramExp, indexerInfo, new[] { columnIndexExp });
    var cellValueExp = Expression.Variable(typeof(object), "o7thPropValue");
    var convertExp = Expression.Condition(
        Expression.Equal(
            cellValueExp, 
            Expression.Constant(DBNull.Value)), 
        Expression.Default(property.PropertyType), 
        Expression.Convert(cellValueExp, property.PropertyType));
    var cellValueReadExp = Expression.Block(new[] { cellValueExp },
        Expression.Assign(cellValueExp, cellExp), convertExp);
    var bindExp = Expression.Assign(
        Expression.Property(targetExp, property), cellValueReadExp);
    exps.Add(bindExp);
}

这确实有条件的检查是这样的：

This does the conditional check this way:

value = reader[index];
property = value == DBNull.Value ? default(T): value;

这篇关于检查对数据库记录映射空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！