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:-

		table creation

		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)
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.

		create UI

		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");

        //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();
        if (res > 0)

            Response.Write("Data Inserted Successfully");


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


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

		execute code

		Figure 3

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

		sp output

		Figure 4