Dynamic Caching & ADO DataSets
by John Cheng and Hong Rong

Listing One

using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Collections;

namespace RDView
{
  public interface IRDView
  {
    bool Open(string oleDBDriver, string dataSource, string user, 
              string password, bool keepConnection, int cacheSize);
    bool Close();
    DataRow GetRow(int rowIndex);
    string GetRowAsString(int rowIndex);
    int RowCount();
    string GetLastError();
  }
  public class RDView : IRDView
  {
    OleDbConnection conn;
    string sqlStr;
    string sqlDiffStrTemplate;
    DataSet ds = new DataSet();
    string error;
    int blockSize = 100;
    int totalRowCount = -1;
    const string TblName = "Results";

    public RDView()
    {
    }
    public bool Open(string oleDBDriver, string dataSource, string user, 
                     string password, bool keepConnection, 
   int cacheSize)
    {
      blockSize = cacheSize;
      if (blockSize > 0)
        blockSize = blockSize > 10 ? blockSize : 10;
      StringBuilder sqlb = new StringBuilder();
      sqlb.Append("provider=");
      sqlb.Append(oleDBDriver);
      sqlb.Append(";data source=");
      sqlb.Append(dataSource);
      sqlb.Append(";user id=");
      sqlb.Append(user);
      sqlb.Append(";password=");
      sqlb.Append(password);
            
      try
      {
        conn = new OleDbConnection(sqlb.ToString());
        if (keepConnection)
          conn.Open();
        return true;
      }
      catch (Exception e)
      {
        error = e.Message;
      }
      return false;
    }
    public bool Close()
    {
      if (conn.State != ConnectionState.Closed)
        conn.Close();
      return true;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="sql">The SQL string</param>
    /// <param name="comparingString">The comparing string that differenciate 
    /// current row with the previous row. For example, if you have "order by 
    /// emp_id" in your SQL string. The comparing string should be: 
    /// emp_id>'{i}', where i is the selection position, and {i} will be 
    /// replaced by the ith selection of the last row.
    /// </param>
    /// <param name="cacheSize">give -1 for no cache</param>
    public void SetSelect(string sql, string comparingString)
    {
      sqlStr = sql;
      if (comparingString == null || comparingString.Length == 0)
        sqlDiffStrTemplate = "";
      else
      {
        string tmp = sqlStr.ToUpper();
        int i = tmp.IndexOf("ORDER");
        if (i > 0)
        {
          int j = tmp.IndexOf("WHERE");
          if (j < 0)
            sqlDiffStrTemplate = sqlStr.Substring(0, i) + "WHERE " + 
                             comparingString + " " + sqlStr.Substring(i);
          else
            sqlDiffStrTemplate = sqlStr.Substring(0, i) + "AND " + 
                             comparingString + " " + sqlStr.Substring(i);
        }
        else
          sqlDiffStrTemplate = "";
      }
    }
    /// <summary>
    /// Build a SQL string base on a template, and replace the placeholder 
    /// with data from the comparing row
    /// </summary>
    /// <param name="sqlTemplate">SQL string template</param>
    /// <param name="compareRow">data row to use to fill the 
    /// template</param>
    /// <returns></returns>
    private string BuildSQLDifferencialString(string sqlTemplate, 
                                                         DataRow compareRow)
    {
      string sqlString = "";
      int i=0, j=-1;
      do 
      {
        i = sqlTemplate.IndexOf("{", i);
        if (i>0) 
        {
          sqlString += sqlTemplate.Substring(j+1, i-j-1);
          j = sqlTemplate.IndexOf("}", i);
          if (j > 0)
          {
            string posToken = sqlTemplate.Substring(i+1, j-i-1);
            int pos = Convert.ToInt32(posToken);

            sqlString += compareRow.ItemArray[pos];
          }
          i++;
        }
      }while ( i > 0 && j > 0);
      if (j>0)
        sqlString += sqlTemplate.Substring(j+1);
      return sqlString;
    }
    /// <summary>
    /// Query a block of data start from start, to end (inclusive)
    /// </summary>
    /// <param name="start"></param>
    /// <param name="end"></param>
    /// <returns></returns>
    private bool QueryBlock(int start, int end)
    {
      bool incrementalQuery = false;
      string sql;
      if (start == 0)
        sql = sqlStr;
      else if (sqlDiffStrTemplate.Length == 0)
        sql = sqlStr;
      else
      {
        sql = BuildSQLDifferencialString(sqlDiffStrTemplate, 
                                  ds.Tables[TblName].Rows[start-1]);
        incrementalQuery = true;
      }
      try
      {
        OleDbCommand cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql;
        OleDbDataAdapter da = new OleDbDataAdapter();
        da.SelectCommand = cmd;

        int rowCount = end - start;
        int fillCount, startRecord;
        if (incrementalQuery)
          startRecord = 0;
          startRecord = start;
        fillCount = da.Fill(ds, startRecord, rowCount, TblName);
        return true;
      }
      catch(Exception e)
      {
        error = e.Message;
        return false;
      }
    }
    public DataRow GetRow(int rowIndex)
    {
      if (totalRowCount < 0)
        totalRowCount = RowCount();
      if (totalRowCount < 0)
        return null;
      if (rowIndex >= totalRowCount)
      {
        error = "Row index out of range";
        return null;
      }
      int lastRetrievedIndex = -1;
      if (ds.Tables[TblName] != null) 
        lastRetrievedIndex = ds.Tables[TblName].Rows.Count - 1;
      if (rowIndex > lastRetrievedIndex)
        QueryBlock(lastRetrievedIndex+1, rowIndex + blockSize);
      DataRow row = ds.Tables[TblName].Rows[rowIndex];
      return row;
    }
    public string GetRowAsString(int rowIndex)
    {
      DataRow row = GetRow(rowIndex);
      int columnCount = row.ItemArray.Length;
      StringBuilder resultCat = new StringBuilder();
      for (int j=0; j<columnCount; j++)
      {
        resultCat.Append(row.ItemArray[j].ToString());
        resultCat.Append('\0');
      }
      return resultCat.ToString();  
    }
    /// <summary>
    /// Return the total number rows from the give query. The return -1
    ///  in case of error.
    /// </summary>
    /// <returns></returns>
    public int RowCount()
    {
      if (totalRowCount >= 0)
        return totalRowCount;
      string countSql = sqlStr;
      countSql = countSql.ToUpper();
      int i = countSql.IndexOf("FROM");
      if (i < 0)
        return totalRowCount = -1;
      bool iOpenedConn = false;
      try
      {
        if (conn.State != ConnectionState.Open)
        {
          iOpenedConn = true;
          conn.Open();
        }
        countSql = "SELECT COUNT(*) " + countSql.Substring(i);
        OleDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = countSql;
        totalRowCount = Convert.ToInt32(cmd.ExecuteScalar().ToString());
        if (iOpenedConn)
          conn.Close();
      }
      catch (Exception e)
      {
        error = e.Message;
        totalRowCount = -1;
      }
      return totalRowCount;
    }
    public string GetLastError()
    {
      return error;
    }
  }
}





5


