Insert Update Delete data in Gridview asp.net using SQLDataadapter method

In previous asp.net post we have discussed about insert update and delete data in gridview using dataset and table  adapter method.

In this asp.net post we will learn insert edit update and delete data in gridview control in asp.net using SQLConnection and SQLDataAdapter method. we can do the same thing by using other connection method like LINQ connection and SQLCommand and stored procedure method.

For understand edit update delete gridview example, First we insert records in to database and select records from database and bind it to gridview control, then update and delete record in gridview using sql dataadater method.

 Step for Insert Edit Update Delete GridView asp.net example

Step 1 – Create database table and create new asp.net web application.

Step 2 – Design asp.net web page for gridview example.

Step 3 – Add name space for sql connection DataTable object. 

Step 4 – using System.Data.SqlClient; using System.Data;

Step 5 – AutoGenerateDeleteButton=”True” and AutoGenerateEditButton=”True”

Step 6 –Declare SQLConnction and SQLDataAdapter object for connectivity.

Step 7 – Insert record to gridview and update, delete records in gridview.

For understand this asp.net gridview example we need to create sql database table.

Here, we have created UserMst table for doing this example.

This is the script for create sql table.

CREATE TABLE [dbo].[UserMst]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
)

Now, after creating sql table design asp.net web page for gridview example.

 HTML Source code for asp.net web page

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 614px;
}
.style3
{
color: #FF0000;
font-size: xx-large;
}
.style4
{
color: #006600;
font-size: xx-large;
}
.style7
{
}
.style11
{
width: 360px;
}
.style12
{
font-weight: bold;
text-align: right;
width: 69px;
}
.style13
{
width: 69px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
 
<table align="center" class="style1" style="border: thin solid #008080">
<tr>
<td
 
style="border-bottom-style: solid; border-bottom-width: thin; border-bottom-color: #008080"
class="style3">
<strong>&nbsp;GridView Edit / Update / Delete Example</strong></td>
</tr>
<tr>
<td
style="border-bottom: thin solid #008080; text-align: center;"
class="style4">
&nbsp;</td>
</tr>
<tr>
<td class="style7">
<table align="center" class="style11">
<tr>
<td class="style12">
&nbsp;</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td class="style12">
Name :</td>
<td>
<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style12">
City :</td>
<td>
<asp:TextBox ID="txtcity" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style12">
Email :</td>
<td>
<asp:TextBox ID="txtemail" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style13">
&nbsp;</td>
<td>
<asp:Button ID="btnsave" runat="server" Text="SAVE" onclick="btnsave_Click"
style="font-weight: 700" />
</td>
</tr>
<tr>
<td class="style13">
&nbsp;</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td class="style13">
&nbsp;</td>
<td>
<asp:GridView ID="GridView1" runat="server" BackColor="White"
BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3"
style="text-align: center" Width="284px" AutoGenerateDeleteButton="True"
AutoGenerateEditButton="True" DataKeyNames="ID"
onrowcancelingedit="GridView1_RowCancelingEdit"
onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"
onrowupdating="GridView1_RowUpdating">
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
</td>
</tr>
</table>
</td>
</tr>
</table>
 
</div>
</form>
</body>
</html>

 

Insert Update Delete data in gridview control in asp.net c#
Insert Update Delete data in gridview control in asp.net c#

Now, finishing design make connection between sql database and asp.net web application. In this asp.net gridview example we use sql connection and sql dataadapter method for connectivity. In this method we write sql  query in our code behind page instead of writing sql stored procedure.

Now, set gridview property shows like below.

AutoGenerateDeleteButton=”True”
AutoGenerateEditButton=”True”
DataKeyNames=”ID”

Insert Update Delete data in gridview control in asp.net c#
Insert Update Delete data in gridview control in asp.net c#

Enable Gridview events for Edit update delete records in gridview.

onrowcancelingedit=”GridView1_RowCancelingEdit”
onrowdeleting=”GridView1_RowDeleting”
onrowediting=”GridView1_RowEditing”
onrowupdating=”GridView1_RowUpdating”

Insert Update Delete data using Gridview in asp.net C#

First add namespace for sql connectivity.

using System.Data;
using System.Data.SqlClient;

Here is C# sample code for insert update delete record using gridview in asp.net

SqlConnection SQLConn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=MyExample;Integrated Security=True");

protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
{
BindGrid();
}
}
private void BindGrid()
{

SqlDataAdapter SQLAdapter = new SqlDataAdapter("Select * from usermst", SQLConn);
DataTable DT = new DataTable();
SQLAdapter.Fill(DT);

GridView1.DataSource = DT;
GridView1.DataBind();

}
protected void btnsave_Click(object sender, EventArgs e)
{
SqlDataAdapter SQLAdapter = new SqlDataAdapter("insert into usermst values('"+ txtname.Text + "','"+ txtcity.Text + "','" + txtemail.Text+"')", SQLConn);
DataTable DT = new DataTable();
SQLAdapter.Fill(DT);

BindGrid();
}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindGrid();
}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindGrid();
}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int idd = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);

TextBox tname = GridView1.Rows[e.RowIndex].Cells[2].Controls[0] as TextBox;
TextBox tcity = GridView1.Rows[e.RowIndex].Cells[3].Controls[0] as TextBox;
TextBox temail = GridView1.Rows[e.RowIndex].Cells[4].Controls[0] as TextBox;

SqlDataAdapter SQLAdapter = new SqlDataAdapter("update usermst set name='" + tname.Text + "', city='" + tcity.Text + "',email='" + temail.Text + "' where id="+idd+"", SQLConn);
DataTable DT = new DataTable();
SQLAdapter.Fill(DT);

GridView1.EditIndex = -1;
BindGrid();
}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int idd = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);
SqlDataAdapter SQLAdapter = new SqlDataAdapter("delete from usermst where id=" + idd + "", SQLConn);
DataTable DT = new DataTable();
SQLAdapter.Fill(DT);

BindGrid();
}

 

 Gridview Example Result :

Insert Update Delete data in gridview control in asp.net c#
Insert Update Delete data in gridview control in asp.net c#
Insert Update Delete data in gridview control in asp.net c#
Insert Update Delete data in gridview control in asp.net c#

 

Author: Meera Academy

1 thought on “Insert Update Delete data in Gridview asp.net using SQLDataadapter method

Leave a Reply

Your email address will not be published. Required fields are marked *