Microsoft Technology: Insert, Update, Delete in ASP.NET Gridview, DataSource as SQL Server, MS Access (mdb/accdb), XML and Framework as 2.0 / 3.0 / 3.5 / 4.0 (VS 2005/2008/2010)

Introduction

GridView is derived from WebControl class.It is used to display data in tabular format, This control is inbuilt with many features like paging, sorting, editing etc.Its better to know the CRUD functionality with Gridview Control.Here I'm going to explain Insert, Update, Delete functionalities with Gridview using DataSource as SQL Server, MS Access and XML. For your reference I have attached solutions for Visual Studio 2005, Visual Studio 2008 and Visual Studio 2010. I hope this article will help you to understand the CRUD functionality with GridView.

Using the Code

I have created a new ASP.NET Web Application in Visual Studio (2005/2008/2010). The project is named as EditableGridView. The below given steps will help you to understand the components that I have created for this project.

Created MS Access Database and placed under App_Data folder.
Created XML file and placed under XML Folder
Created SQL Server Database and Copied the Database Script to DatabaseScript folder
Connection String added in web.config
CSS file for Gridview under CSS folder
Class files created in egClass folder
egMain.html created as startup page
Different ASPX files created to handle different data sources CRUD operations (SQL,XML and Access)
Once this all done we are ready to see it in action......

Solution Explorer screenshot is given below...

Setup Data Sources

To run this project you need to make sure that the datasources are configured correctly, Here I will explain the steps for configuring SQL, Access and XML datasources for EditableGridView project.

SQL

Create database named "EditableGridView" in SQL Server (any version), use the below given script to create the database with preloaded data in Employee table, you can find this script under Datascript folder.

USE [master]
GO
 
/****** Object:  Database [EditableGridView] Script Date: 07/05/2012 01:10:26 ***/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'EditableGridView')
DROP DATABASE [EditableGridView]
GO
 
 
/****** Object:  Database [EditableGridView] Script Date: 07/05/2012 01:10:26 ***/
CREATE DATABASE [EditableGridView]
GO
 
USE [EditableGridView]
GO
 
/****** Object:  Table [dbo].[Employee] Script Date: 07/05/2012 01:12:10 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Employee](
 [ID] [bigint] IDENTITY(1,1) NOT NULL,
 [EmployeeCode] [varchar](max) NOT NULL,
 [EmployeeName] [varchar](max) NOT NULL,
 [DepartmentId] [int] NOT NULL,
 [DepartmentName] [varchar](max) NOT NULL,
 [EmployeeGroup] [varchar](max) NOT NULL,
 [Email] [varchar](max) NOT NULL,
 [isActive] [bit] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
 /****** Object:  Employee    Script Date: 7/5/2012 1:26:50 AM ******/
SET NOCOUNT ON
INSERT INTO [Employee] ([EmployeeCode], [EmployeeName], [DepartmentId],

[DepartmentName], [EmployeeGroup], [Email], [isActive])
VALUES ('113561', 'Shemeer', 3, 'IT', 'Admin','shemeer@xyz.com', 1)
INSERT INTO [Employee] ([EmployeeCode], [EmployeeName], [DepartmentId],

[DepartmentName], [EmployeeGroup], [Email], [isActive])
VALUES ('763423', 'Jacob', 2, 'Marketinng', 'User','jacob@xyz.com', 1)
INSERT INTO [Employee] ([EmployeeCode], [EmployeeName], [DepartmentId],

 [DepartmentName], [EmployeeGroup], [Email], [isActive])
VALUES ('986766', 'Abhilash', 1,'Sales', 'User','abhi@xyz.com', 0)
INSERT INTO [Employee] ([EmployeeCode], [EmployeeName], [DepartmentId],

 [DepartmentName], [EmployeeGroup], [Email], [isActive])
VALUES ('864391', 'Aydin', 3, 'IT', 'Super User','aydin@xyz.com', 1)
INSERT INTO [Employee] ([EmployeeCode], [EmployeeName], [DepartmentId],

 [DepartmentName], [EmployeeGroup], [Email], [isActive])
VALUES ('233189', 'Dave', 1, 'IT', 'Admin','dave@xyz.com', 1)

Make sure to change the databse server and credential details in connection string. If you are changing the database name then accordingly update the database name in <connectionStrings>

Access

Access database file is placed in App_Data folder of this project. The already attached mdb file is compatible with Access 2002 and above. If you are changing the mdb/accdb file location then accordingly update the connection string also.

XML

The XML file is placed under the XML folder, In case if you want to change the location the corresponding path needs to be updated in the web.config.

Editable GridView CSS

GridView rendered as <HTML> Tables, So any CSS design that can be used for a <TABLE> also can be used for a GridView. You can change the look and feel of a GridView by applying CSS. CSS can be applied to the Gridview as well as to the HeaderRow, FooterRow, Row, AlternateRow and Pager Style etc. Instead of adding the style formatting directly to the GridView, we can just apply the CSS. The below given CSS file contains CSS for GridView, Header-row, Alternate-row, Row, FooterRow, EmptyDataTemplate Controls, Highlighting Rows etc. You can easily customize this grid view by modifying the Main.css. Main.css can be found in CSS folder of this project.

