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)

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>  

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