It is always better to use server side controls for CRUD operations that would allow us to easily manipulate and present data in the page with built-in and custom properties. It is also important to provide visually pleasing and user friendly pages particularly when the page involves more user interaction for operations like creating, updating or deleting records in a database. So let us use ASP.NET GridView with the simple and rich Twitter Bootstrap's Modal jQuery plugin to implement a complete CRUD package in ASP.NET Web Forms Application.
In this article, I am going to use a table in MySql Database for demonstration purpose. To run the sample application provided in the above link, you have to create required table in MySql database as explained below.
1. First let us set up the project with required libraries.
Download bootstrap files from here.
Include the latest jQuery library, bootstrap files (bootstrap.js,bootstrap.css) from the download to your project.
mysql> create table tblCountry(Code varchar(10),Name varchar(100),Continent varchar(50),Region varchar(50),Population bigint,IndepYear int);
For your ease I am giving few insert statements with dummy values with which you can add a few rows in the table you have created instantly.
insert into tblCountry values('ABW','Aruba','North America','Caribbean',10300000,1956);
insert into tblCountry values('AFG','Afghanistan','Asia','Southern and Central Asia',227200,1919);
insert into tblCountry values('AGO','Angola','Africa','Central Africa',128780008,1975);
insert into tblCountry values('AIA','Anguilla','North America','Caribbean',80000,1942);
insert into tblCountry values('ALB','Albania','Europe','Southern Europe',3401200,1912);
3. Now let us proceed and add a Web Page to our Web forms application. I am going to break down the code into several parts for easy understanding.
First of all add a ScriptManager control to your webform as we are going to do everything in AJAX way. Then let us add an Update Panel with Gridview and add asynchronouspostback trigger for GridViewRowCommand Event. We are not going to use the default crud functionality that GridView provides but going to achieve the same using GridView RowCommand Event just like how we displayed details in my previous article.
First of all add a ScriptManager control to your webform as we are going to do everything in AJAX way. Then let us add an Update Panel with Gridview and add asynchronouspostback trigger for GridViewRowCommand Event. We are not going to use the default crud functionality that GridView provides but going to achieve the same using GridView RowCommand Event just like how we displayed details in my previous article.
<asp:ScriptManager runat="server" ID="ScriptManager1" />
<!-- Placing GridView in UpdatePanel-->
<asp:UpdatePanel ID="upCrudGrid" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server"
Width="940px" HorizontalAlign="Center"
OnRowCommand="GridView1_RowCommand"
AutoGenerateColumns="false" AllowPaging="true"
DataKeyNames="Code"
CssClass="table table-hover table-striped">
<Columns>
<asp:ButtonField CommandName="detail"
ControlStyle-CssClass="btn btn-info"
ButtonType="Button" Text="Detail" HeaderText="Detailed View">
<ControlStyle CssClass="btn btn-info"></ControlStyle>
</asp:ButtonField>
<asp:ButtonField CommandName="editRecord"
ControlStyle-CssClass="btn btn-info"
ButtonType="Button" Text="Edit" HeaderText="Edit Record">
<ControlStyle CssClass="btn btn-info"></ControlStyle>
</asp:ButtonField>
<asp:ButtonField CommandName="deleteRecord"
ControlStyle-CssClass="btn btn-info"
ButtonType="Button" Text="Delete" HeaderText="Delete Record">
<ControlStyle CssClass="btn btn-info"></ControlStyle>
</asp:ButtonField>
<asp:BoundField DataField="Code" HeaderText="Code" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Continent"
HeaderText="Continent" />
<asp:BoundField DataField="Region"
HeaderText="Region" />
<asp:BoundField DataField="Population"
HeaderText="Population" />
<asp:BoundField DataField="IndepYear"
HeaderText="Independence Year" />
</Columns>
</asp:GridView>
<asp:Button ID="btnAdd" runat="server"
Text="Add New Record" CssClass="btn btn-info"
OnClick="btnAdd_Click" />
</ContentTemplate>
<Triggers>
</Triggers>
</asp:UpdatePanel>
Next let us add code for detail view popup that displays selected gridview row in a detailsview control.
<!-- Detail Modal Starts here-->
<div id="detailModal" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h3 id="myModalLabel">Detailed View</h3>
</div>
<div class="modal-body">
<asp:UpdatePanel ID="UpdatePanel2" runat="server">
<ContentTemplate>
<asp:DetailsView ID="DetailsView1" runat="server" CssClass="table table-bordered table-hover" BackColor="White" ForeColor="Black" FieldHeaderStyle-Wrap="false" FieldHeaderStyle-Font-Bold="true" FieldHeaderStyle-BackColor="LavenderBlush" FieldHeaderStyle-ForeColor="Black" BorderStyle="Groove" AutoGenerateRows="False">
<Fields>
<asp:BoundField DataField="Code" HeaderText="Code" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Continent" HeaderText="Continent" />
<asp:BoundField DataField="Population" HeaderText="Population" />
<asp:BoundField DataField="IndepYear" HeaderText="Independence Year" />
</Fields>
</asp:DetailsView>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="GridView1" EventName="RowCommand" />
<asp:AsyncPostBackTrigger ControlID="btnAdd" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
<div class="modal-footer">
<button class="btn btn-info" data-dismiss="modal" aria-hidden="true">Close</button>
</div>
</div>
</div><!-- Detail Modal Ends here -->
Next let us add code for Edit Modal Popup that contains required controls to update the existing values in a particular record.
<!-- Edit Modal Starts here -->
<div id="editModal" class="modal hide fade"
tabindex="-1" role="dialog" aria-labelledby="editModalLabel"
aria-hidden="true">
<div class="modal-header">
<button type="button" class="close"
data-dismiss="modal" aria-hidden="true">×</button>
<h3 id="editModalLabel">Edit Record</h3>
</div>
<asp:UpdatePanel ID="upEdit" runat="server">
<ContentTemplate>
<div class="modal-body">
<table class="table">
<tr>
<td>Country Code :
<asp:Label ID="lblCountryCode" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td>Population :
<asp:TextBox ID="txtPopulation" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>Country Name:
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>Continent:
<asp:TextBox ID="txtContinent1" runat="server"></asp:TextBox>
</td>
</tr>
</table>
</div>
<div class="modal-footer">
<asp:Label ID="lblResult" Visible="false" runat="server"></asp:Label>
<asp:Button ID="btnSave" runat="server" Text="Update" CssClass="btn btn-info" OnClick="btnSave_Click" />
<button class="btn btn-info" data-dismiss="modal" aria-hidden="true">Close</button>
</div>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="GridView1" EventName="RowCommand" />
<asp:AsyncPostBackTrigger ControlID="btnSave" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</div>
<!-- Edit Modal Ends here -->
<!-- Add Record Modal Starts here-->
<div id="addModal" class="modal hide fade" tabindex="-1" role="dialog"
aria-labelledby="addModalLabel" aria-hidden="true">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal"
aria-hidden="true">×</button>
<h3 id="addModalLabel">Add New Record</h3>
</div>
<asp:UpdatePanel ID="upAdd" runat="server">
<ContentTemplate>
<div class="modal-body">
<table class="table table-bordered table-hover">
<tr>
<td>Country Code :
<asp:TextBox ID="txtCode" runat="server">
</asp:TextBox>
</td>
</tr>
<tr>
<td>Country Name :
<asp:TextBox ID="txtCountryName" runat="server">
</asp:TextBox>
</td>
</tr>
<tr>
<td>Continent Name:
<asp:TextBox ID="txtContinent" runat="server">
</asp:TextBox>
</td>
</tr>
<tr>
<td>Region:
<asp:TextBox ID="txtRegion" runat="server">
</asp:TextBox>
</td>
</tr>
<tr>
<td>Population:
<asp:TextBox ID="txtTotalPopulation" runat="server">
</asp:TextBox>
</td>
</tr>
<tr>
<td>Year of Independence
<asp:TextBox ID="txtIndYear" runat="server">
</asp:TextBox>
</td>
</tr>
</table>
</div>
<div class="modal-footer">
<asp:Button ID="btnAddRecord" runat="server" Text="Add"
CssClass="btn btn-info" OnClick="btnAddRecord_Click" />
<button class="btn btn-info" data-dismiss="modal"
aria-hidden="true">Close</button>
</div>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="btnAddRecord" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</div>
<!--Add Record Modal Ends here-->
Finally let us add code for confirming delete operation in a modal popup as shown below,
<!-- Delete Record Modal Starts here-->
<div id="deleteModal" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="delModalLabel" aria-hidden="true">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h3 id="delModalLabel">Delete Record</h3>
</div>
<asp:UpdatePanel ID="upDel" runat="server">
<ContentTemplate>
<div class="modal-body">
Are you sure you want to delete the record?
<asp:HiddenField ID="hfCode" runat="server" />
</div>
<div class="modal-footer">
<asp:Button ID="btnDelete" runat="server" Text="Delete" CssClass="btn btn-info" OnClick="btnDelete_Click" />
<button class="btn btn-info" data-dismiss="modal" aria-hidden="true">Cancel</button>
</div>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="btnDelete" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</div><!--Delete Record Modal Ends here -->
Now we are done with the markup. Note that I have used UpdatePanel on every section of code, just to make the GridView operations function totally in AJAX way without refreshing the whole page.
Before proceeding to code behind let me explain how Bootstrap Modal works. The contents to be shown on modal popup must be placed within a div with class value set to "modal" which will be hidden by default on page load and to fire the modal popup, we need to add one line of jquery code ($('#modaldivid').modal('show');) on a button or link click as we wish. With this note, let us proceed further by implementing necessary code in code-behind file.
3. Add MySql Database connection string to Web.config file.
<add connectionString="server=localhost;uid=root;password=xxxx;database=world; pooling=false;" providerName="MySql.Data.MySqlDataClient" name="MySqlConnString" />
4. In the code-behind page, let us first write code to fetch data from database and display on Page Load. This code to fetch data from database and bind to gridview for display will be reused in the later sections since after every operation (create, update or delete) we have to reload our gridview with latest information from database.
DataTable dt;
protected void Page_Load(object sender, EventArgs e)
{
BindGrid();
}
public void BindGrid()
{
try
{
//Fetch data from mysql database
string connString = ConfigurationManager.ConnectionStrings["MySqlConnString"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
string cmd = "select * from tblCountry limit 10";
MySqlDataAdapter dAdapter = new MySqlDataAdapter(cmd, conn);
DataSet ds = new DataSet();
dAdapter.Fill(ds);
dt = ds.Tables[0];
//Bind the fetched data to gridview
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (MySqlException ex)
{
System.Console.Error.Write(ex.Message);
}
}
Next let us write code to perform core operations (add, update and delete record from database).
private void executeUpdate(string code,int population,string countryname,string continent)
{
string connString = ConfigurationManager.ConnectionStrings["MySqlConnString"].ConnectionString;
try
{
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
string updatecmd = "update tblCountry set Population=@population, Name=@countryname,Continent=@continent where Code=@code";
MySqlCommand updateCmd = new MySqlCommand(updatecmd,conn);
updateCmd.Parameters.AddWithValue("@population", population);
updateCmd.Parameters.AddWithValue("@countryname", countryname);
updateCmd.Parameters.AddWithValue("@continent", continent);
updateCmd.Parameters.AddWithValue("@code", code);
updateCmd.ExecuteNonQuery();
conn.Close();
}
catch (MySqlException me)
{
System.Console.Error.Write(me.InnerException.Data);
}
}
private void executeAdd(string code, string name, string continent,string region, int population, int indyear)
{
string connString = ConfigurationManager.ConnectionStrings["MySqlConnString"].ConnectionString;
try
{
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
string insertcmd = "insert into tblCountry (Code,Name,Continent,Region,Population,IndepYear) values (@code,@name,@continent,@region,@population,@indyear)";
MySqlCommand addCmd = new MySqlCommand(insertcmd, conn);
addCmd.Parameters.AddWithValue("@code", code);
addCmd.Parameters.AddWithValue("@name", name);
addCmd.Parameters.AddWithValue("@continent", continent);
addCmd.Parameters.AddWithValue("@region", region);
addCmd.Parameters.AddWithValue("@population", population);
addCmd.Parameters.AddWithValue("@indyear", indyear);
addCmd.ExecuteNonQuery();
conn.Close();
}
catch (MySqlException me)
{
System.Console.Write(me.Message);
}
}
private void executeDelete(string code)
{
string connString = ConfigurationManager.ConnectionStrings["MySqlConnString"].ConnectionString;
try
{
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
string deletecmd = "delete from tblCountry where Code=@code";
MySqlCommand delCmd = new MySqlCommand(deletecmd, conn);
delCmd.Parameters.AddWithValue("@code", code);
delCmd.ExecuteNonQuery();
conn.Close();
}
catch (MySqlException me)
{
System.Console.Write(me.Message);
}
}
When the page initially runs, records are picked up from database and displayed in gridview. Once the user clicks the detail/edit/delete button for any row, control is passed to Gridview's RowCommand Event with the corresponding Command Name of the button that is clicked.
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
int index = Convert.ToInt32(e.CommandArgument);
if (e.CommandName.Equals("detail"))
{
string code = GridView1.DataKeys[index].Value.ToString();
IEnumerable<DataRow> query = from i in dt.AsEnumerable()
where i.Field<String>("Code").Equals(code)
select i;
DataTable detailTable = query.CopyToDataTable<DataRow>();
DetailsView1.DataSource = detailTable;
DetailsView1.DataBind();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("$('#detailModal').modal('show');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "DetailModalScript", sb.ToString(), false);
}
else if (e.CommandName.Equals("editRecord"))
{
GridViewRow gvrow = GridView1.Rows[index];
lblCountryCode.Text = HttpUtility.HtmlDecode(gvrow.Cells[3].Text).ToString();
txtPopulation.Text = HttpUtility.HtmlDecode(gvrow.Cells[7].Text);
txtName.Text = HttpUtility.HtmlDecode(gvrow.Cells[4].Text);
txtContinent1.Text = HttpUtility.HtmlDecode(gvrow.Cells[5].Text);
lblResult.Visible = false;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("$('#editModal').modal('show');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "EditModalScript", sb.ToString(), false);
}
else if (e.CommandName.Equals("deleteRecord"))
{
string code = GridView1.DataKeys[index].Value.ToString();
hfCode.Value = code;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("$('#deleteModal').modal('show');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "DeleteModalScript", sb.ToString(), false);
}
}
Heads Up!
Do not use the word "edit" and "delete" as command name for edit button, since these are reserved names that will automatically fire the Gridview RowEditing and RowDeleting events causing a runtime error. We are not using the GridView's default CRUD option here so use different words for the RowCommand event to function as expected.
Finally, in the 'OnClick' event of the buttons present inside each of the modal popup let us add necessary code to call the core methods that executes update, add and delete operations. This part also updates the user once the operation is completed successfully and closes the modal popup after refreshing the gridview with latest data.
// Handles Update Button Click Event
protected void btnSave_Click(object sender, EventArgs e)
{
string code=lblCountryCode.Text;
int population=Convert.ToInt32(txtPopulation.Text);
string countryname = txtName.Text;
string continent=txtContinent1.Text;
executeUpdate(code,population,countryname,continent);
BindGrid();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("alert('Records Updated Successfully');");
sb.Append("$('#editModal').modal('hide');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "EditHideModalScript", sb.ToString(), false);
}
//Handles Add record button click in main form
protected void btnAdd_Click(object sender, EventArgs e)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("$('#addModal').modal('show');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "AddShowModalScript", sb.ToString(), false);
}
//Handles Add button click in add modal popup
protected void btnAddRecord_Click(object sender, EventArgs e)
{
string code = txtCode.Text;
string name = txtCountryName.Text;
string region = txtRegion.Text;
string continent = txtContinent.Text;
int population = Convert.ToInt32(txtTotalPopulation.Text);
int indyear = Convert.ToInt32(txtIndYear.Text);
executeAdd(code, name, continent, region,population, indyear);
BindGrid();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("alert('Record Added Successfully');");
sb.Append("$('#addModal').modal('hide');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "AddHideModalScript", sb.ToString(), false);
}
//Handles Delete button click in delete modal popup
protected void btnDelete_Click(object sender, EventArgs e)
{
string code=hfCode.Value;
executeDelete(code);
BindGrid();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("alert('Record deleted Successfully');");
sb.Append("$('#deleteModal').modal('hide');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "delHideModalScript", sb.ToString(), false);
}
That's it! When the user clicks on detail button for any row,
When the user clicks on 'Edit' Button,
Once the update is done,
When the user clicks Add new Record button in the main form,
This is all I have been looking for. Thanks ALL IN ONE IT TUTORIAL
ReplyDelete