UserDB.cs

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

namespace CRUDOperationAJAX.Models
{
    public class UserDB
    {
        //declare connection string
        string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        //Return list of all Users
        public List<User> ListAll()
        {
            List<User> lst = new List<User>();
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                SqlCommand com = new SqlCommand("SelectUser", con);
                com.CommandType = CommandType.StoredProcedure;
                SqlDataReader rdr = com.ExecuteReader();
                while (rdr.Read())
                {
                    lst.Add(new User
                    {
                        UserId = Convert.ToInt32(rdr["UserId"]),
                        UserName = rdr["UserName"].ToString(),
                        IsActive = Convert.ToBoolean(rdr["IsActive"]),
                        Password = rdr["Password"].ToString(),

                    });
                }
                return lst;
            }
        }

        //Method for Adding an User
        public bool Add(User usr)
        {

            int i;
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                SqlCommand com = new SqlCommand("InsertUpdateUser", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@Id", usr.UserId);
                com.Parameters.AddWithValue("@UserName", usr.UserName);
                com.Parameters.AddWithValue("@Password", usr.Password);
                com.Parameters.AddWithValue("@IsActive", usr.IsActive);
                com.Parameters.AddWithValue("@Action", "Insert");
                i = com.ExecuteNonQuery();
            }
            if(i==1)
            return true;
            return false;
        }

        //Method for Updating User record
        public bool Update(User usr)
        {
            int i;
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                SqlCommand com = new SqlCommand("InsertUpdateUser", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@Id", usr.UserId);
                com.Parameters.AddWithValue("@UserName", usr.UserName);
                com.Parameters.AddWithValue("@Password", usr.Password);
                com.Parameters.AddWithValue("@IsActive", usr.IsActive);
                com.Parameters.AddWithValue("@Action", "Update");
                i = com.ExecuteNonQuery();
            }
            if (i == 1)
                return true;
            return false;
        }

        //Method for Deleting an User
        public bool Delete(int ID)
        {
            int i;
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                SqlCommand com = new SqlCommand("DeleteUser", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@Id", ID);
                i = com.ExecuteNonQuery();
            }
            if (i == 1)
                return true;
            return false;
        }

        public bool AddUser(User usr)
        {

            int i;
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                SqlCommand com = new SqlCommand("InsertUpdateUser", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@Id", usr.UserId);
                com.Parameters.AddWithValue("@UserName", usr.UserName);
                com.Parameters.AddWithValue("@Password", usr.Password);
                com.Parameters.AddWithValue("@IsActive", usr.IsActive);
                com.Parameters.AddWithValue("@Action", "Insert");
                i = com.ExecuteNonQuery();
            }
            if (i == 1)
                return true;
            return false;
        }

    }
}