.grid
{
    font-family: tahoma;
    font-size: 12px;
    border: solid 1px #7f7f7f;
    border-collapse: collapse;
    color: #333333;
    width: 100%;
}
.grid th
{
    border-color: #989898 #cbcbcb #989898 #989898;
    border-style: solid solid solid none;
    border-width: 1px 1px 1px 1px;
    color: Menu;
    padding: 4px 5px 4px 10px;
    vertical-align: bottom;
    text-align: left;
    background-color: Highlight;
}
.grid td
{
    color: #333333;
    padding: 4px 1px 4px 1px;
    border-bottom: solid 1px #BBD9EE;
    padding: 4px 5px 4px 10px;
}
.gridRow
{
    background-color: #B1DEDC;
}

.gridAltRow
{
    background-color: #D1EDE9;
}

.gridEditRow
{
    background-color: #0DC6DE;
}
.gridFooterRow
{
    background-color: #E8ECED;
}
.grid tr.gridRow:hover, .grid tr.gridAltRow:hover
{
    font-family: tahoma;
    font-size: 12px;
    border: solid 1px #7f7f7f;
    border-collapse: collapse;
    background-color: #99BBE1;
}

.hCursor
{
    cursor: pointer;
    cursor: hand;
}

The contents of the above CSS file are
.grid -  this style applies for the whole gridview.
.grid th -  this applies only for the <th> elements, or normally header element
.grid td - this style applies for the <td> elements, cells in the gridview.
.gridRow - this applies for the <tr> elements, gridview row
.gridAltRow - this css is used for applying style to alternate rows in gridview
.gridEditRow - this style is applied only for the row which is in edit mode
.gridFooterRow - this style is used for the FooterRow of the grid where we are doing insertion
.grid tr.gridRow:hover, .grid tr.gridAltRow:hover - this is used for highlighting the grid view row.
.hCursor -  this style is used for setting hand cursor in an entire row.

Gridview Validation

It's very important to validate the user input in any data entry forms. ASP.NET is rich with validation controls and that can be used inside a GridView also. We can use any scripting lauanguage also for validating user inputs. Its important to mention the Validation Group while using ASP.NET Validation controls and make sure to set CausesValidation=false if that controlneed not perform any validation ( eg:- Cancel Button), Here I'm giving a validation sample for GridView .

<FooterTemplate>
    <asp:TextBox ID="txtEmployeeCode" runat="server" MaxLength="6"

Width="50px"></asp:TextBox>
    <asp:RequiredFieldValidator ID="rfvEmployeeCode" ValidationGroup="Insert"

runat="server" ControlToValidate="txtEmployeeCode"

ErrorMessage="Please Enter Employee Code" ToolTip="Please Enter Employee Code"

SetFocusOnError="true" ForeColor="Red">*</asp:RequiredFieldValidator>
    <asp:RegularExpressionValidator ID="reEmployeeCode" runat="server"

ControlToValidate="txtEmployeeCode" ErrorMessage="Please Enter Only Numbers in Employee Code"

ToolTip="Please Enter Only Numbers" SetFocusOnError="true" ForeColor="Red"

ValidationExpression="^\d+$" ValidationGroup="Insert">*</asp:RegularExpressionValidator>
</FooterTemplate>

In the above code I have added <RequiredFieldValidator> and <RegularExpressionValidator>, Employee code is a mandatory field and only number is allowed in this filed that's why I have added the above is validators.

Connection String and XML path

I have kept the SQL Server and Acces connection string in web.config's <connectionStrings> and XML file path in <appSettings>
Connection Strings

  <connectionStrings>
    <add name="egSQLConString" 
     connectionString="server=SHEMEER-PC\SQLEXPRESS2008;database=EditableGridView;

uid=sa;pwd=pass;" providerName="System.Data.SqlClient"/>
    <add name="egACCESSConString_2002_2003" 
     connectionString="Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=|DataDirectory|EditableGridView_Format2002_2003.mdb"

providerName="System.Data.OleDb"/>
    <!--<add name="egACCESSConString_2007ANDABOVE" 
    connectionString="Provider=Microsoft.ACE.OLEDB.12.0;

Data Source=|DataDirectory|EditableGridView.accdb" 
    providerName="System.Data.OleDb"/>-->
  </connectionStrings>

|DataDirectory| (enclosed in pipe symbols) is a substitution string that indicates the path to the database. If you are using MS Access database 2007 and above change the provider in connectionString. Please check the commented line above.
XML Path

  <appSettings> 
    <add key="xmlPath" value="XML/Employee.xml"/>
  </appSettings>

<appSettings> is the best place to keep the application configuration settings.

GridView HTML

GridView HTML is given below, this code is same for all pages in this project.

<asp:GridView ID="gvEG" runat="server" AutoGenerateColumns="False" CssClass="grid"
    AlternatingRowStyle-CssClass="gridAltRow" RowStyle-CssClass="gridRow"

ShowFooter="True" EditRowStyle-CssClass="gridEditRow"

