Click here to Skip to main content
Click here to Skip to main content

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)

, 18 Jul 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Here I will explain Insert, Update, Delete /CRUD / Editable in GridView, Validation Controls, JavaScript, CSS, EmptyDataTemplate, Footer, Default Row, Linq-XML, XML-DataSet, Hightlighting Row, DataDirtectory/App_Data, VS 2005/2008/2010, RowDataBound, RowCommand, RowEditing, RowUpdating, RowCanceling

Table Of Contents   

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. For your reference I have attached source code with this article, you can download whichever zip file is relevant for you.Your votessuggestions and feedback are highly appreciated to improve the quality of this and upcoming articles, please don't forget. 

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;
}  

Summary   

I hope this article helped you to understand some of the GridView operations and how to do insert update delete in sql server, access and xml with GridView.   

Please give your valuable suggestions and feedback for further improvements. Thanks for reading. 

Go to Contents  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shemeer NS
Software Developer (Senior)
India India
Technology Specialist | CodeProject MVP | Visual Studio Gallery Contributor | Author | Geek | Netizen | Husband | ChessPlayer
 
Most of my articles are listed on top 5 of the respective 'Best articles of the month' and some of my articles are published on ASP.NET WebSite's Article of the Day section.
 
Check my contributions in Visual Studio Gallery and Code Project
 
Technical Blog: http://www.shemeerns.com
Facebook: http://facebook.com/shemeernsblog
Twitter : http://twitter.com/shemeerns
Google+ : http://google.com/+Shemeernsblog
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionA network-related or instance-specific error occurred while establishing a connection to SQL Server........ Pinmemberitexp25-Nov-12 18:52 
AnswerRe: A network-related or instance-specific error occurred while establishing a connection to SQL Server........ PinmemberShemeer NS25-Nov-12 19:07 
QuestionCan't pass login password Pinmemberslime1324-Nov-12 4:51 
AnswerRe: Can't pass login password PinmemberShemeer NS24-Nov-12 6:56 
GeneralRe: Can't pass login password Pinmemberslime1324-Nov-12 8:37 
QuestionGood attempt PinmemberSheo Narayan22-Nov-12 18:59 
AnswerRe: Good attempt PinmemberShemeer NS22-Nov-12 21:58 
GeneralMy vote of 1 Pinmembertanujkhurana13-Nov-12 0:47 
GeneralRe: My vote of 1 PinmemberShemeer NS13-Nov-12 2:05 
QuestionVB? Pinmemberbikercaz7-Nov-12 10:30 
AnswerRe: VB? PinmemberShemeer NS7-Nov-12 19:10 
QuestionThere is an issue PinmemberAlbarhami6-Nov-12 21:45 
AnswerRe: There is an issue PinmemberShemeer NS7-Nov-12 0:33 
QuestionI have this error PinmemberSabi161027-Oct-12 1:58 
Both DataSource and DataSourceID are defined on 'gvEG'. Remove one definition.
 
Great article helped alot Smile | :)
AnswerRe: I have this error PinmemberShemeer NS7-Nov-12 0:26 
Questionneed help for datagrid update event Pinmember000mann00016-Oct-12 0:03 
AnswerRe: need help for datagrid update event PinmemberShemeer NS5-Nov-12 9:48 
Questioni have this error. PinmemberMember 94908038-Oct-12 20:54 
AnswerRe: i have this error. PinmemberShemeer NS8-Oct-12 21:05 
GeneralRe: i have this error. PinmemberMember 94908038-Oct-12 22:32 
GeneralRe: i have this error. PinmemberShemeer NS8-Oct-12 23:43 
GeneralGood Work PinmemberArun_Potti4-Oct-12 4:07 
QuestionSource code? Pinmemberraven8er25-Sep-12 6:35 
AnswerRe: Source code? PinmemberShemeer NS25-Sep-12 7:46 
Questiondoubt on stored procedure Pinmemberhasbina23-Sep-12 22:53 
AnswerRe: doubt on stored procedure PinmemberShemeer NS24-Sep-12 0:04 
GeneralMy vote of 5 Pinmemberzura_b2017-Sep-12 8:49 
Questionjust cool Pinmemberzura_b2016-Sep-12 9:46 
AnswerRe: just cool PinmemberShemeer NS18-Sep-12 23:29 
QuestionDoubt Pinmemberhasbina13-Sep-12 1:20 
AnswerRe: Doubt PinmemberShemeer NS18-Sep-12 23:28 
Questionplease help me... Pinmemberhasbina12-Sep-12 20:57 
AnswerRe: please help me... PinmemberShemeer NS12-Sep-12 21:35 
GeneralRe: please help me... Pinmemberhasbina12-Sep-12 22:04 
GeneralRe: please help me... PinmemberShemeer NS14-Sep-12 4:39 
GeneralRe: please help me... Pinmemberjahfer16-Sep-12 3:53 
QuestionPlease Help Me Out... Pinmembersbanglorkar11-Sep-12 21:50 
AnswerRe: Please Help Me Out... PinmemberShemeer NS12-Sep-12 21:34 
AnswerRe: Please Help Me Out... PinmemberShemeer NS14-Sep-12 4:45 
GeneralRe: Please Help Me Out... Pinmemberjoeller18-Jan-13 11:16 
GeneralRe: Please Help Me Out... PinmvpShemeer NS18-Jan-13 18:50 
GeneralMy vote of 5 Pinmemberfquezada6-Sep-12 10:56 
GeneralMy vote of 3 Pinmembercpsglauco6-Sep-12 6:27 
GeneralRe: My vote of 3 PinmemberShemeer NS6-Sep-12 19:59 
GeneralRe: My vote of 3 Pinmembercpsglauco6-Sep-12 21:39 
GeneralRe: My vote of 3 PinmemberShemeer NS6-Sep-12 23:59 
GeneralMy vote of 5 Pinmemberfiarrra24-Aug-12 2:59 
QuestionMy vote of 5 PinmemberVani Kulkarni8-Aug-12 21:43 
AnswerRe: My vote of 5 PinmemberShemeer NS11-Aug-12 1:33 
GeneralMy vote of 3 PinmemberRajendra Bhandari5-Aug-12 23:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.1 | Last Updated 19 Jul 2012
Article Copyright 2012 by Shemeer NS
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid