«

»

Dec 24

how to create insert delete and update using stored procedure in asp.net

 

Insert:

create procedure spinsert(@UserId nvarchar(50),@UserName nvarchar(50)=null)

as

begin

insert into proc1 values(@UserId,@UserName)

end

 

 

delete:

create proc spdelete(@UserId nvarchar(50)=null)

as

begin

delete from proc1 where UserId=@UserId

end

 

 

Update:

 

create proc spupdate(@UserId nvarchar(50)=null,@UserName nvarchar(50)=null)

as

begin

update proc1 set UserId=@UserId,UserName=@UserName where UserId=@UserId

end

select:

create proc spselect(@UserId nvarchar(50)=null,@UserName nvarchar(50)=null)

as

begin

select UserName from proc1 where UserId=@UserId

end

 

 

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

 

namespace stp

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        SqlConnection con = new SqlConnection(“uid=sa;password=123;database=naseer”);

 

      

 

 private void insert_Click(object sender, EventArgs e)

        {

            SqlCommand cmd = new SqlCommand(“spinsert”,con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue(“@UserId”,textBox1.Text);

            cmd.Parameters.AddWithValue(“@UserName”,textBox2.Text);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            MessageBox.Show(“inserted succesfully”);

        }

 

      

 

 private void delete_Click(object sender, EventArgs e)

        {

            SqlCommand cmd = new SqlCommand(“spdelete”, con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue(“@UserId”, textBox1.Text);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            MessageBox.Show(“deleted succesfully”);

        }

 

      

 

 

 private void update_Click(object sender, EventArgs e)

        {

            SqlCommand cmd = new SqlCommand(“spupdate”, con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue(“@UserId”, textBox1.Text);

            cmd.Parameters.AddWithValue(“@UserName”, textBox2.Text);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            MessageBox.Show(“updated succesfully”);

        }

 

 

     

 private void select_Click(object sender, EventArgs e)

        {

            SqlCommand cmd = new SqlCommand(“spselect”, con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue(“@UserId”, textBox1.Text);

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();

            da.Fill(ds);

            DataTable dt = ds.Tables[0];

            if (dt.Rows.Count > 0)

            {

                textBox2.Text = dt.Rows[0][“UserName”].ToString();

            }

            else

            {

                MessageBox.Show(“Please enter Correct UserID”);

            }

            

         

        }

 

    }

}

 

 

 

Thanks&Regards,

Admin.

19 comments

Skip to comment form

  1. arpan patel

    simple coading

  2. Mak Damakale

    its really very helpful to understand how to create and working with stored procedures in .net framework…..thanks sir.

  3. Soundararajan

    Simple and perfect. Thanks 🙂

    1. w45ry

      nice blog

  4. w45ry

    nice blog

  5. Anonymous

    Really Very nice

    thanks

  6. Naseer

    Thanqq

  7. Anonymous

    using this code i am executing my application. Nice code

  8. Suresh

    Superb!!!!

  9. geethu

    please explain each line in that stored procedure code

  10. Naseer

    Geethu–Could you let me know in which line of code you are facing issue to understand, so that i will explain that

    Admin

  11. vinoth

    Thanks for giving nice illustration

  12. sudheer

    how to create parameters for select,add,delete,update for stored procedure

  13. rasel

    how can show this data by gridview.

  14. Ramesh kumar Ray

    I am very happy Iwant to know how Gridoperation is operate.

  15. Admin_Naseer

    Rasel please refer http://codedada.com/how-to-export-gridview-data-to-excel-in-asp-net-using-c/ to bind data to girdview

  16. Bharathi

    Very nice…

  17. saket

    string connetionString = null;
    SqlConnection cnn;
    connetionString = “Data Source=IITMSPC12\\MSSQLSERVER2012;Initial Catalog=TEST;User ID=sa;Password=iitms”;
    cnn = new SqlConnection(connetionString);
    //SqlCommand insert = new SqlCommand(“INSERT INTO STUDENT(idStudentInfo,Name,Father_Name,Age,Semester) VALUES(‘” +this.textBoxId.Text+ “‘,'” +this.textBoxName.Text+ “‘,'” +this.textBoxFatherName.Text+ “‘,'” +this.textBoxAge.Text+ “‘,'” +this.textBoxSemester.Text+ “‘)”);
    SqlCommand cmd = new SqlCommand(“STUDENT_PROCEDURE”,cnn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue(“@Action”, “INSERT”);
    cmd.Parameters.AddWithValue(“@idStudentInfo”, this.textBoxId.Text);
    cmd.Parameters.AddWithValue(“@Name”, this.textBoxName.Text);
    cmd.Parameters.AddWithValue(“@Father_Name”, this.textBoxFatherName.Text);
    cmd.Parameters.AddWithValue(“@Age”, this.textBoxAge.Text);
    cmd.Parameters.AddWithValue(“@Semester”, this.textBoxSemester.Text);
    cmd.Connection = cnn;
    cnn.Open();
    int i;
    i=cmd.ExecuteNonQuery();
    //cnn.Close();
    if (i>0)
    {
    MessageBox.Show(“Inserted Successfully”);
    }
    else
    {
    MessageBox.Show(“Please Check the Fields”);
    }
    cnn.Close();

    Not able to insert successfully plz help

    1. saket

      string connetionString = null;
      SqlConnection cnn;
      connetionString = “Data Source=IITMSPC12\\MSSQLSERVER2012;Initial Catalog=TEST;User ID=sa;Password=iitms”;
      cnn = new SqlConnection(connetionString);
      //SqlCommand insert = new SqlCommand(“INSERT INTO STUDENT(idStudentInfo,Name,Father_Name,Age,Semester) VALUES(‘” +this.textBoxId.Text+ “‘,'” +this.textBoxName.Text+ “‘,'” +this.textBoxFatherName.Text+ “‘,'” +this.textBoxAge.Text+ “‘,'” +this.textBoxSemester.Text+ “‘)”);
      SqlCommand cmd = new SqlCommand(“STUDENT_PROCEDURE”,cnn);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue(“@Action”, “INSERT”);
      cmd.Parameters.AddWithValue(“@idStudentInfo”, this.textBoxId.Text);
      cmd.Parameters.AddWithValue(“@Name”, this.textBoxName.Text);
      cmd.Parameters.AddWithValue(“@Father_Name”, this.textBoxFatherName.Text);
      cmd.Parameters.AddWithValue(“@Age”, this.textBoxAge.Text);
      cmd.Parameters.AddWithValue(“@Semester”, this.textBoxSemester.Text);
      cmd.Connection = cnn;
      cnn.Open();
      int i;
      i=cmd.ExecuteNonQuery();
      //cnn.Close();
      if (i>0)
      {
      MessageBox.Show(“Inserted Successfully”);
      }
      else
      {
      MessageBox.Show(“Please Check the Fields”);
      }
      cnn.Close();

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>