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