Alma Alma - 16 days ago 12
C# Question

Check if return value from stored procedure is null in C# using Microsoft Enterprise Library

I am using Microsoft Enterprise Library for database access and connection. I have a method that is executing a stored procedure that is returning a record.

My problem is when I wanted to check the result that is coming back from

ExecuteReader
, even if the stored procedure does not return anything the
result.FieldCount
is not 0 so it passes the

if (result.FieldCount == 0)


check and goes to else and try to read the empty data and goes to exception.

How I can check if the result has value?

This is my method:

public Model.Customer GetRegisteredCustomerUsernameandPass(int Customer_ID)
{
Model.Customer model = null;

string myConnection = System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ToString();
SqlDatabase db = new SqlDatabase(myConnection);

using (DbCommand command = db.GetStoredProcCommand("Get_CustomerByCustomerID"))
{
db.AddInParameter(command, "Customer_ID", DbType.Int32, Customer_ID);

var result = db.ExecuteReader(command);

try
{
if (result.FieldCount == 0)
model = null;
else
{
result.Read();

model = new Model.Customer()
{
Username = result.GetString(0),
Password = result.GetString(1)
};
}
}
catch (Exception ex)
{
}

return model;
}
}

Answer

From the documentation of FieldCount (emphasis mine):

Executing a query that, by its nature, does not return rows (such as a DELETE query), sets FieldCount to 0. However. this should not be confused with a query that returns 0 rows (such as SELECT * FROM table WHERE 1 = 2) in which case FieldCount returns the number of columns in the table

Instead, the Read method on the Reader advances the reader to the next record and returns a boolean denoting whether or not there are more rows. You can check this for false:

if (result.Read())
{
    model = new Model.Customer()
    {
        Username = result.GetString(0),
        Password = result.GetString(1)
    };
}

This is often used in a while loop when multiple rows are expected back:

while (result.Read())
{
    //do something with the current row
}

Alternatively you can use the HasRows property which:

Gets a value that indicates whether the SqlDataReader contains one or more rows.

Comments