How to apply CRUD and linq to entity on gridview using ASP.Net 3.5?

Before you start, you need the following software or installer:

Visual Studio 2008 Professional Edition/ Team System Edition

Visual Studio 2008 Service Pack 1

.NET Framework 3.5

.NET Framework 3.5 Service Pack 1

Windows 7 or Windows XP

You need to create a data entry form that will enable you to create an entity or record, retrieve an entity or record, update an existing entity or record, and delete an existing entity or record.

Let us start with creating an entity. First you need to create a multiview control on your designer mode. Then add tables, rows, columns. Create two views, one for creating an entity, the other is for searching or retrieving an entity then perform also updating and deleting an existing entity.

Here is the designer script:

<%@ Page Title=”” Language=”C#” MasterPageFile=”~/DataEntry.Master” AutoEventWireup=”true” CodeBehind=”CityPage.aspx.cs” Inherits=”Web.CityPage” %>

<asp:Content ID=”Content1″ ContentPlaceHolderID=”Content” runat=”server”>

    <asp:ScriptManager ID=”ScriptManager” runat=”server” ></asp:ScriptManager>

    <asp:UpdatePanel ID=”upCity” runat=”server” UpdateMode=”Conditional”>

        <ContentTemplate>

            <table>

                <tr>

                    <td>

                    </td>

                    <td>

                    </td>

                    <td>

                    </td>

                    <td>

                    </td>

                </tr>

                <tr>

                    <td>

                    </td>

                    <td colspan=”2″>

                        <asp:MultiView ID=”mvCity” runat=”server” ActiveViewIndex=”0″>

                            <asp:View ID=”vwAddCity” runat=”server”>

                                <table>

                                    <tr>

                                        <td>

                                            &nbsp;</td>

                                        <td colspan=”2″>

                                            <asp:ValidationSummary ID=”vsCity” runat=”server”

                                                ValidationGroup=”validateCity” />

                                        </td>

                                        <td style=”width: 49px”>

                                            &nbsp;</td>

                                    </tr>

                                    <tr>

                                        <td>

                                        </td>

                                        <td>

                                        </td>

                                        <td style=”width: 316px”>

                                        </td>

                                        <td style=”width: 49px”>

                                        </td>

                                    </tr>

                                    <tr>

                                        <td>

                                        </td>

                                        <td>

                                            CityName:

                                        </td>

                                        <td style=”width: 316px”>

                                            <asp:TextBox ID=”txtCityName” runat=”server” Width=”250px” MaxLength=”50″ ></asp:TextBox>

                                            <asp:RequiredFieldValidator ID=”rfvCityName” runat=”server”

                                                ControlToValidate=”txtCityName” ErrorMessage=”City Name is required.”

                                                ValidationGroup=”validateCity”>*</asp:RequiredFieldValidator>

                                        </td>

                                        <td style=”width: 49px”>

                                        </td>

                                    </tr>

                                    <tr>

                                        <td>

                                        </td>

                                        <td>

                                            CityCode:

                                        </td>

                                        <td style=”width: 316px”>

                                            <asp:TextBox ID=”txtCityCode” runat=”server” Width=”250px” MaxLength=”3″></asp:TextBox>

                                            <asp:RequiredFieldValidator ID=”rfvCityCode” runat=”server”

                                                ControlToValidate=”txtCityCode” ErrorMessage=”City Code is required”

                                                ValidationGroup=”validateCity”>*</asp:RequiredFieldValidator>

                                        </td>

                                        <td style=”width: 49px”>

                                        </td>

                                    </tr>

                                    <tr>

                                        <td>

                                        </td>

                                        <td>

                                            Country: </td>

                                        <td style=”width: 316px”>

                                            <asp:DropDownList ID=”ddlCityCountry” runat=”server” Width=”250px”>

                                            </asp:DropDownList>

                                        </td>

                                        <td style=”width: 49px”>

                                            </td>

                                    </tr>

                                    <tr>

                                        <td>

                                        </td>

                                        <td>

                                            &nbsp;</td>

                                        <td style=”width: 316px”>

                                            <asp:Button ID=”btnSaveCity” runat=”server” onclick=”btnSaveCity_Click”

                                                Text=”Save” ValidationGroup=”validateCity” />

                                            <asp:Button ID=”btnCancelCity” runat=”server” onclick=”btnCancelCity_Click”

                                                Text=”Cancel” />

                                            <asp:Button ID=”btnShowSearchCity” runat=”server”

                                                onclick=”btnShowSearchCity_Click” Text=”Search City” />

                                        </td>

                                        <td style=”width: 49px”>

                                            &nbsp;</td>

                                    </tr>

                                    <tr>

                                        <td>

                                        </td>

                                        <td>

                                        </td>

                                        <td style=”width: 316px”>

                                        </td>

                                        <td style=”width: 49px”>

                                        </td>

                                    </tr>

                                </table>

                            </asp:View>

                            <asp:View ID=”vwSearchCity” runat=”server”>

                                <table>

                                    <tr>

                                        <td>

                                        </td>

                                        <td>

                                            Search City:

                                        </td>

                                        <td>

                                            <asp:TextBox ID=”txtSearchCity” runat=”server” ></asp:TextBox>

                                            <asp:Button ID=”btnSearchCity” runat=”server” onclick=”btnSearchCity_Click”

                                                Text=”Search City” />

                                            <asp:Button ID=”btnAddCity” runat=”server” onclick=”btnAddCity_Click”

                                                Text=”Add City” />

                                        </td>

                                        <td>

                                            &nbsp;</td>

                                    </tr>

                                    <tr>

                                        <td>

                                        </td>

                                        <td colspan=”2″>

                                            <asp:GridView ID=”gvCity” runat=”server” AllowPaging=”True”

                                                AllowSorting=”True” AutoGenerateColumns=”False”

                                                EmptyDataText=”No Records Found”

                                                onpageindexchanging=”gvCity_PageIndexChanging”

                                                onrowcancelingedit=”gvCity_RowCancelingEdit”

                                                onrowdeleting=”gvCity_RowDeleting” onrowediting=”gvCity_RowEditing”

                                                onrowupdating=”gvCity_RowUpdating” onsorting=”gvCity_Sorting”>

                                                <Columns>

                                                    <asp:BoundField DataField=”CityID” HeaderText=”CityID” ReadOnly=”True”

                                                        SortExpression=”CityID” Visible=”False” />

                                                    <asp:TemplateField HeaderText=”City” SortExpression=”CityName”>

                                                        <EditItemTemplate>

                                                            <asp:TextBox ID=”txtGridCityName” runat=”server” Text='<%# Bind(“CityName”) %>’ MaxLength=”50″></asp:TextBox>

                                                        </EditItemTemplate>

                                                        <ItemTemplate>

                                                            <asp:Label ID=”lblGridCityName” runat=”server” Text='<%# Bind(“CityName”) %>’></asp:Label>

                                                            <asp:TextBox ID=”txtGridCityName” runat=”server” Text='<%# Bind(“CityName”) %>’ MaxLength=”50″ Visible=”false”></asp:TextBox>

                                                        </ItemTemplate>

                                                    </asp:TemplateField>

                                                    <asp:TemplateField HeaderText=”City Code” SortExpression=”CityCode”>

                                                        <EditItemTemplate>

                                                            <asp:TextBox ID=”txtGridCityCode” runat=”server” Text='<%# Bind(“CityCode”) %>’ MaxLength=”3″></asp:TextBox>

                                                        </EditItemTemplate>

                                                        <ItemTemplate>

                                                            <asp:Label ID=”lblGridCityCode” runat=”server” Text='<%# Bind(“CityCode”) %>’></asp:Label>

                                                            <asp:TextBox ID=”txtGridCityCode” runat=”server” Text='<%# Bind(“CityCode”) %>’ MaxLength=”3″ Visible=”false”></asp:TextBox>

                                                        </ItemTemplate>

                                                    </asp:TemplateField>

                                                    <asp:TemplateField HeaderText=”Country ID” SortExpression=”CountryID”

                                                        Visible=”False”>

                                                        <EditItemTemplate>

                                                            <asp:TextBox ID=”txtGridCountryID” runat=”server” Text='<%# Eval(“CountryID”) %>’></asp:TextBox>

                                                        </EditItemTemplate>

                                                        <ItemTemplate>

                                                            <asp:Label ID=”lblGridCountryID” runat=”server” Text='<%# Bind(“CountryID”) %>’></asp:Label>

                                                        </ItemTemplate>

                                                    </asp:TemplateField>

                                                    <asp:TemplateField HeaderText=”Country” SortExpression=”CountryName”>

                                                        <EditItemTemplate>

                                                            <asp:DropDownList ID=”ddlGridCountryName” runat=”server”  ></asp:DropDownList>

                                                        </EditItemTemplate>

                                                        <ItemTemplate>

                                                            <asp:Label ID=”lblGridCountryName” runat=”server” Text='<%# Bind(“CountryName”) %>’></asp:Label>

                                                            <asp:DropDownList ID=”ddlGridCountryName” runat=”server”  Visible=”false” ></asp:DropDownList>

                                                        </ItemTemplate>

                                                    </asp:TemplateField>

                                                    <asp:TemplateField ShowHeader=”False”>

                                                        <EditItemTemplate>

                                                            <asp:Button ID=”btnGridUpdateCity” runat=”server” CausesValidation=”True”

                                                                CommandName=”Update” Text=”Update” />

                                                            &nbsp;<asp:Button ID=”btnGridCancelCity” runat=”server” CausesValidation=”False”

                                                                CommandName=”Cancel” Text=”Cancel” />

                                                        </EditItemTemplate>

                                                        <ItemTemplate>

                                                            <asp:Button ID=”btnGridEditCity” runat=”server” CausesValidation=”False”

                                                                CommandName=”Edit” Text=”Edit” />

                                                        </ItemTemplate>

                                                    </asp:TemplateField>

                                                    <asp:TemplateField ShowHeader=”False”>

                                                        <ItemTemplate>

                                                            <asp:Button ID=”btnGridDeleteCity” runat=”server” CausesValidation=”False”

                                                                CommandName=”Delete” Text=”Delete” />

                                                            <asp:HiddenField ID=”hfCityID” runat=”server” Value='<%# Bind(“CityID”) %>’ />

                                                        </ItemTemplate>

                                                    </asp:TemplateField>

                                                </Columns>

                                            </asp:GridView>

                                        </td>

                                        <td>

                                        </td>

                                    </tr>

                                    <tr>

                                        <td>

                                        </td>

                                        <td>

                                        </td>

                                        <td>

                                        </td>

                                        <td>

                                        </td>

                                    </tr>

                                </table>

                            </asp:View>

                        </asp:MultiView>

                    </td>

                    <td>

                    </td>

                </tr>

                <tr>

                    <td>

                    </td>

                    <td>

                    </td>

                    <td>

                    </td>

                    <td>

                    </td>

                </tr>

            </table>           

        </ContentTemplate>

        <Triggers>

        </Triggers>

    </asp:UpdatePanel>

