Crud Operation in Asp.net Using Stored Procedure

In this Article I will explain, How to Insert, update, delete,retrieve.clear in asp.net .
My Out Put looks like this.

Create storedprocedure for Insert, update, delete, retrieve.

USE [chinnu]
GO

/****** Object:  StoredProcedure [dbo].[SP_InsertEmployee]    Script Date: 07/10/2013 07:05:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[SP_InsertEmployee]
(
@Empid Int,
@EmpName nvarchar(50),

@EmpAddress nvarchar(Max)

)
As Begin

Insert into Employee(Empid,EmpName,EmpAddress) values (@Empid,@EmpName,@EmpAddress)

end


GO

USE [chinnu]
GO

/****** Object:  StoredProcedure [dbo].[SP_updateEmployee]    Script Date: 07/10/2013 07:05:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 Create procedure [dbo].[SP_updateEmployee]
(
@Empid Int,
@EmpName nvarchar(50),

@EmpAddress nvarchar(Max)

)

As Begin


update Employee set EmpName = @EmpName,EmpAddress = @EmpAddress where Empid = @Empid

End
GO


USE [chinnu]
GO

/****** Object:  StoredProcedure [dbo].[SP_DeleteEmployee]    Script Date: 07/10/2013 07:06:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_DeleteEmployee] (@Empid int)
AS
 delete from Employee where Empid = @Empid
GO

USE [chinnu]
GO

/****** Object:  StoredProcedure [dbo].[Sp_RetriveEmployee]    Script Date: 07/10/2013 07:06:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[Sp_RetriveEmployee]
(
@Empid Int


)

as

begin 

select * from dbo.Employee where Empid = @Empid;

end
GO

Create a New Project in Visiual Studio as shown in the Screen Shot below..

Right Click on the Soultion, Add a new webform, I given name as SPCrud.

 Design the UI Screen as per your requirement.
Write the UI design Code inSPCrud.aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SPCrud.aspx.cs" Inherits="SPCrudExample.SPCrud" %>





  
    

    

  
    

    


    

     EmpId
    
    
    
    
    

    

     
    EmpName
    
    
    

    

    
     EmpAddress
    
    
    
    



      

         
          
          
      

         
 
        
    

Write the code in SPCrud.aspx.cs page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace SPCrudExample
{
    public partial class SPCrud : System.Web.UI.Page
    {
        String ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btninsert_Click(object sender, EventArgs e)
        {
          
            SqlConnection con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_InsertEmployee";
            cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = txtempid.Text.Trim();
            cmd.Parameters.Add("@EmpName", SqlDbType.VarChar).Value = txtempname.Text.Trim();
            cmd.Parameters.Add("@EmpAddress", SqlDbType.VarChar).Value = txtempaddress.Text.Trim();

            cmd.Connection = con;
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                lblMessage.Text = "Record inserted successfully";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        protected void btnupdate_Click(object sender, EventArgs e)
        {

          
            SqlConnection con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_UpdateEmployee";
            cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = txtempid.Text.Trim();
            cmd.Parameters.Add("@EmpName", SqlDbType.VarChar).Value = txtempname.Text.Trim();
            cmd.Parameters.Add("@EmpAddress", SqlDbType.VarChar).Value = txtempaddress.Text.Trim();

            cmd.Connection = con;
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                lblMessage.Text = "Record updated successfully";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        protected void btndelete_Click(object sender, EventArgs e)
        {

            SqlConnection con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_DeleteEmployee";
            cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = txtempid.Text.Trim();
          

            cmd.Connection = con;
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                lblMessage.Text = "Record Deleted successfully";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        protected void btnretrive_Click(object sender, EventArgs e)
        {

            SqlConnection con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
          
            cmd.CommandText = "Sp_RetriveEmployee";
            cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = txtempid.Text.Trim();
            con.Open();
     SqlDataReader   dr = cmd.ExecuteReader();
           


            if (dr.Read())
            {
               
                txtempname.Text = dr["EmpName"].ToString();
                txtempaddress.Text = dr["EmpAddress"].ToString();
                lblMessage.Text = "Record Retrived successfully";
            }
             dr.Close();
          con.Close();
          
        }

        protected void Btnclear_Click(object sender, EventArgs e)
        {

            txtempid.Text = "";
            txtempname.Text = "";
            txtempaddress.Text = "";
            lblMessage.Text = "Record Cleared successfully";
        }
      
    }
}

Write the Connection string code in Web.config

OutPut:-

ExampleSimple Insert,Update,retrieve,Delete Operations in Asp.net

Read More»