Zero integer from reader

Question

how do 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 can not 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) ); 

Answer 1

Just convert it like in the previous line

 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"] 

Answer 2

Write a simple package, for example. as an extension method and check 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); } 

using:

 var result = reader.SafeGetInt(colName); 

Answer 3

There are several approaches here. Unfortunately, DBNull makes this unpleasant - the API reader[name] returns an object , which can be your value, or it can be DBNull.Value - so you need to check this ( is ) and process it. Another approach is to use the reader.IsDBNull(ordinal) API, but as you will notice: this requires a sequence number (column index), not a name. In any 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 can make this a lot easier for you; one Query<T>(tsql, args).SingleOrDefault() will deal with all of this, including nulls, Nullable<T> and a number of other scripts.

Answer 4

Perhaps try this extension:

 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"); } 

Answer 5

Comparison against DBNull .

 var resPin = reader["Mem_ResPin"]; if(!Convert.IsDBNull(resPin)) mb.Mem_ResPin = resPin as int?; else mb.Mem_ResPin = new Nullable<int>(); 

Answer 6

I use the general extension method for all database throws:

 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; } 

Trying to consider every situation that I came across in our code. Adjust the conditions necessary for your situation.

Using:

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

Answer 7

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

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