</asp:Content>

Here is the source code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using DAL;

using System.Configuration;

namespace Web{

    public partial class CityPage : System.Web.UI.Page

    {

        #region “Variables”

        Cities city;

        Countries country;

        IOrderedQueryable cityCountry;

        List<Cities> cityList;

        List<Countries> countryList;

        CitiesBLL cityBll;

        CountriesBLL countryBll;

        CitiesCountriesBLL cityCountryBll;

        Entity dbEntity;

        string connString = ConfigurationManager.ConnectionStrings[“Entity”].ConnectionString;

        #endregion

        #region “Methods”

        private void MessageBox(string message)

        {

            ScriptManager.RegisterStartupScript(this, this.GetType(), “onclick”, “alert(‘” + message + “‘);”, true);

        }

        private void ResetEntry()

        {

            this.txtCityCode.Text = string.Empty;

            this.txtCityName.Text = string.Empty;

            this.txtSearchCity.Text = string.Empty;

            this.ddlCityCountry.SelectedIndex = -1;

            this.gvCity.DataSource = null;

            this.gvCity.DataBind();

        }

        private void PopulateCountryList()

        {

            try

            {

                countryList = countryBll.RetrieveCountryList();

                this.ddlCityCountry.DataSource = countryList;

                this.ddlCityCountry.DataTextField = “CountryName”;

                this.ddlCityCountry.DataValueField = “CountryID”;

                this.ddlCityCountry.DataBind();

            }

            catch (System.Exception ex)

            {

                MessageBox(ex.Message + ” ” + ex.StackTrace);

            }

        }

