Abstract:
Here Lingraj Gowda has explained with an example how
do insert, update, edit and delete with confirmation and using ASP.Net
AJAX features along with JQuery to ASP.Net GridView control
You
might have seen many articles explaining ASP.Net GridView Add (Insert),
Edit, Update and Delete functionality, but this is different and how
I’ll explain as we progress. My main objective in this article is to
keep it simple and cover multiple aspects in one article.
Concept
Basically
I have tried to make the normal Add (Insert), Edit, Update and delete
functions in ASP.Net GridView simple and also combining the powers of
ASP.Net AJAX with that of JQuery to give an elegant and charming user
experience.
Below is the connection string from the web.config
<connectionStrings>
<add name="conString" connectionString="Data Source=.\SQLExpress;
database=Northwind;Integrated Security=true"/>
connectionStrings>
The GridView
Below
is the markup of the ASP.Net GridView control that I’ll be using to
demonstrate the various features explained in this article.
<div id = "dvGrid" style ="padding:10px;width:550px">
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" Width = "550px"
AutoGenerateColumns = "false" Font-Names = "Arial"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
HeaderStyle-BackColor = "green" AllowPaging ="true" ShowFooter = "true"
OnPageIndexChanging = "OnPaging" onrowediting="EditCustomer"
onrowupdating="UpdateCustomer" onrowcancelingedit="CancelEdit"
PageSize = "10" >
<Columns>
<asp:TemplateField ItemStyle-Width = "30px" HeaderText = "CustomerID">
<ItemTemplate>
<asp:Label ID="lblCustomerID" runat="server"
Text='<%# Eval("CustomerID")%>'>asp:Label>
ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCustomerID" Width = "40px"
MaxLength = "5" runat="server">asp:TextBox>
FooterTemplate>
asp:TemplateField>
<asp:TemplateField ItemStyle-Width = "100px" HeaderText = "Name">
<ItemTemplate>
<asp:Label ID="lblContactName" runat="server"
Text='<%# Eval("ContactName")%>'>asp:Label>
ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtContactName" runat="server"
Text='<%# Eval("ContactName")%>'>asp:TextBox>
EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtContactName" runat="server">asp:TextBox>
FooterTemplate>
asp:TemplateField>
<asp:TemplateField ItemStyle-Width = "150px" HeaderText = "Company">
<ItemTemplate>
<asp:Label ID="lblCompany" runat="server"
Text='<%# Eval("CompanyName")%>'>asp:Label>
ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCompany" runat="server"
Text='<%# Eval("CompanyName")%>'>asp:TextBox>
EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCompany" runat="server">asp:TextBox>
FooterTemplate>
asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lnkRemove" runat="server"
CommandArgument = '<%# Eval("CustomerID")%>'
OnClientClick = "return confirm('Do you want to delete?')"
Text = "Delete" OnClick = "DeleteCustomer">asp:LinkButton>
ItemTemplate>
<FooterTemplate>
<asp:Button ID="btnAdd" runat="server" Text="Add"
OnClick = "AddNewCustomer" />
FooterTemplate>
asp:TemplateField>
<asp:CommandField ShowEditButton="True" />
Columns>
<AlternatingRowStyle BackColor="#C2D69B" />
asp:GridView>
ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID = "GridView1" />
Triggers>
asp:UpdatePanel>
div>
The GridView has 3 data columns
1. Customer ID
2. Contact Name
3. Company Name
I have added a LinkButton in 4th column which will act as custom column for delete functionality. The reason to use a custom button is to provide the JavaScript confirmation box to the user when he clicks Delete. For Edit and Update I have added a command field which will act as the 5th column.
There’s
also a Footer Row with 3 TextBoxes which will be used to add new
records to the database and an Add button which will be used to add the
records.
I have enabled pagination and finally wrapped the complete Grid in update panel and the update panel in a div dvGrid and the reason to that I’ll explain later in the article
Binding the GridView
Below is the code to bind the GridView in the page load event of the page
C#
private String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
}
}
private void BindData()
{
string strQuery = "select CustomerID,ContactName,CompanyName" +
" from customers";
SqlCommand cmd = new SqlCommand(strQuery);
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
VB.Net
Private strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindData()
End If
End Sub
Private Sub BindData()
Dim strQuery As String = "select CustomerID,ContactName,CompanyName" & _
" from customers"
Dim cmd As New SqlCommand(strQuery)
GridView1.DataSource = GetData(cmd)
GridView1.DataBind()
End Sub
Below is the screenshot of the GridView being populated using the above code
Adding new record
As
discussed above I have placed 3 textboxes and a button in the Footer
Row of the ASP.Net GridView control in order to add new record to the
database. On the onclick event if the button the records are inserted
into the SQL Server Database and the GridView is updated
C#
protected void AddNewCustomer(object sender, EventArgs e)
{
string CustomerID=((TextBox)GridView1.FooterRow.FindControl("txtCustomerID")).Text;
string Name = ((TextBox)GridView1.FooterRow.FindControl("txtContactName")).Text;
string Company = ((TextBox)GridView1.FooterRow.FindControl("txtCompany")).Text;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into customers(CustomerID, ContactName, CompanyName) " +
"values(@CustomerID, @ContactName, @CompanyName);" +
"select CustomerID,ContactName,CompanyName from customers";
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = CustomerID;
cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = Name;
cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = Company;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
VB.Net
Protected Sub AddNewCustomer(ByVal sender As Object, ByVal e As EventArgs)
Dim CustomerID As String = DirectCast(GridView1.FooterRow _
.FindControl("txtCustomerID"), TextBox).Text
Dim Name As String = DirectCast(GridView1 _
.FooterRow.FindControl("txtContactName"), TextBox).Text
Dim Company As String = DirectCast(GridView1 _
.FooterRow.FindControl("txtCompany"), TextBox).Text
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "insert into customers(CustomerID, ContactName, " & _
"CompanyName) values(@CustomerID, @ContactName, @CompanyName);" & _
"select CustomerID,ContactName,CompanyName from customers"
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = CustomerID
cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = Name
cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = Company
GridView1.DataSource = GetData(cmd)
GridView1.DataBind()
End Sub
You
will notice I am firing two queries one to insert the data and second
to select the updated data and then rebind the GridView. The figure
below displays how new records are added.
Edit and Update existing records
As
described above I have used command field in order to provide the Edit
functionality. Below is the code snippet which is used to edit and
update the records
C#
protected void EditCustomer(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindData();
}
protected void CancelEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindData();
}
protected void UpdateCustomer(object sender, GridViewUpdateEventArgs e)
{
string CustomerID = ((Label)GridView1.Rows[e.RowIndex]
.FindControl("lblCustomerID")).Text;
string Name = ((TextBox)GridView1.Rows[e.RowIndex]
.FindControl("txtContactName")).Text;
string Company = ((TextBox)GridView1.Rows[e.RowIndex]
.FindControl("txtCompany")).Text;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update customers set ContactName=@ContactName," +
"CompanyName=@CompanyName where CustomerID=@CustomerID;" +
"select CustomerID,ContactName,CompanyName from customers";
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = CustomerID;
cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = Name;
cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = Company;
GridView1.EditIndex = -1;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
VB.Net
Protected Sub EditCustomer(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
GridView1.EditIndex = e.NewEditIndex
BindData()
End Sub
Protected Sub CancelEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
GridView1.EditIndex = -1
BindData()
End Sub
Protected Sub UpdateCustomer(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
Dim CustomerID As String = DirectCast(GridView1.Rows(e.RowIndex) _
.FindControl("lblCustomerID"), Label).Text
Dim Name As String = DirectCast(GridView1.Rows(e.RowIndex) _
.FindControl("txtContactName"), TextBox).Text
Dim Company As String = DirectCast(GridView1.Rows(e.RowIndex) _
.FindControl("txtCompany"), TextBox).Text
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "update customers set ContactName=@ContactName," _
& "CompanyName=@CompanyName where CustomerID=@CustomerID;" _
& "select CustomerID,ContactName,CompanyName from customers"
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = CustomerID
cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = Name
cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = Company
GridView1.EditIndex = -1
GridView1.DataSource = GetData(cmd)
GridView1.DataBind()
End Sub
You
can view above I am simply getting the data from the textboxes in the
Footer Row and then firing an update query along with the select query
so that the ASP.Net GridView control is also updated. The figure below
displays the Edit and Update functionality.
Deleting existing record with Confirmation
As
said above I am using custom delete button instead of ASP.Net GridView
delete command field and the main reason for that is to add a
confirmation.
C#
protected void DeleteCustomer(object sender, EventArgs e)
{
LinkButton lnkRemove = (LinkButton)sender;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete from customers where " +
"CustomerID=@CustomerID;" +
"select CustomerID,ContactName,CompanyName from customers";
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value
= lnkRemove.CommandArgument;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
VB.Net
Protected Sub DeleteCustomer(ByVal sender As Object, ByVal e As EventArgs)
Dim lnkRemove As LinkButton = DirectCast(sender, LinkButton)
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "delete from customers where " & _
"CustomerID=@CustomerID;" & _
"select CustomerID,ContactName,CompanyName from customers"
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value _
= lnkRemove.CommandArgument
GridView1.DataSource = GetData(cmd)
GridView1.DataBind()
End Sub
Based
on the sender argument I am getting the reference of the LinkButton
that is clicked and with the CommandArgument of the LinkButton I am
getting the ID of the record to be deleted. After the delete query I am
firing a select query and the rebinding the GridView.
Pagination
For pagination I have added the OnPageIndexChanging event on which I am assigning the new page index to the ASP.Net GridView control and then rebinding the data.
C#
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
BindData();
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
VB.Net
Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
BindData()
GridView1.PageIndex = e.NewPageIndex
GridView1.DataBind()
End Sub
ASP.Net AJAX and JQuery
As you have seen in the start I had added an Update Panel and a DIV along with ASP.Net GridView Control.
Basically
the Update Panel will give the asynchronous calls thus not reloading
the complete page and the JQuery will block the UI until the update
panel is refreshed completely. But instead of blocking the complete page
I am blocking only the contents of the DIV dvGrid. To achieve this I am using the JQuery BlockUI Plugin
<script type = "text/javascript" src = "scripts/jquery-1.3.2.min.js">script>
<script type = "text/javascript" src = "scripts/jquery.blockUI.js">script>
<script type = "text/javascript">
function BlockUI(elementID) {
var prm = Sys.WebForms.PageRequestManager.getInstance();
prm.add_beginRequest(function() {
$("#" + elementID).block({ message: '<img src="images/loadingAnim.gif" />'
,css: {},
overlayCSS: {backgroundColor:'#000000',opacity: 0.6, border:'3px solid #63B2EB'
}
});
});
prm.add_endRequest(function() {
$("#" + elementID).unblock();
});
}
$(document).ready(function() {
BlockUI("dvGrid");
$.blockUI.defaults.css = {};
});
script>
That’s
all the scripting required and the following is achieved with the above
scripts. It will block the Grid until the update panel finishes its
work. Refer the figure below
That’s
it. With this the article comes to an end, hope you liked it I’ll get
back soon with a new one. Download the sample in VB.Net and C# using the
link below.
