Calling a stored procedure with output parameters

In order to understand how the process flows, we need to base it with an example. Firstly we create a stored procedure named ‘save student’ in order to insert the name, department, and marks of students in the table. The table also contains the id column consisting of the id of the students but we set it to identity so that will be automatically generated and we need not pass input for that column. We provided the stored procedure with three parameters to accept the input and an output parameter as id which will return the id of student that is newly added. We used the scope_identity() to return the last identity value inserted in the identity column in the same scope.

Initially, the table is as follows but we will notice the changes after executing the stored procedure.

In order to execute the stored procedure, we provide the inputs and also declare a variable that will hold the value returned by the stored procedure.

Finally, we got the desired output as we got the entries inserted into the table.

Now we develop a windows form application and we will be executing the stored procedure using the C# code. The front end of the application will look as follows

Now we provide the connection string in the App.config file to provide the connectivity with the database to the application.

We add one more class in the project named as ‘SQL helper’ to define various methods and to execute the stored procedure through the C# code.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Configuration;

using System.Threading.Tasks;

using System.Data.SqlClient;

using System.Data;

using System.Windows.Forms;




namespace blog

{

    class sqlhelper

    {   public string ConnectionString

        {

            get

            {

                return ConfigurationManager.ConnectionStrings["mydb"].ConnectionString;

            }

        }

        SqlConnection con;

        SqlCommand cmd;

        DataTable dt;

        public sqlhelper()

        {

            con = new SqlConnection(ConnectionString);

            cmd = new SqlCommand();

            cmd.Connection = con;




        }

        public DataTable Getrecords(string selectcommand)

        {

            cmd.CommandType = CommandType.Text;

            cmd.CommandText = selectcommand;

            con.Open();

            SqlDataReader rdr = cmd.ExecuteReader();

            dt = new DataTable();

            dt.Load(rdr);

            con.Close();

            return dt;

        }

        public string Submit(string name,string dept,int marks)

        {

            cmd.CommandText = "savestudent";        //We mention the name of the stored procedure

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Clear();

            cmd.Parameters.AddWithValue("@name",name);      //Parameters are added using AddWithValue()

            cmd.Parameters.AddWithValue("@department",dept);

            cmd.Parameters.AddWithValue("@marks",marks);

            SqlParameter p1 = new SqlParameter();

            p1.ParameterName = "@id";

            p1.Direction = ParameterDirection.Output;

            p1.DbType = DbType.Int32;

            cmd.Parameters.Add(p1);          //Output parameter is added using Add()

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            string stud_id= p1.Value.ToString();

            return stud_id;

        }

    }

}

Now to provide the functionality to the submit button we write the below code

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;




namespace blog

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        sqlhelper sql = new sqlhelper();

        private void Form1_Load(object sender, EventArgs e)

        {

            DataTable dt = sql.Getrecords("Select * from student");

            dataGridView1.DataSource=  dt;

        }




        private void button1_Click(object sender, EventArgs e)

        {

            string studentid = sql.Submit(textBox1.Text, textBox2.Text, Convert.ToInt32(textBox3.Text));

            label4.Text = "Student ID is" + studentid;

            DataTable dt = sql.Getrecords("Select * from student");

            dataGridView1.DataSource = dt;

        }

    }

}

OUTPUT

We learned calling a stored procedure with output parameters using the C# code.