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




5 comments:

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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete