how two get data from 2 different table c#

Question

I have two table.I need to get calorificValue from the food table and daily_gained from the calorie_tracker table to then make some calculations.I've written this code, I know it not efficent. It retrieves daily_gained but failed to get calorificValue.

MySqlCommand cmd = new MySqlCommand("SELECT name,calorificValue FROM myfitsecret.food where name=@name", cnn);
MySqlCommand cmd2 = new MySqlCommand("SELECT sportsman_id,daily_gained FROM myfitsecret.calorie_tracker where sportsman_id=@sportsman_id", cnn);
cmd2.Parameters.AddWithValue("@sportsman_id", Login.userID);

string s = (comboBox1.SelectedItem).ToString();
cmd.Parameters.AddWithValue("@name",s);
cmd2.Connection.Open();
MySqlDataReader rd = cmd2.ExecuteReader(CommandBehavior.CloseConnection);
int burned = 0;
if (rd.HasRows) // if entered username and password have the data
{
    while (rd.Read()) // while the reader can read 
    {
        if (rd["sportsman_id"].ToString() == Login.userID) // True for admin
        {
            burned += int.Parse(rd["daily_gained"].ToString());
        }
    }
}
cmd2.Connection.Close();
cmd.Connection.Open();

MySqlDataReader rd2 = cmd.ExecuteReader(CommandBehavior.CloseConnection);

if (rd2.HasRows) // if entered username and password have data
{
    while (rd2.Read()) // while the reader can read 
    {
        if (rd2["name"].ToString() == s)
        {
            burned += int.Parse(rd2["calorificValue"].ToString());
        }
    }
}
MessageBox.Show(burned+"");
DataTable tablo = new DataTable();
string showTable = "SELECT * from myfitsecret.calorie_tracker where sportsman_id=@sportsman_id";
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand showCommand = new MySqlCommand();
showCommand.Connection = cnn;
showCommand.CommandText = showTable;
showCommand.CommandType = CommandType.Text;
showCommand.Parameters.AddWithValue("@sportsman_id", Login.userID);
adapter.SelectCommand = showCommand;
adapter.Fill(tablo);

dataGridView1.DataSource = tablo;
cnn.Close();

Answer 1

Why don't you just use the scalar function SUM and let the database do its job instead of writing a lot of code?

int burned = 0;
string s = comboBox1.SelectedItem.ToString();
cnn.Open();
string cmdText = @"SELECT SUM(calorificValue) 
                   FROM myfitsecret.food 
                   WHERE name=@name";
using(MySqlCommand cmd = new MySqlCommand(cmdText, cnn))
{
    cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = s;
    object result = cmd.ExecuteScalar();
    burned += (result != null ? Convert.ToInt32(result) : 0);
}
cmdText = @"SELECT SUM(daily_gained) 
            FROM myfitsecret.calorie_tracker 
            WHERE sportsman_id=@sportsman_id";
using(MySqlCommand cmd = new MySqlCommand(cmdText, cnn))
{
    cmd.Parameters.Add("@sportsman_id", MySqlDbType.Int32).Value = Login.userID;
    object result = cmd.ExecuteScalar();
    burned += (result != null ? Convert.ToInt32(result) : 0);
}

Not visible from your code, but also the connection should be created inside a using statement (very important with MySql that is very restrictive with simultaneous open connections)

We could also use a different approach putting the two commands together and separating them with a semicolon. This is called batch commands and they are both executed with just one trip to the database. Of course we need to fallback using the MySqlDataReader to get the two results passing from the first one to the second one using the NextResult() method (see here MSDN for Sql Server)

string cmdText = @"SELECT SUM(calorificValue) 
                   FROM myfitsecret.food 
                   WHERE name=@name;
                   SELECT SUM(daily_gained) 
                   FROM myfitsecret.calorie_tracker 
                   WHERE sportsman_id=@sportsman_id";
using(MySqlCommand cmd = new MySqlCommand(cmdText, cnn))
{
    // Add both parameters to the same command
    cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = s;
    cmd.Parameters.Add("@sportsman_id", MySqlDbType.Int32).Value = Login.userID;
    cnn.Open();
    using(MySqlDataReader reader = cmd.ExecuteReader())
    {
        // get sum from the first result
        if(reader.Read()) burned += Convert.ToInt32(reader[0]);

        // if there is a second resultset, go there
        if(reader.NextResult())
           if(reader.Read())
              burned += Convert.ToInt32(reader[0]);
    }
}