I am trying to return an output parameter from a stored procedure that I have in my database. However, I get an error: "Object reference not set to an instance of an object.", because my parameter is return as Null.
Here is my code:
public IList<UserExperimentHistory> FindPaginated(int pageNumber, int rowsByPage, string orderByType, out int totalCount)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand(FindPaginatedSql, connection);
command.Parameters.AddWithValue("@OrderBy", orderByType);
command.Parameters.AddWithValue("@RecordFrom", pageNumber * rowsByPage - rowsByPage + 1);
command.Parameters.AddWithValue("@RecordTo", pageNumber * rowsByPage);
SqlParameter output = new SqlParameter("@Rows", SqlDbType.Int);
output.Direction = ParameterDirection.Output;
command.Parameters.Add(output);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
totalCount = (int)output.Value;
var experiments = new List<UserExperimentHistory>();
while (reader.Read())
{
experiments.Add(ReadUserExperiment(reader));
}
return experiments;
}
//if (connection.State != ConnectionState.Open) connection.Open();
//command.ExecuteNonQuery();
//totalCount = Convert.ToInt32(command.Parameters["@Rows"].Value);
}
}