locked
Calling stored procedure from code RRS feed

  • Question

  • Hi

    I have below stored procedure which runs an update query and returns the number of rows affected.

    CREATE PROCEDURE [dbo].[UpdateApplicants] 
    @RowCount int output
    AS
    BEGIN
    	SET NOCOUNT ON;
    UPDATE [Applicants]
    SET [Status] = 'Current'
    FROM [dbo].[Applicants]
    SELECT @RowCount=@@ROWCOUNT
    END
    GO
    



    How can I run this stored procedure from .net code and then get the return value in a variable?

    Thanks

    Regards

    Wednesday, April 27, 2011 1:14 AM

Answers

  •  

    Hi Yahya,

    Welcome!

    Here are two helpful links:

     http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

     http://www.codeproject.com/KB/cs/simplecodeasp.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Y a h y a Wednesday, April 27, 2011 1:19 PM
    Wednesday, April 27, 2011 6:00 AM
  • string cstr = "your connection string goes here";
          using (SqlConnection conn = new SqlConnection(cstr))
          {
            conn.Open();
    
            SqlCommand cmd = new SqlCommand("UpdateApplicants", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
    
            // output parm
            SqlParameter RowCount = cmd.Parameters.Add("@RowCount ", System.Data.SqlDbType.Int);
            RowCount .Direction = System.Data.ParameterDirection.Output ;
    
    
            cmd.ExecuteNonQuery();
    
            if ((int)RowCount .Value > 0)
            {
              // no of rows effected
            }
            else
            {
              // no row effected
            }
          }
    
    
    

    Please mark the replies as answers if they help and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    • Marked as answer by Y a h y a Wednesday, April 27, 2011 1:19 PM
    Wednesday, April 27, 2011 11:46 AM

All replies

  •  

    Hi Yahya,

    Welcome!

    Here are two helpful links:

     http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

     http://www.codeproject.com/KB/cs/simplecodeasp.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Y a h y a Wednesday, April 27, 2011 1:19 PM
    Wednesday, April 27, 2011 6:00 AM
  • string cstr = "your connection string goes here";
          using (SqlConnection conn = new SqlConnection(cstr))
          {
            conn.Open();
    
            SqlCommand cmd = new SqlCommand("UpdateApplicants", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
    
            // output parm
            SqlParameter RowCount = cmd.Parameters.Add("@RowCount ", System.Data.SqlDbType.Int);
            RowCount .Direction = System.Data.ParameterDirection.Output ;
    
    
            cmd.ExecuteNonQuery();
    
            if ((int)RowCount .Value > 0)
            {
              // no of rows effected
            }
            else
            {
              // no row effected
            }
          }
    
    
    

    Please mark the replies as answers if they help and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    • Marked as answer by Y a h y a Wednesday, April 27, 2011 1:19 PM
    Wednesday, April 27, 2011 11:46 AM