ADO_NET CRUD

AdoCRUD.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.SqlClient;

namespace WebApplication9
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        public class Employee
        {
            public int ID { get; set; }
            [Required]
            public string Name { get; set; }
            [Required]
            public string Gender { get; set; }
            [Required]
            public string Department { get; set; }
            [Required]
            public string City { get; set; }
        }

        public class EmployeeDataAccessLayer
        {
               //<add name="OscarConnectionString" 
               // connectionString="Data Source=NITRO;Initial Catalog=DataBaseName;Persist Security Info=True;User ID=sa;Password=Passs:P"
               // providerName="System.Data.SqlClient"/>

            //string connectionString = "Data Source=NITRO;Initial Catalog=v15_JockeyTestComp01;Persist Security Info=True;User ID=sa;Password=Microsoft201";
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OscarConnectionString"].ConnectionString;

            //To View all employees details    
            public IEnumerable<Employee> GetAllEmployees()
            {
                List<Employee> lstemployee = new List<Employee>();

                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spGetAllEmployees", con);
                    cmd.CommandType = CommandType.StoredProcedure;

                    con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        Employee employee = new Employee();

                        employee.ID = Convert.ToInt32(rdr["EmployeeID"]);
                        employee.Name = rdr["Name"].ToString();
                        employee.Gender = rdr["Gender"].ToString();
                        employee.Department = rdr["Department"].ToString();
                        employee.City = rdr["City"].ToString();

                        lstemployee.Add(employee);
                    }
                    con.Close();
                }
                return lstemployee;
            }

            //To Add new employee record    
            public void AddEmployee(Employee employee)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spAddEmployee", con);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@Name", employee.Name);
                    cmd.Parameters.AddWithValue("@Gender", employee.Gender);
                    cmd.Parameters.AddWithValue("@Department", employee.Department);
                    cmd.Parameters.AddWithValue("@City", employee.City);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }

            //To Update the records of a particluar employee  
            public void UpdateEmployee(Employee employee)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spUpdateEmployee", con);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@EmpId", employee.ID);
                    cmd.Parameters.AddWithValue("@Name", employee.Name);
                    cmd.Parameters.AddWithValue("@Gender", employee.Gender);
                    cmd.Parameters.AddWithValue("@Department", employee.Department);
                    cmd.Parameters.AddWithValue("@City", employee.City);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }

            //Get the details of a particular employee  
            public Employee GetEmployeeData(int? id)
            {
                Employee employee = new Employee();

                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    string sqlQuery = "SELECT * FROM tblEmployee WHERE EmployeeID= " + id;
                    SqlCommand cmd = new SqlCommand(sqlQuery, con);

                    con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        employee.ID = Convert.ToInt32(rdr["EmployeeID"]);
                        employee.Name = rdr["Name"].ToString();
                        employee.Gender = rdr["Gender"].ToString();
                        employee.Department = rdr["Department"].ToString();
                        employee.City = rdr["City"].ToString();
                    }
                }
                return employee;
            }

            //To Delete the record on a particular employee  
            public void DeleteEmployee(int? id)
            {

                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spDeleteEmployee", con);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@EmpId", id);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }  
        protected void Page_Load(object sender, EventArgs e)
        {


        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            var dato = new EmployeeDataAccessLayer();
            var employee = new Employee
            {
                Name = "oscar",
                Gender = "Male",
                Department = "La Libertad",
                
                https://1drv.ms/u/s!Amq5h9JNJnfygsFVvTbrM5wwI_MgGA
                
                City = "El Salvador"
            };
            dato.AddEmployee(employee);
        }
    }
}