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
