Skip to main content

Select, Insert, Delete, and Update using Stored Procedure in ASP.NET


Select, Insert, Delete, and Update using Stored Procedure in ASP.NET

Introduction

 
Here, we will see how to create select, insert, update, delete statements using stored procedures in SQL Server And ASP .NET .First of all we create a table.


CREATE TABLE demo(
    id    int     IDENTITY(1,1)     NOT NULL     PRIMARY KEY,
    name    nvarchar(50)    NULL,
    city    nvarchar(50)    NULL
)

                                                 OR

Select, Insert, Delete, and Update using Stored Procedure in ASP.NET
Here I inserted few records in the demo table .

pkeasylife.blogspot.com

Here, we create a stored procedure with SELECT, INSERT, UPDATE, and DELETE SQL statements. The SELECT SQL statement is used to fetch rows from table. The INSERT statement is used to insert new records to a table. The UPDATE statement is used to edit and update the existing record. The DELETE statement is used to delete records from a demo table. The following SQL stored procedure is used perform CRUD operation (insert, update, delete, and select rows from a table), depending on the Action type parameter.


CREATE Procedure SingleProcedure
@id int=' ',
@name nvarchar(50)=' ',
@city nvarchar(50)=' ',
@Action nvarchar(50)=' '
As
Begin
    If @Action='Select'
        Begin
            Select * from demo
        End
    Else if @Action='Insert'
        Begin
            Insert into demo(name, city)values(@name, @city)
        End
    Else if @Action='Update'
        Begin
            Update demo Set name=@name, city=@city where id=@id
        End
    Else if @Action='Delete'
        Begin
            Delete from demo where id=@id
        End
    Else if @Action='Search'
        Begin
            Select * from demo where id=@id
        End
End


Now let's write code for form and gridview markup,

Note: Here I am not going to mention all html code, I will mention just text field and important tags/controls, You can design based on your requirment.

CrudwithProcedure.aspx

        
       
<asp:GridView CssClass="container" ID="GridView1" runat="server" AutoGenerateColumns="false" AutoGenerateDeleteButton="true" OnRowDeleting="delete"
                    BorderStyle="Double" OnRowEditing="GV_RowEditing" OnRowUpdating="GV_RowUpdating" OnRowCancelingEdit="GV_Rowcanceling" EmptyDataText="No Record Found">
                    
                    <HeaderStyle BorderStyle="Groove" />
                    <RowStyle BackColor="WindowFrame" Font-Size="Large" HorizontalAlign="Center" />
                    <Columns>
                        <asp:TemplateField HeaderText="ID">
                            <ItemTemplate>
                                <asp:Label ID="lblid" runat="server"  Text='<%# Eval("id")%>'></asp:Label><br />
                            </ItemTemplate>
                            </asp:TemplateField>

                        <asp:TemplateField HeaderText="Name">
                            <ItemTemplate>
                                <asp:Label ID="lblname" runat="server"  Text='<%# Eval("name")%>'>                                       </asp:Label><br />
                            </ItemTemplate>
                             <EditItemTemplate>
                                <asp:TextBox ID="txtName" runat="server" Text='<%# Eval("name")%>'>                                </asp:TextBox>
              
                            </EditItemTemplate>
                        
                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="City">
                            <ItemTemplate>
                                <asp:Label ID="lblcity" runat="server"  Text='<%# Eval("city")%>'>                                               </asp:Label><br />
                            </ItemTemplate>
                             <EditItemTemplate>
                                <asp:TextBox ID="txtCity" runat="server" Text='<%# Eval("city")%>'>                                       </asp:TextBox>
                            </EditItemTemplate>                     
                        </asp:TemplateField>
                        
                        <asp:TemplateField ShowHeader="false">
                            <EditItemTemplate>
                       <asp:LinkButton ID="LinkButton1" runat="server"                                                      CommandName="Update" Text></asp:LinkButton>
                                &nbsp;
                                <asp:LinkButton ID="LinkButton2" runat="server"                                                             CommandName="cancel" Text="Cancel"></asp:LinkButton>

                            </EditItemTemplate>
                            <ItemTemplate>
             <asp:LinkButton ID="LinkButton3" runat="server"  CommandName="Edit"                                     Text="Edit"></asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>

<table border="1">
<tr>
    <td style="width: 150px">
        Name:<br />
        <asp:TextBox ID="txtname" runat="server" />
    </td>
    <td style="width: 150px">
        City:<br />
        <asp:TextBox ID="txtcity" runat="server" />
    </td>
    <td style="width: 100px">
        <asp:Button ID="Submit" runat="server" Text="Submit" OnClick="Insert" />
    </td>
    <td style="width: 150px">
    
        <asp:Label ID="lblmsg" runat="server" />
    </td>

    <td style="width: 150px">
        Enter ID to Search:<br />
        <asp:TextBox ID="txtid" runat="server" Width="140" />
    </td>
    <td style="width: 100px">
        <asp:Button ID="txtsearch" runat="server" Text="Search" OnClick="search" />
    </td>
</tr>
</table>



    Now We will Write C# Code in CrudwithProcedure.aspx.cs file

CrudwithProcedure.aspx.cs

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.Data.SqlClient;

public partial class SingleProcedureDemo : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=demo;Integrated Security=True");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            view();
        }
    }