        private void PopulateGridView()

        {

            try

            {

                cityCountry = cityCountryBll.RetrieveCityCountryByName(this.txtSearchCity.Text.Trim(), Convert.ToString(Session[“SortExpression”]), Convert.ToString(Session[“SortDirection”]));

                this.gvCity.DataSource = cityCountry;

                this.gvCity.DataBind();

            }

            catch (System.Exception ex)

            {

                MessageBox(ex.Message + ” ” + ex.StackTrace);

            }

        }

        private void PopulateGridCountryList(DropDownList countryDDL, string id)

        {

            try

            {

                countryList = countryBll.RetrieveCountryList();

                countryDDL.DataSource = countryList;

                countryDDL.DataTextField = “CountryName”;

                countryDDL.DataValueField = “CountryID”;

                countryDDL.DataBind();

                countryDDL.SelectedValue = id;

            }

            catch (System.Exception ex)

            {

                MessageBox(ex.Message + ” ” + ex.StackTrace);

            }

        }

        #endregion

        #region “Events”

        protected void Page_Init(object sender, EventArgs e)

        {

            city = new Cities();

            country = new Countries();

            cityCountry = null;

            cityList = new List<Cities>();

            countryList = new List<Countries>();

            cityBll = new CitiesBLL();

            countryBll = new CountriesBLL();

            cityCountryBll = new CitiesCountriesBLL();

            dbEntity = new Entity(connString);

        }

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!this.Page.IsPostBack)

