public List<Role> SelectRole()
        {
            using (SqlConnection conn = DBHelp.GetConnection())
            {
                string sql = @"select RoleID,RoleName from Role";

                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = sql;
                conn.Open();

                SqlDataReader sdr = cmd.ExecuteReader();

                List<Role> list = new List<Role>();

                while (sdr.Read())
                {
                    Role role = new Role();
                    role.RoleId = Convert.ToInt32(sdr["RoleID"]);
                    role.RoleName = sdr["RoleName"].ToString();

                    list.Add(role);
                }

                sdr.Close();

                conn.Close();

                return list;

            }
        }
 public static bool InsertRoles(Role role)
 {
     if (RoleService.InsertRole(role))
     {
         return true;
     }
     else
     {
         return false;
     }
 }
        public static bool InsertRole(Role role)
        {
            using (SqlConnection conn = DBHelp.GetConnection())
            {
                string sql = @"insert into Role (RoleName) values (@RoleName)";

                SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@RoleName",role.RoleName)
            };

                int i = DBHelp.ExecuteCommand(sql, para);
                if (i > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        private Users PopUserFromDataReader(IDataReader dr)
        {
            Users u = new Users();

            u.UserID = dr["UserID"].ToString();
            u.UserName = dr["UserName"].ToString();
            u.UserPwd = dr["UserPwd"].ToString();

            Role role = new Role();

            role.RoleId = Convert.ToInt32(dr["RoleId"]);
            role.RoleName = dr["RoleName"].ToString();

            u.Role = role;

            return u;
        }
        public List<Users> SelectUserName(string userName)
        {
            using (SqlConnection conn = DBHelp.GetConnection())
            {
                string sql = @"select UserId,UserName,RoleName from Users,[Role]where Users.roleId  = [Role].roleId and UserName='******' ";
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;

                conn.Open();

                SqlDataReader sdr = cmd.ExecuteReader();

                List<Users> list = new List<Users>();

                while (sdr.Read())
                {
                    Users user = new Users();
                    user.UserID = sdr["userID"].ToString();
                    user.UserName = sdr["userName"].ToString();

                    Role role = new Role();
                    role.RoleName = sdr["RoleName"].ToString();
                    user.RoleName = role.RoleName;

                    list.Add(user);
                }

                sdr.Close();

                conn.Close();

                return list;
            }
        }