CODEWITHSUNDEEP
c#asp.netsql-server-2005stored-proceduresado.net
TheTechGuy
TheTechGuy

Reputation: 17354

Run stored procedure in C#, pass parameters and capture the output result

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.

Upvotes: 2

Views: 14606

Answers (5)

canon
canon

Reputation: 41675

  1. Don't use SqlCommand.ExecuteNonQuery() if you actually want data from a result set.

  2. Make sure your procedure uses set nocount on

  3. 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");

Upvotes: 3

Vidya
Vidya

Reputation: 193

Simple code to get return value from SQL Server 

SqlConnection myConnection = new SqlConnection(myconnectionString); 

SqlCommand myCommand = new SqlCommand(); 
myCommand.CommandType = CommandType.StoredProcedure; 
myCommand.CommandText = "usp_GetCustomer";

myCommand.Parameters.Add("@USER_NAME", SqlDbType.VarChar).Value = sUserName;   // user name that you pass to the stored procedure
myCommand.Parameters.Add("@Month", SqlDbType.VarChar).Value = iMonth;    //Month that you pass to the stored procedure

// to get return value from the stored procedure
myCommand.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

myConnection .Open();
myCommand.ExecuteScalar();

// Returnvalue from the stored procedure
int iReturnValue = Convert.ToInt32(command.Parameters["@ReturnValue"].Value);

Upvotes: 0

Vidya
Vidya

Reputation: 193

using(SqlConnection myConnection = new SqlConnection(myconnectionString))
{ 
    SqlCommand myCommand = new SqlCommand(); 
    myCommand.CommandType = CommandType.StoredProcedure; 
    myCommand.CommandText = "usp_GetCustomer";     

    myCommand.Parameters.Add("@USER_NAME", SqlDbType.VarChar).Value = sUserName;    // user name that you pass to stored procedure
    myCommand.Parameters.Add("@Month", SqlDbType.VarChar).Value = iMonth;    // Month that you pass to stored procedure
    // to get return value from stored procedure
    myCommand.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;     

    myConnection .Open();
    myCommand.ExecuteScalar();     

    // Returnvalue from stored procedure
    return Convert.ToInt32(command.Parameters["@ReturnValue"].Value); 
}

Upvotes: 0

Guffa
Guffa

Reputation: 700322

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();
  }
}

Upvotes: 1

Anders Abel
Anders Abel

Reputation: 69260

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.

Upvotes: 1

Related Questions