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




19 comments:

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

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

    more updates like this
    .NET Online Training Hyderabad

    ReplyDelete
  3. This comment has been removed by the author.

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

    ReplyDelete
  5. This is one of the best articles we can find out on the internet Reporting everything in information Regarding the patient matter.

    Web Designing Company Gurgaon
    T shirt printing near me
    Solan live updates and news

    ReplyDelete

  6. Good job in presenting the correct content with the clear explanation. The content looks real with valid information. Good Work

    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery





    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Gym and Fitness equipment store in India - Buy best quality Exercise & Fitness equipment's online for lowest price at Ansonsports.com

    fitness equipment stores in india
    online sports and fitness shop in india
    buy fitness equipments online
    buy home gym equipments online in india

    ReplyDelete
  10. Your article was perfect had helped me alot in finding the right thing i had searching this stuff thank you so much for provding me this information.me been very sad after reading this announcement…


    jantar mantar in delhi


    insectsight


    womens cardigans

    tour to kodaikanal

    places to visit in naintal

    places to visit in delhi

    ReplyDelete
  11. Hello,

    Great Post. It's very Useful Information. In Future, Hope To See More Post. Thanks You For Sharing.
    CTET Coaching In Noida
    UPTET Coaching In Noida
    B.Ed Entrance Coaching In Noida
    Thanks
    Shweta Singh

    ReplyDelete