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();

When you say "failed", are you getting an exception being thrown? — abrown, May 14 '16 at 21:31
@abrown rd2.HasRows returns False.Thus I cant reach the calorific valure from food table. — ekn, May 14 '16 at 21:35
I know it seems obvious, are you sure you have data in the database for that specific user? Can you verify that the @name parameter is not set to a default value of empty string? — abrown, May 14 '16 at 21:37
cmd2 returns 2500.it is correct.Why I cant get the calorific value from other table using same structure. — ekn, May 14 '16 at 21:44
I was just trying to establish whether the value of s (obtained from the combobox) is the correct name value, and whether there is actually data in the food table for that name. — abrown, May 14 '16 at 21:50

Steve · Accepted Answer · 2016-05-14T22:24:57.863

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]);
    }
}

it gives an error .MySqlDbType does not contain a definition for Int.@Steve — ekn, May 14 '16 at 22:10
@ Steve Again it does not find the calorific values from table :( name type is string I changed Varchar to string it does not work. — ekn, May 14 '16 at 22:40
I can't say. There is a where condition and if there is no match then the result is zero. You should try using the debugger. Put a breakpoint where the code reads the value from the combobox and check what value is assigned to the variable _s_ used to filter the records. Probably is not what you think. Out of curiosity, how do you fill the combobox? — Steve, May 14 '16 at 22:46

score 0 · Answer 2 · answered May 14 '16 at 21:54

Your issues could be around closing a connection and then trying to open it again. Either way it's fairly inefficient to be closing and opening connections.

MySqlCommand cmd = new MySqlCommand("SELECT name,calorificValue FROM myfitsecret.food where name=@name", cnn);
string s = (comboBox1.SelectedItem).ToString();
cmd.Parameters.AddWithValue("@name",s);


MySqlCommand cmd2 = new MySqlCommand("SELECT SUM(daily_gained) FROM myfitsecret.calorie_tracker where sportsman_id=@sportsman_id", cnn);
cmd2.Parameters.AddWithValue("@sportsman_id", Login.userID);
cnn.Open();

MySqlDataReader rd = cmd.ExecuteReader();

if (rd.HasRows) // if entered username and password have data
{
    while (rd.Read()) // while the reader can read 
    {
        burned += int.Parse(rd["calorificValue"].ToString());
    }
}

burned = cmd2.ExecuteScalar();
MessageBox.Show(burned+"");
cnn.Close();

how two get data from 2 different table c#

2 Answers2