I want to make a loop which adds an object to a list. Whenever I do this it seems to keep adding the same object.

private List<Events> GetEvents()
        {
            List<Events> dataEvents = new List<Events>();
            MySqlConnection con = new MySqlConnection(cs);
            con.Open();
            string sql = "SELECT * FROM Events";
            var cmd = new MySqlCommand(sql, con);
            MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Events x = new Events(rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2), rdr.GetInt32(3), rdr.GetDateTime(4));
                dataEvents.Add(x);
            }

            con.Close();
            return dataEvents;
        }

List<Events> newEvent = GetEvents();

Here's how I constructed my Events class:

public class Events
    {
        private static int _ID;
        private static string _Type;
        private static int _UserID;
        private static int _LokaalID;
        private static DateTime _Created_On;
        public Events(int id, string type, int UserID, int LokaalID, DateTime Created_On)
        {
            _ID = id;
            _Type = type;
            _UserID = UserID;
            _LokaalID = LokaalID;
            _Created_On = Created_On;
        }


    public int ID { get { return _ID; } }
    public string Type { get { return _Type; } }
    public int UserID { get { return _UserID; } }
    public int LokaalID { get { return _LokaalID; } }
    public DateTime Created_On { get { return _Created_On; } }

    }

        private List<Events> GetEvents()
        {
            using var con = new MySqlConnection(cs);
            return conn.Query<Event>("SELECT * FROM Events").ToList();
        }

Yep.. That's all you have to do*; Dapper will make the objects, open/close the connection, read the data, populate it into the objects etc..

*Well, unless your C# property names don't align with the column names, in which case, use AS to rename your columns so they do match:

//assume the column is called EventId and the C# prop is ID
conn.Query<Event>("SELECT EventId as ID, ... FROM Events").ToList()

To do parameterized queries:

conn.Query<Event>("SELECT * FROM Events WHERE ID = @someId", new { someId = 123 }).ToList()

It does boatloads more, but this should get you started and mean you don't have to spend hours writing the most tedious GetInt, GetString, Get.. Get.. code known to humankind..

CodePudding user response：

Remove the static modifier from your fields:

public class Events
{
    private int _ID;
    private string _Type;
    private int _UserID;
    private int _LokaalID;
    private DateTime _Created_On;

    public Events(int id, string type, int UserID, int LokaalID, DateTime Created_On)
    {
        _ID = id;
        _Type = type;
        _UserID = UserID;
        _LokaalID = LokaalID;
        _Created_On = Created_On;
    }


    public int ID { get { return _ID; } }
    public string Type { get { return _Type; } }
    public int UserID { get { return _UserID; } }
    public int LokaalID { get { return _LokaalID; } }
    public DateTime Created_On { get { return _Created_On; } }
}