ADO.NET what to use to set multiple output parameters - ExecuteScalar, ExecuteReader or ExecuteNonQuery or nothing?

Question

I have following working code. This has 1 input and two output parameters. Which ado.net method should I use?

OneInputTwoOutput oneInputTwoOutput = new OneInputTwoOutput();

var Param = new DynamicParameters();
Param.Add("@Input1", Input1);
Param.Add("@Output1", dbType: DbType.Boolean, direction: ParameterDirection.Output);
Param.Add("@Output2", dbType: DbType.Boolean, direction: ParameterDirection.Output);

try
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["connectionString"]))
    {
        using (SqlCommand cmd = new SqlCommand("GetOneInputTwoOutput", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@Input1", SqlDbType.Int).Value = Input1;
            cmd.Parameters.Add("@Output1", SqlDbType.Bit).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@Output2", SqlDbType.Bit).Direction = ParameterDirection.Output;

            con.Open();

            dealerStatus.Output1= (cmd.Parameters["@Output1"].Value != DBNull.Value ? Convert.ToBoolean(cmd.Parameters["@Output1"].Value) : false);
            dealerStatus.Output2= (cmd.Parameters["@Output2"].Value != DBNull.Value ? Convert.ToBoolean(cmd.Parameters["@Output2"].Value) : false);

            con.Close();
        }
    }
}
catch (SqlException err)
{
}

I read this link: Get output parameter value in ADO.NET

This suggests using cmd.ExecuteNonQuery(). But even not using this cmd.ExecuteNonQuery(), I am able to set the output parameters.

Can someone explain how? And what should be used here?

score 2 · Accepted Answer · answered Oct 04 '16 at 08:25

Pretty simple, really:

if you want to return a result set with exactly one row, one column (e.g. from a SELECT COUNT(*)... operation or something similar), then use ExecuteScalar() (in brief: result set - one row, one column)
if you want to return a result set with multiple row, multiple columns - then use ExecuteReader() to return a SqlDataReader that can then be used to iterate through the rows returned, and fetch all data (in brief: result set - multiple rows, multiple columns for each row)
if anything else (if you run a command that doesn't return any result set), then use ExecuteNonQuery(). This is typically used for INSERT, UPDATE, and DELETE operations, but it can also be used if your query just doesn't return a result set, but instead sets some output parameters (in brief: no result set returned)

ADO.NET what to use to set multiple output parameters - ExecuteScalar, ExecuteReader or ExecuteNonQuery or nothing?

1 Answers1