FooterStyle-CssClass="gridFooterRow" 
    OnRowCancelingEdit="gvEG_RowCancelingEdit"
    OnRowCommand="gvEG_RowCommand" OnRowDataBound="gvEG_RowDataBound" OnRowDeleting="gvEG_RowDeleting"
    OnRowEditing="gvEG_RowEditing" OnRowUpdating="gvEG_RowUpdating" DataKeyNames="ID,DepartmentId">
    <Columns>
        <asp:TemplateField HeaderText="Employee Code" HeaderStyle-HorizontalAlign="Left"
            ControlStyle-Width="50px">
            <EditItemTemplate>
                <asp:TextBox ID="txtEmployeeCode" runat="server" Text='<%# Bind("EmployeeCode") %>'
                    MaxLength="6" Width="50px"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvEmployeeCode" ValidationGroup="Update" runat="server"
                    ControlToValidate="txtEmployeeCode" ErrorMessage="Please Enter Employee Code"
                    ToolTip="Please Enter Employee Code" SetFocusOnError="true" 
                    ForeColor="Red">*</asp:RequiredFieldValidator>
                <asp:RegularExpressionValidator ID="reEmployeeCode" runat="server" 
                ControlToValidate="txtEmployeeCode"
                    ErrorMessage="Please Enter Only Numbers in Employee Code" 
                    ToolTip="Please Enter Only Numbers"
                    SetFocusOnError="true" ForeColor="Red" ValidationExpression="^\d+$"
                        ValidationGroup="Update">*</asp:RegularExpressionValidator>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtEmployeeCode" runat="server" MaxLength="6" Width="50px"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvEmployeeCode" ValidationGroup="Insert" runat="server"
                    ControlToValidate="txtEmployeeCode" ErrorMessage="Please Enter Employee Code"
                    ToolTip="Please Enter Employee Code" SetFocusOnError="true" 
                    ForeColor="Red">*</asp:RequiredFieldValidator>
                <asp:RegularExpressionValidator ID="reEmployeeCode" runat="server" 
                ControlToValidate="txtEmployeeCode"
                    ErrorMessage="Please Enter Only Numbers in Employee Code" 
                    ToolTip="Please Enter Only Numbers"
                    SetFocusOnError="true" ForeColor="Red" ValidationExpression="^\d+$" 
                    ValidationGroup="Insert">*</asp:RegularExpressionValidator>
            </FooterTemplate>
            <ItemTemplate>
                <%# Eval("EmployeeCode")%>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Name" HeaderStyle-HorizontalAlign="Left" ControlStyle-Width="90px">
            <EditItemTemplate>
                <asp:TextBox ID="txtEmployeeName" runat="server" Text='<%# Bind("EmployeeName") %>'
                    Width="90px"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvEmployeeName" ValidationGroup="Update" runat="server"
                    ControlToValidate="txtEmployeeName" ErrorMessage="Please Enter Name" 
                    ToolTip="Please Enter Name"
                    SetFocusOnError="true" ForeColor="Red">*</asp:RequiredFieldValidator>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtEmployeeName" runat="server" Width="90px"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvEmployeeName" ValidationGroup="Insert" runat="server"
                    ControlToValidate="txtEmployeeName" ErrorMessage="Please Enter Name" 
                    ToolTip="Please Enter Name"
                    SetFocusOnError="true" ForeColor="Red">*</asp:RequiredFieldValidator>
            </FooterTemplate>
            <ItemTemplate>
                <%# Eval("EmployeeName") %>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Department" HeaderStyle-HorizontalAlign="Left">
            <EditItemTemplate>
                <asp:DropDownList ID="ddlDepartment" runat="server" DataTextField="Name" DataValueField="Id">
                </asp:DropDownList>
            </EditItemTemplate>
            <ItemTemplate>
                <%# Eval("DepartmentName")%>
            </ItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="ddlDepartment" runat="server" DataTextField="Name" DataValueField="Id">
                </asp:DropDownList>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Group" HeaderStyle-HorizontalAlign="Left">
            <EditItemTemplate>
                <asp:DropDownList ID="ddlEmployeeGroup" runat="server" 
                SelectedValue='<%# Eval("EmployeeGroup") %>'>
                    <asp:ListItem Text="User" Value="User"></asp:ListItem>
                    <asp:ListItem Text="Admin" Value="Admin"></asp:ListItem>
                    <asp:ListItem Text="Super User" Value="Super User"></asp:ListItem>
                </asp:DropDownList>
            </EditItemTemplate>
            <ItemTemplate>
                <%# Eval("EmployeeGroup")%>
            </ItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="ddlEmployeeGroup" runat="server">
                    <asp:ListItem Text="User" Value="User" Selected="True"></asp:ListItem>
                    <asp:ListItem Text="Admin" Value="Admin"></asp:ListItem>
                    <asp:ListItem Text="Super User" Value="Super User"></asp:ListItem>
                </asp:DropDownList>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Email" HeaderStyle-HorizontalAlign="Left" ControlStyle-Width="100px">
            <ItemTemplate>
                <%# Eval("Email")%>
            </ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtEmail" runat="server" Width="100px" />
                <asp:RequiredFieldValidator ID="rfvEmail" ValidationGroup="Insert" runat="server"
                    ControlToValidate="txtEmail" ErrorMessage="Please Enter Email" ToolTip="Please Enter Email"
                    SetFocusOnError="true" ForeColor="Red">*</asp:RequiredFieldValidator>
                <asp:RegularExpressionValidator ID="reEmail" runat="server" ControlToValidate="txtEmail"
                    ErrorMessage="Please Enter a Valid Email" ToolTip="Please Enter a Valid Email"
                    SetFocusOnError="true" ForeColor="Red" 
                    ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
                    ValidationGroup="Insert">*</asp:RegularExpressionValidator>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Active">
            <EditItemTemplate>
                <asp:CheckBox ID="chkActive" runat="server" Checked='<%# Eval("isActive") %>' />
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="lblActive" runat="server" Text='<%# Eval("isActive") %>'></asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:CheckBox ID="chkActive" runat="server" />
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Edit" ShowHeader="False" HeaderStyle-HorizontalAlign="Left">
            <EditItemTemplate>
                <asp:LinkButton ID="lnkUpdate" runat="server" CausesValidation="True" CommandName="Update"
                    Text="Update" OnClientClick="return confirm('Update?')" 
                    ValidationGroup="Update"></asp:LinkButton>
                <asp:ValidationSummary ID="vsUpdate" runat="server" ShowMessageBox="true" ShowSummary="false"
                    ValidationGroup="Update" Enabled="true" HeaderText="Validation Summary..." />
                <asp:LinkButton ID="lnkCancel" runat="server" CausesValidation="False" CommandName="Cancel"
                    Text="Cancel"></asp:LinkButton>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:LinkButton ID="lnkAdd" runat="server" CausesValidation="True" CommandName="Insert"
                    ValidationGroup="Insert" Text="Insert"></asp:LinkButton>
                <asp:ValidationSummary ID="vsInsert" runat="server" ShowMessageBox="true" ShowSummary="false"
                    ValidationGroup="Insert" Enabled="true" HeaderText="Validation..." />
            </FooterTemplate>
            <ItemTemplate>
                <asp:LinkButton ID="lnkEdit" runat="server" CausesValidation="False" CommandName="Edit"
                    Text="Edit"></asp:LinkButton>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Delete" ShowHeader="False" HeaderStyle-HorizontalAlign="Left">
            <ItemTemplate>
                <asp:LinkButton ID="lnkDelete" runat="server" CausesValidation="False" CommandName="Delete"
                    Text="Delete" OnClientClick="return confirm('Delete?')"></asp:LinkButton>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" />
        </asp:TemplateField>
    </Columns>
    <EmptyDataTemplate>
        <table class="grid" cellspacing="0" rules="all" border="1" id="gvEG" style="border-collapse: collapse;">
            <tr>
                <th align="left" scope="col">
                    Employee Code
                </th>
                <th align="left" scope="col">
                    Name
                </th>
                <th align="left" scope="col">
                    Department
                </th>
                <th align="left" scope="col">
                    Group
                </th>
                <th align="left" scope="col">
                    Email
                </th>
                <th scope="col">
                    Ative
                </th>
                <th align="left" scope="col">
                    Edit
                </th>
                <th scope="col">
                    Delete
                </th>
            </tr>
            <tr class="gridRow">
                <td colspan="8">
                    No Records found...
                </td>
            </tr>
            <tr class="gridFooterRow">
                <td>
                    <asp:TextBox ID="txtEmployeeCode" runat="server" MaxLength="6"></asp:TextBox>
                </td>
                <td>
                    <asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:DropDownList ID="ddlDepartment" runat="server" DataTextField="Name" 
                    DataValueField="Id"></asp:DropDownList>
                </td>
                <td>
                    <asp:DropDownList ID="ddlEmployeeGroup" runat="server">
                        <asp:ListItem Text="User" Value="User" Selected="True"></asp:ListItem>
                        <asp:ListItem Text="Admin" Value="Admin"></asp:ListItem>
                        <asp:ListItem Text="Super User" Value="Super User"></asp:ListItem>
                    </asp:DropDownList>
                </td>
                <td>
                    <asp:TextBox ID="txtEmail" runat="server" />
                </td>
                <td>
                    <asp:CheckBox ID="chkActive" runat="server" />
                </td>
                <td colspan="2" align="justify" valign="middle">
                    <asp:LinkButton ID="lnkAdd" runat="server" CausesValidation="false" 
                    CommandName="emptyInsert" Text="emptyInsert"></asp:LinkButton>
                </td>
            </tr>
        </table>
    </EmptyDataTemplate>
