Friday, 20 October 2017

Entity Framework CRUD Operations with GridView in ASP.NET

In this tutorial, I am going to explain you CRUD (Create, Read, Update and Delete) operations using Entity Framework with GridView control in ASP.NET. I will be using Visual Studio 2013, SQL-Server 2008R2 and Entity Framework 6.0. Below is the step by step tutorial.

Creating Database Table

We will be using two tables tbl_UserMaster and tbl_Country. tbl_UserMaster table is used to store User Details and tbl_Country table is used to bind country name in DropDownList while adding new record and editing existing record.

UserMaster and Country Table
Creating table

Below is the script to create tables and add dummy data into it.

create table tbl_UserMaster
(
UserId int primary key identity,
Name varchar(50),
Email varchar(50),
ContactNo varchar(15),
Gender varchar(10),
Country varchar(50)
)
go
insert into tbl_UserMaster values
('Rahul','Rahul@gmail.com','9123456789','Male','India'),
('John','John@gmail.com','8123456789','Male','Austraila'),
('Mary','Mary@gmail.com','7123456789','Female','USA'),
('Mike','Mike@gmail.com','6123456789','Male','UK')
go
create table tbl_Country
(
CountryId int primary key identity,
Name varchar(50)
)
go
insert into tbl_Country values
('India'),('USA'),('UK'),('Austraila')

Creating ASP.NET Empty Project

Next step is to create ASP.NET Empty WebForm project.
Go to FileNewProject. A new window will be open as shown below.
Now go to WebVisual Studio 2012 → select .NET Framework 4.5 → select ASP.NET Empty Web Application and give project name and click on OK.

Creating asp.net 4.5 empty project

Now, an asp.net empty project will be created.

Adding Entity Data Model And Entity Framework

Now, next step is to add entity data model and entity framework.

Go to WebApplication1 root folder → AddNew Item. A new window will be open as shown below.
Go to Data Tab and select ADO.NET Entity Data Model and give its name SampleModel.edmx and click on OK.
Adding entity data model
Select Generate from database and Click on next
Generate from database
Click on New Connection.
Create new connection
Select your server, write your credentials, select database and test for connection.
Select database
Write connection string name.
Connection string name
Select Entity Framework version 6.0 and click on Next.
Entity Framework version 6.0
Click select your table tbl_Country and tbl_UserMaster and click on Finish.
Selecting Database Objects

Adding WebForm

Now, add a new webform to the project. Create a GridView Control as shown below.

