Retrieve a scalar result

from the Artful MySQL Tips List


The simplest way to retrieve a scalar result is via the MySqlCommand.ExecuteScalar method:

    public object ScalarQueryResult( string sql ) {
      object obj = null;
      MySqlCommand cmd = new MySqlCommand();
      try {
        cmd.CommandText = sql;
        cmd.Connection = Connector.Connection;
        obj = cmd.ExecuteScalar();
      }
      catch ( MySqlException ex ) {
        this.ErrMsg( ex );
      }
      finally {
        cmd.Dispose();
      }
      return obj;
    }


It returns an object, which must then be cast into a type that's suitable for the retrieved value. Thus for a method to retrieve a rowcount under a Where clause, write ...

    public Int64 TableRows( string schema, string table, string where ) {
      string sql = "SELECT COUNT(*) FROM " + schema  + "." + table + " " + where;
      return Int64.Parse( ScalarQueryResult( sql ).ToString() );
    }


and to retrieve the data type of a column ...

    public string ColumnType( 
      string schema, string table, 
      string column ) {
      string sql = 
        "SELECT data_type FROM " +  information_schema.columns " +
        "WHERE table_schema='" + schema + "'" + "AND " +
        "table_name='" + table +  "' AND column_name='" +  column + "'";
      return ScalarQueryResult( sql ).ToString();
    }  


Return to the Artful MySQL Tips page