//Insert Record
    protected void Insert(object sender, EventArgs e)
    {
        int i;

        SqlCommand cmd = new SqlCommand("SingleProcedure", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@name", txtname.Text);
        cmd.Parameters.AddWithValue("@city", txtcity.Text);
        cmd.Parameters.AddWithValue("@Action", "Insert");

        con.Open();
        i = cmd.ExecuteNonQuery();
        con.Close();
        if (i == 1)
        {
            lblmsg.Text = "Data saved";
            view();
        }
        else
        {
              lblmsg.Text = "Data not saved";
        }
    }

//View Record
    public void view()
    {
        SqlCommand cmd = new SqlCommand("SingleProcedure", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Action", "Select");

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        con.Open();
        da.Fill(ds);
        con.Close();
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }

//Delete Record
    protected void delete(object sender, GridViewDeleteEventArgs e)
    {

        GridView1.EditIndex = -1;
        GridViewRow gr1 = GridView1.Rows[e.RowIndex];
        Label l1 = (Label)gr1.FindControl("lblid");

        SqlCommand cmd1 = new SqlCommand("SingleProcedure", con);
        cmd1.CommandType = CommandType.StoredProcedure;
        cmd1.Parameters.AddWithValue("@id", l1.Text);
        cmd1.Parameters.AddWithValue("@Action", "Delete");

        con.Open();
        int r = cmd1.ExecuteNonQuery();

        con.Close();
        if (r == 1)
        {
      lblmsg.Text = "Data Deleted";
            view();
        }
        else
        {
      lblmsg.Text = "Data Not Deleted";
        }
    }


//Enable TextBox and Allow it to update
    protected void GV_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        view();
         lblmsg.Text = "";
    }
//Update Record
    protected void GV_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        GridViewRow row = GridView1.Rows[e.RowIndex];
        int id = Convert.ToInt32((row.FindControl("lblid") as Label).Text);
        string name = (row.FindControl("txtName") as TextBox).Text;
        string city = (row.FindControl("txtCity") as TextBox).Text;
        SqlCommand cmd = new SqlCommand("SingleProcedure", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@name", name);
        cmd.Parameters.AddWithValue("@city", city);
        cmd.Parameters.AddWithValue("@id", id);
        cmd.Parameters.AddWithValue("@Action", "Update");

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        lblmsg.Text = "Record Updated Successfully";
        GridView1.EditIndex = -1;
        view();
    }
//Cancel Updating of Record
    protected void GV_Rowcanceling(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        view();

    }

//Search Record
    protected void search(object sender, EventArgs e)
    {
        if (txtsearch.Text == "")
        {
            view();
            Response.Write("<script language=javascript>alert('Enter A Number to Search Record')</script>");

        }
        else
        {
            int id;
            id = Convert.ToInt32(txtsearch.Text);
            SqlCommand cmd = new SqlCommand("SingleProcedure", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@Action", "Search");

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            con.Open();
            da.Fill(ds);
            con.Close();
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
    }
}

Now debug and run your Demo program.
I hope it will be helpful to you people if any issue ocurred you can comment.
Labels:

Comments

Post a Comment

Popular posts from this blog

How To Hack Chrome Dinosaur Game

How to hack Chrome Dinosaur Advance Video What is Chrome Dinosaur Game?     Chrome Dinosaur game is also known as T-REX game. It is a simple infinite runner up game. Which is control by using Space key to jump and to start game also And press down arrow to duck.    When we get Chrome Dinosaur Game?     By default, When we try to search something without Internet or when our Wi-Fi or internet service are shut down. At that time google Chrome redirect us at the chrome Dino game.     You can also play it when your device is connected with internet. You can Open it by just typing chrome://dino in your URL bar.                                                   ...
Post a Comment
Read more

Find missing number from array range C, C++

Missing number between array's lowest value and highest value elements in C.             #include <stdio.h> int main ( ) { int arr [ 5 ] = { 2 , 5 , 20 , 18 , 15 } ; int min = arr [ 0 ] , max = arr [ 0 ] , i , j , flag = 0 ; for ( i = 0 ; i < 5 ; i ++ ) { if ( arr [ i ] > max ) { max = arr [ i ] ; //find max value } if ( arr [ i ] < min ) { min = arr [ i ] ; //find min value } } for ( i = min ; i < max ; i ++ ) { for ( j = 0 ; j < 5 ; j ++ ) { if ( i == arr [ j ] ) { flag = 1 ; break ; } else { flag = 0 ; } } if ( flag == 0 ) { printf ( "%d " , i ) ; } } return 0 ; }       ...
Post a Comment
Read more

Android Hello World Application

"Hello World" application. That will display "Hello World" in the middle of the screen in the red color with white background. Now Here We will create a android application that will display "Hello world" in the middle of the screen. Create a Hello Application with empty activity. and Write below mentioned code in your " activity_main.xml" file. <? xml version ="1.0" encoding ="utf-8" ?> < LinearLayout xmlns: android ="http://schemas.android.com/apk/res/android" xmlns: app ="http://schemas.android.com/apk/res-auto" xmlns: tools ="http://schemas.android.com/tools" android :layout_width ="match_parent" android :layout_height ="match_parent" android :gravity ="center" android :background ="#ffffff" tools :context =".MainActivity" > < TextView android :layout_wi...
Post a Comment
Read more