Get Return from SQL Server store procedure

   


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

class Test
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    mySqlConnection.Open();

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText =
      "EXECUTE @MyProductID = AddProduct3 @MyProductName, " +
      "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " +
      "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " +
      "@MyReorderLevel, @MyDiscontinued";

    mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int);
    mySqlCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output;
    mySqlCommand.Parameters.Add("@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget";
    mySqlCommand.Parameters.Add("@MySupplierID", SqlDbType.Int).Value = 1;
    mySqlCommand.Parameters.Add("@MyCategoryID", SqlDbType.Int).Value = 1;
    mySqlCommand.Parameters.Add("@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box";
    mySqlCommand.Parameters.Add("@MyUnitPrice", SqlDbType.Money).Value = 5.99;
    mySqlCommand.Parameters.Add("@MyUnitsInStock", SqlDbType.SmallInt).Value = 10;
    mySqlCommand.Parameters.Add("@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5;
    mySqlCommand.Parameters.Add("@MyReorderLevel", SqlDbType.SmallInt).Value = 5;
    mySqlCommand.Parameters.Add("@MyDiscontinued", SqlDbType.Bit).Value = 1;

    SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

    while (mySqlDataReader.Read())
    {
      Console.WriteLine("mySqlDataReader[" ProductName"] = " +
        mySqlDataReader["ProductName"]);
      Console.WriteLine("mySqlDataReader[" UnitPrice"] = " +
        mySqlDataReader["UnitPrice"]);
    }

    mySqlDataReader.Close();

    Console.WriteLine("New ProductID = " + mySqlCommand.Parameters["@MyProductID"].Value);

    mySqlConnection.Close();
  }
}