adonet.cs

namespace DotNetCoreRepository.Repository
{
    public class EmployeeRepository : IEmployeeRepository
    {
        private readonly IConfiguration configuration;
        public EmployeeRepository(IConfiguration configuration)
        {
            this.configuration = configuration;
        }
        public void AddUpdateEmployee(EmployeeModel em)
        {
            using (var con = new SqlConnection(this.Sqlconnection()))
            {
                try
                {
                    SqlCommand cmd;
                    con.Open();
                    cmd = new SqlCommand("Employee_InsertUpdate", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserID", em.UserID);
                    cmd.Parameters.AddWithValue("@UserName", em.UserName);
                    cmd.Parameters.AddWithValue("@City", em.City);
                    cmd.Parameters.AddWithValue("@State", em.State);
                    cmd.Parameters.AddWithValue("@Mobile", em.Mobile);
                    cmd.Parameters.AddWithValue("@Profile", em.Profile);
                    cmd.ExecuteNonQuery();
                    con.Close();

                }
                catch (Exception)
                {

                    throw;
                }
            
            }
        }
        public void DeleteEmployee(int id)
        {
            try
            {
                using (var con = new SqlConnection(this.Sqlconnection()))
                {
                    SqlCommand cmd;
                    con.Open();
                    cmd = new SqlCommand("Employee_Delete", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserID", id);
                    cmd.ExecuteNonQuery();
                    con.Close();

                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        public List GetAllEmployee()
        {
            try
            {
                using (var con = new SqlConnection(this.Sqlconnection()))
                {
                    DataTable dt = new DataTable();
                    SqlDataAdapter adp = new SqlDataAdapter("EMPLOYEE_SELECT", con);
                    adp.Fill(dt);

                    List lst = new List();

                    foreach (DataRow dr in dt.Rows)
                    {
                        EmployeeModel em = new EmployeeModel();
                        em.UserID = Convert.ToInt32(dr["UserID"]);
                        em.UserName = Convert.ToString(dr["UserName"]);
                        em.Mobile = Convert.ToString(dr["Mobile"]);
                        em.Profile = Convert.ToString(dr["Profile"]);
                        em.State = Convert.ToString(dr["State"]);
                        em.City = Convert.ToString(dr["City"]);
                        lst.Add(em);
                    }
                    return lst;
                }

            }
            catch (Exception)
            {
                throw;
            }
        }
        public EmployeeModel GetEmployee(int id)
        {
            try
            {
                using (var con = new SqlConnection(this.Sqlconnection()))
                {
                    EmployeeModel em = new EmployeeModel();
                    con.Open();
                    SqlCommand cmd = new SqlCommand("EMPLOYEE_SELECT", con);
                    cmd.Parameters.AddWithValue("UserID", id);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        em.UserID = Convert.ToInt32(dr["UserID"]);
                        em.UserName = Convert.ToString(dr["UserName"]);
                        em.Mobile = Convert.ToString(dr["Mobile"]);
                        em.Profile = Convert.ToString(dr["Profile"]);
                        em.State = Convert.ToString(dr["State"]);
                        em.City = Convert.ToString(dr["City"]);
                    }
                    con.Close();
                    return em;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        private string Sqlconnection()
        {
            return configuration.GetConnectionString("MySqlCon").ToString();
        }
    }
}