.net c#来自读取器的可为空的整数值

Nullable integer values from reader
2021-06-20
  •  译文(汉语)
  •  原文(英语)

我们如何从Sql Data Reader中读取整数null值

SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read() == true)
{
    mb.Id = (int)reader["Id"];
    mb.Mem_NA = (string)reader["Mem_NA"];
    mb.Mem_ResAdd4 = reader["Mem_ResAdd4"] == System.DBNull.Value ? null : (string)reader["Mem_ResAdd4"];
    //
   mb.Mem_ResPin = reader["Mem_ResPin"] as  int? ?? default(int);
  // shows the error "Object cannot be cast from DBNull to other types."
 }

mb.Mem_ResPin无法从读取器读取

 CREATE TABLE [dbo].[Mem_Basic] (
[Id]          INT           IDENTITY (1, 1) NOT NULL,
[Mem_NA]      VARCHAR (100) NOT NULL,
[Mem_ResAdd4] VARCHAR (100) NULL,
[Mem_ResPin]  INT           NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
速聊1:
什么是mb.Mem_ResPin声明?
速聊2:
我知道我们已经进行了这次对话,但是"精简版"可以使这项工作正常进行,而无需您在每一步中都对ADO.NET有所了解...只是说说-我们编写它的原因是...避免正是这样的痛苦
速聊3:
,我可以充分证明为什么Dapper是当今市场上最好的ORM!而且它是免费的!
解决过程1

就像上一行一样进行转换

mb.Mem_ResAdd4 = reader["Mem_ResAdd4"] == System.DBNull.Value ? null : (string)reader["Mem_ResAdd4"];
//
    mb.Mem_ResPin = reader["Mem_ResPin"]== System.DBNull.Value ? default(int):(int)reader["Mem_ResPin"]
速聊1:
显示错误"无法将对象从DBNull强制转换为其他类型".
速聊2:
看到我对DBNull治疗的反应
解决过程2

我对所有数据库强制转换使用通用扩展方法:

public static T? DbCast<T>(this object dbValue)
        where T : struct
    {
        if (dbValue == null)
        {
            return null;
        }
        if (dbValue is System.DBNull)
        {
            return null;
        }
        T? value = dbValue as T?;
        if (value != null)
        {
            return value;
        }
        var conv = dbValue as IConvertible;
        if (conv != null)
        {
            value = (T)conv.ToType(typeof(T), CultureInfo.InvariantCulture);
        }
        return value;
    }

它试图处理到目前为止我们遇到的每一种情况.根据需要调整条件.

用法:

int? value = reader["Mem_ResAdd4"].DbCast<int>()
解决过程3

编写一个简单的包装,例如作为扩展方法并检查IsDBNull内部:

public static int SafeGetInt(this SqlDataReader reader, string colName)
{
    var colIndex = reader.GetOrdinal(colName);
    return !reader.IsDBNull(colIndex) ? reader.GetInt32(colIndex) : default(int);
}

用法:

var result = reader.SafeGetInt(colName);
解决过程4

也许尝试这种扩展:

public static class Helper
{
    public static T GetSafe<T>(this SqlDataReader reader, string name)
    {
        var value = reader[name];
        return value == DBNull.Value ? default(T) : (T) value;
    }
}

并像这样使用:

        if (reader.Read())
        {
            Mb mb = new Mb();
            mb.Id = reader.GetSafe<int>("Id");
            mb.Mem_NA = reader.GetSafe<string>("Mem_NA");
            mb.Mem_ResAdd4 = reader.GetSafe<string>("Mem_ResAdd4");

            mb.Mem_ResPin = reader.GetSafe<int>("ResPin");
        }
解决过程5

这里有几种方法.不幸的是,这样DBNull做很麻烦reader[name]-API返回的aobject可能是您的值,也可能是DBNull.Value-您必须检查(is)并进行处理.另一种方法是使用reader.IsDBNull(ordinal)API,但您会注意到:需要序号(列索引)而不是name.在这两种情况下,您都可以添加诸如实用程序方法之类的内容以帮助:

static object Read(IDataReader reader, string name)
{
    var val = reader[name];
    return val is DBNull ? (object)null : val;
}

然后(例如):

mb.Mem_ResPin = (int?)Read(reader, "Mem_ResPin")

但是,再次:"dapper"之类的工具可能会让您更轻松;一个Query<T>(tsql, args).SingleOrDefault()可以处理所有这些问题,包括null Nullable<T>,和一系列其他情况.

速聊1:
此代码将导致强制转换为(int?).首先,您需要取消装箱到常规int.
速聊2:
不,你不知道.试试吧.更糟糕的是:取消装箱到int(而不是int?)实际上会使它NullReferenceException在值是时抛出一个null(这显然是这里的预期情况)
解决过程6

您可以 SqlDataReader.GetSqlInt32用来处理Nullable<int>:

SqlInt32 resPin = reader.GetSqlInt32(reader.GetOrdinal("Mem_ResPin"));
mb.Mem_ResPin = resPin.IsNull ? (int?) null : resPin.Value;
解决过程7

比较是反对DBNull.

var resPin = reader["Mem_ResPin"];
if(!Convert.IsDBNull(resPin))
  mb.Mem_ResPin = resPin as int?;
else
  mb.Mem_ResPin = new Nullable<int>();
速聊1:
您确定mb.Mem_ResPin是一种int?类型吗?

how we read integer null values from Sql Data Reader

SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read() == true)
{
    mb.Id = (int)reader["Id"];
    mb.Mem_NA = (string)reader["Mem_NA"];
    mb.Mem_ResAdd4 = reader["Mem_ResAdd4"] == System.DBNull.Value ? null : (string)reader["Mem_ResAdd4"];
    //
   mb.Mem_ResPin = reader["Mem_ResPin"] as  int? ?? default(int);
  // shows the error "Object cannot be cast from DBNull to other types."
 }

