C# Account Class with CRUD Operations

C# Account Class

This C# code defines an Account class with methods for performing CRUD (Create, Read, Update, Delete) operations on a SQL Server database.

    
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Web;
      using System.Configuration;
      using System.Data.SqlClient;
    
  

Account Class Definition

    
      public class Account
      {
        private string connStr = ConfigurationManager.ConnectionStrings["DatabaseContext"].ConnectionString;
        private string _Login_ID, _Password, _Name, _Email;

        public string Login_ID
        {
          get { return _Login_ID; }
          set { _Login_ID = value; }
        }

        public Account()
        { /* Add more Constructors */ }

        public int Insert()
        {
          string query = "INSERT INTO Users(Login_ID, Password, Name, Email) Values(@Login_ID, @Password, @Name, @Email)";
          using (SqlConnection conn = new SqlConnection(connStr))
          {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
              cmd.Parameters.AddWithValue("@Login_ID", Login_ID);
              output = cmd.ExecuteNonQuery();
            }
          }
        }

        public int Update(string pLogin_ID, string pPassword, string pName, string pEmail)
        {
          string query = "UPDATE Users SET Password = @Password, Name = @Name, Email = @Email WHERE Login_ID = @Login_ID";
          using (SqlConnection conn = new SqlConnection(connStr))
          {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
              cmd.Parameters.AddWithValue("@Login_ID", pLogin_ID);
              output = cmd.ExecuteNonQuery();
            }
          }
        }

        public int Delete(string pLogin_ID)
        {
          string query = "DELETE FROM Users WHERE Login_ID = @Login_ID";
          using (SqlConnection conn = new SqlConnection(connStr))
          {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
              cmd.Parameters.AddWithValue("@Login_ID", pLogin_ID);
              output = cmd.ExecuteNonQuery();
            }
          }
        }

        public List<Account> Retrieve()
        {
          List<Account> aList = new List<Account>();
          string query = "SELECT * FROM Users Order By Login_ID";
          using (SqlConnection conn = new SqlConnection(connStr))
          {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
              SqlDataReader dr = cmd.ExecuteReader();
              if (dr != null) // If there is something to read
              {
                while (dr.Read())
                {
                  string nLogin_ID = dr["Login_ID"].ToString();
                  Account acc = new Account(nLogin_ID, nPassword, nName, nEmail);
                  aList.Add(acc);
                }
              }
            }
          }
          return aList;
        }
      }
    
  

ASP.NET GridView Integration

The following code snippets demonstrate how to integrate the Account class with an ASP.NET GridView control.

    
      <asp:GridView ID="gvAccount" runat="server"
        OnRowDeleting="gvAccount_RowDeleting"
        OnRowEditing="gvAccount_RowEditing"
        OnRowUpdating="gvAccount_RowUpdating"
        OnSelectedIndexChanged="gvAccount_SelectedIndexChanged"
        AutoGenerateColumns="False" OnRowCancelingEdit="gvAccount_RowCancelingEdit">
      </asp:GridView>
    
  

Code-Behind Logic

    
      Account acc = new Account();

      protected void Page_Load(object sender, EventArgs e)
      {
        if (!Page.IsPostBack)
        {
          bind();
        }
      }

      protected void bind()
      {
        List<Account> accList = new List<Account>();
        accList = acc.Retrieve();
        gvAccount.DataSource = accList;
        gvAccount.DataBind();
      }

      protected void gvAccount_SelectedIndexChanged(object sender, EventArgs e)
      {
        GridViewRow row = gvAccount.SelectedRow;
        string Login_ID = row.Cells[0].Text;
        Response.Redirect("AccountDetails.aspx?Login_ID=" + Login_ID);
      }

      protected void gvAccount_RowEditing(object sender, GridViewEditEventArgs e)
      {
        gvAccount.EditIndex = e.NewEditIndex;
        bind();
      }

      protected void gvAccount_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
      {
        gvAccount.EditIndex = -1;
        bind();
      }

      protected void gvAccount_RowDeleting(object sender, GridViewDeleteEventArgs e)
      {
        string Login_ID = gvAccount.DataKeys[e.RowIndex].Value.ToString();
        int result = acc.Delete(Login_ID);
        if (result > 0)
        {
          Response.Write("<script>alert('Success');</script>");
          bind();
        }
      }

      protected void gvAccount_RowUpdating(object sender, GridViewUpdateEventArgs e)
      {
        GridViewRow row = (GridViewRow)gvAccount.Rows[e.RowIndex];
        string Login_ID = ((TextBox)row.Cells[0].Controls[0]).Text;
        string Password = ((TextBox)row.Cells[1].Controls[0]).Text;
        string Name = ((TextBox)row.Cells[2].Controls[0]).Text;
        string Email = ((TextBox)row.Cells[3].Controls[0]).Text;
        int result = acc.Update(Login_ID, Password, Name, Email);
        if (result > 0)
          gvAccount.EditIndex = -1;
        bind();
      }

      protected void btn_Insert_Click(object sender, EventArgs e)
      {
        Response.Redirect("~/insert.aspx");
      }
    
  

Database Connection String

The connection string used in this example is:

    
      <add name="DatabaseContext" connectionString="AttachDbFilename=|DataDirectory|\Database.mdf; Integrated Security=True" providerName="System.Data.SqlClient"/>