</asp:GridView>

Code-Behind

In this article, for ease of understanding I have kept all webforms design and code behind same. whereas the class used for differenet datasource operations will be different according to the page, if its sql page then the class object will be used egSQL class, you can find below listed pages in this project, the file name itself says the purpose of the page.

Here I'm going to explain the code behind of egSQL page.
Fill Data to Employee Grid
This method is used to bind the GridView. Here I'm creating a List<EmployeeInfo> to hold the records that returned from the data-source. and binding the EmployeeInfo List object to the GridView.

private void FillEmployeeGrid()
{
    List<EmployeeInfo> objEmpList = new List<EmployeeInfo>();
    objEmpList = new mainSQL().getEmployeeList();
 
    gvEG.DataSource = objEmpList;
    gvEG.DataBind();
}

RowDataBound
In this event I'm binding the drop-downs in the gridview's editing row and footer row. In editting row i'm setting the selected value from the DataKey that I have mentioned in the Gridview.

protected void gvEG_RowDataBound(object sender, GridViewRowEventArgs e)
{
 
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        DropDownList ddlDepartment = (DropDownList)e.Row.FindControl("ddlDepartment");
        if (ddlDepartment != null)
        {
            ddlDepartment.DataSource = new mainSQL().getDepartmentList();
            ddlDepartment.DataBind();
            ddlDepartment.SelectedValue = gvEG.DataKeys[e.Row.RowIndex].Values[1].ToString();
        }
    }
    if (e.Row.RowType == DataControlRowType.EmptyDataRow)
    {
        DropDownList ddlDepartment = (DropDownList)e.Row.FindControl("ddlDepartment");
        if (ddlDepartment != null)
        {
            ddlDepartment.DataSource = new mainSQL().getDepartmentList();
            ddlDepartment.DataBind();
        }
    }
    else if (e.Row.RowType == DataControlRowType.Footer)
    {
        DropDownList ddlDepartment = (DropDownList)e.Row.FindControl("ddlDepartment");
        ddlDepartment.DataSource = new mainSQL().getDepartmentList(); ;
        ddlDepartment.DataBind();
    }
}

