Databases Reference
In-Depth Information
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. There are pros and cons of using stored procedures and
inline SQL in a sharding environment. As you may already know, using inline SQL can introduce security threats,
such as SQL injection; and stored procedures are typically much faster because they are precompiled. However, keep
in mind that using stored procedures introduces a new level of complexity because you will need to make sure your
stored procedures are identical across all your shard databases.
Deleting a record from the shard is virtually identical. The command object is created with the required stored
procedure parameters in 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);
■
the
ExecuteShardNonQuery
method behaves differently depending on its database gUiD parameter. if it
has no 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. 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 will see how to use round-robin calls
when adding records in the shard shortly.
Note
Figure
9-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 (by selecting the entire row), 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. If you run this sample program, make
sure to follow the configuration steps found in the Introductions tab.
