Mega Code Archive

 

Categories / C# Tutorial / ADO Net

 

Retrieving a Return Value from a 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( );             }         }     }