The RSSBus Salesforce Data Provider for ADO.NET makes retrieving data from Salesforce a breeze. Sometimes you may need to return tens of thousands of records. How do you get all this information without huge delays? The caching features of the Salesforce ADO.NET Data Provider make this problem a cinch.

Creating the Cache

Queries from a local cache bring data back much faster than directly from Salesforce.com. Populating and accessing the cache requires setting just two values, Offline and Auto Cache, in a connection string.

• Step 1: Open Visual Studio and create a new C# project.
• Step 2: Add a reference to System.Data.RSSBus.SalesForce to the project and import it to your main class.
• Step 3: Add the following code sample to your project:

string connectionString = 
	"User=user;" +
	"Password=password;" +
	"Access Token=token" +
	"Offline=False;" +
	"Auto Cache=True;" +
	"Cache Location=C:\\mycache.db;";

SalesForceConnection conn = new SalesForceConnection(connectionString);
SalesForceDataAdapter dataAdapter = new SalesForceDataAdapter("Select * From Accounts", conn);
DataTable table = new DataTable();
dataAdapter.Fill(table);

Note in the connectionString that "Offline=False;", which tells the Data Provider to query Salesforce instead of the cache, and that "Auto Cache=True;", which populates the cache with the data pulled from Salesforce in your query

• Step 4: Modify the connection string so that it fits your Salesforce connection settings.
• Step 5: Run the code sample without debugging to get it to execute faster.

As an optimization measure, it is possible Salesforce has returned only some of the rows matching your query, with additional rows waiting to be fetched. Salesforce appends query results with a column entitled "QueryLocator". The value in the "QueryLocator" column for all rows is the same, provided more results need fetched. To fetch more results via the QueryLocator insert the following code:

String queryLocator = null;

do {
	String query = "SELECT * FROM Accounts";
		
	if (!string.IsNullOrWhiteSpace(queryLocator)) {
		query += string.Format(" WHERE QueryLocator='{0}'", queryLocator);
	}
	
	SalesForceConnection conn = new SalesForceConnection(connectionString);
	SalesForceDataAdapter dataAdapter = new SalesForceDataAdapter(query.ToString(), conn);
	dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
	dataAdapter.Fill(table);

	if (table.Rows.Count > 0) {
		int lastRow = table.Rows.Count - 1;
		queryLocator = table.Rows[lastRow]["QueryLocator"].ToString();
	}
} while (!string.IsNullOrEmpty(queryLocator));

Now that you have a cache, you can make queries to it. Not only that, but the internal cache is not limited by the Salesforce API. This allows you to make more advanced SQLite SELECT calls that are not available when accessing directly from the Data Source. Here's how to access the cache:

• Step 1: Set the Offline connection value to "True" to direct the adapter to the cache.
• Step 2: To disable the SQL Parser from performing error checking, set the Query Passthrough connection value to "True", or add it to the connection string if it did not previously exist.

Congratulations! You can now get data directly from the cache. Try comparing the performance between getting data from the cache and directly from Salesforce. You will find that it is much faster to get cached data than data directly from Salesforce. In addition, unlike SalesForce, the cache can always return all rows of data.

Keeping the Cache up to Date

Once you have a cache, you do not need to get everything from Salesforce again to update the cache. Instead, you can use the LastModifiedDate column available on every table in the RSSBus Data Provider. However, since comparative operators (aside from equals) are not available in Salesforce, you will need to use a SOQLWHERE condition in your SELECT statement.

Here's a modified version of the previous code sample illustrating how:

// First, get the most recent modified time from the cache. 
// Set Query Passthrough to true to run directly against SQLite.
connectionString = connectionString.Replace("Offline=False", "Offline=True");
connectionString = connectionString.Replace("Query Passthrough=False", "Query Passthrough=True");
String query = "SELECT max(LastModifiedDate) as MaxTime FROM Accounts";
SalesForceDataAdapter dataAdapter = new SalesForceDataAdapter(query, connectionString);
dataAdapter.Fill(table);

// Now set that to be our most recent date. 
// If there are no rows, set date to beginning of epoch to retrieve all rows.
string date;
if (datatable.Rows.Count > 0)
	date = (string)datatable.Rows[0]["MaxTime"];
else
	date = "1970-01-01 00:00:00.000";

DateTime dateTime = DateTime.ParseExact(date, "yyyy-MM-dd HH:mm:ss.fff", null);
date = string.Format("{0:s}Z", dateTime); //SOQL-specific date formatting

// Now search the live data source for only modified dates as new or newer than the one we 
// found. Filling a table will cause the cache to be updated with the results when 
// "Auto Cache=True" in the connection string.
connectionString = connectionString.Replace("Offline=True", "Offline=False");
connectionString = connectionString.Replace("Query Passthrough=True", "Query Passthrough=False");
SalesForceConnection conn = new SalesForceConnection(connectionString);
String query = String.Format("SELECT * FROM Accounts WHERE (SOQLWHERE = 'LastModifiedDate>={1}')", date);
dataAdapter = new SalesForceDataAdapter(query, conn);
dataAdapter.Fill(table);

Automating the Caching Process

There is just one more step to keeping your cache up to date. You will simply need to make an automatic process that updates the cache on a regular basis. We are including a sample project that uses the Timer object to regularly update the cache based on the table names passed to it. Click here to download the sample project. You will also need to install the Salesforce ADO.NET Data Provider to run the demo. You can download a free trial here.