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.