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

Hi guys,

 This blog we will discuss how to create insert, update,delete and select stored procedure and implement it in asp.net.

Stored procedure for Insert:

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

as

begin

insert into proc1 values(@UserId,@UserName)

end

Stored prodecure for delete:

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

as

begin

delete from proc1 where UserId=@UserId

end

 Stored procedure for 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

 Stored procedure for select:

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

as

begin

select UserName from proc1 where UserId=@UserId

end

 Now we will  discuss how to  write code in code behind  page for  insert , update , delete and update.

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

            Lblmsg.Text =”Insert Successfully”;

        }

      

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

           lblMsg.Text =”Delete data 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”);

            LblMsg.Text =”Updated data successfully”;

        }

     

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

                              lblMsg.Text =”plz enter correct userid”;

            }

            

         

        }

    }

}