Select the SQL Server data in the WPF application


Having problems with accessing what is inside my list in a wpf application of a mafia game I am creating.

Basically I read from SQL Server 2016, then add it to my user collection list. Later when I use my list in a display, they all are there.

However the moment I use a foreach to loop through it to set a temp user equal to a found username, it only finds hard coded users and not ones added using the data read from SQL Server. Need help.

SQL Server read code:

using (connect = new SqlConnection(connetionString))

    string readString = "select * from Users";
    SqlCommand readCommand = new SqlCommand(readString, connect);

    using (SqlDataReader dataRead = readCommand.ExecuteReader())
        if (dataRead != null)
            while (dataRead.Read())
                tempEmail = dataRead["Email"].ToString();
                tempName = dataRead["Name"].ToString();

                UserCollection.addUser(tempEmail, tempName);


UserCollection relevant parts

private static List<User> UserList = new List<User>();

// add a user
public static void addUser(string email, string name)
        UserList.Add(new User(email, name, 0, "unset", false, false, false, false, false, false,
            false, "", false, false, false, 0, 0));

//return list of users for use elsewhere
public static List<User> ReturnUserList()
        return UserList;

Use of a list to set tempPlayer in a wpf window

PlayersList = UserCollection.ReturnUserList();

// tempPlayer = UserCollection.ReturnAUser(sessionUser);
foreach (var element in PlayersList)
    if (element.UserName == sessionUser)
        tempPlayer = element;

Example of code where the list works.

// set listing of current players
ListOfPlayers = UserCollection.ReturnUserList();

var tempList = from player in ListOfPlayers
               where player.UserBlocked == false
               select new
                         Name = player.UserName,
                         Email = player.UserEmail,

this.PlayerListBox.ItemsSource = tempList;

hard coded User add that works fine and can be found by foreach statement from my app.xaml.cs

UserCollection.addUser("g", "Tom");

Firstly, is there a reason why you need a static method to add users to a collection? Even if you need access to the list via a static accessor, you are better having a static property on the same class which you're using to read the DB

The following snippet should hopefully be of some help.

public class UserManagement {

    //Static property
    private static List<User> _users;

    public static List<User> Users {
        get {
            if (_users == null) {
                _user = new List<User>();
            return _users;
        set { }

    //Static load method must be called before accessing Users
    public static void LoadDBUsers() {

        using (SqlConnection connection = new SqlConnection(connetionString)) {
            string readString = "select * from Users";
            using (SqlCommand command = new SqlCommand(readString, connection)) {
                using (SqlDataReader reader = command.ExecuteReader()) {
                    while (reader.Read()) {
                        String tempEmail = reader["Email"].ToString();
                        String tempName = reader["Name"].ToString();
                        User user = new User(tempEmail, tempName, 0, "unset", false, false, false, false, false, false, false, "", false, false, false, 0, 0));

To use from another class :

var dbUsers = UserManagement.Users;

If you have another list of users (say from a file), you could add a loader method to your class which will handle that for you.

If at some point you need to clear down the list of users, you can simply set it to a new instance...

UserManagement.Users = new List<User>();

A slightly better way to do this would be to remove the static property Users first, change the method definition to return a List<User> from the LoadDBUsers method - eg.

public static List<User> LoadDBUsers() {
    List<User> Users = new List<User>();

    //Handle the database logic as in the previous example..
    //Then at the end of the method..
    return Users;

and call as follows

List<User> dbUsers = UserManagement.LoadDBUsers();

Using the latter approach, you don't need to worry about multiple locations in your code maintaining a static property. Just call and assign it to a variable. It also has the advantage that you will always get the most current list of users from the DB without having to clear down and reload the list before you access it from the static property.

An added advantage of not using a global static property is that it can avoid potential memory issues. Static properties can be difficult to dispose by the garbage collector if a reference to them is held open. With instance variables, it's quite obvious when one goes out of scope and is not referenced anymore, but with static variables, the reference is sometimes not disposed until the program ends.

In many cases, this isn't an issue, but in larger systems it can be a pain to debug.