In this article I will explain with an example, how to return
Output Parameter from
Stored Procedure in Windows Forms (WinForms) Application using C# and VB.Net.
Database
I have made use of the following table Fruits with the schema as follows.
![Return Output parameter from Stored Procedure in C# and VB.Net]()
I have already inserted few records in the table.
![Return Output parameter from Stored Procedure in C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Stored Procedure
1. FruitId – This is an INPUT Parameter used to pass the Id of the Fruit.
2. FruitName – This is an OUTPUT Parameter used to fetch the Name of the Fruit based on its FruitId.
Note: Output Parameter is identified by the keyword OUTPUT.
CREATE PROCEDURE [GetFruitName]
@FruitId INT,
@FruitName VARCHAR(30) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @FruitName = FruitName
FROM Fruits
WHERE FruitId = @FruitId
END
Form Design
The Form consists of following controls:
Label – For displaying text.
TextBox – For capturing Fruit Id.
Button – For fetching Fruit Name.
The Button has been assigned with a Click event handler.
![Return Output parameter from Stored Procedure in C# and VB.Net]()
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Return Output parameter from Stored Procedure in C# and VB.Net
When the Submit Button is clicked, first the connection is read from App.Config file.
The SqlCommand class object is created and FruitId is added as parameter along with its Value i.e. the FruitId entered in the TextBox.
Then, the second parameter FruitName is added which is an Output Parameter hence it cannot be added using AddWithValue function hence it is added using the Add method of SqlCommand class with its Data Type and Size specified.
Once the FruitName parameter is added, its Direction is set to Output since by default the Direction of all parameter is Input.
Then, using ExecuteNonQuery function of SqlCommand class the FruitName value is fetched using Value property of SqlParameter class.
Finally, the Fruit Name is displayed in MessageBox.
C#
private void OnSubmit(object sender, EventArgs e)
{
string spName = "GetFruitName";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(spName, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FruitId", int.Parse(txtFruitId.Text.Trim()));
cmd.Parameters.Add("@FruitName", SqlDbType.VarChar, 30);
cmd.Parameters["@FruitName"].Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Fruit Name: " + cmd.Parameters["@FruitName"].Value.ToString());
}
}
}
VB.Net
Private Sub OnSubmit(sender As Object, e As EventArgs) Handles btnSubmit.Click
Dim spName As String = "GetFruitName"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(spName, con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@FruitId", Integer.Parse(txtFruitId.Text.Trim()))
cmd.Parameters.Add("@FruitName", SqlDbType.VarChar, 30)
cmd.Parameters("@FruitName").Direction = ParameterDirection.Output
con.Open()
cmd.ExecuteNonQuery()
con.Close()
MessageBox.Show("Fruit Name: " & cmd.Parameters("@FruitName").Value.ToString())
End Using
End Using
End Sub
Screenshot
![Return Output parameter from Stored Procedure in C# and VB.Net]()
Downloads