GridView and CSS Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvUserMaster" runat="server" AutoGenerateColumns="false" PageSize="5" AllowPaging="true"
    DataKeyNames="UserId" ShowFooter="true" CellPadding="3" ForeColor="#333333" OnPageIndexChanging="gvUserMaster_PageIndexChanging"
    OnRowEditing="gvUserMaster_RowEditing" OnRowUpdating="gvUserMaster_RowUpdating" OnRowCancelingEdit="gvUserMaster_RowCancelingEdit"
    OnRowDeleting="gvUserMaster_RowDeleting" AlternatingRowStyle-BackColor="White" GridLines="Horizontal">
    <Columns>
        <asp:TemplateField HeaderText="User Name" HeaderStyle-HorizontalAlign="Left" HeaderStyle-Width="150px">
            <ItemTemplate>
                <asp:Label ID="lblName" runat="server" Text='<%# Bind("Name") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="etxtName" runat="server" Text='<%# Bind("Name") %>'></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvetxtName" runat="server" ControlToValidate="etxtName" ErrorMessage="*"
                    Display="Dynamic" ForeColor="Red" SetFocusOnError="True" ValidationGroup="vgEditRecord">
                </asp:RequiredFieldValidator>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="ftxtName" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvftxtName" runat="server" ControlToValidate="ftxtName" ErrorMessage="*"
                    Display="Dynamic" ForeColor="Red" SetFocusOnError="True" ValidationGroup="vgAddRecord">
                </asp:RequiredFieldValidator>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="User Email" HeaderStyle-HorizontalAlign="Left" HeaderStyle-Width="150px">
            <ItemTemplate>
                <asp:Label ID="lblEmail" runat="server" Text='<%# Bind("Email") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="etxtEmail" runat="server" Text='<%# Bind("Email") %>'></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvetxtEmail" runat="server" ControlToValidate="etxtEmail" ErrorMessage="*"
                    Display="Dynamic" ForeColor="Red" SetFocusOnError="True" ValidationGroup="vgEditRecord"></asp:RequiredFieldValidator>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="ftxtEmail" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvftxtEmail" runat="server" ControlToValidate="ftxtEmail" ErrorMessage="*"
                    Display="Dynamic" ForeColor="Red" SetFocusOnError="True" ValidationGroup="vgAddRecord">
                </asp:RequiredFieldValidator>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Contact Number" HeaderStyle-HorizontalAlign="Left" HeaderStyle-Width="150px">
            <ItemTemplate>
                <asp:Label ID="lblContactNo" runat="server" Text='<%# Bind("ContactNo") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="etxtContactNo" runat="server" Text='<%# Bind("ContactNo") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="ftxtContactNo" runat="server"></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Gender" HeaderStyle-HorizontalAlign="Left" HeaderStyle-Width="150px">
            <ItemTemplate>
                <asp:Label ID="lblGender" runat="server" Text='<%# Bind("Gender") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:DropDownList ID="eddlGender" runat="server">
                    <asp:ListItem Text="-Select-" Value="-Select-"></asp:ListItem>
                    <asp:ListItem Text="Male" Value="Male"></asp:ListItem>
                    <asp:ListItem Text="Female" Value="Female"></asp:ListItem>
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="rfveddlGender" runat="server" ControlToValidate="eddlGender" ErrorMessage="*"
                    Display="Dynamic" ForeColor="Red" SetFocusOnError="True" InitialValue="-Select-" ValidationGroup="vgEditRecord">
                </asp:RequiredFieldValidator>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="fddlGender" runat="server">
                    <asp:ListItem Text="-Select-" Value="-Select-"></asp:ListItem>
                    <asp:ListItem Text="Male" Value="Male"></asp:ListItem>
                    <asp:ListItem Text="Female" Value="Female"></asp:ListItem>
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="rfvfddlGender" runat="server" ControlToValidate="fddlGender" ErrorMessage="*"
                    Display="Dynamic" ForeColor="Red" SetFocusOnError="True" InitialValue="-Select-" ValidationGroup="vgAddRecord">
                </asp:RequiredFieldValidator>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country" HeaderStyle-HorizontalAlign="Left" HeaderStyle-Width="150px">
            <ItemTemplate>
                <asp:Label ID="lblCountry" runat="server" Text='<%# Bind("Country") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:DropDownList ID="eddlCountry" runat="server"></asp:DropDownList>
                <asp:RequiredFieldValidator ID="rfveddlCountry" runat="server" ControlToValidate="eddlCountry" ErrorMessage="*"
                    Display="Dynamic" ForeColor="Red" SetFocusOnError="True" InitialValue="-Select-" ValidationGroup="vgEditRecord">
                </asp:RequiredFieldValidator>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="fddlCountry" runat="server"></asp:DropDownList>
                <asp:RequiredFieldValidator ID="rfvfddlCountry" runat="server" ControlToValidate="fddlCountry" ErrorMessage="*"
                    Display="Dynamic" ForeColor="Red" SetFocusOnError="True" InitialValue="-Select-" ValidationGroup="vgAddRecord">
                </asp:RequiredFieldValidator>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Action" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Width="150px">
            <ItemTemplate>
                <asp:Button ID="btnEdit" Text="Edit" runat="server" CommandName="Edit" CssClass="button" CausesValidation="false" />
                &nbsp;
                <asp:Button ID="btnDelete" Text="Delete" runat="server" CommandName="Delete"
                    CssClass="button" OnClientClick='return confirm("Are you sure you want to delete this record?");' CausesValidation="false" />
            </ItemTemplate>
            <EditItemTemplate>
                <asp:Button ID="btnUpdate" Text="Update" runat="server" CommandName="Update" CssClass="button" ValidationGroup="vgEditRecord" />
                &nbsp;
                <asp:Button ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel" CssClass="button" CausesValidation="false" />
            </EditItemTemplate>
            <FooterTemplate>
                <asp:Button ID="fbtnAdd" runat="server" Text="Add New User" OnClick="fbtnAdd_Click" CssClass="button" Style="width: auto !important;"
                    ValidationGroup="vgAddRecord" />
            </FooterTemplate>
        </asp:TemplateField>
    </Columns>
    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <FooterStyle BackColor="#B5C7DE" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#507CD1" ForeColor="White" HorizontalAlign="Center" />
    <PagerStyle CssClass="gvPagercss" />
    <RowStyle BackColor="#ffffef" />
    <EditRowStyle BackColor="#e1a66c" />
    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#F5F7FB" />
    <SortedAscendingHeaderStyle BackColor="#6D95E1" />
    <SortedDescendingCellStyle BackColor="#E9EBEF" />
    <SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
