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.

  1. Use [GridData]  
  2.   
  3. CREATE TABLE [dbo].[OperatingSystem](  
  4. [OSId] [int] IDENTITY(1,1) NOT NULL,  
  5. [OSName] [varchar](100) NULL,  
  6. [CreateDate] [datetime] NULL,  
  7. [Status] [smallintNULL,  
  8. CONSTRAINT [PK_OperatingSystem] PRIMARY KEY CLUSTERED  
  9. (  
  10. [OSId] ASC  
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  12. ON [PRIMARY]  
  13.   
  14. GO  
  15.   
  16. SET ANSI_PADDING OFF  
  17. GO  
  18.   
  19. ALTER TABLE [dbo].[OperatingSystem] ADD DEFAULT (getdate()) FOR [CreateDate]  
  20. GO  
  21.   
  22. ALTER TABLE [dbo].[OperatingSystem] ADD DEFAULT ('1'FOR [Status]  
  23. GO  
  24.   
  25. Stored Procedures are,  
  26.   
  27. //Create Procedure  
  28. Create Procedure [dbo].[InsertSystemName]  
  29. (  
  30. @OSName varchar(50)  
  31. )  
  32. AS  
  33. BEGIN  
  34. insert into OperatingSystem(OSName) values(@OSName)  
  35. END  
  36.   
  37. //Get Data Procedure  
  38. ALTER Procedure [dbo].[GetSystemsData]  
  39. As  
  40. Begin  
  41. select * from OperatingSystem where Status='1'  
  42. End  
  43.   
  44.   
  45. //Update Procedure  
  46. ALTER Procedure [dbo].[UpdateSystems]  
  47. (  
  48. @Id int,  
  49. @Name varchar(100),  
  50. @Status int  
  51. )  
  52. As  
  53. BEGIN  
  54. update OperatingSystem set OSName=@Name,Status=@Status where OSId=@Id  
  55. END  
  56.   
  57. //Delete Procedure  
  58. ALTER Procedure [dbo].[DeleteSystemsData]  
  59. (  
  60. @Id int  
  61. )  
  62. As  
  63. Begin  
  64. update OperatingSystem set Status='0' where OSId=@Id  
  65.   
  66. 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.

  1. public static string CONNECTION_STRING = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;  
  2.   
  3. public static DataSet ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SqlParameter[] param)  
  4. {  
  5. DataSet ds = new DataSet();  
  6.   
  7. using (SqlConnection con = new SqlConnection(CONNECTION_STRING))  
  8. {  
  9. using (SqlCommand cmd = con.CreateCommand())  
  10. {  
  11. cmd.CommandType = cmdType;  
  12. cmd.CommandText = CommandName;  
  13. cmd.Parameters.AddRange(param);  
  14. try  
  15. {  
  16. if (con.State != ConnectionState.Open)  
  17. {  
  18. con.Open();  
  19. }  
  20.   
  21. using (SqlDataAdapter da = new SqlDataAdapter(cmd))  
  22. {  
  23. da.Fill(ds);  
  24. }  
  25. }  
  26. catch  
  27. {  
  28. throw;  
  29. }  
  30. }  
  31. }  
  32. return ds;  
  33. }  
  34.   
  35. // This function will be used to execute CUD(CRUD) operation of parameterized commands  
  36. public static bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars)  
  37. {  
  38. int result = 0;  
  39.   
  40. using (SqlConnection con = new SqlConnection(CONNECTION_STRING))  
  41. {  
  42. using (SqlCommand cmd = con.CreateCommand())  
  43. {  
  44. cmd.CommandType = cmdType;  
  45. cmd.CommandText = CommandName;  
  46. cmd.Parameters.AddRange(pars);  
  47.   
  48. try  
  49. {  
  50. if (con.State != ConnectionState.Open)  
  51. {  
  52. con.Open();  
  53. }  
  54. result = cmd.ExecuteNonQuery();  
  55. }  
  56. catch  
  57. {  
  58. throw;  
  59. }  
  60. }  
  61. }  
  62. return (result > 0);  
  63. }  
  64.   
  65. Getting To EntityLayer, add a class and named as GridData.CS and write following code:  
  66.   
  67. public class OperatingSystemEntity  
  68. {  
  69. public int OSId { getset; }  
  70. public string OSName { getset; }  
  71. public DateTime CreateDate { getset; }  
  72. public int Status { getset; }  
  73. }  
  74.   
  75. Getting to DAL, Add a class and named as GridData.cs and write the following code:  
  76. public class GridData  
  77. {  
  78. public bool CreateSystem(OperatingSystemEntity SEntity)  
  79. {  
  80. SqlParameter[] parameters = new SqlParameter[]  
  81. {  
  82. new SqlParameter("@OSName",SEntity.OSName),  
  83.   
  84. };  
  85. return SqlHelper.ExecuteNonQuery("InsertSystemName", CommandType.StoredProcedure, parameters);  
  86. }  
  87.   
  88. public List<OperatingSystemEntity> GetSystemsData(OperatingSystemEntity SEntity)  
  89. {  
  90. List<OperatingSystemEntity> ListEntry = null;  
  91. SqlParameter[] parameters = new SqlParameter[]  
  92. {  
  93.   
  94. };  
  95. using (DataSet ds = SqlHelper.ExecuteParamerizedSelectCommand("GetSystemsData", CommandType.StoredProcedure, parameters))  
  96. {  
  97. if (ds.Tables.Count > 0)  
  98. {  
  99.   
  100. ListEntry = new List<OperatingSystemEntity>();  
  101.   
  102. foreach (DataRow row2 in ds.Tables[0].Rows)  
  103. {  
  104. OperatingSystemEntity entry = new OperatingSystemEntity();  
  105. entry.OSId = Convert.ToInt32(row2["OSId"].ToString());  
  106. entry.OSName = row2["OSName"].ToString();  
  107. entry.CreateDate = Convert.ToDateTime(row2["CreateDate"].ToString());  
  108. entry.Status = Convert.ToInt32(row2["Status"].ToString());  
  109. ListEntry.Add(entry);  
  110. }  
  111. }  
  112. }  
  113.   
  114. return ListEntry;  
  115. }  
  116.   
  117. public bool UpdateSystems(OperatingSystemEntity SEntity)  
  118. {  
  119. SqlParameter[] parameters = new SqlParameter[]  
  120. {  
  121. new SqlParameter("@Id",SEntity.OSId),  
  122. new SqlParameter("@Name",SEntity.OSName),  
  123. new SqlParameter("@Status",SEntity.Status),  
  124. };  
  125.   
  126. return SqlHelper.ExecuteNonQuery("UpdateSystems", CommandType.StoredProcedure, parameters);  
  127. }  
  128.   
  129. public bool DeleteSystem(OperatingSystemEntity SEntity)  
  130. {  
  131. SqlParameter[] parameters = new SqlParameter[]  
  132. {  
  133. new SqlParameter("@Id",SEntity.OSId),  
  134. };  
  135.   
  136. return SqlHelper.ExecuteNonQuery("DeleteSystemsData", CommandType.StoredProcedure, parameters);  
  137. }  
  138. }  

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:

  1. <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">  
  2. <script language="javascript" type="text/javascript">  
  3. function validate() {  
  4.   
  5. if (document.getElementById("<%=txtname.ClientID %>").value == "") {  
  6.   
  7. alert("Please Enter Name");  
  8. document.getElementById("<%=txtname.ClientID %>").focus();  
  9. return false;  
  10. }  
  11.   
  12. }  
  13. </script>  
  14. </asp:Content>  
  15. <asp:Content ID="Content2" ContentPlaceHolderID="Body" runat="server">  
  16. <div>  
  17. <h2 style="width: 80%">  
  18. <u>Create System</u></h2>  
  19. <table>  
  20. <tr>  
  21. <td colspan="2">  
  22. <asp:Label runat="server" ID="lblmsgerror" Font-Bold="true"></asp:Label>  
  23. </td>  
  24. </tr>  
  25. <tr>  
  26. <td>  
  27. <asp:Label ID="lblname" runat="server" Text="SystemName"></asp:Label>  
  28. </td>  
  29. <td>  
  30. <asp:TextBox ID="txtname" runat="server"></asp:TextBox>  
  31. </td>  
  32. </tr>  
  33. <tr>  
  34. <td>  
  35. </td>  
  36. <td>  
  37. <asp:Button ID="btnsave" runat="server" Text="Save" OnClick="btnsave_Click" />  
  38. </td>  
  39. </tr>  
  40. </table>  
  41. </div>  
  42. <h2 style="width: 80%">  
  43. <u>Total Availble Systems</u></h2>  
  44. <p>  
  45. <asp:Label runat="server" ID="lblmsg" ForeColor="Red" Font-Bold="true"></asp:Label>  
  46. </p>  
  47. <div>  
  48. <asp:GridView ID="gvSystem" runat="server" AutoGenerateColumns="false" OnRowUpdating="gvSystem_RowUpdating"  
  49. OnRowEditing="gvSystem_RowEditing" OnRowDeleting="gvSystem_RowDeleting" DataKeyNames="OSId"  
  50. OnRowCancelingEdit="gvSystem_RowCancelingEdit">  
  51. <Columns>  
  52. <asp:TemplateField>  
  53. <EditItemTemplate>  
  54. <asp:ImageButton ID="imgbtnUpdate" CommandName="Update" runat="server" ImageUrl="~/Images/Update.jpg"  
  55. ToolTip="Update" Height="20px" Width="20px" />  
  56. <asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/Cancel.jpg"  
  57. ToolTip="Cancel" Height="20px" Width="20px" />  
  58. </EditItemTemplate>  
  59. <ItemTemplate>  
  60. <asp:ImageButton ID="imgbtnEdit" CommandName="Edit" runat="server" ImageUrl="~/Images/Edit.jpg"  
  61. ToolTip="Edit" Height="20px" Width="20px" />  
  62. <asp:ImageButton ID="imgbtnDelete" CommandName="Delete" OnClientClick="return confirm('Are you sure you want to delete selected record?')"  
  63. Text="Edit" runat="server" ImageUrl="~/images/Delete.jpg" ToolTip="Delete" Height="20px"  
  64. Width="20px" />  
  65. </ItemTemplate>  
  66. </asp:TemplateField>  
  67. <asp:TemplateField HeaderText="System Name">  
  68. <EditItemTemplate>  
  69. <asp:TextBox ID="txtname" runat="server" Text='<%# Eval("OSName")%>'></asp:TextBox>  
  70. </EditItemTemplate>  
  71. <ItemTemplate>  
  72. <asp:Label ID="lblname" runat="server" Text='<%# Eval("OSName")%>'></asp:Label>  
  73. </ItemTemplate>  
  74. </asp:TemplateField>  
  75. <asp:TemplateField HeaderText="Date Of Created">  
  76. <ItemTemplate>  
  77. <asp:Label ID="lbldate" runat="server" Text='<%# Eval("CreateDate")%>'></asp:Label>  
  78. </ItemTemplate>  
  79. </asp:TemplateField>  
  80. <asp:TemplateField HeaderText="Status">  
  81. <EditItemTemplate>  
  82. <asp:TextBox ID="txtstatus" runat="server" Text='<%# Eval("Status")%>'></asp:TextBox>  
  83. </EditItemTemplate>  
  84. <ItemTemplate>  
  85. <asp:Label ID="lblstatus" runat="server" Text='<%# Eval("Status")%>'></asp:Label>  
  86. </ItemTemplate>  
  87. </asp:TemplateField>  
  88. </Columns>  
  89. </asp:GridView>  
  90. </div>  
  91. </asp:Content> 

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

  1. OperatingSystemEntity SEntity = new OperatingSystemEntity();  
  2. GridData GData = new GridData();  
  3.   
  4. protected void Page_Load(object sender, EventArgs e)  
  5. {  
  6. btnsave.Attributes.Add("onclick""return validate()");  
  7. if (!IsPostBack)  
  8. {  
  9. BindData();  
  10. }  
  11. }  
  12.   
  13. protected void btnsave_Click(object sender, EventArgs e)  
  14. {  
  15. SEntity.OSName = txtname.Text;  
  16.   
  17. if (GData.CreateSystem(SEntity) == true)  
  18. {  
  19. lblmsgerror.ForeColor = Color.Green;  
  20. lblmsgerror.Text = "System Name Saved Successfully";  
  21. BindData();  
  22. txtname.Text = "";  
  23. }  
  24.   
  25. else  
  26. {  
  27. lblmsgerror.ForeColor = Color.Red;  
  28. lblmsgerror.Text = "System Name Already Exists ";  
  29. }  
  30. }  
  31.   
  32. private void BindData()  
  33. {  
  34. List<OperatingSystemEntity> SList = GData.GetSystemsData(SEntity);  
  35.   
  36. if (SList.Count != 0)  
  37. {  
  38. gvSystem.DataSource = SList;  
  39. gvSystem.DataBind();  
  40. }  
  41. else  
  42. {  
  43. lblmsg.Text = "No Data found..";  
  44. }  
  45. }  
  46.   
  47. private static String GetTextFromRowBox(GridViewRow row, String field)  
  48. {  
  49. return ((TextBox)row.FindControl(field)).Text;  
  50. }  
  51.   
  52. protected void gvSystem_RowUpdating(object sender, GridViewUpdateEventArgs e)  
  53. {  
  54. int id = Convert.ToInt32(gvSystem.DataKeys[e.RowIndex].Values["OSId"].ToString());  
  55. GridViewRow row = gvSystem.Rows[e.RowIndex];  
  56.   
  57. SEntity.OSName = GetTextFromRowBox(row, "txtname");  
  58. SEntity.Status = Convert.ToInt32(GetTextFromRowBox(row, "txtstatus").ToString());  
  59. SEntity.OSId = id;  
  60.   
  61. if (GData.UpdateSystems(SEntity) == true)  
  62. {  
  63. gvSystem.EditIndex = -1;  
  64. BindData();  
  65. lblmsg.Text = "Records Updated sucessfully";  
  66. }  
  67.   
  68. else  
  69. {  
  70. lblmsg.Text = "Updation Failed";  
  71. }  
  72. }  
  73.   
  74. protected void gvSystem_RowEditing(object sender, GridViewEditEventArgs e)  
  75. {  
  76. gvSystem.EditIndex = e.NewEditIndex;  
  77. BindData();  
  78. }  
  79.   
  80. protected void gvSystem_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)  
  81. {  
  82. gvSystem.EditIndex = -1;  
  83. BindData();  
  84. }  
  85.   
  86. protected void gvSystem_RowDeleting(object sender, GridViewDeleteEventArgs e)  
  87. {  
  88. int ID = Convert.ToInt32(gvSystem.DataKeys[e.RowIndex].Values["OSId"].ToString());  
  89.   
  90. deleterecords(ID);  
  91. }  
  92.   
  93. private void deleterecords(int ID)  
  94. {  
  95. SEntity.OSId = ID;  
  96.   
  97. if (GData.DeleteSystem(SEntity) == true)  
  98. {  
  99. lblmsg.Text = "Records deleted sucessfully...";  
  100. BindData();  
  101. lblmsg.Text = "";  
  102. }  
  103. else  
  104. {  
  105. lblmsg.ForeColor = Color.Red;  
  106. lblmsg.Text = "Records deleted failed...";  
  107. BindData();  
  108. lblmsg.Text = "";  
  109. }  

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.