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
– vcsjones, Commented Aug 12, 2018 at 4:17
Relevant: When would SqlCommand.ExecuteReader() return null? — Tyler Roper
– Tyler Roper, Commented Aug 12, 2018 at 4: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. — Richardissimo
– Richardissimo, Commented Aug 12, 2018 at 5:34
The question has not been tagged with 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
– Richardissimo, Commented Aug 12, 2018 at 5:46

Hossein · Accepted Answer · 2018-08-12 07:08:00Z

2

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());

            }

edited Aug 12, 2018 at 7:08

answered Aug 12, 2018 at 4:59

Hossein

3,1233 gold badges18 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richardissimo Over a year ago

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.

John H · Accepted Answer · 2018-08-24 10:54:01Z

1

for checking null ,try this:

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

answered Aug 24, 2018 at 10:54

John H

315 bronze badges

Collectives™ on Stack Overflow

Net Core -- Sql Data Reader suddenly null

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related