ASP.NET GridView: CRUD Operations

• Download source - 3.7 MB
• Download source (external link)

To set up an ASP.NET GridView control with Add, Edit, Delete and Cancel.

Introduction

I used Visual Studio 2013, Update 2 to develop the sample code. The application framework used is .NET 4.5  and the database is LocalDB. I have styled the GridView pagination using bs.pagination.js which is hosted in GitHub here.

Please download the sample project (GridViewProject), unzip it, open the folder and click on GridViewProject.sln to open it in Visual Studio 2013.

Background

The GridView control in ASP.NET Webforms is a great tool for a developer to use to perform CRUD operations on tabular data. It provides for a very efficient and user friendly interface with which to manipulate data. This short article will help show how to set one up with all the necessary functionalities included.

Using the Code

Create a new project in VS 2013. Select ASP.NET Web Application. At the top, select .NET framework 4.5.1, name your application then click next.

On the next screen, select Web Forms template and change authentication as needed. I checked “No Authentication” for the sample code. Click “OK” to initialize the application for development.

Copy the bs.pagination.js file you downloaded from GitHub to the Scripts folder that is created by the Visual Studio application. Next, under Solution Explorer, open the Site.Master file. Add this line of code under Script Manager:

<%--To learn more about bundling scripts in ScriptManager 
see http://go.microsoft.com/fwlink/?LinkID=301884 --%>
<%--Framework Scripts--%>
<asp:ScriptReference Name="MsAjaxBundle" />
<asp:ScriptReference Name="jquery" />
<asp:ScriptReference Path="~/Scripts/bs.pagination.js" />  // <-- New addition
<asp:ScriptReference Name="bootstrap" />
<asp:ScriptReference Name="respond" />

We shall be using Entity Framework 6.1.1 (latest at print) with code first. In Solution Explorer, right click on your project name, and then Click on “Manage Nuget Packages“.

On the left hand side, click on “Online” and then type “Entity Framework” in the search box.
Install the framework.

Next, in Solution Explorer, create a folder named “Models“. Then create a class named “Currency.cs” under the “Models” folder.

Add the following code to Currency.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace GridViewProject.Models
{
    public class Currency
    {
        [ScaffoldColumn(false)]
        [Key]
        public int CurrencyId { get; set; }     

        [Required, StringLength(100), Display(Name = "Currency Name")]
        public string CurrencyName { get; set; }

        [Required, StringLength(20), Display(Name = "Currency Symbol")]
        public string CurrencySymbol { get; set; }

        [Required, DataType(DataType.Currency), Display(Name = "Exchange Rate")]
        public decimal ExchangeRate { get; set; }

        [ScaffoldColumn(false)]
        [Required, DataType(DataType.DateTime)]
        public DateTime Created { get; set; }

        [ScaffoldColumn(false)]
        [DataType(DataType.DateTime)]
        public DateTime? Modified { get; set; }
    }
}

Create a new class under “Models” and name it “ApplicationContext.cs”.

Add the following code to ApplicationContext.cs:

using GridViewProject.Models;
using System.Data.Entity;

namespace GridViewProject.Models
{
    public class ApplicationContext : DbContext
    {
        public ApplicationContext()
            : base("GridViewContext")
        {

        }
        public DbSet<Currency> Currencies { get; set; }
    }
}

Next, we will initiate a database initializer class to get the database ready for first time use.

Create another Class in the “Models” folder and name it DatabaseInitializer.cs.

Add the following code to DatabaseInitializer.cs.

using GridViewProject.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity;

namespace GridViewProject.Models
{
    public class DatabaseInitializer : DropCreateDatabaseAlways<ApplicationContext>
    {
        protected override void Seed(ApplicationContext context)
        {
            // Here we add some seed (test) data
            GetCurrencies().ForEach(c => context.Currencies.Add(c));
        }

        private static List<Currency> GetCurrencies()
        {
            var currencies = new List<Currency> 
            {
                new Currency 
                { 
                    CurrencyId = 1, 
                    CurrencyName = "Dollar",
                    CurrencySymbol = "$",
                    ExchangeRate = 87,
                    Created = DateTime.Now
                }, 
                new Currency 
                { 
                    CurrencyId = 2, 
                    CurrencyName = "Sterling Pound",
                    CurrencySymbol = "£",
                    ExchangeRate = 131,
                    Created = DateTime.Now
                }
            };

            return currencies;
        }
    }
}

Next, open the Global.aspx.cs file and make the following changes (shown in code):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Optimization;
using System.Web.Routing;
using System.Web.Security;
using System.Web.SessionState;
using System.Data.Entity;        // <-- new addition
using GridViewProject.Models;    // <-- new addition

