Net Core -- Sql Data Reader suddenly null

Question

NullReferenceException: Object reference not set to an instance of an object. WebApplication1.Controllers.LabsDal.GetDefinition(string connectionString, string key) in LabsDal.cs on line while (rdr2.Read())

conn.Open();
using (var cmd = new SqlCommand("Lab_GetDefinitionList", conn))
{
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@key", key);
    SqlDataReader rdr2 = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
    while (rdr2.Read())  // <---- ERROR HERE
    {
        result.Elements.Add(rdr2.GetString(0));
    }
}

I have tried many variation and have made no progress. Else where the equivalent code is working fine.

To my knowledge `ExecuteReader` will never return null, which is the only way I can see a null-ref happening on that line. What does the debugger say is going on? — vcsjones, Aug 12 '18 at 04:17
Relevant: [When would SqlCommand.ExecuteReader() return null?](https://stackoverflow.com/questions/1072093/when-would-sqlcommand-executereader-return-null) — Tyler Roper, Aug 12 '18 at 04:22
Unrelated tips: SqlDataReader is IDisposable so should be in a `using` block. You may want to take a look at [we stop using AddWithValue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). — Richardissimo, Aug 12 '18 at 05:34
The question has not been tagged with [tag:sql-server] so can you confirm that the SqlCommand being used here is the out-of-box one, rather than a home-brewed one with the same name. — Richardissimo, Aug 12 '18 at 05:46

Hossein · Answer 1 · 2018-08-12T07:08:00.050

You need to check for IsDBNull:

rdr2.Read()
if(rdr2.HasRows)
{
    if(!rdr2.IsDBNull(colIndex))
       result.Elements.Add(rdr2.GetString(colIndex));
}

Or you can use SqlDataAdapter :

using (SqlCommand cmd = new SqlCommand())
            {
                DataSet ds = new DataSet();
                cmd.Connection = new SqlConnection(connectionString);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandTimeout = 900;
                cmd.CommandText = "Lab_GetDefinitionList";
                cmd.Parameters.AddWithValue("@key", key);

                cmd.Connection.Open();
                //
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;

                da.Fill(ds);
                cmd.Connection.Close();

                // **check if return data**
                if(ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                     result.Elements.Add(ds.Tables[0].Rows[0]["YourColumnName"].ToString());

            }

It's more common to create the connection outside the command (as has been done in the question), since you may do multiple commands within the same connection. You don't need the `if` before opening the connection, it will always need opening after creation and before use. SqlConnection and SqlDataAdapter are both IDisposable so each should be in a `using` block. Once you've done that the Close is redundant since the implicit Dispose when exiting that block will call that. When only reading the first value from the first row, ExecuteScalar is much simpler. — Richardissimo, Aug 12 '18 at 05:44

score 1 · Answer 2 · answered Aug 24 '18 at 10:54

1

for checking null ,try this:

if(rdr2.HasRows)
{
    if(!rdr2.IsDBNull(colIndex))
       result.Elements.Add(rdr2.GetString(colIndex));
}

answered Aug 24 '18 at 10:54

John H

31
5

Net Core -- Sql Data Reader suddenly null

2 Answers2