Insert
In RowCommand method I'm inserting a new employee information to the database/xml. Here I'm reading all the controls value and assigning to a EmployeeInfo object and then passing this object to the class method. I have added a template for adding a new employee in <EmptyDataTemplate>, handling the new insert from <EmptyDataTemplate> is left for the reader.

protected void gvEG_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName.Equals("Insert"))
    {
        EmployeeInfo eInfo = new EmployeeInfo();
        eInfo.EmployeeCode = Convert.ToString(((TextBox)gvEG.FooterRow.FindControl("txtEmployeeCode")).Text);
        eInfo.EmployeeName = ((TextBox)gvEG.FooterRow.FindControl("txtEmployeeName")).Text;
        eInfo.DepartmentId = Convert.ToInt32(((DropDownList)gvEG.FooterRow.FindControl("ddlDepartment")).SelectedValue);
        eInfo.DepartmentName = Convert.ToString(((DropDownList)gvEG.FooterRow.FindControl("ddlDepartment")).SelectedItem.Text);
        eInfo.EmployeeGroup = ((DropDownList)gvEG.FooterRow.FindControl("ddlEmployeeGroup")).SelectedValue;
        eInfo.Email = ((TextBox)gvEG.FooterRow.FindControl("txtEmail")).Text;
        eInfo.isActive = ((CheckBox)gvEG.FooterRow.FindControl("chkActive")).Checked;
        new mainSQL().insertEmployeeInfo(eInfo);
        FillEmployeeGrid();
    }
}

Edit
Here by setting edit row index for the gridview. we are enabling editing for that row.

protected void gvEG_RowEditing(object sender, GridViewEditEventArgs e)
{
    gvEG.EditIndex = e.NewEditIndex;
    FillEmployeeGrid();
}

Cancel Edit
By changing edit row index to -1 we can reset the editing in the GridView row.

protected void gvEG_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    gvEG.EditIndex = -1;
    FillEmployeeGrid();
}

Update
Updating the Employee information are done in this method. Here I'm assigning the value from the editing row to a EmployeeInfo object and passing this to update method of the class object. and also resetting the edit row index to -1 .

protected void gvEG_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    EmployeeInfo eInfo = new EmployeeInfo();
    eInfo.ID = Convert.ToInt64(gvEG.DataKeys[e.RowIndex].Values[0].ToString());
    eInfo.EmployeeCode = Convert.ToString(((TextBox)gvEG.Rows[e.RowIndex].FindControl("txtEmployeeCode")).Text);
    eInfo.EmployeeName = ((TextBox)gvEG.Rows[e.RowIndex].FindControl("txtEmployeeName")).Text;
    eInfo.DepartmentId = Convert.ToInt32(((DropDownList)gvEG.Rows[e.RowIndex].FindControl("ddlDepartment")).SelectedValue);
    eInfo.DepartmentName = Convert.ToString(((DropDownList)gvEG.Rows[e.RowIndex].FindControl("ddlDepartment")).SelectedItem.Text);
    eInfo.EmployeeGroup = ((DropDownList)gvEG.Rows[e.RowIndex].FindControl("ddlEmployeeGroup")).SelectedValue;
    eInfo.isActive = ((CheckBox)gvEG.Rows[e.RowIndex].FindControl("chkActive")).Checked;
    new mainSQL().updateEmployeeInfo(eInfo);
    gvEG.EditIndex = -1;
    FillEmployeeGrid();
}

Delete
From this method we are deleting the employe record from the datasource . Here I'm reading the ID value from the DataKeyNames assigned to the GridView and passing that ID to Delete method of the class object.

protected void gvEG_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    Int64 ID = Convert.ToInt64(gvEG.DataKeys[e.RowIndex].Values[0].ToString());
    new mainSQL().deleteEmployeeInfo(ID);
    FillEmployeeGrid();
}

