UserDataAccess.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data.Sql;
using OT_lib.Entity;
using OT_lib;

namespace OT_lib.DataAccess
{
    public class UsersDataAccess  {

        sqlConnection _connection = null;

        public UsersDataAccess(string key)
        {
            _connection = new sqlConnection(key);
        }

        public int CreateUser(UserEntity user){

             SqlConnection oCon = null;
             SqlCommand oCmd = null;
            try
            {

               oCon = _connection.openConn();
                string sSql = string.Format("INSERT INTO Users VALUES ('{0}','{1}','{2}')", user.username, user.email, user.password);
                oCmd = new SqlCommand(sSql, oCon);

                oCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                //clean
                oCon.Close();
                oCmd.Dispose();

                oCmd = null;
                oCon = null;
            }
            return 0;
        }

        public int EditUser(UserEntity userEntity) {

            SqlConnection oCon = null;
            SqlCommand oCmd = null;

            try
            {
                oCon = _connection.openConn();
                oCmd.Parameters.Add(new SqlParameter("@usr_Id", userEntity.usr_Id));
                oCmd.Parameters.Add(new SqlParameter("@username", userEntity.username));
                oCmd.Parameters.Add(new SqlParameter("@email", userEntity.email));
                oCmd.Parameters.Add(new SqlParameter("@password", userEntity.password));

                string sqlCmd = string.Format("UPDATE Users SET username = @username, email = @email, @password = password WHERE  usr_Id = @usr_Id");
                oCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                //clean
                oCon.Close();
                oCmd.Dispose();

                oCmd = null;
                oCon = null;
            }

            return 0;
        }

        public int GetUserId() {


            return 0;
        }

        public List<UserEntity> GetAllUsers(){

            SqlConnection oCon = null;
            SqlCommand oCmd = null;
            List<UserEntity> Users = null;
            SqlDataReader dr;

            try
            {
                oCon = _connection.openConn();
                string sql = string.Format("SELECT * FROM Users");
                oCmd.CommandText = sql;
                dr = oCmd.ExecuteReader();

                if (dr.HasRows)
                {
                    Users = new List<UserEntity>();

                    while (dr.Read())
                    {
                        UserEntity user = new UserEntity();
                        user.email = dr["email"].ToString();
                        user.password = dr["password"].ToString();
                        user.username = dr["username"].ToString();
                        user.usr_Id = Convert.ToInt32(dr["usr_Id"]);
                        Users.Add(user);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return Users;
        }
    }
}