SQL Server database information to properties class c#

Question

I need a method that takes the information of a selected row (with another method, already written (SQL Count)) and puts it in an object called Gebruiker (user).

I already have a method that puts the information from an object in to the database with parameters, but this doesn't work for the other way.

This is my object class:

namespace BurnThatFat
{
    class Gebruikerklasse
    {
        public string Naam;
        public string Achternaam;
        public int Leeftijd;
        public string Geslacht;
        public int Huidiggewicht;
        public int Streefgewicht;
        public string Gebruikersnaam;
        public string Wachtwoord;

        public override string ToString()
        {
            return Naam;
        }
    }
}

and this is the method that puts the information from the object to the database:

public void SignUp(string commandText, Gebruikerklasse gebruiker)
{
    // nieuwe connectie maken
    // ontvangt de query vanuit 'buttonclick' en voert hem hier uit
    // als ExecuteNonQuery niet kan worden uitgevoerd is er iets fout gegaan. D.m.v een bool moet hij dan een bericht tonen
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(commandText, conn))
    {
        conn.Open();

        cmd.Parameters.AddWithValue("@Naam", gebruiker.Naam);
        cmd.Parameters.AddWithValue("@Achternaam", gebruiker.Achternaam);
        cmd.Parameters.AddWithValue("@Leeftijd", gebruiker.Leeftijd);
        cmd.Parameters.AddWithValue("@Geslacht", gebruiker.Geslacht);
        cmd.Parameters.AddWithValue("@Huidiggewicht", gebruiker.Huidiggewicht);
        cmd.Parameters.AddWithValue("@Streefgewicht", gebruiker.Streefgewicht);
        cmd.Parameters.AddWithValue("@Gebruikersnaam", gebruiker.Gebruikersnaam);
        cmd.Parameters.AddWithValue("@Wachtwoord", gebruiker.Wachtwoord);

        int a = cmd.ExecuteNonQuery();

        if (a == 1)
        {
            Success = true;
        }
        else if (a == -1)
        {
            Success = false;
        }

        conn.Close();
    }
}

So how do I have to do this? I don't know how to google this really. I think I'm using the wrong words while googling, because im getting non related things...

Edit: added screenshots

So I need the information in this table: http://prnt.sc/dsg95v

To be stored in this object: http://prnt.sc/dsghl1

I already have code (above) that returns the information from the object to the table. I do that with parameters.

I really don't know where to start with database to object...

Edit again: something like this:

public void DatabaseTransferObject(string commandText, Gebruikerklasse gebruiker)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(commandText, conn))
    {
        conn.Open();

        gebruiker.Naam = //code to get Naam (name) from table Gebruiker (user);
        gebruiker.Leeftijd = //code to get Leeftijd(age) from table Gebruiker (user);

        conn.Close();
    }
}

This the SQL code to get all the information from the row with the given @username

"Select * from Gebruiker where Gebruikersnaam = @Gebruikersnaam;


Show source
| class   | c#   | sql   | visual-studio   | database   2017-01-07 01:01 1 Answers

Answers ( 1 )

  1. 2017-01-07 19:01

    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.

◀ Go back