JIT Data Fetching

One of the problem with using a datagrid is that if you simply bind a “select * from emp” dataset to it then all rows from the EMP table will be retrieved and rendered – this is just not workable for tables with a large number of rows.

This is the Microsoft implementation of this the virtual just in time fetcher for SQL*Server from which I based my Oracle implementation on.

Implementing Virtual Mode with Just-In-Time Data Loading in the Windows Forms Data Grid View Control. http://msdn2.microsoft.com/en-us/library/ms171624

Changes I had to make to get this working with an Oracle database:

The SQL*Server connection string in the Form1.cs file needs to be changed to an Oracle connection string.

connectionString = "Data Source=muon;Persist Security Info=True;User ID=scott;"+
                     "Password=tiger;Unicode=False;Omit Oracle Connection Name=True";

I created a database table emp_large in the scott/tiger user that contains 14,000+ rows of data for testing purposes:

private string table = "emp_large";

All references to the following classes must be changed:

        SqlCommand -> OracleCommand
        SqlConnection -> OracleConnection
        SqlDataAdapter -> OracleDataAdapter
        SqlException -> OracleException

and where ever these changes occur the following reference must also be modified

  using System.Data.SqlClient; -> using System.Data.OracleClient;

If we were trying to be database agnostic then these classes should all be replaced with Factory methods and some sort of database abstraction layer.

The following changes all appears in the C# file “DataRetreiver.cs” this module is response for physically fetching data from the Oracle database.

  public int RowCount
    {
        get
        {
            // Return the existing value if it has already been determined.
            if (rowCountValue != -1)
            {
                return rowCountValue;
            }
 
            // Retrieve the row count from the database.
            command.CommandText = "SELECT COUNT(*) FROM " + tableName;
            rowCountValue = (int)command.ExecuteScalar();
            return rowCountValue;
        }
    }

This implementation should also work with the Oracle database but it was found that is does not with out the following change

rowCountValue = Convert.ToInt16(command.ExecuteScalar());

The reason for this is that I suspect the Execute Scalar method returns an object but when used with the Oracle drivers C# can't autobox the conversion and must be told explicity. Very ODD.

The most substantial modification is the sql statement that fetches a limited number of rows from the database, the SQL Server code appears below:

 command.CommandText = "Select Top " + rowsPerPage + " " +
 CommaSeparatedListOfColumnNames + " From " + tableName +
            " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " +
            lowerPageBoundary + " " + columnToSortBy + " From " +
            tableName + " Order By " + columnToSortBy +
            ") Order By " + columnToSortBy;
 adapter.SelectCommand = command;
 
 
 DataTable table = new DataTable();
 table.Locale = System.Globalization.CultureInfo.InvariantCulture;
 adapter.Fill(table);

and the Oracle implementation. To improve SGA caching I have introduced bind variables where the paging variables are needed. As the command (Oracle Command) object is reused in other parts of the code after the adapter.Fill() method has executed the parameters must be cleared to prevent them being applied to other statements. Additional exception handling was included to aid debugging.

  // Retrieve the specified number of rows from the database, starting
  // with the row specified by the lowerPageBoundary parameter.
  try
  {
    command.CommandText = "select * "+
                        "from (select p.*,rownum rnum "+
                              "from (select * from "+tableName+
                                     " order by "+columnToSortBy+") P "+
                              "where rownum <= :pROWNUM ) " +
                         "where rnum > :pRNUM " +
                         "order by "+columnToSortBy;
 
 
    command.Parameters.Add("pROWNUM", OracleType.Number).Value = rowsPerPage+lowerPageBoundary;
    command.Parameters.Add("pRNUM", OracleType.Number).Value = lowerPageBoundary;
 
 
    adapter.SelectCommand = command;
 
 
    DataTable table = new DataTable();
    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
    adapter.Fill(table);
    command.Parameters.Clear();       ///<--- As the command object is reused elsewhere
  }
  catch (OracleException oe)
  {
      System.Windows.Forms.MessageBox.Show("Oracle Exception: "+oe.Message+
                                           "\n\n"+command.CommandText);
      throw;
  }

The idea for the SQL query came from a Web solution of the problem.

A sorting and paging Data Grid loading data on-demand, saving changes in bulk from and to an Oracle database, in ASP.NET. http://www.codeproject.com/aspnet/OracleDataGrid.asp