Learn C# ASP.NET MVC WCF SQL Angular: curd opeartion in gridview

In this article , I am going to explain how to insert , update and delete in grid view using stored procedure.

For these operation we require a table, the given below code snippet for creating table in sql.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[userdtl](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Age] [int] NULL,

[Department] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

Now, given below aspx code copy in your aspx page

<%@ Page Language="C#" AutoEventWireup="true"CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<legend>Add/Edit Employee</legend>

<table>

<tr>

<td>Employee Name:</td>

<asp:HiddenField ID="hdnid" runat="server" />

</td>

</tr>

<tr>

<td>Employee Age:</td>

</tr>

<tr>

<td>Employee Department:</td>

</tr>

<tr>

<asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />

<asp:Label ID="lblErrormsg" runat="server" ForeColor="Red"></asp:Label>

<asp:Label ID="lblSuccessmsg" runat="server" ForeColor="Green"></asp:Label>

</td>

</tr>

</table>

</fieldset>

<legend>Emp Detail</legend>

<asp:GridView ID="gvempdtl" runat="server" AutoGenerateColumns="False"

BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"

CellPadding="3">

<asp:TemplateField HeaderText="Id">

<asp:Label ID="lblId" runat="server" Text='<%#Eval("Id") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name">

<asp:Label ID="lblName" runat="server" Text='<%#Eval("Name") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Age">

<asp:Label ID="lblAge" runat="server" Text='<%#Eval("Age") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Department">

<asp:Label ID="lblDepartment" runat="server" Text='<%#Eval("Department") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:Button ID="btnEdit" runat="server" Text="Edit" onclick="btnEdit_Click"/>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:Button ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click" OnClientClick="return confirm('Are you sure, you want to delete');"/>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</fieldset>

</form>

</body>

</html>

Then , copy the give below cs code in your aspx.cs file.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindEmployeeDeatil();

}

private void BindEmployeeDeatil()

{

SqlConnection con = new SqlConnection("Data Source=.;Persist Security Info=True;User ID=sa;Password=pramod");

con.Open();

SqlCommand cmd = new SqlCommand("Usp_userdtl", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@mode", "S");

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

if (ds != null && ds.Tables.Count > 0)

{

gvempdtl.DataSource = ds.Tables[0];

gvempdtl.DataBind();

}

private void Reset()

{

hdnid.Value = string.Empty;

txtname.Text = string.Empty;

txtdept.Text = string.Empty;

txtAge.Text = string.Empty;

btnSave.Text = "Save";

BindEmployeeDeatil();

}

protected void btnSave_Click(object sender, EventArgs e)

{

try

{

lblSuccessmsg.Text = string.Empty;

lblErrormsg.Text = string.Empty;

string mode, Id = string.Empty;

if (string.IsNullOrEmpty(hdnid.Value))

{

mode = "I";

Id = "0";

}

else

{

mode = "U";

Id = hdnid.Value;

}

SqlConnection con = new SqlConnection("Data Source=.;Persist Security Info=True;User ID=sa;Password=pramod");

con.Open();

SqlCommand cmd = new SqlCommand("Usp_userdtl", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@mode", mode);

cmd.Parameters.AddWithValue("@Id", Id);

cmd.Parameters.AddWithValue("@Name", txtname.Text);

cmd.Parameters.AddWithValue("@Age", txtAge.Text);

cmd.Parameters.AddWithValue("@Department", txtdept.Text);

int result = cmd.ExecuteNonQuery();

if (result > 0)

{

if (btnSave.Text.Trim() == "Save")

{

lblSuccessmsg.Text = "Saved Successfully";

}

else if (btnSave.Text.Trim() == "Update")

{

lblSuccessmsg.Text = "Updated Successfully";

}

Reset();

}

else

{

lblErrormsg.Text = "Please Contact to Administrator";

}

catch (Exception ex)

{

lblErrormsg.Text = ex.Message;

}

protected void btnEdit_Click(object sender, EventArgs e)

{

try

{

lblSuccessmsg.Text = string.Empty;

lblErrormsg.Text = string.Empty;

Button btn = sender as Button;

GridViewRow row = (GridViewRow)btn.NamingContainer;

hdnid.Value = (row.FindControl("lblId") as Label).Text;

txtname.Text = (row.FindControl("lblName") as Label).Text;

txtAge.Text = (row.FindControl("lblAge") as Label).Text;

txtdept.Text = (row.FindControl("lblDepartment") as Label).Text;

btnSave.Text = "Update";

}

catch (Exception ex)

{

lblErrormsg.Text = ex.Message;

}

protected void btnDelete_Click(object sender, EventArgs e)

{

try {

lblSuccessmsg.Text = string.Empty;

lblErrormsg.Text = string.Empty;

Button btn = sender as Button;

GridViewRow row = (GridViewRow)btn.NamingContainer;

SqlConnection con = new SqlConnection("Data Source=.;Persist Security Info=True;User ID=sa;Password=pramod");

con.Open();

SqlCommand cmd = new SqlCommand("Usp_userdtl", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@mode", "D");

cmd.Parameters.AddWithValue("@Id", (row.FindControl("lblId") asLabel).Text);

int result = cmd.ExecuteNonQuery();

if (result > 0)

{

lblSuccessmsg.Text = "Deleted Successfully";

Reset();

}

else {

lblErrormsg.Text = "Please Contact to Administrator";

}

catch (Exception ex)

{

lblErrormsg.Text = ex.Message;

}

Now run your website, Output is

Learn C# ASP.NET MVC WCF SQL Angular

Sunday, May 22, 2016

Insert Update Delete In GridView in asp.net c# using stored procedure