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]
  3. CREATE TABLE [dbo].[OperatingSystem](
  4. [OSId] [int] IDENTITY(1,1) NOT NULL,
  5. [OSName] [varchar](100) NULL,
  6. [CreateDate] [datetime] NULL,
  7. [Status] [smallint] NULL,
  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 = ON) ON [PRIMARY]
  12. ) ON [PRIMARY]
  14. GO
  16. SET ANSI_PADDING OFF
  17. GO
  19. ALTER TABLE [dbo].[OperatingSystem] ADD DEFAULT (getdate()) FOR [CreateDate]
  20. GO
  22. ALTER TABLE [dbo].[OperatingSystem] ADD DEFAULT ('1') FOR [Status]
  23. GO
  25. Stored Procedures are,
  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
  37. //Get Data Procedure
  38. ALTER Procedure [dbo].[GetSystemsData]
  39. As
  40. Begin
  41. select * from OperatingSystem where Status='1'
  42. End
  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
  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
  66. End
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;
    3. public static DataSet ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SqlParameter[] param) {
    4. DataSet ds = new DataSet();
    6. using(SqlConnection con = new SqlConnection(CONNECTION_STRING)) {
    7. using(SqlCommand cmd = con.CreateCommand()) {
    8. cmd.CommandType = cmdType;
    9. cmd.CommandText = CommandName;
    10. cmd.Parameters.AddRange(param);
    11. try {
    12. if (con.State != ConnectionState.Open) {
    13. con.Open();
    14. }
    16. using(SqlDataAdapter da = new SqlDataAdapter(cmd)) {
    17. da.Fill(ds);
    18. }
    19. } catch {
    20. throw;
    21. }
    22. }
    23. }
    24. return ds;
    25. }
    27. // This function will be used to execute CUD(CRUD) operation of parameterized commands
    28. public static bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars) {
    29. int result = 0;
    31. using(SqlConnection con = new SqlConnection(CONNECTION_STRING)) {
    32. using(SqlCommand cmd = con.CreateCommand()) {
    33. cmd.CommandType = cmdType;
    34. cmd.CommandText = CommandName;
    35. cmd.Parameters.AddRange(pars);
    37. try {
    38. if (con.State != ConnectionState.Open) {
    39. con.Open();
    40. }
    41. result = cmd.ExecuteNonQuery();
    42. } catch {
    43. throw;
    44. }
    45. }
    46. }
    47. return (result > 0);
    48. }
    50. //Getting To EntityLayer, add a class and named as GridData.CS and write following code:
    51. public class OperatingSystemEntity {
    52. public int OSId {
    53. get;
    54. set;
    55. }
    56. public string OSName {
    57. get;
    58. set;
    59. }
    60. public DateTime CreateDate {
    61. get;
    62. set;
    63. }
    64. public int Status {
    65. get;
    66. set;
    67. }
    68. }
    70. //Getting to DAL, Add a class and named as GridData.cs and write the following code:
    71. public class GridData {
    72. public bool CreateSystem(OperatingSystemEntity SEntity) {
    73. SqlParameter[] parameters = new SqlParameter[] {
    74. new SqlParameter("@OSName", SEntity.OSName),
    76. };
    77. return SqlHelper.ExecuteNonQuery("InsertSystemName", CommandType.StoredProcedure, parameters);
    78. }
    80. public List < OperatingSystemEntity > GetSystemsData(OperatingSystemEntity SEntity) {
    81. List < OperatingSystemEntity > ListEntry = null;
    82. SqlParameter[] parameters = new SqlParameter[] {
    84. };
    85. using(DataSet ds = SqlHelper.ExecuteParamerizedSelectCommand("GetSystemsData", CommandType.StoredProcedure, parameters)) {
    86. if (ds.Tables.Count > 0) {
    88. ListEntry = new List < OperatingSystemEntity > ();
    90. foreach(DataRow row2 in ds.Tables[0].Rows) {
    91. OperatingSystemEntity entry = new OperatingSystemEntity();
    92. entry.OSId = Convert.ToInt32(row2["OSId"].ToString());
    93. entry.OSName = row2["OSName"].ToString();
    94. entry.CreateDate = Convert.ToDateTime(row2["CreateDate"].ToString());
    95. entry.Status = Convert.ToInt32(row2["Status"].ToString());
    96. ListEntry.Add(entry);
    97. }
    98. }
    99. }
    101. return ListEntry;
    102. }
    104. public bool UpdateSystems(OperatingSystemEntity SEntity) {
    105. SqlParameter[] parameters = new SqlParameter[] {
    106. new SqlParameter("@Id", SEntity.OSId),
    107. new SqlParameter("@Name", SEntity.OSName),
    108. new SqlParameter("@Status", SEntity.Status),
    109. };
    111. return SqlHelper.ExecuteNonQuery("UpdateSystems", CommandType.StoredProcedure, parameters);
    112. }
    114. public bool DeleteSystem(OperatingSystemEntity SEntity) {
    115. SqlParameter[] parameters = new SqlParameter[] {
    116. new SqlParameter("@Id", SEntity.OSId),
    117. };
    119. return SqlHelper.ExecuteNonQuery("DeleteSystemsData", CommandType.StoredProcedure, parameters);
    120. }
    121. }
    122. }
    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() {
      5. if (document.getElementById("<%=txtname.ClientID %>").value == "") {
      7. alert("Please Enter Name");
      8. document.getElementById("<%=txtname.ClientID %>").focus();
      9. return false;
      10. }
      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" OnRowEditing="gvSystem_RowEditing" OnRowDeleting="gvSystem_RowDeleting" DataKeyNames="OSId" OnRowCancelingEdit="gvSystem_RowCancelingEdit">
      49. <Columns>
      50. <asp:TemplateField>
      51. <EditItemTemplate>
      52. <asp:ImageButton ID="imgbtnUpdate" CommandName="Update" runat="server" ImageUrl="~/Images/Update.jpg" ToolTip="Update" Height="20px" Width="20px" />
      53. <asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/Cancel.jpg" ToolTip="Cancel" Height="20px" Width="20px" />
      54. </EditItemTemplate>
      55. <ItemTemplate>
      56. <asp:ImageButton ID="imgbtnEdit" CommandName="Edit" runat="server" ImageUrl="~/Images/Edit.jpg" ToolTip="Edit" Height="20px" Width="20px" />
      57. <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" />
      58. </ItemTemplate>
      59. </asp:TemplateField>
      60. <asp:TemplateField HeaderText="System Name">
      61. <EditItemTemplate>
      62. <asp:TextBox ID="txtname" runat="server" Text='<%# Eval("OSName")%>'></asp:TextBox>
      63. </EditItemTemplate>
      64. <ItemTemplate>
      65. <asp:Label ID="lblname" runat="server" Text='<%# Eval("OSName")%>'></asp:Label>
      66. </ItemTemplate>
      67. </asp:TemplateField>
      68. <asp:TemplateField HeaderText="Date Of Created">
      69. <ItemTemplate>
      70. <asp:Label ID="lbldate" runat="server" Text='<%# Eval("CreateDate")%>'></asp:Label>
      71. </ItemTemplate>
      72. </asp:TemplateField>
      73. <asp:TemplateField HeaderText="Status">
      74. <EditItemTemplate>
      75. <asp:TextBox ID="txtstatus" runat="server" Text='<%# Eval("Status")%>'></asp:TextBox>
      76. </EditItemTemplate>
      77. <ItemTemplate>
      78. <asp:Label ID="lblstatus" runat="server" Text='<%# Eval("Status")%>'></asp:Label>
      79. </ItemTemplate>
      80. </asp:TemplateField>
      81. </Columns>
      82. </asp:GridView>
      83. </div>
      84. </asp:Content>
      In NewGrid.aspx.cs file, write the following code:
        1. OperatingSystemEntity SEntity = new OperatingSystemEntity();
        2. GridData GData = new GridData();
        4. protected void Page_Load(object sender, EventArgs e) {
        5. btnsave.Attributes.Add("onclick", "return validate()");
        6. if (!IsPostBack) {
        7. BindData();
        8. }
        9. }
        11. protected void btnsave_Click(object sender, EventArgs e) {
        12. SEntity.OSName = txtname.Text;
        14. if (GData.CreateSystem(SEntity) == true) {
        15. lblmsgerror.ForeColor = Color.Green;
        16. lblmsgerror.Text = "System Name Saved Successfully";
        17. BindData();
        18. txtname.Text = "";
        19. } else {
        20. lblmsgerror.ForeColor = Color.Red;
        21. lblmsgerror.Text = "System Name Already Exists ";
        22. }
        23. }
        25. private void BindData() {
        26. List < OperatingSystemEntity > SList = GData.GetSystemsData(SEntity);
        28. if (SList.Count != 0) {
        29. gvSystem.DataSource = SList;
        30. gvSystem.DataBind();
        31. } else {
        32. lblmsg.Text = "No Data found..";
        33. }
        34. }
        36. private static String GetTextFromRowBox(GridViewRow row, String field) {
        37. return ((TextBox) row.FindControl(field)).Text;
        38. }
        40. protected void gvSystem_RowUpdating(object sender, GridViewUpdateEventArgs e) {
        41. int id = Convert.ToInt32(gvSystem.DataKeys[e.RowIndex].Values["OSId"].ToString());
        42. GridViewRow row = gvSystem.Rows[e.RowIndex];
        44. SEntity.OSName = GetTextFromRowBox(row, "txtname");
        45. SEntity.Status = Convert.ToInt32(GetTextFromRowBox(row, "txtstatus").ToString());
        46. SEntity.OSId = id;
        48. if (GData.UpdateSystems(SEntity) == true) {
        49. gvSystem.EditIndex = -1;
        50. BindData();
        51. lblmsg.Text = "Records Updated sucessfully";
        52. } else {
        53. lblmsg.Text = "Updation Failed";
        54. }
        55. }
        57. protected void gvSystem_RowEditing(object sender, GridViewEditEventArgs e) {
        58. gvSystem.EditIndex = e.NewEditIndex;
        59. BindData();
        60. }
        62. protected void gvSystem_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) {
        63. gvSystem.EditIndex = -1;
        64. BindData();
        65. }
        67. protected void gvSystem_RowDeleting(object sender, GridViewDeleteEventArgs e) {
        68. int ID = Convert.ToInt32(gvSystem.DataKeys[e.RowIndex].Values["OSId"].ToString());
        70. deleterecords(ID);
        71. }
        73. private void deleterecords(int ID) {
        74. SEntity.OSId = ID;
        76. if (GData.DeleteSystem(SEntity) == true) {
        77. lblmsg.Text = "Records deleted sucessfully...";
        78. BindData();
        79. lblmsg.Text = "";
        80. } else {
        81. lblmsg.ForeColor = Color.Red;
        82. lblmsg.Text = "Records deleted failed...";
        83. BindData();
        84. lblmsg.Text = "";
        85. }
        86. }
        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.