We will do insert, update and delete functionality in a GridView with modal popup, we will proceed step by step to learn how easily we can complete this functionality in any project. We will try to create a basic user page where you can see all the users in a grid with paging and buttons to add new user, update an existing user and delete any existing user with confirmation box. We will always open modal popup from code behind so we can execute all the necessary methods.
First of all we will create a table Users
CREATE TABLE [Users](
[UserID] [int] IDENTITY(100,1) NOT NULL,
[UserName] [varchar](50) NULL,
[Address] [varchar](100) NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY ([UserID])
)
Now we will create UserEntity class to pass data from here to there (it should be in a separate layer but we will use in the same application)
public class UserEntity
{
public Int32 UserId { get; set; }
public String UserName { get; set; }
public String Address { get; set; }
public String City { get; set; }
public String State { get; set; }
}
We will user ajax script manager and update panel to stop page post back on every event, so add them on the page:
<asp:ScriptManager ID="ScriptManager" runat="server" />
<asp:UpdatePanel ID="upnlUsers" runat="server">
<ContentTemplate>
</ContentTemplate>
</asp:UpdatePanel>
All the code we will put inside the content template.
Here is the complete page HTML:
<!—Add button to add new user -->
<div>
<asp:Button ID="btnAdd" runat="server" Text="Add New Role"
OnClick="btnAdd_Click" />
</div>
<!— GridView code HTML to show users -->
<asp:GridView ID="gvUsers" runat="server"
AutoGenerateColumns="false"
Width="100%"
CssClass="grid"
GridLines="none"
DataKeyNames="UserID"
EmptyDataText = "No record found!"
EmptyDataRowStyle-CssClass ="gvEmpty">
<Columns>
<asp:BoundField HeaderText="ID" DataField="UserID" />
<asp:BoundField HeaderText="User Name" DataField="UserName" />
<asp:BoundField HeaderText="Address" DataField="Address" />
<asp:BoundField HeaderText="City" DataField="City" />
<asp:TemplateField HeaderText="Action">
<ItemTemplate>
<asp:ImageButton ID="ibtnEdit" runat="server"
ImageUrl="/Images/btn-edit.png" OnClick="ibtnEdit_Click" />
<asp:ImageButton ID="ibtnDelete" runat="server"
ImageUrl="/Images/btn-delete.jpg"
OnClientClick="javascript:return confirm('Do you want to delete it?');"
OnClick="ibtnDelete_Click" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<div id="pnlAddPopup" runat="server" style="width:500px; background-color:#ffffff;">
<div id="popupheader" class="popuHeader">
<asp:Label ID="lblHeader" runat="server" Text="Add New User" />
<span style="float:right">
<img id="imgClose" src="/Images/btn-close.png" alt="close" title="Close" />
</span>
</div>
<div>
<asp:HiddenField ID="hfUserID" runat="server" Value="0" />
<table border="0" class="table-border" >
<tr>
<td>User Name</td>
<td><asp:TextBox ID="txtUserName" runat="server" /></td>
</tr>
<tr>
<td>Address</td>
<td>
<asp:TextBox ID="txtAddress" runat="server"
TextMode="MultiLine" Rows="3" Columns="40" />
</td>
</tr>
<tr>
<td>City</td>
<td><asp:TextBox ID="txtCity" runat="server" /></td>
</tr>
<tr>
<td>State</td>
<td><asp:TextBox ID="txtState" runat="server" /></td>
</tr>
<tr>
<td> </td>
<td>
<asp:Button ID="btnSave" runat="server" Text=" Save "
OnClick="btnSave_Click" />
<asp:Button ID="btnCancel" runat="server" Text=" Cancel "
OnClientClick="javascript:$find('mpeUserBehavior').hide();return false;" />
</td>
</tr>
</table>
</div>
<ajax:ModalPopupExtender ID="mpeUser" runat="server"
TargetControlID="hfUserID"
PopupControlID="pnlAddPopup"
BehaviorID="mpeUserBehavior"
DropShadow="true"
CancelControlID="imgClose"
PopupDragHandleControlID="popupheader" />
</div>
Some points which you need to understand, in ModalPopupExtender we used BehaviorID which will help to find the modal popup and close it.
Image button in popup header is used as CancelControlID but we also have a cancel button in our modal popup and here is the code to close the modal popup by cancel button without postback:
OnClientClick =”javascript:$find('mpeUserBehavior').hide();return false;”
How to add events with image button in gridview for edit and delete
Now we will see code behind methods one by one, so start from page load method:
String _connStr = ConfigurationManager
.ConnectionStrings["CrudConnection"].ConnectionString;
#region[Page Load event]
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
LoadData();
}
#endregion
We declared a global variable _connStr to read the connection string from web.config file.
<connectionStrings>
<add name="CrudConnection"
connectionString="data source=my_sqlsrv;Trusted_Connection=true;
Initial Catalog=SampleDB"
providerName="System.Data.SqlClient" />
</connectionStrings>
If you can see we used if not is postback because we don’t want to load record on every postback but only for the first time, on page load.
And here is the code to load data method, which gets data from database and bind to gridview
#region[Load user data from database]
public void LoadData()
{
using(SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = _connStr;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from users Order By userId DESC";
cmd.CommandType = System.Data.CommandType.Text;
DataTable dTable = new DataTable();
// Open connection to use execute reader
if (conn.State == ConnectionState.Closed) conn.Open();
// I used execute reader to read data quickly
SqlDataReader dReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dTable.Load(dReader);
gvUsers.DataSource = dTable;
gvUsers.DataBind();
}
}
#endregion
Note, we used Sql DataReader to load data into data table because it is faster than dataset and ado data adapter.
Add New User button click event handler, which clear all the control in modal popup and show the it
#region[Add button event]
protected void btnAdd_Click(object sender, EventArgs e)
{
ClearPopupControls();
mpeUser.Show();
}
#endregion
#region[Clear Modal Popup controls]
private void ClearPopupControls()
{
hfUserID.Value = "0";
txtUserName.Text = String.Empty;
txtAddress.Text = String.Empty;
txtCity.Text = String.Empty;
txtState.Text = String.Empty;
}
#endregion
Create methods to insert, get, update and delete user record from/to database
#region[Add New User record into database]
public bool AddNewUser(UserEntity user)
{
String insertQuery = @"INSERT INTO [Users]
([UserName], [Address], [City], [State])
VALUES(@UserName, @Address, @City, @State)";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = _connStr;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = insertQuery;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@UserName", user.UserName);
cmd.Parameters.AddWithValue("@Address", user.Address);
cmd.Parameters.AddWithValue("@City", user.City);
cmd.Parameters.AddWithValue("@State", user.State);
if (conn.State == ConnectionState.Closed) conn.Open();
cmd.ExecuteNonQuery();
return true;
}
}
#region[Update User record into database]
public bool UpdateUser(UserEntity user)
{
String updateQuery = @"Update [Users]
SET [UserName] = @UserName
,[Address] = @Address
,[City] = @City
,[State] = @State
Where [UserID] = @UserId";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = _connStr;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = updateQuery;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@UserId", user.UserId);
cmd.Parameters.AddWithValue("@UserName", user.UserName);
cmd.Parameters.AddWithValue("@Address", user.Address);
cmd.Parameters.AddWithValue("@City", user.City);
cmd.Parameters.AddWithValue("@State", user.State);
if (conn.State == ConnectionState.Closed) conn.Open();
cmd.ExecuteNonQuery();
return true;
}
}
#endregion
#region[Delete User record from database]
public bool DeleteUserByID(Int32 userId)
{
String updateQuery = @"Delete [Users] Where [UserID] = @UserId";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = _connStr;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = updateQuery;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@UserId", userId);
if (conn.State == ConnectionState.Closed) conn.Open();
cmd.ExecuteNonQuery();
return true;
}
}
#endregion
#region[Get User to show in popup for edit]
public UserEntity GetUserByID(Int32 userID)
{
String updateQuery = @"Select * From [Users]
Where [UserID] = @UserId";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = _connStr;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = updateQuery;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@UserId", userID);
if (conn.State == ConnectionState.Closed) conn.Open();
SqlDataReader dataReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dataTable = new DataTable();
dataTable.Load(dataReader);
UserEntity existinguser = new UserEntity();
foreach (DataRow row in dataTable.Rows)
{
existinguser.UserId = Convert.ToInt32(row["UserID"]);
existinguser.UserName = Convert.ToString(row["UserName"]);
existinguser.Address = Convert.ToString(row["Address"]);
existinguser.City = Convert.ToString(row["City"]);
existinguser.State = Convert.ToString(row["State"]);
}
return existinguser;
}
}
#endregion
Save Button click event handler to save record into database, nothing is complicated, as we already seen at the time of adding new user we are setting userid to 0 means need to create a user otherwise update user.
#region[Save User]
protected void btnSave_Click(object sender, EventArgs e)
{
UserEntity user = new UserEntity();
user.UserId = Convert.ToInt32(hfUserID.Value);
user.UserName = txtUserName.Text.Trim();
user.Address = txtAddress.Text.Trim();
user.City = txtCity.Text.Trim();
user.State = txtState.Text.Trim();
if (user.UserId == 0)
AddNewUser(user);
else
UpdateUser(user);
LoadData();
}
#endregion
Edit button event from grid view: (Sorry, I missed it earlier)
#region[Edit button click event]
protected void ibtnEdit_Click(object sender, ImageClickEventArgs e)
{
GridViewRow gvRow = (GridViewRow)((ImageButton)sender).NamingContainer;
Int32 UserId = Convert.ToInt32(gvUsers.DataKeys[gvRow.RowIndex].Value);
UserEntity user = GetUserByID(UserId);
// Now set value to modal popup
hfUserID.Value = user.UserId.ToString();
txtUserName.Text = user.UserName;
txtAddress.Text = user.Address;
txtCity.Text = user.City;
txtState.Text = user.State;
mpeUser.Show();
}
#endregion
Use the same modal popup extender which we use for adding new users and same html will be used, when we go for updating the record we will keep Id in hidden field and pass to update the rocord.
Here is the delete event from gridview:
#region[Delete button event]
protected void ibtnDelete_Click(object sender, ImageClickEventArgs e)
{
GridViewRow gvRow = (GridViewRow)((ImageButton)sender).NamingContainer;
Int32 UserId = Convert.ToInt32(gvUsers.DataKeys[gvRow.RowIndex].Value);
// delete and hide the row from grid view
if (DeleteUserByID(UserId))
gvRow.Visible = false;
}
#endregion
With the help of sender and Namingcontainer we are getting the row and then user id to delete the record but before deleting we used JavaScript confirmation box, if user confirms then we delete the record from database and this time we are not re-loading the record from database but directly hiding the row which is deleted so next time the page will refresh, that row will never come. Here is the confirmation box code with delete button in grid view
<asp:ImageButton ID="ibtnDelete" runat="server"
ImageUrl="/Images/btn-delete.jpg"
OnClientClick="javascript:return confirm('Do you want to delete it?');"
OnClick="ibtnDelete_Click" />
|
Process manager with a reputed organization, Fond of learning new features and technology related to C#, ASP.Net, SQL Server, MVC etc.I like to help others, if I can
|
| By Hamden On 01 Sep, 13 Viewed: 24,743 |
In many cases we need to use read only text box so user cannot edit the value but readonly textbox will lost its value after postback. Let’s say you have a read only text box to set date by using ajax calendar so after post back date will be lost, other case might be you are setting some value in... By Ali Adravi On 24 Apr 2013 Viewed: 4,302
There are ways to call a web service method JavaScript, for more detail you can see [how to retrieve data from database using JavaScript in asp.net][1], but is there any way to call a normal method from JavaScript? And the answer is No; show how we can call a code behind method from JavaScript,... By Jonathan King On 08 Apr 2013 Viewed: 12,506
Most of the times we need to use GridView control to show tabular data in our asp.net application. We simply write procedure to search the records and bind them with GridView and show 10 to 20 records per page. Have you ever thought that why you are fetching all the records and show only 10 to 20... By Ali Adravi On 16 Feb 2013 Viewed: 8,569
A nice feature with ASP.Net is the model binding, it reduced our code and effort to bind our well know controls like GridView, DataList, Repeater etc. So let’s see how we can bind our old controls in new and easy way. Let’s see how we bind the our grid before 4.5 1. We write the code to get... By Mike .Net On 17 Jan 2013 Viewed: 3,217
In asp.net there is not control to select multiple files and upload them once with progress bar, so we will use a small third party DLL to achieve this functionality. We will use Flajaxian FileUploader, you can download it from [http://www.flajaxian.com][1] We will create three different images... By Hamden On 12 Jul 2012 Viewed: 6,567