</div>
<%--button css--%>
<style type="text/css">
    .button {
        border: Solid 1px;
        Font-weight: bold;
        color: White;
        Height: 25px;
        width: 70px;
        filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#5384BE', endColorstr='#4386D7'); /* for IE */
        -ms-filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#5384BE', endColorstr='#4386D7'); /* for IE 8 and above */
        background: -webkit-gradient(linear, left top, left bottom, from(#5384BE), to(#4386D7)); /* for webkit browsers */
        background: -moz-linear-gradient(top, #5384BE, #4386D7); /* for firefox 3.6+ */
        background: -o-linear-gradient(top, #5384BE, #4386D7); /* for Opera */
    }
</style>
<%--Pager css--%>
<style type="text/css">
    .gvPagercss {
    background-color: #594c2d;
    color: White;
    padding: 2px;
    margin: 2% auto;
    horizontalalign: left;
    }
    .gvPagercss a {
    margin: auto 1%;
    border-radius: 2px;
    background-color: #329bd8 /*#444*/;
    padding: 5px 10px 5px 10px;
    color: #fff;
    text-decoration: none;
    -o-box-shadow: 1px 1px 1px #111;
    -moz-box-shadow: 1px 1px 1px #111;
    -webkit-box-shadow: 1px 1px 1px #111;
    box-shadow: 1px 1px 1px #111;
    }
    .gvPagercss a:hover {
        background-color: #444;
        color: #fff;
    }
    .gvPagercss span {
    background-color: #FFFFCC;
    color: Black;
    -o-box-shadow: 1px 1px 1px #111;
    -moz-box-shadow: 1px 1px 1px #111;
    -webkit-box-shadow: 1px 1px 1px #111;
    box-shadow: 1px 1px 1px #111;
    border-radius: 2px;
    padding: 5px 10px 5px 10px;
    }
</style>
</form>
</body>
</html>

Below is the complete C# Code.

WebForm1.aspx.cs Code:
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 WebApplication1
{
public partial class WebForm1 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindUserMasterGrid();
        }
    }
    private void BindUserMasterGrid()
    {
        using (SampleEntities entity = new SampleEntities())
        {
            if (entity.tbl_UserMaster.Count() > 0)
            {
                gvUserMaster.DataSource = (from u in entity.tbl_UserMaster
                                            select new { u.UserId, u.Name, u.Email, u.ContactNo, u.Gender, u.Country }).ToList();
                gvUserMaster.DataBind();

                /*
                OR
                you can directly bind all columnn
                gvUserMaster.DataSource = entity.tbl_UserMaster.ToList();
                gvUserMaster.DataBind();*/
            }
            else
            {
                ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "err_msg", "alert('No record found!');", true);
                AddEmptyRow();
            }

            // bind country in footer row dropdownlist
            BindCountryOnFooter();
        }
    }
    private void AddEmptyRow()
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        dt.Columns.Add("UserId", typeof(Int32));
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Email", typeof(string));
        dt.Columns.Add("ContactNo", typeof(string));
        dt.Columns.Add("Gender", typeof(string));
        dt.Columns.Add("Country", typeof(string));
        ds.Tables.Add(dt);
        ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
        gvUserMaster.DataSource = ds;
        gvUserMaster.DataBind();
        gvUserMaster.Rows[0].Visible = false;
    }
    private DataTable GetCountryList()
    {
        DataTable dt = new DataTable();

        if (ViewState["Country"] == null)
        {
            using (SampleEntities entity = new SampleEntities())
            {
                dt.Columns.Add("CountryId", typeof(int));
                dt.Columns.Add("Name", typeof(string));
                foreach (var e in entity.tbl_Country)
                {
                    var row = dt.NewRow();
                    row["CountryId"] = e.CountryId;
                    row["Name"] = e.Name;
                    dt.Rows.Add(row);
                }
            }
            //save country in viewstate
            ViewState["Country"] = dt;
        }
        else
        {
            dt = (DataTable)ViewState["Country"];
        }
        return dt;
    }
    private void BindCountryOnFooter()
    {
        DropDownList fddlCountry = gvUserMaster.FooterRow.FindControl("fddlCountry") as DropDownList;
        fddlCountry.DataSource = GetCountryList();
        fddlCountry.DataTextField = "Name";
        fddlCountry.DataValueField = "CountryId";
        fddlCountry.DataBind();
        fddlCountry.Items.Insert(0, "-Select-");
    }
    protected void fbtnAdd_Click(object sender, EventArgs e)
    {
        TextBox ftxtName = gvUserMaster.FooterRow.FindControl("ftxtName") as TextBox;
        TextBox ftxtEmail = gvUserMaster.FooterRow.FindControl("ftxtEmail") as TextBox;
        TextBox ftxtContactNo = gvUserMaster.FooterRow.FindControl("ftxtContactNo") as TextBox;
        DropDownList fddlGender = gvUserMaster.FooterRow.FindControl("fddlGender") as DropDownList;
        DropDownList fddlCountry = gvUserMaster.FooterRow.FindControl("fddlCountry") as DropDownList;

        using (SampleEntities entity = new SampleEntities())
        {
            tbl_UserMaster user = new tbl_UserMaster();
            user.Name = ftxtName.Text.Trim();
            user.Email = ftxtEmail.Text.Trim();
            user.ContactNo = ftxtContactNo.Text.Trim();
            user.Gender = fddlGender.SelectedItem.Value;
            user.Country = fddlCountry.SelectedItem.Text;
            entity.tbl_UserMaster.Add(user);
            entity.SaveChanges();

            ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "err_msg", "alert('Record added successfully!');", true);
        }
        BindUserMasterGrid();
    }
    protected void gvUserMaster_RowEditing(object sender, GridViewEditEventArgs e)
    {
        // find the employee country name before row editing
        // but in real time we should use Country id instead of country name
        Label lblCountry = (Label)gvUserMaster.Rows[e.NewEditIndex].FindControl("lblCountry");

        //find the employee gender before set to edit mode
        Label lblGender = (Label)gvUserMaster.Rows[e.NewEditIndex].FindControl("lblGender");

        //main code while editing
        gvUserMaster.EditIndex = e.NewEditIndex;
        BindUserMasterGrid();
        //main code while editing

        //find the Country DropDownList of EditItemTemplate
        DropDownList eddlCountry = (DropDownList)gvUserMaster.Rows[gvUserMaster.EditIndex].FindControl("eddlCountry");
        // assigning the Country DataTable to DropDownList
        eddlCountry.DataSource = GetCountryList();
        eddlCountry.DataTextField = "Name";
        eddlCountry.DataValueField = "CountryId";
        eddlCountry.DataBind();
        eddlCountry.Items.Insert(0, "-Select-");

        // set the employees country dropdownlist selected as true using findbyText
        // but in real time you should use FindByValue(CountryId)
        if (eddlCountry.Items.FindByText(lblCountry.Text) != null)
        {
            eddlCountry.Items.FindByText(lblCountry.Text).Selected = true;
        }

        DropDownList eddlGender = (DropDownList)gvUserMaster.Rows[gvUserMaster.EditIndex].FindControl("eddlGender");
        if (eddlGender.Items.FindByValue(lblGender.Text) != null)
        {
            eddlGender.Items.FindByValue(lblGender.Text).Selected = true;
        }
    }
    protected void gvUserMaster_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int UserId = Convert.ToInt32(gvUserMaster.DataKeys[e.RowIndex]["UserId"].ToString());

        TextBox etxtName = gvUserMaster.Rows[e.RowIndex].FindControl("etxtName") as TextBox;
        TextBox etxtEmail = gvUserMaster.Rows[e.RowIndex].FindControl("etxtEmail") as TextBox;
        TextBox etxtContactNo = gvUserMaster.Rows[e.RowIndex].FindControl("etxtContactNo") as TextBox;
        DropDownList eddlGender = gvUserMaster.Rows[e.RowIndex].FindControl("eddlGender") as DropDownList;
        DropDownList eddlCountry = gvUserMaster.Rows[e.RowIndex].FindControl("eddlCountry") as DropDownList;

        using (SampleEntities entity = new SampleEntities())
        {
            // using method syntax
            tbl_UserMaster user = entity.tbl_UserMaster.FirstOrDefault(x => x.UserId == UserId);
            if (user != null)
            {
                user.Name = etxtName.Text.Trim();
                user.Email = etxtEmail.Text.Trim();
                user.ContactNo = etxtContactNo.Text.Trim();
                user.Gender = eddlGender.SelectedItem.Value;
                user.Country = eddlCountry.SelectedItem.Text;
                entity.SaveChanges();
            }

            /*
            OR
            you can also use query syntax
            tbl_UserMaster user = (from u in entity.tbl_UserMaster
                                    where u.UserId == UserId
                                    select u).FirstOrDefault();
            entity.SaveChanges();*/

            gvUserMaster.EditIndex = -1;
            ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "err_msg", "alert('Record updated successfully!');", true);
        }
        BindUserMasterGrid();
    }
    protected void gvUserMaster_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvUserMaster.EditIndex = -1;
        BindUserMasterGrid();
    }
    protected void gvUserMaster_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        // fetch UserId from DataKeys
        int UserId = Convert.ToInt32(gvUserMaster.DataKeys[e.RowIndex]["UserId"].ToString());

        using (SampleEntities entity = new SampleEntities())
        {
            // using method syntax
            tbl_UserMaster user = entity.tbl_UserMaster.FirstOrDefault(x => x.UserId == UserId);
            if (user != null)
            {
                entity.tbl_UserMaster.Remove(user);
                entity.SaveChanges();
            }

            /*
            OR
            you can also use query syntax
            tbl_UserMaster user = (from u in entity.tbl_UserMaster
                                    where u.UserId == UserId
                                    select u).FirstOrDefault();
            entity.tbl_UserMaster.Remove(user);
            entity.SaveChanges();*/

            ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "err_msg", "alert('Record deleted successfully!');", true);
        }
        BindUserMasterGrid();
    }
    protected void gvUserMaster_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvUserMaster.PageIndex = e.NewPageIndex;
        BindUserMasterGrid();
        gvUserMaster.EditIndex = -1;
    }
}
}

Now build and run your application.

When page is load, gridview will be shown as below.
When Gridview is loaded
While editing record with validation and maintain previous state of both dropdownlist.
While editing a record
While deleting record.
While deleting record
Showing gridview pager.
Display pager
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON