C#/MySQL - get a date from database

Question

in my database I have a row from type date. I want to to display those dates in my C# program, but I have problems at writing the query. At the moment I try it using:

public string getAge(string Name) 
    {
        connection con = new connection(); //The object for the class with the connection string 
        con.conopen(); //opens the connection
        string Age = "";
        MySqlCommand cmd_getAge = new MySqlCommand("Select 'Age' from profile where Name = '" + Name + "';", con.con); 
        MySqlDataReader Reader = cmd_getAge.ExecuteReader();
        if (Reader.HasRows)
        {
            try
            {
                while (Reader.Read())
                {
                    Age = Reader.GetString(0);
                }
            }
            finally
            {
                Reader.Close();
                con.conclose();
            }
        }
        return Age;
    }

VS returns me as result just "Age" and also no error.

If it is relevant, I am using Visual Studio Ultimate 2013 on Windows 7.

Answer 1

thank you all for your help! It works now :D Andrew Walters and Joel Coehoorn are both right. For those with the same problem, I post the finished method. It is just the code from Joel Coehoorn with some fixes.

public int getAge_Test(string Name)
{
string sql = "Select Age from profile where Name = @Name ;";
  using (var con = new MySqlConnection("server=127.0.0.1;user id=root;persistsecurityinfo=True;database=social_media"))
        using (var cmd = new MySqlCommand(sql, con))
        {
            cmd.Parameters.Add("@Name", MySqlDbType.VarChar, 50).Value = Name; //MySQL has no defintion for SqldbType and also Nvarchar

            con.Open();
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    DateTime origin = reader.GetDateTime(0);
                    //calculate the age
                    int Age = DateTime.Today.Year - origin.Year;
                    if (origin > DateTime.Today.AddYears(-Age)) Age--; //Visual Studio also does not know "today", just "DateTime.Today"
                    return Age;
                }
            }
        }
        return 0; //or throw an exception here
    }

Answer 2

Explanation in the comments:

//why on earth would you ever return an Age as a string!?
public int getAge(string Name) 
{
    //Notice the placeholder in the string. This is important.
    string sql = "Select Age from profile where Name = @Name ;";

    //I see you have your own connection class. However, you used it wrong.
    //If you can't wrap your connection in a using block or try/finally block
    // you're potentially leaving connections hanging open.
    // Do that enough, and you'll lock yourself out of your database.
    //Better just to provide the connection string as a property
    using (var cn = new MySqlConnection("connection string here"))
    using (var cmd = new MySqlCommand(sql, cn))
    { 
        //use the actual db type and length here
        // this parameter makes your code safe from sql injection attacks
        // without the parameter, you're practically begging to get hacked.
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = Name;  

        cn.Open();          
        using (var reader = cmd.ExecuteReader())
        {
            //no need to ever check HasRows. 
            //If HasRows would return false, so will reader.Read(), and everything still works the same
            if (reader.Read())
            {
                //surely you're not storing this information in the database as a string!?
                // that would be awful.
                // Age should be an integer.
                // More than that, you should be storing a date, and then calculate the age on retrieval
                DateTime origin = Reader.GetDateTime(0);
                int Age = DateTime.Today.Year - origin.Year;
                if (origin > today.AddYears(-Age)) Age--;
                return Age;
            }
        }
    }
    return 0; //or throw an exception here
}

And because the comments make this kind of lengthy, here's the concise version:

public int getAge(string Name) 
{
    string sql = "Select Age from profile where Name = @Name ;";

    using (var cn = new MySqlConnection("connection string here"))
    using (var cmd = new MySqlCommand(sql, cn))
    { 
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = Name;  

        cn.Open();          
        using (var reader = cmd.ExecuteReader())
        {
            if (reader.Read())
            {
                DateTime origin = Reader.GetDateTime(0);
                //calculate the age
                int Age = DateTime.Today.Year - origin.Year;
                if (origin > today.AddYears(-Age)) Age--;
                return Age;
            }
        }
    }
    return 0; //or throw an exception here
}

Answer 3

The issue is that you are selecting the specific value 'Age'. If you remove the quotes around Age, the column's values will be selected instead.

public string getAge(string Name) 
    {
        connection con = new connection(); //The object for the class with the connection string 
        con.conopen(); //opens the connection
        string Age = "";
        MySqlCommand cmd_getAge = new MySqlCommand("Select Age from profile where Name = '" + Name + "';", con.con); 
        MySqlDataReader Reader = cmd_getAge.ExecuteReader();
        if (Reader.HasRows)
        {
            try
            {
                while (Reader.Read())
                {
                    Age = Reader.GetString(0);
                }
            }
            finally
            {
                Reader.Close();
                con.conclose();
            }
        }
        return Age;
    }