SQL Server database information to properties class c#

It seems pretty clear this is homework, and it is far from clear what you are trying to do, so there will be lacunae for you to fill in. There is a fair amount that should be done differently.

DB Design

For some kind of fitness/weight tracker, it really seems like that should be at least 2 tables. One for user data and one for the current weights. The current weight alone isnt very interesting unless you also know previous weights to see if the trend is Up or Down, no? Such a table would also reveal the rate of loss/gain ("getting in shape" can result in less loss than expected - or even a gain - if fat is being converted to muscle).

Something like {Id, UserId, Date, Weight} which associates a number of weight values on specific dates with a user would work.

1. Never store something which can be easily calculated (ie Age). Sooner or later such values will be wrong and/or you build in an excessive amount of maintenance to the system.
2. Never, ever store Passwords as plain text. Hash them. Always.

Gebruikerklasse (User)

1. As per your title, your class has no Properties, just public fields/members. The difference matters a great deal when it comes to data binding.
2. The table seems to store both the login and fitness related data. But the login data plays no role in the weight tracking and vice versa, so you probably should have 2 classes: one using the log in columns and the other using the tables related to weight tracking
3. The db PK Id is not represented which will be critical to updating.

So here is how my User class would look for the fitness related elements:

public class User
{   // critical
    public int Id { get; set; }

    public string Name { get; set; }
    public string LastName { get; set; }
         
    public DateTime DateOfBirth { get; set; }
    public int Age
    {
        get { return DateTime.Now.Date.Year - DateOfBirth.Date.Year; }
    }
    
    public string Gender { get; set; }
    public int CurrentWeight { get; set; }
    public int Targetweight { get; set; }
    
    private static string dbConnStr = "server=127.0.0.1;database=...";
    public User()
    { }
 ...

The log in related data is omitted; another class would deal with that (if I even needed a class for it). Also, the DateOfBirth is used to calculate an age. You can add code to also check the month to use an ANB (Age Nearest) age rather than ALB (Age at Last Birthday) method.

As noted, CurrentWeight should probably be some sort of collection of Date and Weight values.

Next, in OOP you generally want the class to do all the work (or as much as possible) related to managing the data related to it. So rather than a DatabaseTransferObject method to "push" data into the class object, use class methods for it to load the data itself.

There are many ways to do it. This this one uses a static method to create a new User object from row data. This uses MySQL, but the way DBProviders works varies very little from one another:

// create a user object from an ID
public static User Load(int id)
{
    string sql = "SELECT * FROM gebruiker WHERE Id = @id";
    using (var dbCon = new MySqlConnection(dbConnStr))
    using (var cmd = new MySqlCommand(sql, dbCon))
    {
        dbCon.Open();
        cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = id;
        using (var rdr = cmd.ExecuteReader())
        {
            if (rdr.HasRows)
            {
                User U = new User();
                rdr.Read();
                U.Id = id;
                U.Name = rdr.GetString(rdr.GetOrdinal("FirstName"));
                U.LastName = rdr.GetString(rdr.GetOrdinal("LastName"));
                U.DateOfBirth = rdr.GetDateTime(rdr.GetOrdinal("BirthDate"));

                //...
                return U;
            }
            else { return null; }
        }
    }
}

SQL Server lacks the Getxxxx(string) overrides to get data by column by name. For others that have that override like MySQL, it is slightly simpler:

U.Name = rdr.GetString("FirstName");
U.LastName = rdr.GetString("LastName");
U.DateOfBirth = rdr.GetDateTime("BirthDate");

Usage:

User U = new User(1);

An instance method could be used instead to set the properties (this.Name = rdr.GetString("FirstName");); as I said there are many ways to do it. A complementary Save() method would almost certainly be an instance method to INSERT/UPDATE the db from the local data.

Assuming you want to learn, not just get a grade, a simpler way to do that is with an ORM. These map db data to class objects for you. Dapper is a micro-ORM which maps DB data to a class using connection extensions:

User u = dbCon.Query<User>(SQL, new { id = 1 });

Dapper will create a new User object from the record with the Id 1. It won't quite work that simply with your design because the column and property names do not match.