namespace GridViewProject
{
    public class Global : HttpApplication
    {
        void Application_Start(object sender, EventArgs e)
        {
            // Code that runs on application startup
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            // Initialize the database
            Database.SetInitializer(new DatabaseInitializer());     // <-- new addition
        }
    }
}

We shall now modify the Web.config file as follows in order for the database, (gridviewproject.mdf) to be built in the application’s data directory (App_Data), rather than its default location.

<connectionStrings>
    <add name="GridViewContext" 
	connectionString="Data Source=(LocalDB)\v11.0;
	AttachDbFilename=|DataDirectory|\gridviewproject.mdf;Integrated Security=True"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

Build the application, then press F5 to run the application for the first time. It will build the database at the location specified by the connection string.

Under “Solution Explorer“, open Default.aspx and replace the code with the following:

<%@ Page Title="Home Page" Language="C#" 
MasterPageFile="~/Site.Master" 
AutoEventWireup="true" CodeBehind="Default.aspx.cs" 
Inherits="GridViewProject._Default" %>

<asp:Content ID="BodyContent" 
ContentPlaceHolderID="MainContent" runat="server">

   <h2>Currencies Table</h2>
    <script type="text/javascript">
        $(document).ready(function () {

            window.setTimeout(function () {
                $(".alert").fadeTo(1500, 0).slideUp(500, function () {
                    $(this).remove();
                });
            }, 2000);

        });
        function pageLoad() {
            $('.bs-pagination td table').each(function (index, obj) {
                convertToPagination(obj)
            });
        }
    </script>
    <br />
    <p>
        Use the Grid View below to enter your currencies. 
        The <b>Exchange Rate</b> column is the value of the 
        Kenya Shilling to the entered currency.        
    </p>
    <br />
    <div>
        <asp:UpdatePanel ID="UpdatePanelCR" runat="server">
            <ContentTemplate>
                <asp:GridView ID="cRGridView"
                    runat="server"
                    AutoGenerateColumns="False"
                    AllowPaging="True"
                    AllowSorting="True"
                    ShowFooter="True"
                    OnRowEditing="cRGridView_RowEditing"
                    OnRowUpdating="cRGridView_RowUpdating"
                    OnPageIndexChanging="cRGridView_PageIndexChanging"
                    OnRowCancelingEdit="cRGridView_RowCancelingEdit"
                    PagerStyle-CssClass="bs-pagination"
                    ShowHeaderWhenEmpty="True"
                    EmptyDataText="No Records Found"
                    CssClass="table table-striped table-bordered table-hover table-condensed" 
                    Width="600px">
                    <Columns>
                        <asp:TemplateField ItemStyle-Width="30px" HeaderText="CurrencyId" 
				Visible="False">
                            <ItemTemplate>
                                <asp:Label ID="lblCurrencyId" runat="server"
                                    Text='<%# Bind("CurrencyId")%>'></asp:Label>
                            </ItemTemplate>
                            <FooterTemplate>
                                <asp:TextBox ID="txtCurrencyId" Width="40px"
                                    MaxLength="5" runat="server"></asp:TextBox>
                            </FooterTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField ItemStyle-Width="100px" HeaderText="Currency Name">
                            <ItemTemplate>
                                <asp:Label ID="lblCurrencyName" runat="server"
                                    Text='<%# Bind("CurrencyName")%>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="txtCurrencyName" runat="server"
                                    Text='<%# Bind("CurrencyName")%>'></asp:TextBox>
                                <asp:RequiredFieldValidator runat="server" 
				ControlToValidate="txtCurrencyName" Display="Dynamic" 
				ValidationGroup="Edit"
                                    CssClass="text-danger" 
					ErrorMessage="The Currency Name field is required." />
                            </EditItemTemplate>
                            <FooterTemplate>
                                <asp:TextBox ID="txtCurrencyName" runat="server"></asp:TextBox>
                                <asp:RequiredFieldValidator runat="server" 
				ControlToValidate="txtCurrencyName" Display="Dynamic" 
				ValidationGroup="Insert"
                                    CssClass="text-danger" 
					ErrorMessage="The Currency Name field is required." />
                            </FooterTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField ItemStyle-Width="100px" HeaderText="Currency Symbol">
                            <ItemTemplate>
                                <asp:Label ID="lblCurrencySymbol" runat="server"
                                    Text='<%# Bind("CurrencySymbol")%>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="txtCurrencySymbol" runat="server"
                                    Text='<%# Bind("CurrencySymbol")%>'></asp:TextBox>
                                <asp:RequiredFieldValidator runat="server" 
					ControlToValidate="txtCurrencySymbol" 
					Display="Dynamic" ValidationGroup="Edit"
                                    CssClass="text-danger" 
					ErrorMessage="The Currency Symbol field is required." />
                            </EditItemTemplate>
                            <FooterTemplate>
                                <asp:TextBox ID="txtCurrencySymbol" runat="server"></asp:TextBox>
                                <asp:RequiredFieldValidator runat="server" 
					ControlToValidate="txtCurrencySymbol" 
					Display="Dynamic" ValidationGroup="Insert"
                                    CssClass="text-danger" 
					ErrorMessage="The Currency Symbol field is required." />
                            </FooterTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField ItemStyle-Width="100px" HeaderText="Exchange Rate">
                            <ItemTemplate>
                                <asp:Label ID="lblExchangeRate" runat="server"
                                    Text='<%# Bind("ExchangeRate")%>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="txtExchangeRate" runat="server"
                                    Text='<%# Bind("ExchangeRate")%>'></asp:TextBox>
                                <asp:RequiredFieldValidator runat="server" 
					ControlToValidate="txtExchangeRate" Display="Dynamic"
                                    CssClass="text-danger" 
					ErrorMessage="The Exchange Rate field is required." 
					ValidationGroup="Edit" />
                                <asp:RegularExpressionValidator 
				ControlToValidate="txtExchangeRate" runat="server" 
				CssClass="text-danger" Display="Dynamic"
                                    ErrorMessage="Only numbers allowed." 
					ValidationExpression="^[0-9]{0,6}(\.[0-9]{1,2})?$"
                                    ValidationGroup="Edit"></asp:RegularExpressionValidator>
                            </EditItemTemplate>
                            <FooterTemplate>
                                <asp:TextBox ID="txtExchangeRate" runat="server"></asp:TextBox>
                                <asp:RequiredFieldValidator runat="server" 
					ControlToValidate="txtExchangeRate" Display="Dynamic"
                                    CssClass="text-danger" 
					ErrorMessage="The Exchange Rate field is required." 
					ValidationGroup="Insert" />
                                <asp:RegularExpressionValidator ControlToValidate="txtExchangeRate" 
					runat="server" CssClass="text-danger" Display="Dynamic"
                                    ErrorMessage="Only numbers allowed." 
					ValidationExpression="^[0-9]{0,6}(\.[0-9]{1,2})?$"
                                    ValidationGroup="Insert"></asp:RegularExpressionValidator>
                            </FooterTemplate>
                        </asp:TemplateField>
                        <asp:CommandField ItemStyle-Width="100px" ShowEditButton="True" 
				ValidationGroup="Edit" />
                        <asp:TemplateField ItemStyle-Width="50px">
                            <ItemTemplate>
                                <asp:LinkButton ID="lnkRemove" runat="server"
                                    CommandArgument='<%# Bind("CurrencyId")%>'
                                    OnClientClick="return confirm
					('Are you sure you want to delete this row?')"
                                    Text="Delete" OnClick="DeleteCurrency"></asp:LinkButton>
                            </ItemTemplate>
                            <FooterTemplate>
                                <asp:Button ID="btnAdd" runat="server" Text="Add" 
				ValidationGroup="Insert" CssClass="btn btn-primary btn-sm"
                                    OnClick="AddNewCurrency" />
                            </FooterTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
            </ContentTemplate>
            <Triggers>
                <asp:AsyncPostBackTrigger ControlID="cRGridView" />
            </Triggers>
        </asp:UpdatePanel>
    </div>