            {

                PopulateCountryList();

            }

        }

        protected void btnSaveCity_Click(object sender, EventArgs e)

        {

            try

            {

                city = new Cities();

                city.CityCode = this.txtCityCode.Text.Trim();

                city.CityName = this.txtCityName.Text.Trim();

                city.CountryID = Convert.ToInt32(this.ddlCityCountry.SelectedValue);

                if (cityBll.CheckForDuplicateCity(city, false) == false)

                {

                    int result = cityBll.InsertCity(city);

                    if (result > 0)

                        MessageBox(“Successfully add new city, ” + city.CityName);

                    else

                        MessageBox(“Failed to add new city, ” + city.CityName);

                }

                else

                    MessageBox(“Duplicate city name or code, ” + city.CityName);

            }

            catch (System.Exception ex)

            {

                MessageBox(ex.Message + ” ” + ex.StackTrace);

            }

        }

        protected void btnCancelCity_Click(object sender, EventArgs e)

        {

            ResetEntry();

        }

        protected void btnShowSearchCity_Click(object sender, EventArgs e)

        {

            this.mvCity.ActiveViewIndex = 1;

        }

        protected void btnSearchCity_Click(object sender, EventArgs e)

        {

            try

            {

                Session[“SortDirection”] = “ascending”;

                Session[“SortExpression”] = “CityName”;

                PopulateGridView();

            }

            catch (System.Exception ex)

            {

                MessageBox(ex.Message + ” ” + ex.StackTrace);

            }

        }

        protected void btnAddCity_Click(object sender, EventArgs e)

        {

            this.mvCity.ActiveViewIndex = 0;

        }

        protected void gvCity_PageIndexChanging(object sender, GridViewPageEventArgs e)

        {

            this.gvCity.PageIndex = e.NewPageIndex;

            PopulateGridView();

        }

        protected void gvCity_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

        {

            this.gvCity.EditIndex = -1;

            PopulateGridView();

        }

        protected void gvCity_RowDeleting(object sender, GridViewDeleteEventArgs e)

        {

            try

            {

                HiddenField hf = (HiddenField)this.gvCity.Rows[e.RowIndex].Cells[0].FindControl(“hfCityID”);

                int result = cityBll.DeleteCity(Convert.ToInt32(hf.Value));

                if (result > 0)

                    MessageBox(“Successfully delete this city.”);

                else

                    MessageBox(“Failed to delete this city.”);

                PopulateGridView();

            }

            catch (System.Exception ex)

            {

                MessageBox(ex.Message + ” ” + ex.StackTrace);

            }

        }

        protected void gvCity_RowEditing(object sender, GridViewEditEventArgs e)

        {

            try

            {

                this.gvCity.EditIndex = e.NewEditIndex;

                PopulateGridView();

                string countryID = ((TextBox)this.gvCity.Rows[e.NewEditIndex].Cells[3].FindControl(“txtGridCountryID”)).Text;

                DropDownList countryDDL = (DropDownList)this.gvCity.Rows[e.NewEditIndex].Cells[4].FindControl(“ddlGridCountryName”);

                PopulateGridCountryList(countryDDL, countryID);

            }

            catch (System.Exception ex)

            {

                MessageBox(ex.Message + ” ” + ex.StackTrace);

            }

        }

        protected void gvCity_RowUpdating(object sender, GridViewUpdateEventArgs e)

        {

            try

            {

                TextBox name = (TextBox)this.gvCity.Rows[e.RowIndex].Cells[1].FindControl(“txtGridCityName”);

                TextBox code = (TextBox)this.gvCity.Rows[e.RowIndex].Cells[2].FindControl(“txtGridCityCode”);

                DropDownList country = (DropDownList)this.gvCity.Rows[e.RowIndex].Cells[4].FindControl(“ddlGridCountryName”);

                HiddenField hf = (HiddenField)this.gvCity.Rows[e.RowIndex].Cells[6].FindControl(“hfCityID”);

                city.CityCode = code.Text.Trim();

                city.CityID = Convert.ToInt32(hf.Value);

                city.CityName = name.Text.Trim();

                city.CountryID = Convert.ToInt32(country.SelectedValue);

                if (cityBll.CheckForDuplicateCity(city, true) == false)

                {

                    int result = cityBll.UpdateCity(city);

                    if (result > 0)

                        MessageBox(“Successfully update this city, ” + city.CityName);

                    else

                        MessageBox(“Failed to update this city, ” + city.CityName);

                    this.gvCity.EditIndex = -1;

                    PopulateGridView();

                }

                else

                    MessageBox(“Duplicate city Code or name, ” + city.CityName);

            }

            catch (System.Exception ex)

            {

                MessageBox(ex.Message + ” ” + ex.StackTrace);

            }

        }

        protected void gvCity_Sorting(object sender, GridViewSortEventArgs e)

        {

            try

            {

                if (Convert.ToString(Session[“SortDirection”]) == “ascending”)

                {

                    e.SortDirection = SortDirection.Descending;

                    Session[“SortDirection”] = “descending”;

                }

                else

                {

                    e.SortDirection = SortDirection.Ascending;

                    Session[“SortDirection”] = “ascending”;

                }

                Session[“SortExpression”] = e.SortExpression;

                PopulateGridView();

            }

            catch (System.Exception ex)

            {

                MessageBox(ex.Message + ” ” + ex.StackTrace);

            }

        }

        #endregion

    }

}

Advertisements
This entry was posted in ASP.NET 3.5, ASP.NET 3.5 And C# 3.5, C# 3.5, C# 3.5 and ADO.NET Entity Framework 3.5, Linq to Entity. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s