Output variable values not returned from SQL Server SP in C#

 

In this post I’ll explain how to prevent the returning of null values from SQL Server stored procedures in c#

Today I created a simple SQL server stored procedure with an int output parameter and called that sp from a c# application. Everything worked fine except for the output parameter.

Data from the database were returned properly but the value of the output parameter was alsways 0. I used that parameter to get the total number of results from a server side paged result set. The results were returned fine but not the total.

Here’s the code snippet of the c# method

 

using (SqlConnection connection = new SqlConnection(ConnString))
{
    SqlCommand command = new SqlCommand("SearchTips", connection);

    command.Parameters.AddWithValue("@Query", query);
    command.Parameters.AddWithValue("@Total", 0);
    command.Parameters["@Total"].Direction = ParameterDirection.Output;
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
    Tips tips = new Tips();

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Tip tip = new Tip();
            tip.Title = reader.GetString(1);
            tip.Text = reader.GetString(2);
            tip.HTMLText = reader.GetString(3);

            tips.Values.Add(tip);
        } 
        tips.Total = Convert.ToInt32(command.Parameters["Total"].Value);
        tips.Start = start;

        reader.Close();

    }
    return tips;
}

 

It took me sometime to figure out the issue. Note that in the above code I am closing the connection after assigning the value of the Total variable. Actually first you have to close the reader and then retrieve the output parameter to get its value.

So here’s the modified and working code

using (SqlConnection connection = new SqlConnection(ConnString))
{
    SqlCommand command = new SqlCommand("SearchTips", connection);

    command.Parameters.AddWithValue("@Query", query);
    command.Parameters.AddWithValue("@Total", 0);
    command.Parameters["@Total"].Direction = ParameterDirection.Output;
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
    Tips tips = new Tips();

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Tip tip = new Tip();
            tip.Title = reader.GetString(1);
            tip.Text = reader.GetString(2);
            tip.HTMLText = reader.GetString(3);

            tips.Values.Add(tip);
        }  
        reader.Close();

        tips.Total = Convert.ToInt32(command.Parameters["@Total"].Value);
        tips.Start = start;

    }
    return tips;
}

Note that in here I am closing the reader before retrieving the parameter @Total.

And that solved the issue 😀

Happy coding