mb.Mem_ResPin cant read from reader

 CREATE TABLE [dbo].[Mem_Basic] (
[Id]          INT           IDENTITY (1, 1) NOT NULL,
[Mem_NA]      VARCHAR (100) NOT NULL,
[Mem_ResAdd4] VARCHAR (100) NULL,
[Mem_ResPin]  INT           NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
Talk1:
What is mb.Mem_ResPin declared as?
Talk2:
I know we've already had this conversation, but "dapper" would just make this work without you banging your head against ADO.NET every step of the way... just sayin' - there's a reason we wrote it... to avoid exactly this kind of pain
Talk3:
, and I can fully attest to why Dapper is the best ORM on the market today! And it's free no less!
Solutions1

Just convert it, like you do in previous row

mb.Mem_ResAdd4 = reader["Mem_ResAdd4"] == System.DBNull.Value ? null : (string)reader["Mem_ResAdd4"];
//
    mb.Mem_ResPin = reader["Mem_ResPin"]== System.DBNull.Value ? default(int):(int)reader["Mem_ResPin"]
Talk1:
shows the eror "Object cannot be cast from DBNull to other types."
Talk2:
see my response for DBNull treatment
Solutions2

I use a generic extension method for all DB casts:

public static T? DbCast<T>(this object dbValue)
        where T : struct
    {
        if (dbValue == null)
        {
            return null;
        }
        if (dbValue is System.DBNull)
        {
            return null;
        }
        T? value = dbValue as T?;
        if (value != null)
        {
            return value;
        }
        var conv = dbValue as IConvertible;
        if (conv != null)
        {
            value = (T)conv.ToType(typeof(T), CultureInfo.InvariantCulture);
        }
        return value;
    }

It tries to treat every situation we encountered so far. Adjust conditions as needed for you.

Usage:

int? value = reader["Mem_ResAdd4"].DbCast<int>()
Solutions3

Write a simple wrapper e.g. as an extension method and check for IsDBNull inside:

public static int SafeGetInt(this SqlDataReader reader, string colName)
{
    var colIndex = reader.GetOrdinal(colName);
    return !reader.IsDBNull(colIndex) ? reader.GetInt32(colIndex) : default(int);
}

usage:

var result = reader.SafeGetInt(colName);
Solutions4

Maybe try this extention:

public static class Helper
{
    public static T GetSafe<T>(this SqlDataReader reader, string name)
    {
        var value = reader[name];
        return value == DBNull.Value ? default(T) : (T) value;
    }
}

and use like this:

        if (reader.Read())
        {
            Mb mb = new Mb();
            mb.Id = reader.GetSafe<int>("Id");
            mb.Mem_NA = reader.GetSafe<string>("Mem_NA");
            mb.Mem_ResAdd4 = reader.GetSafe<string>("Mem_ResAdd4");

            mb.Mem_ResPin = reader.GetSafe<int>("ResPin");
        }
Solutions5

There are a few approaches here. Unfortunately, DBNull makes this vexing - the reader[name] API returns an object that could be your value, or that could be DBNull.Value - so you have to check for that (is) and handle it. The other approach is to use the reader.IsDBNull(ordinal) API, but as you will note : that needs an ordinal (column index) rather than a name. In either case, you can add things like utility methods to help:

static object Read(IDataReader reader, string name)
{
    var val = reader[name];
    return val is DBNull ? (object)null : val;
}

then (for example):

mb.Mem_ResPin = (int?)Read(reader, "Mem_ResPin")

However, again: tools like "dapper" might make this a lot easier for you; a single Query<T>(tsql, args).SingleOrDefault() would deal with all of this, including nulls, Nullable<T>, and a range of other scenarios.

Talk1:
This code would result in a cast error on casting to (int?). You first need to unbox to a regular int.
Talk2:
no, you don't. Try it. Worse: unboxing to int (rather than int?) would actually make it throw a NullReferenceException whenever the value is null (which is explicitly an expected scenario here)
Solutions6

You can use SqlDataReader.GetSqlInt32 to handle Nullable<int>:

SqlInt32 resPin = reader.GetSqlInt32(reader.GetOrdinal("Mem_ResPin"));
mb.Mem_ResPin = resPin.IsNull ? (int?) null : resPin.Value;
Solutions7

Compare is against DBNull.

var resPin = reader["Mem_ResPin"];
if(!Convert.IsDBNull(resPin))
  mb.Mem_ResPin = resPin as int?;
else
  mb.Mem_ResPin = new Nullable<int>();
Talk1:
Are you sure mb.Mem_ResPin is an int? type?
转载于:https://stackoverflow.com/questions/18530627/nullable-integer-values-from-reader

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入