Employee and Department Class Structure

This are the two classes that used for handling Department and Employee details

Employee Class structure is same as the data-source structure and department class is used for creating and assigning department values in the GridView.

public class EmployeeInfo
{
    public Int64 ID { get; set; }
    public string EmployeeCode { get; set; }
    public string EmployeeName { get; set; }
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    public string EmployeeGroup { get; set; }
    public string Email { get; set; }
    public bool isActive { get; set; }
}
public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
}

SQL Server

All class method looks similar but still for simplicity I have kept different files for handling different data-sources. all classes are created under egClass folder. SQL related codes are written inside mainSQL.cs.

Connection String is reading from web.config and created as a static read only property.

static private string sqlConString
{
   get { return WebConfigurationManager.ConnectionStrings["egSQLConString"].ConnectionString; }
}

This method is created for executing all query to database. Here I'm creating the SqlConnection from the connection string mentioned in connection string and assigning the sql query and connection to SqlCommand object then after executing the query and returning the rows affected from this method to the calling method.

public int executeQuery(string queryString)
{ 
    int rowsAffected = 0;
    using (SqlConnection connection = new SqlConnection(sqlConString))
    {
        using (SqlCommand command = new SqlCommand(queryString, connection))
        {
            command.Connection.Open();
            rowsAffected = command.ExecuteNonQuery();
        }
    }
    return rowsAffected;
}

Load Data

This method is used to bind the employee details to the GridView. Here I'm creating List object of EmployeeInfo and Executing the query to the SQL Server and reading the result from datareader and assigning the the Employee List object, this Object will be used for binding the Gridview.

public List<EmployeeInfo> getEmployeeList()
{
    List<EmployeeInfo> eList = new List<EmployeeInfo>();
    EmployeeInfo objEmp = null;
    string queryString = "SELECT [ID],[EmployeeCode],[EmployeeName],[DepartmentId],[DepartmentName],[EmployeeGroup],[Email],[isActive] FROM [dbo].[Employee]";
    using (SqlConnection connection = new SqlConnection(sqlConString))
    {
        using (SqlCommand command = new SqlCommand(queryString, connection))
        {
            command.Connection.Open();
            using (SqlDataReader dataReader = command.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    objEmp = new EmployeeInfo();
                    objEmp.ID = Convert.ToInt64(dataReader["ID"]);
                    objEmp.EmployeeCode = Convert.ToString(dataReader["EmployeeCode"]);
                    objEmp.EmployeeName = Convert.ToString(dataReader["EmployeeName"]);
                    objEmp.DepartmentId = Convert.ToInt32(dataReader["DepartmentId"]);
                    objEmp.DepartmentName = Convert.ToString(dataReader["DepartmentName"]);
                    objEmp.EmployeeGroup = Convert.ToString(dataReader["EmployeeGroup"]);
                    objEmp.Email = Convert.ToString(dataReader["Email"]);
                    objEmp.isActive = Convert.ToBoolean(dataReader["isActive"]);
                    eList.Add(objEmp);
                }
            }
        }
    }
    return eList;
}

Insert

This method is used to insert new employee information to the SQL database. Here I'm creating the Insert script and passing to the executeQuery method. It's advised to use parametarized query instead of string appending.

public Boolean insertEmployeeInfo(EmployeeInfo eInfo)
{
    string queryString = "INSERT INTO [Employee] ([EmployeeCode], [EmployeeName],

    [DepartmentId],[DepartmentName], [EmployeeGroup],[Email] ,[isActive]) " +
    "VALUES ('" + eInfo.EmployeeCode + "', '" + eInfo.EmployeeName + "',

    " + eInfo.DepartmentId + ",'" + eInfo.DepartmentName + "', '" + eInfo.EmployeeGroup + "', '" +
    eInfo.Email + "', " + Convert.ToInt32(eInfo.isActive) + ")";
    return Convert.ToBoolean(executeQuery(queryString));
}

Update

This method is used to update existing employee information to the SQL database. Here the Input parameter is Employee Info object which is going to update in the database.

public Boolean updateEmployeeInfo(EmployeeInfo eInfo)
{
    string queryString = "UPDATE [Employee] SET  [EmployeeCode] = '" + eInfo.EmployeeCode + "',  [EmployeeName] = '"
    + eInfo.EmployeeName + "',  [DepartmentId] = " + eInfo.DepartmentId + ",

    [DepartmentName] = '" + eInfo.DepartmentName + "', [EmployeeGroup] = '" 
    + eInfo.EmployeeGroup + "',  [isActive] = " +
    Convert.ToInt32(eInfo.isActive) + " WHERE [ID] = " + eInfo.ID;
    return Convert.ToBoolean(executeQuery(queryString));
}

Delete

This method is used to delete a specific employee record from the SQL database. here the input parameter is the EmployeeID , by using this ID we can delete the employee record from database.

public Boolean deleteEmployeeInfo(long ID)
{
    string queryString = "DELETE FROM Employee WHERE [ID] = " + ID;
    return Convert.ToBoolean(executeQuery(queryString));
}

MS Access

