DataRetriever class

from the Artful MySQL Tips List


The DataRetriever class fetches a page of data. It has just one client, Cache, a DBMS-agnostic cache. It has methods to fetch the table rowcount, a column list for the query, and the identity of the sort column. SupplyPageofData uses a LIMIT query.

  public interface IDataPageRetriever {
    DataTable SupplyPageOfData( int lowerPageBoundary, int rowsPerPage );
  }

  // DATARETRIEVER CLASS
  public class DataRetriever : IDataPageRetriever {
    private string tableName, tableArg;
    private MySqlCommand command;

    public DataRetriever( TheUsual.TheUsualConnection connector, string schemaName, string tableName ) {
      MySqlConnection connection = connector.Connection;
      this.command = connection.CreateCommand();
      this.tableName = tableName;
      this.tableArg = schemaName + "." + tableName;
    }

    private int rowCountValue = -1;

    public int RowCount {
      get {
        // Return existing value if known.
        if ( rowCountValue != -1 ) {
          return rowCountValue;
        }
        // Retrieve row count from the database.
        command.CommandText = "SELECT COUNT(*) FROM " + tableArg;
        // NOTE CAST REQUIRED BY .NET FOR MYSQL LONGS
        rowCountValue = ( int ) (long) command.ExecuteScalar();
        return rowCountValue;
      }
    }

    private DataColumnCollection columnsValue;

    public DataColumnCollection Columns {
      get {
        // Return the existing value if it has already been determined.
        if ( columnsValue != null ) {
          return columnsValue;
        }

        // Retrieve the column information from the database.
        command.CommandText = "SELECT * FROM " + tableArg;
        MySqlDataAdapter adapter = new MySqlDataAdapter();
        adapter.SelectCommand = command;
        DataTable table = new DataTable();
        table.Locale = System.Globalization.CultureInfo.InvariantCulture;
        adapter.FillSchema( table, SchemaType.Source );
        columnsValue = table.Columns;
        return columnsValue;
      }
    }

    private string commaSeparatedListOfColumnNamesValue = null;

    private string CommaSeparatedListOfColumnNames {
      get {
        // Return the existing value if it has already been determined.
        if ( commaSeparatedListOfColumnNamesValue != null ) {
          return commaSeparatedListOfColumnNamesValue;
        }
        // Store list of column names for use by SupplyPageOfData.
        System.Text.StringBuilder commaSeparatedColumnNames = new System.Text.StringBuilder();
        bool firstColumn = true;
        foreach ( DataColumn column in Columns ) {
          if ( !firstColumn ) {
            commaSeparatedColumnNames.Append( ", " );
          }
          commaSeparatedColumnNames.Append( column.ColumnName );
          firstColumn = false;
        }
        commaSeparatedListOfColumnNamesValue = commaSeparatedColumnNames.ToString();
        return commaSeparatedListOfColumnNamesValue;
      }
    }

    private string columnToSortBy;
    private MySqlDataAdapter adapter = new MySqlDataAdapter();

    public string ColumnToSortBy {
      get { return columnToSortBy; }
      set { columnToSortBy = value; }
    }
    
    public DataTable SupplyPageOfData( int lowerPageBoundary, int rowsPerPage ) {
      // Store name of ID column
      if ( columnToSortBy == null ) {
        columnToSortBy = this.Columns[0].ColumnName;
      }
      command.CommandText = "SELECT " + CommaSeparatedListOfColumnNames + " FROM " +
                            tableArg + " ORDER BY " + columnToSortBy + " LIMIT " + 
                            lowerPageBoundary.ToString() + "," + rowsPerPage.ToString();
      adapter.SelectCommand = command;
      DataTable table = new DataTable();
      table.Locale = System.Globalization.CultureInfo.InvariantCulture;
      adapter.Fill( table );
      return table;
    }
  } // END DATARETRIEVER CLASS


Return to the Artful MySQL Tips page