5. Updating and Deleting Records in the Shard
At this point, you're ready
to see how updates and deletes take place through the shard. Updates
and deletes against the databases in the shard can either be performed
for records in a given database or against all databases. At a high
level, here are some guidelines you can use to decide on an approach:
Update or delete records in a single database.
You update or delete one or more records in a database when you already
know the database GUID to use. This is the case when you use the shard
to retrieve records, because the database GUID is provided for all
records returned.
Update or delete records across databases.
Generally speaking, you update or delete records across databases in
the shard whenever you don't know which database a record is in, or when
all records need to be evaluated.
To update or delete records
in a single database, you must provide a command parameter that contains
the database GUID to use. Here's the code that updates a single record
in the shard. On lines 1 through 7, the code creates a command object
that calls a stored procedure that requires two parameters. On line 9,
the code adds the database GUID to use. This extra parameter is removed
by the shard library before making the call to the requested database:
1) cmd.CommandText = "sproc_update_user";
2) cmd.CommandType = CommandType.StoredProcedure;
3)
4) cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
5) cmd.Parameters["@id"].Value = int.Parse(labelIDVal.Text);
6) cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 20));
7) cmd.Parameters["@name"].Value = textBoxUser.Text;
8)
9) cmd.Parameters.Add(new SqlParameter(
PYN.EnzoAzureLib.Shard._GUID_, labelGUID.Text));
10)
11) ExecuteShardNonQuery (cmd);
Note that calling a stored procedure isn't required for this code to run. All that is required is that a SqlCommand object be used; the SQL code may very well be inline SQL.
Deleting a record from the
shard is virtually identical. The command object is created with the
required stored procedure parameters from lines 1 through 5. On line 7,
the code adds the database GUID to use:
1) cmd.CommandText = "sproc_delete_user";
2) cmd.CommandType = CommandType.StoredProcedure;
3)
4) cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
5) cmd.Parameters["@id"].Value = int.Parse(labelIDVal.Text);
6)
7) cmd.Parameters.Add(new SqlParameter(
PYN.EnzoAzureLib.Shard._GUID_, labelGUID.Text));
8)
9) ExecuteShardNonQuery (cmd);
NOTE
The ExecuteShardNonQuery
method behaves differently if it has no database GUID parameter (it
executes the query against all databases), if it has a database GUID
parameter with a value (it executes the query against the specified
database), or if it contains a database GUID parameter with a NULL
value (it executes the query against the next database in the shard
using round-robin). You see how to use round-robin calls when adding
records in the shard shortly.
Figure 7 shows the sample application updating a record from the shard. When you click Reload Grid, a SELECT
statement is issued against the shard, which returns the database GUID
for each record. Then, when you select a specific record, the record
details are loaded in the right section of the screen, along with the
record's database GUID. At this point, the record can be updated or
deleted.
Because records are being updated or deleted, the client code clears the cache to force future SELECT statements to fetch records from the databases in the shard. The shard library exposes a ResetCache
method that does just that. You can improve this logic by also
performing the same update or delete operation of records in the cache.
Updating or deleting records
across databases in the shard is even simpler. The following code
executes an inline SQL statement using a SqlCommand
object. Because no database GUID is provided, this statement executes
the statement across all databases in the shard. When you perform
updates or deletes across databases, it's important to set the parallel
flag correctly, as shown on line 1:
1) PYN.EnzoAzureLib.Shard.UseParallel = checkBoxParallel.Checked;
2) cmd.CommandText = "UPDATE TestUsers2 SET LastUpdated = GETDATE()";
3) cmd.CommandType = CommandType.Text;
4) ExecuteShardNonQuery (cmd);
6. Adding Records to the Shard
You see how easy it is to add
records to the shard databases. This shard works best from a performance
standpoint when all databases in the shard have a roughly equal number
of records; this is because parallel processing is performed without any
deterministic logic. As a result, the more spread out your records are
in the shard, the faster it is. You can add records in the shard in two
ways:
In a single database.
If you're loading the shard for the first time, you may decide to load
certain records in specific databases. Or you may decide to load one
database with more records than others, if the hardware is superior.
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) }
NOTE
The call to ExecuteParallelRoundRobinLoad
is different in two ways from all the other methods you've seen so far.
First, there is no need to add the database GUID parameter; it creates
this parameter automatically with a NULL value. Second, this method executes on a List<SqlCommand> object instead of SqlCommand.
Figure 8
shows the sample application screen that creates the array of names to
load in the shard. Six names are added in the shard using round-robin,
as previously described.
