Run stored procedure in C#, pass parameters and capture the output result
Solution 1
Don't use
SqlCommand.ExecuteNonQuery()
if you actually want data from a result set.Make sure your procedure uses
set nocount on
-
Then use
SqlCommand.ExecuteScalar()
return (int)myCommand.ExecuteScalar(); // value of select @@rowcount
Edit: As for your parameters:
myCommand.Parameters.AddWithValue("@username","jsmith");
myCommand.Parameters.AddWithValue("@month","January");
Solution 2
I prefer using linq-to-sql to handle stored procedures. Create a linq-to-sql model, where you add the SP you want to call. This will expose the SP as a function on the generated data context, where the parameters are ordinary C# functions. The returned values will be exposed as a collection of C# objects.
If you have multiple results from the SP things get a bit more complicated, but still quite straight forward.
Solution 3
Use the Parameters
collection of the command to set the parameters, and the ExecuteScalar
to run the query and get the value from the single-row single-column result.
Use using
blocks to make sure that the connection and command are closed and disposed properly in any situation. Note that you have to provide the connection to the command object:
int result;
using (SqlConnection connection = new SqlConnection(myconnectionString)) {
using (SqlCommand command = new SqlCommand(connection)) {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "usp_GetCustomer";
command.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
command.Parameters.Add("@month", SqlDbType.VarChar).Value = month;
connection.Open();
result = (int)myCommand.ExecuteScalar();
}
}
Hammad Khan
Humble, down to earth, experience software Engineer 12+ years of industry experience in ASP.NET, SQL Server, C/C++, Web Development, Automation
Updated on June 04, 2022Comments
-
Hammad Khan almost 2 years
This is a simple task that I want to acheive but ASP.NET makes it quite difficult, next to impossible. I followed this question Running a Stored Procedure in C# Button but found out
ExecuteNonQuery
does not return the output from query.I tried this other approach but can't seem to pass the paremeters in this way
SqlConnection myConnection = new SqlConnection(myconnectionString); SqlCommand myCommand = new SqlCommand(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "usp_GetCustomer"; myCommand.SelectParameter <-- does not exist
Can someone write this simple code, how can I implement it? Basically I am passing a
@username
and@month
(both character strings) to stored procedure and it returns a number that I want to capture and assign to a label control.Thank you
The output from my query is this. It runs a complex query, create a temp table and then it runs
select @@rowcount
and I am capturing that.
-
Hammad Khan over 12 yearsthis will apply to this code stackoverflow.com/questions/4124100/… ?
-
canon over 12 yearsUpdated to include parameter info.
-
Guffa over 12 yearsThe value is returned as a result, not a return value. Also, you need to provide the connection to command for that code to work.
-
Hammad Khan over 12 yearsso it is working upto this point but now I need to change a parameter value and call ExecuteScalar again. The new parameter name is @Type. Can you help?
-
canon over 12 yearsAlternatively, you could leave nocount off, omit the
select @@rowcount
and use the return value ofExecuteNonQuery
for your rowcount. -
Osman Villi almost 9 yearsNot correct this line: "DbType.VarChar". This must be "SqlDbType.VarChar"
-
Guffa almost 9 yearsThe value is returned as a result, not a return value.
-
Guffa almost 9 yearsThe value is returned as a result, not a return value.