Harikeinas Harikeinas - 4 months ago 12
SQL Question

Get value from class

So i have a class

Take
for connecting to mysql. In that class I have a method to call a query to take the last record from mysql table.

public void Balance()
{
string query = "SELECT balance FROM history ORDER BY id DESC LIMIT 1 ";
if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
}
}


In the main form I'm calling that class and that method

take.Balance();


I know, that from code above, i don't get any value but
NULL
, so i am asking how i can take value from that query and put it in the
TextBox
in the main form?

Answer

Let's have look:

// You probably want to return value: decimal, not void
public decimal Balance() {
  // Make sql readable
  string query = 
    @"SELECT balance 
        FROM history 
    ORDER BY id DESC 
       LIMIT 1 ";

  // do not cache connection, but create a new one instead 
  using (MySqlConnection conn = new MySqlConnection(connectionStringHere)) {
    conn.Open();

    // wrap IDisposable into using  
    using (MySqlCommand cmd = new MySqlCommand(query, conn)) {
      // you want to return values: ExecuteReader (or ExecuteScalar) 
      // instead of ExecuteNonQuery
      using (var reader = cmd.ExecuteReader()) {
        if (reader.Read())
          return Convert.ToDecimal(reader.GetValue(0));
        else
          return 0m; // cursor is empty, let's return 0   
      }
    }          
  }
}