Database Reference
In-Depth Information
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 10-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.