Access related codes are written inside mainACCESS.cs. This file can be found under the egClass folder.

Connection String is reading from web.config and created as a static read only property.

static private string accessConString
{
  get { return WebConfigurationManager.ConnectionStrings["egACCESSConString_2002_2003"].ConnectionString; }
}

This method is created for executing all query to Access database.

public int executeQuery(string queryString)
{ 
    int rowsAffected = 0;
    using (OleDbConnection connection = new OleDbConnection(accessConString))
    {
        using (OleDbCommand command = new OleDbCommand(queryString, connection))
        {
            command.Connection.Open();
            rowsAffected = command.ExecuteNonQuery();
        }
    }
    return rowsAffected;
}

Load Data

This method is used to bind the employee details to the GridView from Access database. By using OleDbConnection we can connect to the MS-Access database, accordingly OleDbCommand and OledbReader are used to read the data from Access database.

public List<EmployeeInfo> getEmployeeList()
{
    List<EmployeeInfo> eList = new List<EmployeeInfo>();
    EmployeeInfo objEmp = null;
    string queryString = "SELECT [ID],[EmployeeCode],[EmployeeName],

    [DepartmentId],[DepartmentName],[EmployeeGroup],[Email],[isActive] FROM [dbo].[Employee]";
    using (OleDbConnection connection = new OleDbConnection(accessConString))
    {
        using (OleDbCommand command = new OleDbCommand(queryString, connection))
        {
            command.Connection.Open();
            using (OleDbDataReader dataReader = command.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    objEmp = new EmployeeInfo();
                    objEmp.ID = Convert.ToInt64(dataReader["ID"]);
                    objEmp.EmployeeCode = Convert.ToString(dataReader["EmployeeCode"]);
                    objEmp.EmployeeName = Convert.ToString(dataReader["EmployeeName"]);
                    objEmp.DepartmentId = Convert.ToInt32(dataReader["DepartmentId"]);
                    objEmp.DepartmentName = Convert.ToString(dataReader["DepartmentName"]);
                    objEmp.EmployeeGroup = Convert.ToString(dataReader["EmployeeGroup"]);
                    objEmp.Email = Convert.ToString(dataReader["Email"]);
                    objEmp.isActive = Convert.ToBoolean(dataReader["isActive"]);
                    eList.Add(objEmp);
                }
            }
        }
    }
    return eList;
}

Insert

This method is used to insert new employee information to the Access database. Here I'm creating the Insert script and passing to the executeQuery method. It's advised to use parametarized query instead of string appending.

public Boolean insertEmployeeInfo(EmployeeInfo eInfo)
{
    string queryString = "INSERT INTO [Employee] ([EmployeeCode], [EmployeeName], [DepartmentId],

    [DepartmentName], [EmployeeGroup],[Email] ,[isActive]) " +
    "VALUES ('" + eInfo.EmployeeCode + "', '" + eInfo.EmployeeName + "',

    " + eInfo.DepartmentId + ",'" + eInfo.DepartmentName + "', '" + eInfo.EmployeeGroup + "', '" +
    eInfo.Email + "', " + Convert.ToInt32(eInfo.isActive) + ")";
    return Convert.ToBoolean(executeQuery(queryString));
}

Update

This method is used to update existing employee information to the Access database. Here the Input parameter is Employee Info object which is going to update in the database.

public Boolean updateEmployeeInfo(EmployeeInfo eInfo)
{
    string queryString = "UPDATE [Employee] SET  [EmployeeCode] = '" + eInfo.EmployeeCode + "',

    [EmployeeName] = '" + eInfo.EmployeeName + "',  [DepartmentId] = " + eInfo.DepartmentId + ",

    [DepartmentName] = '" + eInfo.DepartmentName + "', [EmployeeGroup] = '" + eInfo.EmployeeGroup + "',

    [isActive] = " + Convert.ToInt32(eInfo.isActive) + " WHERE [ID] = " + eInfo.ID;
    return Convert.ToBoolean(executeQuery(queryString));
}

Delete

This method is used to delete a specific employee record from the Access database. here the input parameter is the EmployeeID , by using this ID we can delete the employee record from database.

public Boolean deleteEmployeeInfo(long ID)
{
    string queryString = "DELETE FROM Employee WHERE [ID] = " + ID;
    return Convert.ToBoolean(executeQuery(queryString));
}

XML

XML related codes are written inside mainXML.cs. This file can be found under the egClass folder. In VS2010 and VS2008 solutions I'm using Linq for all XML operations, In VS 2005 solution I'm using Dataset for this all operations. For Framework 2.0 specific coding for this section please check the attached file VS 2005.

XML file path is reading from web.config and created as a static read only property.

static private string xmlConString
{
    get { return HttpContext.Current.Server.MapPath(WebConfigurationManager.AppSettings["xmlPath"].ToString()); }
}

If you are using 2.0 Framework then you need to use this...

static private string xmlConString
{
    get { return HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["xmlPath"].ToString()); }
}

Load Data

This method is used to bind the employee details to the GridView from the XML file. Here if the file exists then it will read using XDocument otherwise it will return null

