Getting external data into your store

Extranet 5.6 and 5.7 enabled greater access to the data stored in the underlying database by exposing more data in standard SQL tables and automatically creating SQL Views that simplify the task of authoring SQL Queries to extract information. All of this is intended to support the needs for advanced reporting or data extraction. In other words, they are targeted at getting data out of Extranet. Thinking about how best to get data into the Extranet database is a completely different matter, however.

With the ability to query directly from the SQL database to extract data, it’s tempting to think that standard INSERT and UPDATE statements can be used to push data into the database. It’s important to avoid that temptation.

Do not push data directly into the Extranet SQL database.

There are many valid ways to get data into the Extranet database and they all involve a layer of code at or above the Extranet Framework. These include:

• Web Services
  When driven by external applications to facilitate a push of data into the store, this approach is incredibly convenient. The standard set of Web Services combined with configured Web Service interfaces allow great flexibility in what data is sent into your site. A key advantage to this approach is that the data push can be initiated from remote servers.
  
  
• Bulk Import
  If your needs for data loading don’t require an external application or automated push, then Bulk Import allows you to define all the imported data in a standard Excel Spreadsheet or XML file.
  
  
• In-Store scripts
  Sometimes rather than an external application pushing the data into the store, a better approach would be to pull external data into the store. This can be done as a simple script method and you can even go so far as to define an activity type that can be executed on a schedule to update your site with data form the outside world.
  
  
• Direct push via Extranet Framework APIs
  Using the .Net Exposure of the Framework Assembly classes, you can author an external .Net application to push data into the store.

All of these techniques guarantee that the Framework is involved in the creation and update of all data managed in the Extranet database. There are at least four fundamental reasons why this is necessary:

• The in-memory cache (WOM Cache) of objects needs to stay in synch with the data in the database. This is managed in the Extranet Framework by first updating the cache then committing the changes to SQL. In this way, the synchronization is intact. The update to both will fail or succeed together so there is no opportunity to get them out of synch.
• Another cache to be concerned with is the HTML Cache. By changing the data without the knowledge of the Application or Framework, the HTML cache will go stale and not refresh itself to reflect the new information.
• The Framework is responsible for maintaining the integrity of the database and the corpus. These rules are enforced in the framework layer and not in SQL. As a result, it’s easy to corrupt the database if it were to be updated directly.
• The locking model must be respected. Locks on entities are maintained by the Framework to guarantee the integrity of the data. This is done by the Framework, not the SQL Database.

By using these techniques to keep the Framework involved in any updates to the underlying database, your system will remain whole and happy.

Cheers!