ASP.NET : Stored Procedure ( Insert | Update | Delete ) through Coding


How to Insert | Update | Delete Data Using Stored Procedure in ASP.NET Application

How to Create Insert, Update, Delete Data using Stored Procedure in asp.net

First, Create Database Emp Table as shown in below image

create emp table

Now, Create Stored Procedure for
1.) Insert, 2.) Update, 3.) Delete, 4.) Select

 Create Procedure insert_emp 
   ( @ID int, @Name nvarchar(50), @Address nvarchar(50), @DOB nvarchar(50), @Phone nvarchar(50) )
 As
  BEGIN
   Insert emp(empid, empname, empadd, empdob, empphone) values(@ID,@Name,@Address,@DOB,@Phone) 
  End
----------------------------------------------------------------------------------------------------- 
  Create Procedure update_emp
  ( @ID int, @Name nvarchar(50), @Address nvarchar(50), @DOB nvarchar(50), @Phone nvarchar(50) )
 As
  BEGIN
    Update emp set empname=@Name, empadd=@Address, empdob=@DOB, empphone=@Phone where empid=@ID 
  End
-----------------------------------------------------------------------------------------------------
 Create Procedure delete_emp
  ( @ID int )
 As
  BEGIN
    Delete emp where empid=@ID 
  End
-----------------------------------------------------------------------------------------------------
 Create Procedure select_emp
  ( @ID int, @Name nvarchar(50), @Address nvarchar(50), @DOB nvarchar(50), @Phone nvarchar(50) )
 As
  BEGIN
    Select * from emp where empid=@ID 
 End

C# Coding

C# Coding : Namespace

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

C# Coding : Inserting


public partial class Stored_Procedure_Default : System.Web.UI.Page
{
    string connection = ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString;
    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();


// Code for Insert

    protected void btn_insert_Click(object sender, EventArgs e)
    {
        con.ConnectionString = connection;
        cmd.CommandText = "insert_emp";
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ID", txt_emp_ID.Text.Trim());
        cmd.Parameters.AddWithValue("@Name", txt_emp_name.Text.Trim());
        cmd.Parameters.AddWithValue("@Address", txt_emp_address.Text.Trim());
        cmd.Parameters.AddWithValue("@DOB", txt_emp_dob.Text.Trim());
        cmd.Parameters.AddWithValue("@phone", txt_emp_phone.Text.Trim());
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

C# Coding : Updating


// Code for Update

    protected void btn_update_Click(object sender, EventArgs e)
    {
        con.ConnectionString = connection;
        cmd.CommandText = "update_emp";
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ID", txt_emp_ID.Text.Trim());
        cmd.Parameters.AddWithValue("@Name", txt_emp_name.Text.Trim());
        cmd.Parameters.AddWithValue("@Address", txt_emp_address.Text.Trim());
        cmd.Parameters.AddWithValue("@DOB", txt_emp_dob.Text.Trim());
        cmd.Parameters.AddWithValue("@phone", txt_emp_phone.Text.Trim());
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

C# Coding : Deleteing

// Code for Delete

    protected void btn_delete_Click(object sender, EventArgs e)
    {
        con.ConnectionString = connection;
        cmd.CommandText = "delete_emp";
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ID", txt_emp_ID.Text.Trim());
        con.Open();
        cmd.ExecuteReader();
        con.Close();
    }
}

Tag : ASP.NET
0 Komentar untuk "ASP.NET : Stored Procedure ( Insert | Update | Delete ) through Coding"

Back To Top