</asp:Content>

Next open Default.aspx.cs and copy the code below to the class:

using GridViewProject.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace GridViewProject
{
    public partial class _Default : Page
    {
        protected GridViewProject.Models.ApplicationContext _db = 
				new GridViewProject.Models.ApplicationContext();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                bindCRGridView();
            }
        }

        // Binding the GridView
        private void bindCRGridView()
        {
            // Get existing currencies
            var existingCR = _db.Currencies.ToList();

            // Create Data Table
            DataTable dt = new DataTable();
            dt.Columns.Add("CurrencyId", typeof(int));
            dt.Columns.Add("CurrencyName", typeof(string));
            dt.Columns.Add("CurrencySymbol", typeof(string));
            dt.Columns.Add("ExchangeRate", typeof(decimal));

            if (existingCR.Count > 0)
            {
                foreach (var item in existingCR)
                {
                    dt.Rows.Add(item.CurrencyId, item.CurrencyName, 
				item.CurrencySymbol, item.ExchangeRate);
                }

                cRGridView.DataSource = dt;
                cRGridView.DataBind();

                if (cRGridView.Rows.Count > 0)
                {
                    cRGridView.UseAccessibleHeader = true;
                    cRGridView.HeaderRow.TableSection = TableRowSection.TableHeader;
                }

                cRGridView.Columns[4].Visible = true;
            }
            else
            {
                dt.Rows.Add(dt.NewRow());
                cRGridView.DataSource = dt;
                cRGridView.DataBind();

                cRGridView.Columns[4].Visible = false;
                foreach (GridViewRow row in cRGridView.Rows)
                {
                    if (row.RowType == DataControlRowType.DataRow)
                    {
                        LinkButton lb = ((LinkButton)row.FindControl("lnkRemove"));
                        lb.Visible = false;
                    }
                }
            }
        }

        // Adding new currency
        protected void AddNewCurrency(object sender, EventArgs e)
        {
            string currencyName = ((TextBox)cRGridView.FooterRow.FindControl("txtCurrencyName")).Text;
            string currencySymbol = ((TextBox)cRGridView.FooterRow.FindControl
							("txtCurrencySymbol")).Text;
            string exchangeRate = ((TextBox)cRGridView.FooterRow.FindControl("txtExchangeRate")).Text;

            Currency cTable = new Currency
            {          
                CurrencyName = currencyName,
                CurrencySymbol = currencySymbol,
                ExchangeRate = Decimal.Parse(exchangeRate),
                Created = DateTime.Now
            };

            if (ModelState.IsValid)
            {
                // Save record
                _db.Currencies.Add(cTable);
                _db.SaveChanges();
            }

            // Rebind Grid view
            bindCRGridView();
        }

        // Updating a currency
        protected void cRGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            string currencyId = ((Label)cRGridView.Rows[e.RowIndex].FindControl("lblCurrencyId")).Text;
            string currencyName = ((TextBox)cRGridView.Rows[e.RowIndex].FindControl
								("txtCurrencyName")).Text;
            string currencySymbol = ((TextBox)cRGridView.Rows[e.RowIndex].FindControl
								("txtCurrencySymbol")).Text;
            string exchangeRate = ((TextBox)cRGridView.Rows[e.RowIndex].FindControl
								("txtExchangeRate")).Text;

            using (_db)
            {
                var item = _db.Currencies.Find(Convert.ToInt32(currencyId));

                if (item == null)
                {
                    // The item wasn't found
                    ModelState.AddModelError("", String.Format
				("Item with id {0} was not found", currencyId));
                    return;
                }

                item.CurrencyName = currencyName;
                item.CurrencySymbol = currencySymbol;
                item.ExchangeRate = Decimal.Parse(exchangeRate);
                item.Modified = DateTime.Now;

                if (ModelState.IsValid)
                {
                    // Save changes here
                    _db.SaveChanges();
                }

                cRGridView.EditIndex = -1;
                // Rebind Grid view
                bindCRGridView();
            }
        }

        // Deleting currency
        protected void DeleteCurrency(object sender, EventArgs e)
        {
            LinkButton lnkRemove = (LinkButton)sender;

            using (_db)
            {
                var item = _db.Currencies.Find(Convert.ToInt32(lnkRemove.CommandArgument));

                if (item != null)
                {
                    _db.Currencies.Remove(item);
                    _db.SaveChanges();
                }

                // Rebind Grid view
                bindCRGridView();
            }
        }

        // This event is raised when one of the paging buttons is clicked
        protected void cRGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            cRGridView.PageIndex = e.NewPageIndex;
            bindCRGridView();
        }

        // This event is raised when a row's Edit button is clicked, 
        // but before the GridView control enters edit mode
        protected void cRGridView_RowEditing(object sender, GridViewEditEventArgs e)
        {
            cRGridView.EditIndex = e.NewEditIndex;
            bindCRGridView();
        }

        // This event is raised when the Cancel button of a row in edit mode is clicked, 
        //but before the row exits edit mode
        protected void cRGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            cRGridView.EditIndex = -1;
            bindCRGridView();
        }
    }
}

You can run the application at this stage and perform CRUD operations using the GridView. The pagination starts after 10 items have been added to the Gridview.

To check database data, on “Server Explorer“, right click on GridViewContext -> Tables -> right click on Currencies and then click on “Show Table Data“.

That is it! Hope this is of some help.

CodeProject

The post ASP.NET GridView: CRUD Operations appeared first on KenyaTips.