How to CRUD in Gridview in Asp.Net

CRUD in Nothing but Insert, Update, Edit, Delete in Gridview in Asp.Net. We can Achieve this functionality using Sql Store Procedure or inline Command for Connection we will use Connection String in Web.Config File. Here I have given Simple CRUD Example Using Store procedure or Inline Command How to call both in Code behind. We can User Gridview event like RowCommand, RowDatabound, PageIndexChanged, RowEditting, RowUpdating, RowDeleting, PageIndexChaging etc. Here we will Use RowCommand Event How to Use and How to CRUD using this event to Achieve this Functionality in dot net. So let’s start to CRUD in Gridview.

Create table in Sql Database Server

CREATE TABLE [dbo].[Employeee](

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

[Name] [varchar](50) NOT NULL,

[Gender] [varchar](50) NOT NULL,

[Email] [varchar](50) NOT NULL,

[MobileNumber] [bigint] NOT NULL,

[Bdate] [date] NULL,

CONSTRAINT [PK_Employeee] PRIMARY KEY CLUSTERED

(

[Number] ASC

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

) ON [PRIMARY]

Create Store Procedure in Sql Server

For Insert Employee

Create procedure [dbo].[SP_InsertEmployeee]

@Name varchar(50),

@Gender varchar(50),

@Email varchar(50),

@MobileNumber int,

@bdate date

begin

insert into Employeee values (@Name,@Gender,@Email,@MobileNumber,@bdate)

end

For Update Employee Details

Create procedure [dbo].[SP_Update]

@Name varchar(50),

@Gender varchar(50),

@Email varchar(50),

@MobileNumber int,

@bdate date,

@Number int

begin

update Employeee set Name=@Name,Gender=@Gender,Email=@Email,Bdate=@bdate,MobileNumber=@MobileNumber where Number=@Number;

End

For Delete Employee Details

Create procedure [dbo].[SP_Delete]

@Number int

begin

delete from Employeee where Number=@Number;

End

Step 1: Design your Webpage like below.

<div>

<table>

<tr>

<td>

<asp:Label ID="lblid" runat="server" Text="Number" Enabled="False"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtid" runat="server" Enabled="False"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="lblname" runat="server" Text="Name"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtname" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="lblgender" runat="server" Text="Gender"></asp:Label>

</td>

<td>

<asp:DropDownList ID="ddlgender" runat="server" Width="124px">

</asp:DropDownList>

</td>

</tr>

<tr>

<td>

<asp:Label ID="lblbdate" runat="server" Text="B'Date"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtdate" runat="server"></asp:TextBox>

<asp:ImageButton ID="ImageButton1" runat="server" Height="20px" ImageUrl="~/Images/Koala.jpg"

OnClick="ImageButton1_Click" Width="30px" />

<asp:Calendar ID="Calbdate" runat="server" BackColor="#FFFFCC" BorderColor="#FFCC66"

BorderWidth="1px" DayNameFormat="Shortest" Font-Names="Verdana" Font-Size="8pt"

ForeColor="#663399" Height="200px" ShowGridLines="True" Width="220px" OnSelectionChanged="Calbdate_SelectionChanged">

</asp:Calendar>

</td>

</tr>

<tr>

<td>

<asp:Label ID="lblemail" runat="server" Text="E-Mail"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtemail" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="lblmobile" runat="server" Text="Mobile Number"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtmobile" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" Width="85px" />

</td>

<td>

<asp:Button ID="lblclear" runat="server" Text="Clear" OnClick="lblclear_Click" Width="94px" />

</td>

</tr>

<tr>

<asp:Label ID="lblmsg" runat="server"></asp:Label>

</td>

</tr>

</table>

</div>

<div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowCommand="GridView1_RowCommand"

OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnRowDeleting="GridView1_RowDeleting"

BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"

CellPadding="3" ForeColor="Black" GridLines="Vertical">

<asp:TemplateField HeaderText="Action">

<asp:Button ID="btnedit" runat="server" Text="EDIT" CommandName="EDIT" CommandArgument='<%#Eval("Number") %>' />

<asp:Button ID="btndelete" runat="server" Text="DELETE" CommandName="DELETE" CommandArgument='<%#Eval("Number") %>' />

</ItemTemplate>

<asp:Button ID="btnUpdate" BackColor="Red" ForeColor="White" runat="server" Text="UPDATE"

CommandName="UPDATE" CommandArgument='<%#Eval("Number") %>' />

<asp:Button ID="btncancle" BackColor="Red" ForeColor="White" runat="server" Text="CANCLE" CommandName="CANCLE" CommandArgument='<%#Eval("Number") %>' />

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="ID">

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name">

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Gender">

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Date">

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="E-mail">

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Mobile Number">

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

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

Now Go to Code behind and Code for CRUD let’s Start First Bind Gridview from Database and for that Create Connection String in Web.Config File.

Web.Config

</connectionStrings>

Call Connection String in your Webpage and Bind Gridview and using Connection String.

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

public void BindGridEmployee()

{

SqlConnection con = new SqlConnection(CS);

SqlCommand cmd = new SqlCommand("Select * from Employeee", con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

da.Fill(dt);

GridView1.DataSource = dt;

GridView1.DataBind();

}

Now Bind Gender from Database in Dropdown list Control.

public void BindDDGender()

{

SqlConnection con = new SqlConnection(CS);

SqlCommand cmd = new SqlCommand("Select * from Gender", con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

da.Fill(dt);

ddlgender.DataTextField = "Name";

ddlgender.DataValueField = "ID";

ddlgender.DataSource = dt;

ddlgender.DataBind();

ListItem li = new ListItem("-Select-");

ddlgender.Items.Insert(0, li);

}

Now on Save button Click event Call Validation Method for Validation.

protected void btnSave_Click(object sender, EventArgs e)

{

CheckValidation();

}

public void CheckValidation()

{

if (txtname.Text == "")

{

lblmsg.ForeColor = System.Drawing.Color.Red;

lblmsg.Text = "Enter Name";

}

else if (txtmobile.Text == "")

{

lblmsg.ForeColor = System.Drawing.Color.Red;

lblmsg.Text = "Enter Mobile Number";

}

else if (txtemail.Text == "")

{

lblmsg.ForeColor = System.Drawing.Color.Red;

lblmsg.Text = "Enter E-Mail ID";

}

else if (txtdate.Text == "")

{

lblmsg.ForeColor = System.Drawing.Color.Red;

lblmsg.Text = "Enter Date";

}

else if (ddlgender.SelectedItem.Text == "-Select-")

{

lblmsg.ForeColor = System.Drawing.Color.Red;

lblmsg.Text = "Enter Gender";

}

else

{

SaveData();

}

Now SaveData () Method is Nothing but you can Insert Data by Clicking on Save Button and Also Update.

public void SaveData()

{

if (ViewState["IDID"] == null)

{

SqlConnection con = new SqlConnection(CS);

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

cmd.CommandType = CommandType.StoredProcedure;

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

cmd.Parameters.AddWithValue("@Gender", ddlgender.SelectedItem.Text);

cmd.Parameters.AddWithValue("@Email", txtemail.Text);

cmd.Parameters.AddWithValue("@MobileNumber", txtmobile.Text);

cmd.Parameters.AddWithValue("@bdate", txtdate.Text);

con.Open();

Int32 ID = cmd.ExecuteNonQuery();

if (ID > 0)

{

lblmsg.Text = "Row Number : " + ID + "Inserted";

BindGridEmployee();

ViewState["IDID"] = string.Empty;

Clear();

}

else { lblmsg.Text = "Error !!"; }

}

else

{

SqlConnection con = new SqlConnection(CS);

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

cmd.CommandType = CommandType.StoredProcedure;

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

cmd.Parameters.AddWithValue("@Gender", ddlgender.SelectedItem.Text);

cmd.Parameters.AddWithValue("@Email", txtemail.Text);

cmd.Parameters.AddWithValue("@MobileNumber", txtmobile.Text);

cmd.Parameters.AddWithValue("@bdate", txtdate.Text);

cmd.Parameters.AddWithValue("@Number", ViewState["IDID"].ToString());

con.Open();

Int32 ID = cmd.ExecuteNonQuery();

if (ID > 0)

{

lblmsg.Text = "Row Number : " + ID + "has been Updated !";

BindGridEmployee();

btnSave.Text = "Save";

ViewState["IDID"] = string.Empty;

Clear();

}

else { lblmsg.Text = "Error !!"; }

}

As you can see here I have use View State for Checking Data is for Inserting or updating in Database. Now Create Clear () Method for Clear values from Input Controls.

public void Clear()

{

btnSave.Text = "Save";

txtid.Text = string.Empty;

txtmobile.Text = string.Empty; ;

txtname.Text = string.Empty;

ddlgender.SelectedItem.Text = "-Select-";

txtemail.Text = string.Empty;

txtdate.Text = string.Empty;

BindGridEmployee();

lblmsg.Text = string.Empty;

}

And Call this Method in Clear button Click event.

protected void lblclear_Click(object sender, EventArgs e)

{

Clear();

}

Now Check in Page Load () event and Code for When Page load First time in Browser.

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

btnSave.Text = "Save";

BindGridEmployee();

Calbdate.Visible = false;

BindDDGender();

}

Now u have Used Date Control for Inserting date in Database for that I have used Image button to user can Select from that button controls and Calendar will open and after Visible False. So Generate Image Button Click event and Code for Open Calendar Control and onCaleder Control Selection Changed Input Selected Date in Date Controls.

protected void Calbdate_SelectionChanged(object sender, EventArgs e)

{

txtdate.Text = Calbdate.SelectedDate.ToShortDateString();

Calbdate.Visible = false;

}

protected void ImageButton1_Click(object sender, ImageClickEventArgs e)

{

Calbdate.Visible = true;

}

Now Generate RowEditting event of Gridview for When we Edit that Edit Template Controls will Open.

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

GridView1.EditIndex = e.NewEditIndex;

BindGridEmployee();

}

Now Generate RowUpdating event of Gridview for When we Update Row from Gridview then Changing Index of Gridview rows.

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

GridView1.EditIndex = -1;

BindGridEmployee();

}

As you can Generate PageIndexChanged and PageIndexChanging of Gridview for Changing Index of Page.

protected void GridView1_PageIndexChanged(object sender, GridViewUpdateEventArgs e)

{

GridView1.PageIndex = e.RowIndex;

BindGridEmployee();

}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

GridView1.EditIndex = -1;

BindGridEmployee();

}

Now Generate RowCommand event of Gridview for Editing, Updating, Deleting and Cancel. And Code for CRUD using SqlConnection.

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

{

ID = e.CommandArgument.ToString();

ViewState["IDID"] = ID;

if (e.CommandName == "EDIT")

{

if (ViewState["IDID"] == null)

{

btnSave.Text = "Save";

}

else { btnSave.Text = "Update"; }

SqlConnection con = new SqlConnection(CS);

SqlCommand cmd = new SqlCommand("Select * from Employeee where Number=" + ID, con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

da.Fill(dt);

if (dt.Rows.Count > 0)

{

txtid.Text = dt.Rows[0]["Number"].ToString();

txtname.Text = dt.Rows[0]["Name"].ToString();

ddlgender.SelectedItem.Text = dt.Rows[0]["Gender"].ToString();

txtemail.Text = dt.Rows[0]["Email"].ToString();

txtmobile.Text = dt.Rows[0]["MobileNumber"].ToString();

txtdate.Text = dt.Rows[0]["Bdate"].ToString();

}

else { lblmsg.Text = "Row Not Found !"; }

}

else if (e.CommandName == "UPDATE")

{

SqlConnection con = new SqlConnection(CS);

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

cmd.CommandType = CommandType.StoredProcedure;

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

cmd.Parameters.AddWithValue("@Gender", ddlgender.SelectedItem.Text);

cmd.Parameters.AddWithValue("@Email", txtemail.Text);

cmd.Parameters.AddWithValue("@MobileNumber", txtmobile.Text);

cmd.Parameters.AddWithValue("@bdate", txtdate.Text);

cmd.Parameters.AddWithValue("@Number", ID);

con.Open();

Int32 ID1 = cmd.ExecuteNonQuery();

if (ID1 > 0)

{

lblmsg.ForeColor = System.Drawing.Color.Red;

lblmsg.Text = "Row Number : " + ID + "Updated Done";

BindGridEmployee();

Clear();

}

else

{

lblmsg.ForeColor = System.Drawing.Color.Red;

lblmsg.Text = "Error !!";

}

else if (e.CommandName == "DELETE")

{

SqlConnection con = new SqlConnection(CS);

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

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@Number", ViewState["IDID"].ToString());

con.Open();

Int32 ID1 = cmd.ExecuteNonQuery();

if (ID1 > 0)

{

lblmsg.ForeColor = System.Drawing.Color.Red;

lblmsg.Text = "Row Number : " + ID + "Delete";

BindGridEmployee();

}

else

{

lblmsg.ForeColor = System.Drawing.Color.Red;

lblmsg.Text = "Error !!";

}

else if (e.CommandName == "CANCLE")

{

GridView1.EditIndex = -1;

GridView1.DataBind();

lblmsg.Text = "Row Number : " + ID + "Cancle Done";

BindGridEmployee();

Clear();

}

else { lblmsg.ForeColor = System.Drawing.Color.Red; lblmsg.Text = "ID Not Found !"; BindGridEmployee(); }

}

Now you can run your Webpage and CRUD in Gridview.

D365 F&O Solutions

How to CRUD in Gridview in Asp.Net

Author : Umesh Patel

D365 F&O Solutions

How to CRUD in Gridview in Asp.Net

Author : Umesh Patel

Related Posts