Grid View CRUD Operations Using N-Tier Architecture

Here I am explaining GridView CRUD Operations using N-Tier Architecture.

First here I am explaining databases, table parameters, and Stored Procedures to Create, Read, Update and Delete Operations.

Use [GridData]
CREATE TABLE [dbo].[OperatingSystem](
[OSId] [int] IDENTITY(1,1) NOT NULL,
[OSName] [varchar](100) NULL,
[CreateDate] [datetime] NULL,
[Status] [smallint] NULL,
CONSTRAINT [PK_OperatingSystem] PRIMARY KEY CLUSTERED
(
[OSId] 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
ALTER TABLE [dbo].[OperatingSystem] ADD DEFAULT (getdate()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[OperatingSystem] ADD DEFAULT ('1') FOR [Status]
GO
Stored Procedures are,
//Create Procedure
Create Procedure [dbo].[InsertSystemName]
(
@OSName varchar(50)
)
AS
BEGIN
insert into OperatingSystem(OSName) values(@OSName)
END
//Get Data Procedure
ALTER Procedure [dbo].[GetSystemsData]
As
Begin
select * from OperatingSystem where Status='1'
End
//Update Procedure
ALTER Procedure [dbo].[UpdateSystems]
(
@Id int,
@Name varchar(100),
@Status int
)
As
BEGIN
update OperatingSystem set OSName=@Name,Status=@Status where OSId=@Id
END
//Delete Procedure
ALTER Procedure [dbo].[DeleteSystemsData]
(
@Id int
)
As
Begin
update OperatingSystem set Status='0' where OSId=@Id
End

Next create the solution from Visual Studio. First create a solution add three class libraries named "DAL", "Databaselayer", "EntityLayer" and one web application named "GridViewExample" as ini the following image.
Next add refernces for the solution. First DAL for the DatabaseLayer and EntityLayer.
For the GridViewExample add "DAL", "Databaselayer" and "Entitylayer".
In the DatabaseLayer, add one refernce System.Configuration.

Getting to the Databaselayer, create a classs named "SqlHelper.CS" and write the following code.

Note: Here dbconnection is a connection string for SQL Server.

public static string CONNECTION_STRING = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
public static DataSet ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SqlParameter[] param) {
DataSet ds = new DataSet();
using(SqlConnection con = new SqlConnection(CONNECTION_STRING)) {
using(SqlCommand cmd = con.CreateCommand()) {
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(param);
try {
if (con.State != ConnectionState.Open) {
con.Open();
}
using(SqlDataAdapter da = new SqlDataAdapter(cmd)) {
da.Fill(ds);
}
} catch {
throw;
}
}
}
return ds;
}
// This function will be used to execute CUD(CRUD) operation of parameterized commands
public static bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars) {
int result = 0;
using(SqlConnection con = new SqlConnection(CONNECTION_STRING)) {
using(SqlCommand cmd = con.CreateCommand()) {
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(pars);
try {
if (con.State != ConnectionState.Open) {
con.Open();
}
result = cmd.ExecuteNonQuery();
} catch {
throw;
}
}
}
return (result > 0);
}
//Getting To EntityLayer, add a class and named as GridData.CS and write following code:
public class OperatingSystemEntity {
public int OSId {
get;
set;
}
public string OSName {
get;
set;
}
public DateTime CreateDate {
get;
set;
}
public int Status {
get;
set;
}
}
//Getting to DAL, Add a class and named as GridData.cs and write the following code:
public class GridData {
public bool CreateSystem(OperatingSystemEntity SEntity) {
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@OSName", SEntity.OSName),
};
return SqlHelper.ExecuteNonQuery("InsertSystemName", CommandType.StoredProcedure, parameters);
}
public List < OperatingSystemEntity > GetSystemsData(OperatingSystemEntity SEntity) {
List < OperatingSystemEntity > ListEntry = null;
SqlParameter[] parameters = new SqlParameter[] {
};
using(DataSet ds = SqlHelper.ExecuteParamerizedSelectCommand("GetSystemsData", CommandType.StoredProcedure, parameters)) {
if (ds.Tables.Count > 0) {
ListEntry = new List < OperatingSystemEntity > ();
foreach(DataRow row2 in ds.Tables[0].Rows) {
OperatingSystemEntity entry = new OperatingSystemEntity();
entry.OSId = Convert.ToInt32(row2["OSId"].ToString());
entry.OSName = row2["OSName"].ToString();
entry.CreateDate = Convert.ToDateTime(row2["CreateDate"].ToString());
entry.Status = Convert.ToInt32(row2["Status"].ToString());
ListEntry.Add(entry);
}
}
}
return ListEntry;
}
public bool UpdateSystems(OperatingSystemEntity SEntity) {
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@Id", SEntity.OSId),
new SqlParameter("@Name", SEntity.OSName),
new SqlParameter("@Status", SEntity.Status),
};
return SqlHelper.ExecuteNonQuery("UpdateSystems", CommandType.StoredProcedure, parameters);
}
public bool DeleteSystem(OperatingSystemEntity SEntity) {
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@Id", SEntity.OSId),
};
return SqlHelper.ExecuteNonQuery("DeleteSystemsData", CommandType.StoredProcedure, parameters);
}
}
}

Then the Solution Explorer will be such as follows:

Getting to GridViewExample Web Application, add a new form named NewGrid.aspx and add the following code:

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
<script language="javascript" type="text/javascript">
function validate() {
if (document.getElementById("<%=txtname.ClientID %>").value == "") {
alert("Please Enter Name");
document.getElementById("<%=txtname.ClientID %>").focus();
return false;
}
}
</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="Body" runat="server">
<div>
<h2 style="width: 80%">
<u>Create System</u></h2>
<table>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="lblmsgerror" Font-Bold="true"></asp:Label>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblname" runat="server" Text="SystemName"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnsave" runat="server" Text="Save" OnClick="btnsave_Click" />
</td>
</tr>
</table>
</div>
<h2 style="width: 80%">
<u>Total Availble Systems</u></h2>
<p>
<asp:Label runat="server" ID="lblmsg" ForeColor="Red" Font-Bold="true"></asp:Label>
</p>
<div>
<asp:GridView ID="gvSystem" runat="server" AutoGenerateColumns="false" OnRowUpdating="gvSystem_RowUpdating" OnRowEditing="gvSystem_RowEditing" OnRowDeleting="gvSystem_RowDeleting" DataKeyNames="OSId" OnRowCancelingEdit="gvSystem_RowCancelingEdit">
<Columns>
<asp:TemplateField>
<EditItemTemplate>
<asp:ImageButton ID="imgbtnUpdate" CommandName="Update" runat="server" ImageUrl="~/Images/Update.jpg" ToolTip="Update" Height="20px" Width="20px" />
<asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/Cancel.jpg" ToolTip="Cancel" Height="20px" Width="20px" />
</EditItemTemplate>
<ItemTemplate>
<asp:ImageButton ID="imgbtnEdit" CommandName="Edit" runat="server" ImageUrl="~/Images/Edit.jpg" ToolTip="Edit" Height="20px" Width="20px" />
<asp:ImageButton ID="imgbtnDelete" CommandName="Delete" OnClientClick="return confirm('Are you sure you want to delete selected record?')" Text="Edit" runat="server" ImageUrl="~/images/Delete.jpg" ToolTip="Delete" Height="20px" Width="20px" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="System Name">
<EditItemTemplate>
<asp:TextBox ID="txtname" runat="server" Text='<%# Eval("OSName")%>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblname" runat="server" Text='<%# Eval("OSName")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date Of Created">
<ItemTemplate>
<asp:Label ID="lbldate" runat="server" Text='<%# Eval("CreateDate")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Status">
<EditItemTemplate>
<asp:TextBox ID="txtstatus" runat="server" Text='<%# Eval("Status")%>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblstatus" runat="server" Text='<%# Eval("Status")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</asp:Content>

In NewGrid.aspx.cs file, write the following code:

OperatingSystemEntity SEntity = new OperatingSystemEntity();
GridData GData = new GridData();
protected void Page_Load(object sender, EventArgs e) {
btnsave.Attributes.Add("onclick", "return validate()");
if (!IsPostBack) {
BindData();
}
}
protected void btnsave_Click(object sender, EventArgs e) {
SEntity.OSName = txtname.Text;
if (GData.CreateSystem(SEntity) == true) {
lblmsgerror.ForeColor = Color.Green;
lblmsgerror.Text = "System Name Saved Successfully";
BindData();
txtname.Text = "";
} else {
lblmsgerror.ForeColor = Color.Red;
lblmsgerror.Text = "System Name Already Exists ";
}
}
private void BindData() {
List < OperatingSystemEntity > SList = GData.GetSystemsData(SEntity);
if (SList.Count != 0) {
gvSystem.DataSource = SList;
gvSystem.DataBind();
} else {
lblmsg.Text = "No Data found..";
}
}
private static String GetTextFromRowBox(GridViewRow row, String field) {
return ((TextBox) row.FindControl(field)).Text;
}
protected void gvSystem_RowUpdating(object sender, GridViewUpdateEventArgs e) {
int id = Convert.ToInt32(gvSystem.DataKeys[e.RowIndex].Values["OSId"].ToString());
GridViewRow row = gvSystem.Rows[e.RowIndex];
SEntity.OSName = GetTextFromRowBox(row, "txtname");
SEntity.Status = Convert.ToInt32(GetTextFromRowBox(row, "txtstatus").ToString());
SEntity.OSId = id;
if (GData.UpdateSystems(SEntity) == true) {
gvSystem.EditIndex = -1;
BindData();
lblmsg.Text = "Records Updated sucessfully";
} else {
lblmsg.Text = "Updation Failed";
}
}
protected void gvSystem_RowEditing(object sender, GridViewEditEventArgs e) {
gvSystem.EditIndex = e.NewEditIndex;
BindData();
}
protected void gvSystem_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) {
gvSystem.EditIndex = -1;
BindData();
}
protected void gvSystem_RowDeleting(object sender, GridViewDeleteEventArgs e) {
int ID = Convert.ToInt32(gvSystem.DataKeys[e.RowIndex].Values["OSId"].ToString());
deleterecords(ID);
}
private void deleterecords(int ID) {
SEntity.OSId = ID;
if (GData.DeleteSystem(SEntity) == true) {
lblmsg.Text = "Records deleted sucessfully...";
BindData();
lblmsg.Text = "";
} else {
lblmsg.ForeColor = Color.Red;
lblmsg.Text = "Records deleted failed...";
BindData();
lblmsg.Text = "";
}
}

Like that we have completed the CRUD Operations for the Gridview in the N-Tier architecture. If you have any doubts then please leave a comment. I will explain ASAP. The output will appear as in the following:

Thanks and happy coding.