Caching Metadata

For each query you wish to perform, the RSSBus Salesforce ADO.NET Provider must rely on the metadata or schemas that describe the tables queried. If the metadata is not present, the RSSBus Salesforce ADO.NET Provider establishes a separate connection with the live data source to retrieve the metadata before querying the data. This process can be slow and expensive, especially if multiple tables need to be queried.

The solution is to cache table metadata. Instead of retrieving the metadata fresh for each table per each query, caching the metadata locally stores the metadata that describes the table. Thus, the metadata from the live data source need be fetched only once. To enable this feature, simply set a cache location in your connection string and RSSBus Salesforce ADO.NET Provider handles the rest automatically. The RSSBus Salesforce ADO.NET Provider will cache the metadata the first time it is needed and use it onwards.

Do note that altering the schema on the live data source -- say, in the event a column is added or dropped -- is not reflected automatically in the local metadata cache. So you would want to delete the cache file if you expect your data definitions to have changed.

The following code can be used to build the metadata cache. This is especially useful in using the data provider in Entity Framework.

String connectionString = "Cache Location=C:\\cache.db;User=myUser;Password=myPassword;Access Token=myToken;";
using (SalesForceConnection conn = new SalesForceConnection(connectionString)) {
  SalesForceDataAdapter adp = new SalesForceDataAdapter("SELECT * FROM sys_tables", conn);
  DataTable table = new DataTable();
  adp.Fill(table);
  Console.WriteLine("Total number of tables found: " + table.Rows.Count);

  for (int i = 0; i < table.Rows.Count; i++) {
    SalesForceCommand cmd = new SalesForceCommand(String.Format("SELECT * FROM sys_tablecolumns WHERE TableName=[{0}]", (String)table.Rows[i]["TableName"]), conn);
    SalesForceDataReader rdr = cmd.ExecuteReader();
    Console.WriteLine("Cached metadata for table " + table.Rows[i]["TableName"] + " [" + i + "]");
  }
  Console.WriteLine();
  Console.WriteLine("All tables cached.");
}