Let's build a shard library that can be used by
applications that need to load and update data against multiple SQL
Azure databases as quickly and transparently as possible. For the
purpose of building an efficient shard library, you stipulate the
following requirements for the shard:
Adding new databases should be simple and transparent to the client code.
Adding new databases shouldn't affect performance negatively.
The library should function with SQL Server, SQL Azure, or both.
The library should optionally cache results for fast retrieval.
The library should support mass or selective reads and writes.
Data returned by the library should be accepted as a data source for controls.
These requirements have very specific implications from a technology standpoint.
Table 2. Technologies Used to Build the Shard
|Configuration file||1||The configuration file stores the list of databases that make up the shard.|
the TPL lets the library to spawn multiple threads to use computers
with multiple CPUs, allowing parallel execution of SQL statements.|
|SqlClient||3||Using SqlCommand objects allows the shard to connect to both SQL Azure and SQL Server databases.|
|Caching||4||Caching lets the library store results temporarily to avoid unnecessary roundtrips.|
library creates a virtual column for each record returned that stores a
breadcrumb identifying the database a record it came from.|
|DataTable||6||The library returns a DataTable object that can be bound to objects easily.|
1. Designing the Shard Library Object
The library accepts requests
directly from client applications and can be viewed as an API. Note that
you're using extension methods to make this API blend in with the
existing SqlCommand class; this in turn minimizes the amount of code on the client and makes the application easier to read.
shows where the library fits in a typical application design. It also
shows how the library hides the complexity of parallel processing and
caching from the client application. Finally, the shard library
abstracts the client code from dealing directly with multiple databases.
Figure 1. Shard library object diagram
A sample application is
provided to demonstrate how to use the shard library. Although the
application is very simple, it uses all the features of the shard for