All the examples discussed so far loaded all the data into memory. This is a simple and convenient way to do things, and it works in many cases.

In some cases, however, there may be too much data to load into memory at once. Consider for example a table with a million rows or more. Even if you could load all this data into memory, the process would take a long time.

There are many ways to deal with these scenarios. You could create queries that summarize and cache the data on the server, or use specialized OLAP data providers. In either case, you would end up with tables that can be used with C1Olap.

But there are also simpler options. Suppose the database contains information about thousands of companies, and users only want to see a few at a time. Instead of relying only on the filtering capabilities of C1Olap, which happen on the client, you could delegate some of the work to the server, and load only the companies the user wants to see. This is easy to accomplish and does not require any special software or configurations on the server.

For example, consider the following CachedDataTable class (this class is used in the "SqlFilter" sample installed with C1Olap):

/// <summary>
/// Extends the <see cref="DataTable"/> class to load and cache
/// data on demand using a <see cref="Fill"/> method that takes 
/// a set of keys as a parameter.
/// </summary>
class CachedDataTable : DataTable
{
  public string ConnectionString { get; set; }
  public string SqlTemplate { get; set; }
  public string WhereClauseTemplate { get; set; }
  Dictionary<object, bool> _values =
     new Dictionary<object, bool>();
 
  // constructor
  public CachedDataTable(string sqlTemplate, 
    string whereClauseTemplate, string connString)
  {
    ConnectionString = connString;
    SqlTemplate = sqlTemplate;
    WhereClauseTemplate = whereClauseTemplate;
  }
 
  // populate the table by adding any missing values
  public void Fill(IEnumerable filterValues, bool reset)
  {
    // reset table if requested
    if (reset)
    {
      _values.Clear();
      Rows.Clear();
    }
 
    // get a list with the new values
    List<object> newValues = GetNewValues(filterValues);
    if (newValues.Count > 0)
    {
      // get sql statement and data adapter
      var sql = GetSqlStatement(newValues);
      using (var da = new OleDbDataAdapter(sql, ConnectionString))
      {
        // add new values to the table
        int rows = da.Fill(this);
      }
    }
  }
  public void Fill(IEnumerable filterValues)
  {
    Fill(filterValues, false);
  }

This class extends the regular DataTable class and provides a Fill method that can either repopulate the table completely or add additional records based on a list of values provided. For example, you could start by filling the table with two customers (out of several thousand) and then add more only when the user requested them.

Note that the code uses an OleDbDataAdapter. This is because the sample uses an MDB file as a data source and an OleDb-style connection string. To use this class with Sql Server data sources, you would replace the OleDbDataAdapter with a SqlDataAdapter.

The code above is missing the implementation of two simple methods given below.

 // gets a list with the filter values that are not already in the
  // current values collection; 
  // and add them all to the current values collection.
  List<object> GetNewValues(IEnumerable filterValues)
  {
    var list = new List<object>();
    foreach (object value in filterValues)
    {
      if (!_values.ContainsKey(value))
      {
        list.Add(value);
        _values[value] = true;
      }
    }
    return list;
  }
 
  // gets a sql statement to add new values to the table
  string GetSqlStatement(List<object> newValues)
  {
    return string.Format(SqlTemplate, GetWhereClause(newValues));
  }
  string GetWhereClause(List<object> newValues)
  {
    if (newValues.Count == 0 || string.IsNullOrEmpty(WhereClauseTemplate))
    {
      return string.Empty;
    }
 
    // build list of values
    StringBuilder sb = new StringBuilder();
    foreach (object value in newValues)
    {
      if (sb.Length > 0) sb.Append(", ");
      if (value is string)
      {
        sb.AppendFormat("'{0}'", ((string)value).Replace("'", "''"));
      }
      else
      {
        sb.Append(value);
      }
    }
 
    // build where clause
    return string.Format(WhereClauseTemplate, sb);
  }
}

The GetNewValues method returns a list of values that were requested by the user but are still not present in the DataTable. These are the values that need to be added.

The GetSqlStatement method builds a new SQL statement with a WHERE clause that loads the records requested by the user that haven’t been loaded yet. It uses string templates provided by the caller in the constructor, which makes the class general.

Now that the CachedDataTable is ready, the next step is to connect it with C1Olap and enable users to analyze the data transparently, as if it were all loaded in memory.

To do this, open the main form, add a C1OlapPage control to it, and then add the following code to the form:

public partial class Form1 : Form
{
  List<string> _customerList;
  List<string> _activeCustomerList;
  const int MAX_CUSTOMERS = 12;

These fields will contain a complete list of all the customers in the database, a list of the customers currently selected by the user, and the maximum number of customers that can be selected at any time. Set the maximum number of customers to a relatively small value to prevent users from loading too much data into the application at once.

Next, we need to get a complete list of all the customers in the database so the user can select the ones he wants to look at. Note that this is a long but compact list. It contains only the customer name, not any of the associated details such as orders, order details, and so on. Here is the code that loads the full customer list:

public Form1()
{
  InitializeComponent();
 
  // get complete list of customers
  _customerList = new List<string>();
  var sql = @"SELECT DISTINCT Customers.CompanyName" +
    "AS [Customer] FROM Customers";
  var da = new OleDbDataAdapter(sql, GetConnectionString());
  var dt = new DataTable();
  da.Fill(dt);
  foreach (DataRow dr in dt.Rows)
  {
    _customerList.Add((string)dr["Customer"]);
  }

Next, we need a list that contains the customers that the user wants to look at. We persist this list as a property setting, so it is preserved across sessions. The setting is called "Customers" and is of type "StringCollection". You create this by right-clicking the project node in the solution explorer, selecting "Properties", and then the "Settings" tab as before:

 

 

And here is the code that loads the "active" customer list from the new setting:

// get active customer list
  _activeCustomerList = new List<string>();
  foreach (string customer in Settings.Default.Customers)
  {
    _activeCustomerList.Add(customer);
  }

Now we are ready to create a CachedDataTable and assign it to the DataSource property:

// get data into the CachedDataTable
  var dtSales = new CachedDataTable(
    Resources.SqlTemplate, 
    Resources.WhereTemplate, 
    GetConnectionString());
  dtSales.Fill(_activeCustomerList);
 
  // assign data to C1OlapPage control
  _c1OlapPage.DataSource = dtSales;
 
  // show default view
  var olap = _c1OlapPage.OlapEngine;
  olap.BeginUpdate();
  olap.RowFields.Add("Customer");
  olap.ColumnFields.Add("Category");
  olap.ValueFields.Add("Sales");
  olap.EndUpdate();

The CachedDataTable constructor uses three parameters:

 

• SqlTemplate
  This is a standard SQL SELECT statement where the "WHERE" clause is replaced by a placeholder. The statement is fairly long, and is defined as an application resource. To see the actual content please refer to the "SqlFilter" sample.
• WhereTemplate
  This is a standard SQL WHERE statement that contains a template that will be replaced with the list of values to include in the query. It is also defined as an application resource which contains this string: "WHERE Customers.CompanyName in ({0})".
• ConnectionString
  This parameter contains the connection string that is used to connect to the database. Our sample uses the same GetConnectionString method introduced earlier, that returns a reference to the NorthWind database installed with C1Olap.

Now that the data source is ready, we need to connect it to C1Olap to ensure that:

1. The user can see all the customers in the C1Olap filter (not just the ones that are currently loaded) and
2. When the user modifies the filter, new data is loaded to show any new customers requested.

To accomplish item 1, we need to assign the complete list of customers to the C1OlapField.Values property. This property contains a list of the values that are displayed in the filter. By default, C1Olap populates this list with values found in the raw data. In this case, the raw data contains only a partial list, so we need to provide the complete version instead.

To accomplish item 2, we need to listen to the PropertyChanged event, which fires when the user modifies any field properties including the filter. When this happens, we retrieve the list of customers selected by the user and pass that list to the data source.

This is the code that accomplishes this:

 // custom filter: customers in the list, customers currently active
  var field = olap.Fields["Customer"];
  var filter = field.Filter;
  filter.Values = _customerList;
  filter.ShowValues = _activeCustomerList.ToArray();
  filter.PropertyChanged += filter_PropertyChanged;

And here is the event handler that updates the data source when the filter changes:

// re-query database when list of selected customers changes
void filter_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
  // get reference to parent filter
  var filter = sender as C1.Olap.C1OlapFilter;
 
  // get list of values accepted by the filter
  _activeCustomerList.Clear();
  foreach (string customer in _customerList)
  {
    if (filter.Apply(customer))
    {
      _activeCustomerList.Add(customer);
    }
  }
 
  // skip if no values were selected
  if (_activeCustomerList.Count == 0)
  {
    MessageBox.Show(
      "No customers selected, change will not be applied.",
      "No Customers");
    return;
  }
 
  // trim list if necessary
  if (_activeCustomerList.Count > MAX_CUSTOMERS)
  {
    MessageBox.Show(
      "Too many customers selected, list will be trimmed.",
      "Too Many Customers");
    _activeCustomerList.RemoveRange(MAX_CUSTOMERS,
      _activeCustomerList.Count - MAX_CUSTOMERS);
  }
 
  // get new data
  var dt = _c1OlapPage.DataSource as CachedDataTable;
  dt.Fill(_activeCustomerList);
}

The code starts by retrieving the field’s Filter and then calling the filter’s Apply method to build a list of customers selected by the user. After some bounds-checking, the list is passed to the CachedDataTable which will retrieve any missing data. After the new data is loaded, the C1OlapPage is notified and automatically refreshes the view.

Before running the application, there is one last item to consider. The field’s Filter property is only taken into account by the C1OlapEngine if the field in “active” in the view. “Active” means the field is a member of the RowFields, ColumnFields, ValueFields, or FilterFields collections. In this case, the “Customers” field has a special filter and should always be active. To ensure this, we must handle the engine’s Updating event and make sure the “Customers” field is always active.

Here is the code that ensures the “Customers” field is always active:

public Form1()
{
    InitializeComponent();
 
    // ** no changes here **
 
    // make sure Customer field is always in the view
    // (since it is always used at least as a filter)
    _c1OlapPage.Updating += _c1OlapPage_Updating;
}
 
// make sure Customer field is always in the view
// (since it is always used at least as a filter)
void  _c1OlapPage_Updating(object sender, EventArgs e)
{
    var olap = _c1OlapPage.OlapEngine;
    var field = olap.Fields["Customer"];
    if (!field.IsActive)
    {
        olap.FilterFields.Add(field);
    }
}

If you run the application now, you will notice that only the customers included in the “Customers” setting are included in the view:

This looks like the screens shown before. The difference is that this time the filtering is done on the server. Data for most customers has not even been loaded into the application.

To see other customers, right-click the "Customer" field and select "Field Settings"; then edit the filter by selecting specific customers or by defining a condition as shown below:

When you click OK, the application will detect the change and will request the additional data from the CachingDataTable object. Once the new data has been loaded, C1Olap will detect the change and update the OLAP table automatically: