Stored Procedure (with input parameter) in ADO.Net

For Asp.net Training Fill the Enquiry Form

Stored Procedure

(with input parameter) in ADO.Net

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.

Step 1:-

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)

Step 2:-

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

Step 3:-

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("Data Source=ISHA;Initial Catalog=isha;Persist Security Info=True;User ID=isha;Password=pass@123");

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

       

        }

Step 4:-

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