how to call stored procedure in c#

What is Stored Procedure

Stored procedure is the precompiled form of queries which executed to perform some task. We can also call stored procedure using Ado.net.

In this task I have created insertion procedure for the following table insertion:-


		
		
		

		Figure 1

In this table id is an identity column.

Create Stored Procedure

Create stored procedure for the insertion. I have created the stored procedure using input parameters:-


		
		
create proc data_ins( @name varchar(100),@age int)
as
insert into emp values( @name, @age)

Now open the visual studio->File->New->Website->Empty Website

Add Web Form and create User interface for the user to take the name and age from the user.



		
		

		Figure 2

On button click write the following code to call the stored procedure:-


		
	//Take input from the controls
        string name = TextBox1.Text;
        int age = Convert.ToInt32(TextBox2.Text);

        //Create connection and open it.
        SqlConnection con = new SqlConnection("connection string of your system");
        con.Open();

        //create command object to pass the connection and other information
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;

        //set command type as stored procedure
        cmd.CommandType = CommandType.StoredProcedure;

        //pass the stored procedure name
        cmd.CommandText = "data_ins";

        //pass the parameter to stored procedure
        cmd.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar)).Value=name;
        cmd.Parameters.Add(new SqlParameter("@age",SqlDbType.Int)).Value=age;

        //Execute the query
        int res = cmd.ExecuteNonQuery();
        con.Close();
        if (res > 0)
        {

            Response.Write("Data Inserted Successfully");


        }
        else
        {

            Response.Write("Data Not Inserted!!!! Try Again");
        
        }

Now execute the code and you will get following output:-


		
		

		Figure 3

Now let’s check the database to check whether data inserted successfully or not:-


		
		
		

		Figure 4