- this style is used for the FooterRow of the grid where we are doing insertion
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
if that controlneed not perform any validation ( eg:- Cancel Button), Here I'm giving a validation sample for GridView .
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.
I have kept the SQL Server and Acces connection string in web.config's
<connectionStrings> and XML file path in
<appSettings>
Connection Strings
Collapse | Copy Code
<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"/>
-->
</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
Collapse | Copy Code
<appSettings>
<add key="xmlPath" value="XML/Employee.xml"/>
</appSettings>
<appSettings> is the best place to keep the application configuration settings.
GridView HTML is given below, this code is same for all pages in this project.
Collapse | Copy Code
<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>
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.
Collapse | Copy Code
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.
Collapse | Copy Code
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.
Collapse | Copy Code
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.
Collapse | Copy Code
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.
Collapse | Copy Code
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 .
Collapse | Copy Code
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.
Collapse | Copy Code
protected void gvEG_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Int64 ID = Convert.ToInt64(gvEG.DataKeys[e.RowIndex].Values[0].ToString());
new mainSQL().deleteEmployeeInfo(ID);
FillEmployeeGrid();
}
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.
Collapse | Copy Code
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; }
}
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.
Collapse | Copy Code
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.
Collapse | Copy Code
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;
}
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.
Collapse | Copy Code
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;
}
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.
Collapse | Copy Code
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));
}
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.
Collapse | Copy Code
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));
}
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.
Collapse | Copy Code
public Boolean deleteEmployeeInfo(long ID)
{
string queryString = "DELETE FROM Employee WHERE [ID] = " + ID;
return Convert.ToBoolean(executeQuery(queryString));
}
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.
Collapse | Copy Code
static private string accessConString
{
get { return WebConfigurationManager.ConnectionStrings["egACCESSConString_2002_2003"].ConnectionString; }
}
This method is created for executing all query to Access database.
Collapse | Copy Code
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;
}
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.
Collapse | Copy Code
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;
}
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.
Collapse | Copy Code
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));
}
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.
Collapse | Copy Code
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));
}
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.
Collapse | Copy Code
public Boolean deleteEmployeeInfo(long ID)
{
string queryString = "DELETE FROM Employee WHERE [ID] = " + ID;
return Convert.ToBoolean(executeQuery(queryString));
}
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.
Collapse | Copy Code
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...
Collapse | Copy Code
static private string xmlConString
{
get { return HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["xmlPath"].ToString()); }
}
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
Collapse | Copy Code
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;
}
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.
Collapse | Copy Code
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);
}
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);
}
return true;
}
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.
Collapse | Copy Code
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);
return true;
}
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.
Collapse | Copy Code
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);
return true;
}
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.
