SqlDataReader.GetString and sqlnullvalueexception

I am new to C#. I was executing some select queries from database tables using System.Data.SqlClient classes. I got sqlnullvalueexception while executing some select query. On googling I come to know that if the value is null in the database, SqlDataReader.GetString (or it's variants) will throw sqlnullvalueexception. What is the best coding practice for this?

if (!sqlDataReader.IsDBNull(n)) value = r.GetString(n);

Any better way of coding?

If you don't want to repeat this a lot, just create a helper function, like this:

public static class DataReaderExtensions
{
    public static string GetStringOrNull(this IDataReader reader, int ordinal)
    {
        return reader.IsDBNull(ordinal) ? null : reader.GetString(ordinal);
    }

    public static string GetStringOrNull(this IDataReader reader, string columnName)
    {
        return reader.GetStringOrNull(reader.GetOrdinal(columnName));
    }
}

Which you can call like this:

value = reader.GetStringOrNull(n);

SqlDataReader.IsDBNull(Int32) Method (System.Data.SqlClient , Create(DbDataReader dataReader) at Microsoft. SqlClient.SqlDataReader.​GetString(Int32 i) at lambda_method(Closure , DbDataReader ). I am using Entity Framework Core in an ASP.NET Core application and Controller action and I haven't changed something to the working code nor to the database but I can't tell what is the query performed by Entity Framework Core.

The code you posted is fine. You could also do something like that :

value = r[n] as string;

If the value in the database is null, r[n] will return DBNull.Value, and the cast to string will return null.

2.2: System.Data.SqlTypes.SqlNullValueException: 'Data is Null , Se non si vuole ripetere questo molto, basta creare una funzione di supporto, in questo modo: public static class DataReaderExtensions { public static string  System.Data.SqlTypes.SqlNullValueException was unhandled by user code HResult=-2146232015 Message=Data is Null. This method or property cannot be called on Null values.

That really is the best way to go about it if you wish to avoid any exceptions. You need to decide whether or not a null field represents an exceptional situation in your code - if it doesn't then use this method. If it does then I would suggest that you either allow the exception to be thrown or catch the exception and wrap it in a more meaniful exception and throw that one.

But the main thing to know is that this is the standard way to retrieve values from a data reader when a null field does not represent an exceptional situation in the application domain.

2.2.0-preview1: System.Data.SqlTypes.SqlNullValueException , SqlNullValueException: Data is Null. This method or property Data.SqlClient.​SqlDataReader.GetString(Int32 i) +96 SqlSiteMapProvider. 2.2: System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.' #14635 SecretivePerson opened this issue Feb 7, 2019 · 9 comments

This worked for me:

value = reader.GetValue(n).ToString();

c# - SqlDataReader.GetString e sqlnullvalueexception, En Google, llego a saber que si el valor es nulo en la base de datos, SqlDataReader.GetString (o sus variantes) lanzará sqlnullvalueexception. ¿Cuál es la  Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more ToListAsync() - SqlNullValueException: Data is Null.

How do I deal with a null value being read by the ADO.net , SqlNullValueException. The value of the column was null (IsDBNull(Int32) == true), retrieving a non-SQL type. InvalidCastException. T doesn't match the type returned

[SOLVED], Gets a value that indicates whether the column contains non-existent or missing values.

SqlDataReader.GetString y sqlnullvalueexception C#, The [Required] data annotation suddenly triggering this SqlNullValueException had us stuck for the last few months too. This update to EF Core 2.2.1 was part of a recent Azure Functions update so while our other services were working normally with the exact same queries, our Azure Functions started throwing these errors.

Comments
  • are you sure about the second method reader.IsDBNull(columnName)? I think it only takes an ordinal.
  • @AlexanderN - I think you're right. It should be reader.IsDBNull(reader.GetOrdinal(columnName))