CODEWITHSUNDEEP
c#sql.netsql-serverstored-procedures
isxaker
isxaker

Reputation: 9466

Which method of the SqlCommand is more preferable when i need to get only out params?

I'm asking about only sync methods of the SqlCommand class. There are three methods (as everyone know) - ExecuteReader(), ExecuteScalar() and ExecuteNonQuery().

Which kind of this methods is more suitable for stored procedure likes this :

CREATE PROCEDURE [dbo].[pr_test]
    @partherId UNIQUEIDENTIFIER,
    @lowerBound SMALLINT = -1 out,
    @upperBound SMALLINT = -1 out
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        @lowerBound = ISNULL(MIN(SrartDayNumber), -1)
        ,@upperBound = ISNULL(MAX(EndDayNumber), -1)
    FROM [CpsOther].[dbo].[FinDocument] f
    WHERE f.partherId = @partherId
END

I need only out params and nothing else. I don't know which method of the SqlCommand is more suitable in this situation? Or it's doesn't matter. (The results are same)

int lowerBound = -1;
int upperBound = -1;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
    using (SqlCommand command = new SqlCommand())
    {
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "[dbo].[pr_test]";

        SqlParameter lowerBoundParam = new SqlParameter
                                       {
                                           ParameterName = "@lowerBound",
                                           Value = lowerBound,
                                           Direction = ParameterDirection.Output
                                       };
        SqlParameter upperBoundParam = new SqlParameter
                                       {
                                           ParameterName = "@upperBound",
                                           Value = upperBound,
                                           Direction = ParameterDirection.Output
                                       };
        command.Parameters.AddWithValue("@partnerId", Guid.Empty);
        command.Parameters.Add(lowerBoundParam);
        command.Parameters.Add(upperBoundParam);

        connection.Open();

        object result = command.ExecuteScalar();
        //or object result = command.ExecuteNonQuery();
        lowerBound = lowerBoundParam.Value as int? ?? -1;
        lowerBound = lowerBoundParam.Value as int? ?? -1;
    }
}

Upvotes: 0

Views: 65

Answers (1)

Michael Gunter
Michael Gunter

Reputation: 12811

ExecuteNonQuery is the better solution for this. The other two are for commands that return a rowset.

To elaborate:

  • ExecuteReader is for situations where you want to iterate over a set of rows being returned by the command.

  • ExecuteScalar is for situations where you want to receive the first column of the first row being returned. It will automatically discard all other row data.

  • ExecuteNonQuery is for commands that do not return rowsets directly.

They all have the same abilities as regards parameters with directions of Output, InputOutput or ReturnValue. The only difference is how they deal with rowsets.

Upvotes: 4

Related Questions