public List<EmployeeInfo> getEmployeeList()
{
    XDocument xmlContent = new XDocument();
    if (File.Exists(xmlConString))
    {
        xmlContent = XDocument.Load(xmlConString);
        var content = from data in xmlContent.Descendants("Employee")
                        select new EmployeeInfo
                        {
                            ID = ((Int64)data.Element("ID")),
                            EmployeeCode = ((string)data.Element("EmployeeCode")),
                            EmployeeName = ((string)data.Element("EmployeeName")),
                            DepartmentId = ((Int32)data.Element("DepartmentId")),
                            DepartmentName = ((string)data.Element("DepartmentName")),
                            EmployeeGroup = ((string)data.Element("EmployeeGroup")),
                            Email = ((string)data.Element("Email")),
                            isActive = ((bool)data.Element("isActive"))
                        };
        return content.ToList();
    }
    return null;
}

Insert

This method is used to insert new employee information to the XML file. Here the input parameter is the new employee info.if the XML exists then it will append the employee info with a new ID generated otherwise this method will create an XML file with this info , ID as 1.

public Boolean insertEmployeeInfo(EmployeeInfo eInfo)
{
    if (!File.Exists(xmlConString))
    {
        XElement Profile = new XElement("Employee", new XElement("Employee",
                new XElement("ID", 1),
                new XElement("EmployeeCode", eInfo.EmployeeCode),
                new XElement("EmployeeName", eInfo.EmployeeName),
                new XElement("DepartmentId", eInfo.DepartmentId),
                new XElement("DepartmentName", eInfo.DepartmentName),
                new XElement("EmployeeGroup", eInfo.EmployeeGroup),
                new XElement("Email", eInfo.Email),
                new XElement("isActive", eInfo.isActive)));
        Profile.Save(xmlConString);
        //("XML File Created");
    }
    else
    {
        XElement doc = XElement.Load(xmlConString);
        XElement newProfile = new XElement("Employee",
                new XElement("ID", doc.Elements("Employee").Max(m => int.Parse(m.Element("ID").Value) + 1)),
                new XElement("EmployeeCode", eInfo.EmployeeCode),
                new XElement("EmployeeName", eInfo.EmployeeName),
                new XElement("DepartmentId", eInfo.DepartmentId),
                new XElement("DepartmentName", eInfo.DepartmentName),
                new XElement("EmployeeGroup", eInfo.EmployeeGroup),
                new XElement("Email", eInfo.Email),
                new XElement("isActive", eInfo.isActive));
        doc.Add(newProfile);
        doc.Save(xmlConString);
        //XML Appended
    }
    return true;
}

Update

This method is used to update existing employee information to the XML file. Here the Input parameter is Employee Info object and this information will be updated to the matched ID of the employee row.

public Boolean updateEmployeeInfo(EmployeeInfo eInfo)
{
XDocument doc = XDocument.Load(xmlConString); 
IEnumerable<XElement> oMemberList =

doc.Element("Employees").Elements("Employee");
var oMember = (from member in oMemberList
where
member.Element("ID").Value == eInfo.ID.ToString()
select member).SingleOrDefault();
oMember.SetElementValue("EmployeeCode", eInfo.EmployeeCode);
oMember.SetElementValue("EmployeeName", eInfo.EmployeeName);
oMember.SetElementValue("DepartmentId", eInfo.DepartmentId);
oMember.SetElementValue("DepartmentName", eInfo.DepartmentName);
oMember.SetElementValue("EmployeeGroup", eInfo.EmployeeGroup);
oMember.SetElementValue("Email", eInfo.Email);
oMember.SetElementValue("isActive", eInfo.isActive);
doc.Save(xmlConString);
//XML Updated
return true;
}

Delete

This method is used to delete a specific employee record from the XML. here the input parameter is the EmployeeID , by using this ID we can delete the employee record from XML file.

public Boolean deleteEmployeeInfo(long ID)
{ 
XDocument xml = new XDocument();
xml = XDocument.Load(xmlConString);
xml.Element("Employees").Elements("Employee").Where(x => x.Element("ID").Value.Trim() ==

ID.ToString()).Remove();
xml.Save(xmlConString);
//XML Element Deleted
return true;
}

1 comment:

UnknownNovember 16, 2012 at 11:24 AM
Very informative nice blog. It is most efficient to insert elements in the end of the grid (this is true for all grids). This method of adding data basically doesn't depend on the number of already inserted elements. Adding data to the beginning of grid or to a sorted grid heavily depends on the number of already inserted elements. This is especiallytrue for insertion to the beginning of a grid, which should be avoided. more detail visit dapfor. com

Microsoft Technology

Menus

Sunday, November 11, 2012

Insert, Update, Delete in ASP.NET Gridview, DataSource as SQL Server, MS Access (mdb/accdb), XML and Framework as 2.0 / 3.0 / 3.5 / 4.0 (VS 2005/2008/2010)

Introduction

Using the Code

Setup Data Sources

SQL

Access

XML

Editable GridView CSS

Gridview Validation

Connection String and XML path

GridView HTML

Code-Behind

Employee and Department Class Structure

SQL Server

Load Data

Insert

Update

Delete

MS Access

Load Data

Insert

Update

Delete

XML

Load Data

Insert

Update

Delete

1 comment: