Designing for High Performance - Pro SQL Azure - page 214

Across databases. Usually, you load records in the shard without specifying a
database. The shard library uses a round-robin mechanism to load records.
Adding a record in a specific database is no different than updating or deleting a record in a
database; all you need to do is create a SqlCommand object, set the INSERT statement, and add a
SqlParameter indicating the database GUID to use.
Adding one or more records across databases requires a slightly different approach. The round-
robin logic stores the last database used to insert records in the shard. The shard library exposes two
methods to perform inserts:
• ExecuteShardNonQuery . As you've seen previously, this method extends the
SqlCommand object and executes statements against the next database in the shard
(round-robin) if the GUID parameter is NULL . This convention is used to let the
shard library know that it should move its internal database pointer to the next
database in the shard for the next round-robin call.
• ExecuteParallelRoundRobinLoad . This method extends List<SqlCommand> and
provides a mechanism to create a collection of SqlCommand objects. Each
SqlCommand object contains an INSERT statement to execute. This method adds a
NULL database GUID and calls ExecuteShardNonQuery to execute all the statements
with round-robin support. This construct simplifies loading a shard quickly by
spreading INSERT statements evenly across all databases.
The following code shows how the client prepares the call to ExecuteParallelRoundRobinLoad . Line 1
creates a collection of SqlCommand objects. Then, on line 3, an outer loop executes for each value found in
the userName array (this is a list of names to add to the shard). From lines 5 to 16, a SqlCommand object is
created for each name to INSERT and is added to the collection. Line 22 makes the actual call to
ExecuteParallelRoundRobinLoad . Finally, on line 23, if all goes well, the library's cache is cleared:
1)

•

List<SqlCommand> commands = new List<SqlCommand>();

2)

3) foreach (string name in userName)

4) {

5) if (name != null && name.Trim().Length > 0)

6) {

7) SqlCommand cmdToAdd = new SqlCommand();

8) cmdToAdd.CommandText = "sproc_add_user";

9) cmdToAdd.CommandType = CommandType.StoredProcedure;

10)
11) cmdToAdd.Parameters.Add(
12) new SqlParameter("name", SqlDbType.NVarChar, 20));

13) cmdToAdd.Parameters["name"].Value = name;
14)
15) commands.Add(cmdToAdd);
16) }
17) }
18)
19) // Make the call!
20) if (commands.Count > 0)
21) {
22) commands.ExecuteParallelRoundRobinLoad();
23) Shard.ResetCache();
24) }