Post
Ask Question

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] [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]  
  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.     DataSet ds = new DataSet();  
    5.   
    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.                 }  
    15.   
    16.                 using(SqlDataAdapter da = new SqlDataAdapter(cmd)) {  
    17.                     da.Fill(ds);  
    18.                 }  
    19.             } catch {  
    20.                 throw;  
    21.             }  
    22.         }  
    23.     }  
    24.     return ds;  
    25. }  
    26.   
    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;  
    30.   
    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);  
    36.   
    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. }  
    49.   
    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. }  
    69.   
    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),  
    75.   
    76.         };  
    77.         return SqlHelper.ExecuteNonQuery("InsertSystemName", CommandType.StoredProcedure, parameters);  
    78.     }  
    79.   
    80.     public List < OperatingSystemEntity > GetSystemsData(OperatingSystemEntity SEntity) {  
    81.         List < OperatingSystemEntity > ListEntry = null;  
    82.         SqlParameter[] parameters = new SqlParameter[] {  
    83.   
    84.         };  
    85.         using(DataSet ds = SqlHelper.ExecuteParamerizedSelectCommand("GetSystemsData", CommandType.StoredProcedure, parameters)) {  
    86.             if (ds.Tables.Count > 0) {  
    87.   
    88.                 ListEntry = new List < OperatingSystemEntity > ();  
    89.   
    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.         }  
    100.   
    101.         return ListEntry;  
    102.     }  
    103.   
    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.         };  
    110.   
    111.         return SqlHelper.ExecuteNonQuery("UpdateSystems", CommandType.StoredProcedure, parameters);  
    112.     }  
    113.   
    114.     public bool DeleteSystem(OperatingSystemEntity SEntity) {  
    115.         SqlParameter[] parameters = new SqlParameter[] {  
    116.             new SqlParameter("@Id", SEntity.OSId),  
    117.         };  
    118.   
    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() {  
      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" 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();  
        3.   
        4. protected void Page_Load(object sender, EventArgs e) {  
        5.     btnsave.Attributes.Add("onclick", "return validate()");  
        6.     if (!IsPostBack) {  
        7.         BindData();  
        8.     }  
        9. }  
        10.   
        11. protected void btnsave_Click(object sender, EventArgs e) {  
        12.     SEntity.OSName = txtname.Text;  
        13.   
        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. }  
        24.   
        25. private void BindData() {  
        26.     List < OperatingSystemEntity > SList = GData.GetSystemsData(SEntity);  
        27.   
        28.     if (SList.Count != 0) {  
        29.         gvSystem.DataSource = SList;  
        30.         gvSystem.DataBind();  
        31.     } else {  
        32.         lblmsg.Text = "No Data found..";  
        33.     }  
        34. }  
        35.   
        36. private static String GetTextFromRowBox(GridViewRow row, String field) {  
        37.     return ((TextBox) row.FindControl(field)).Text;  
        38. }  
        39.   
        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];  
        43.   
        44.     SEntity.OSName = GetTextFromRowBox(row, "txtname");  
        45.     SEntity.Status = Convert.ToInt32(GetTextFromRowBox(row, "txtstatus").ToString());  
        46.     SEntity.OSId = id;  
        47.   
        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. }  
        56.   
        57. protected void gvSystem_RowEditing(object sender, GridViewEditEventArgs e) {  
        58.     gvSystem.EditIndex = e.NewEditIndex;  
        59.     BindData();  
        60. }  
        61.   
        62. protected void gvSystem_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) {  
        63.     gvSystem.EditIndex = -1;  
        64.     BindData();  
        65. }  
        66.   
        67. protected void gvSystem_RowDeleting(object sender, GridViewDeleteEventArgs e) {  
        68.     int ID = Convert.ToInt32(gvSystem.DataKeys[e.RowIndex].Values["OSId"].ToString());  
        69.   
        70.     deleterecords(ID);  
        71. }  
        72.   
        73. private void deleterecords(int ID) {  
        74.     SEntity.OSId = ID;  
        75.   
        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.


        Similar Articles