Get Return from SQL Server store procedure : Store Procedure « Database ADO.net « C# / C Sharp






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








Related examples in the same category

1.Add parameters to SqlCommand to call stored procedure
2.Call the SQL Server AddProduct() stored procedure with SqlCommand
3.Illustrates simple stored procedures with unnamed parameters in the queryIllustrates simple stored procedures with unnamed parameters in the query
4.Populate a DataSet object using a store procedure
5.Call the SQL Server AddProduct() store procedure
6.Call Simple Store Procedure
7.illustrates how to call a SQL Server stored procedure
8.Call a store procedure