Tuesday, 29 October 2013

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

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


  1. wow, dis will be really helpful fo ol f us mam... thnks fo posting dis really important stuff :)

  2. Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
    Digital Marketing Company in India
    seo Company in India

  3. This blog is having the general information. Got a creative work and this is very different one.We have to develop our creativity mind.This blog helps for this. Thank you for this blog. This is very interesting and useful.

    digital marketing course in chennai
    software testing training in chennai

  4. Great post! I am see the programming coding and step by step execute the outputs.I am gather this coding more information. It's helpful for me my friend. Also great blog here with all of the valuable information you have.
    Dot Net Training in Chennai

  5. Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.

    Best Linux training in Noida
    Linux Training Institute in Noida
    Shell Scripting Training Institute in Noida

  6. Really nice blog post. provided a helpful information. I hope that you will post

    more updates like this
    .NET Online Training Hyderabad

  7. This comment has been removed by the author.

  8. thanks for your post Here is STUCORNER the Best DIGITAL marketing training institute in delhi you can visit their site:Stucorner