Retrieving a Return Value from a Stored Procedure : SqlConnection Stored Procedure « ADO.Net « C# / CSharp Tutorial

1. C# / CSharp Tutorial
2. ADO.Net
3. SqlConnection Stored Procedure

/*
CREATE PROCEDURE Person.GetContacts
    @RowCount int OUTPUT
AS
    SET NOCOUNT ON
    SELECT * FROM Person.Contact
    SET @RowCount = @@ROWCOUNT
    RETURN @RowCount
*/


using System;
using System.Data;
using System.Data.SqlClient;

    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                SqlCommand command =new SqlCommand("Person.GetContacts", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;
                SqlParameter retParam = command.Parameters.Add("@RetVal", SqlDbType.Int);
                retParam.Direction = ParameterDirection.ReturnValue;

                connection.Open( );
                SqlDataReader dr = command.ExecuteReader( );
                Console.WriteLine("After execution, return value = {0}", retParam.Value);

                int rowCount = 0;
                while (dr.Read( )){
                    rowCount++;
                }
                dr.Close( );
                connection.Close( );
            }
        }
    }