CODEWITHSUNDEEP
c#entity-framework
Lio Programista
Lio Programista

Reputation: 163

Execute Stored Procedure with parameters and return value

I am trying to execute a stored procedure that takes parameters and return a parameter. When I run the procedure separately in Management Studio everything works OK. But if I try to run the procedure from the code I am getting the "Execution Timeout Expired" error. I am suspecting that I am not passing the output parameter correctly:

List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@BankType", "Checking"));
parameters.Add(new SqlParameter("@VendorEmail", "[email protected]"));
parameters.Add(new SqlParameter("@ID", 12345));

SqlParameter outputParameter = new SqlParameter();
outputParameter.ParameterName = "@Confirm";
outputParameter.SqlDbType = System.Data.SqlDbType.Bit;
outputParameter.Direction = System.Data.ParameterDirection.Output;

parameters.Add(outputParameter);

myEntity.Database.ExecuteSqlCommand("exec TestStoredProc @BankType, @VendorEmail, @ID, @Confirm out", parameters.ToArray());

Upvotes: 0

Views: 952

Answers (2)

Vanghern
Vanghern

Reputation: 202

Try this approach:

 var bankParameter =
                   new SqlParameter("@BankType", SqlDbType.VarChar) { Value = "Checking" };

        var emailParameter =
            new SqlParameter("@VendorEmail", SqlDbType.VarChar) { Value = "[email protected]" };

        var idParameter =
            new SqlParameter("@ID", SqlDbType.Int32) { Value = 12345 };

        var conStr = "yourConnectionString";
        using (SqlConnection sConn = new SqlConnection(conStr))
        {
            using (SqlCommand sComm = new SqlCommand("TestStoredProc", sConn))
            {
                sComm.CommandTimeout = 60;
                sComm.CommandType = CommandType.StoredProcedure;

                sComm.Parameters.Add(bankParameter);
                sComm.Parameters.Add(emailParameter);
                sComm.Parameters.Add(idParameter);

                var returnParameter = sComm.Parameters.Add("@Confirm", SqlDbType.Bit);
                returnParameter.Direction = ParameterDirection.ReturnValue;


                sConn.Open();

                //// NonQuery
                sComm.ExecuteNonQuery();
                var result = returnParameter.Value;

            }
        }

This piece of code have some flaws (such as no conStr given or wrong data types), but should work if you adjust them to your needs. Main difference is that my Direction is 

returnParameter.Direction = ParameterDirection.ReturnValue;

and your is

outputParameter.Direction = System.Data.ParameterDirection.Output;

Also, I added CommandTimeout = 60, because, it is possible, that there is nothing wrong with your code, but your SP is just way too long.

Upvotes: 2

Whatamidoing
Whatamidoing

Reputation: 490

Can't comment due to reputation.

But your code does not add the outputParameter to parameters.

Upvotes: 1

Related Questions