public void TestQueryShardsAsync()
{
// Create new sharded connection so we can test the OpenAsync call as well.
//
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
using (MultiShardDataReader sdr = ExecAsync(conn, cmd).Result)
{
int recordsRetrieved = 0;
while (sdr.Read())
{
recordsRetrieved++;
var dbNameField = sdr.GetString(0);
var testIntField = sdr.GetFieldValue <int>(1);
var testBigIntField = sdr.GetFieldValue <Int64>(2);
Logger.Log("RecordRetrieved: dbNameField: {0}, TestIntField: {1}, TestBigIntField: {2}, RecordCount: {3}",
dbNameField, testIntField, testBigIntField, recordsRetrieved);
}
Assert.AreEqual(recordsRetrieved, 9);
}
}
}
}
public void TestSimpleSelect(MultiShardExecutionPolicy policy)
{
// What we're doing:
// Grab all rows from each test database.
// Load them into a MultiShardDataReader.
// Iterate through the rows and make sure that we have 9 total.
//
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable";
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
cmd.ExecutionPolicy = policy;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
int recordsRetrieved = 0;
Logger.Log("Starting to get records");
while (sdr.Read())
{
recordsRetrieved++;
string dbNameField = sdr.GetString(0);
int testIntField = sdr.GetFieldValue <int>(1);
Int64 testBigIntField = sdr.GetFieldValue <Int64>(2);
string shardIdPseudoColumn = sdr.GetFieldValue <string>(3);
string logRecord =
string.Format(
"RecordRetrieved: dbNameField: {0}, TestIntField: {1}, TestBigIntField: {2}, shardIdPseudoColumnField: {3}, RecordCount: {4}",
dbNameField, testIntField, testBigIntField, shardIdPseudoColumn, recordsRetrieved);
Logger.Log(logRecord);
Debug.WriteLine(logRecord);
}
sdr.Close();
Assert.AreEqual(recordsRetrieved, 9);
}
}
}
}
public static void Main()
{
SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder
{
UserID = userName,
Password = password,
ApplicationName = applicationName
};
#region Create Shards
// Bootstrap the shard map manager, register shards, and store mappings of tenants to shards
// Note that you can keep working with existing shard maps. There is no need to
// re-create and populate the shard map from scratch every time.
Console.WriteLine("Checking for existing shard map and creating new shard map if necessary.");
Sharding sharding = new Sharding(server, shardmapmgrdb, connStrBldr.ConnectionString);
sharding.RegisterNewShard(server, shard1, connStrBldr.ConnectionString, tenantId1);
sharding.RegisterNewShard(server, shard2, connStrBldr.ConnectionString, tenantId2);
#endregion
#region Insert Items
// Do work for tenant 1 :-)
// Create and save a new Blog
Console.Write("Enter a name for a new Blog: ");
var name = Console.ReadLine();
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext <int>(sharding.ShardMap, tenantId1, connStrBldr.ConnectionString))
{
var blog = new Blog {
Name = name
};
db.Blogs.Add(blog);
db.SaveChanges();
}
});
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext <int>(sharding.ShardMap, tenantId1, connStrBldr.ConnectionString))
{
// Display all Blogs for tenant 1
var query = from b in db.Blogs
orderby b.Name
select b;
Console.WriteLine("All blogs for tenant id {0}:", tenantId1);
foreach (var item in query)
{
Console.WriteLine(item.Name);
}
}
});
// Do work for tenant 2 :-)
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext <int>(sharding.ShardMap, tenantId2, connStrBldr.ConnectionString))
{
// Display all Blogs from the database
var query = from b in db.Blogs
orderby b.Name
select b;
Console.WriteLine("All blogs for tenant id {0}:", tenantId2);
foreach (var item in query)
{
Console.WriteLine(item.Name);
}
}
});
// Create and save a new Blog
Console.Write("Enter a name for a new Blog: ");
var name2 = Console.ReadLine();
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext <int>(sharding.ShardMap, tenantId2, connStrBldr.ConnectionString))
{
var blog = new Blog {
Name = name2
};
db.Blogs.Add(blog);
db.SaveChanges();
}
});
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext <int>(sharding.ShardMap, tenantId2, connStrBldr.ConnectionString))
{
// Display all Blogs from the database
var query = from b in db.Blogs
orderby b.Name
select b;
Console.WriteLine("All blogs for tenant id {0}:", tenantId2);
foreach (var item in query)
{
Console.WriteLine(item.Name);
}
}
});
#endregion
#region Query
Console.WriteLine("Staring Multi Shard Read via MultiShardCommand");
/// Multi Shard querying
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (MultiShardConnection conn = new MultiShardConnection(
sharding.ShardMap.GetShards(),
connStrBldr.ConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT BlogId, Name FROM Blogs";
cmd.CommandType = CommandType.Text;
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
var c2Field = sdr.GetFieldValue <int>(0);
var c1Field = sdr.GetFieldValue <string>(1);
Blog blog = new Blog(c2Field, c1Field, null);
Console.WriteLine("Blog Entry: " + blog.Name);
}
}
}
}
});
Console.WriteLine("Staring Multi Shard Read via Elastic Scale");
//Elastic Query
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (SqlConnection conn = new SqlConnection(elasticqueryString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT BlogId, Name FROM Blogs";
cmd.CommandType = CommandType.Text;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
var c2Field = sdr.GetFieldValue <int>(0);
var c1Field = sdr.GetFieldValue <string>(1);
Blog blog = new Blog(c2Field, c1Field, null);
Console.WriteLine("Blog Entry: " + blog.Name);
}
}
}
}
});
#endregion
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
}
public void TestShardNamePseudoColumnOption()
{
bool[] pseudoColumnOptions = new bool[2];
pseudoColumnOptions[0] = true;
pseudoColumnOptions[1] = false;
// do the loop over the options.
// add the excpetion handling when referencing the pseudo column
//
foreach (bool pseudoColumnPresent in pseudoColumnOptions)
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.CompleteResults;
cmd.ExecutionOptions = pseudoColumnPresent ? MultiShardExecutionOptions.IncludeShardNameColumn :
MultiShardExecutionOptions.None;
using (MultiShardDataReader sdr = cmd.ExecuteReader(CommandBehavior.Default))
{
Assert.AreEqual(0, sdr.MultiShardExceptions.Count);
int recordsRetrieved = 0;
int expectedFieldCount = pseudoColumnPresent ? 4 : 3;
int expectedVisibleFieldCount = pseudoColumnPresent ? 4 : 3;
Assert.AreEqual(expectedFieldCount, sdr.FieldCount);
Assert.AreEqual(expectedVisibleFieldCount, sdr.VisibleFieldCount);
while (sdr.Read())
{
recordsRetrieved++;
var dbNameField = sdr.GetString(0);
var testIntField = sdr.GetFieldValue <int>(1);
var testBigIntField = sdr.GetFieldValue <Int64>(2);
try
{
string shardIdPseudoColumn = sdr.GetFieldValue <string>(3);
if (!pseudoColumnPresent)
{
Assert.Fail("Should not have been able to pull the pseudo column.");
}
}
catch (IndexOutOfRangeException)
{
if (pseudoColumnPresent)
{
Assert.Fail("Should not have encountered an exception.");
}
}
}
Assert.AreEqual(recordsRetrieved, 9);
}
}
}
}
}
/// <summary>
/// Executes a sql command on an elastic scale DB over all shards available in the provided shard map and returns incidents.
/// </summary>
public static IList <IncidentModel> ExecuteMultiShardQuery(string credentialsConnectionString, string commandText, params Shard[] shards)
{
if (shards == null)
{
throw new ArgumentNullException(nameof(shards));
}
if (credentialsConnectionString == null)
{
throw new ArgumentNullException(nameof(credentialsConnectionString));
}
if (commandText == null)
{
throw new ArgumentNullException(nameof(commandText));
}
// Get the shards to connect to
List <IncidentModel> result = new List <IncidentModel>();
// Create the multi-shard connection
using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString))
{
// Create a simple command
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
// Append a column with the shard name where the row came from
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
// Allow for partial results in case some shards do not respond in time
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
// Allow the entire command to take up to 30 seconds
cmd.CommandTimeout = 30;
// Execute the command.
// We do not need to specify retry logic because MultiShardDataReader will internally retry until the CommandTimeout expires.
using (MultiShardDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var incidentId = reader.GetFieldValue <int>(0);
var departmentType = reader.GetFieldValue <int>(1);
var title = reader.GetFieldValue <string>(2);
var desc = reader.GetFieldValue <string>(3);
var region = reader.GetFieldValue <int>(4);
var shardName = ExtractDatabaseName(reader.GetFieldValue <string>(5));
var incident = new IncidentModel
{
IncidentId = incidentId,
DepartmentType = departmentType,
ShardName = shardName,
Description = desc,
Title = title,
RegionId = region
};
result.Add(incident);
}
}
}
}
return(result);
}
/// <summary>
/// Executes a sql command on an elastic scale DB over all shards available in the provided shard map and returns incidents.
/// </summary>
public static IList <IncidentModel> ExecuteMultiShardQuery(string credentialsConnectionString, string commandText, params Shard[] shards)
{
if (shards == null)
{
throw new ArgumentNullException(nameof(shards));
}
if (credentialsConnectionString == null)
{
throw new ArgumentNullException(nameof(credentialsConnectionString));
}
if (commandText == null)
{
throw new ArgumentNullException(nameof(commandText));
}
// Get the shards to connect to
List <IncidentModel> result = new List <IncidentModel>();
// Create the multi-shard connection
using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString))
{
// Create a simple command
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
// Append a column with the shard name where the row came from
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
// Allow for partial results in case some shards do not respond in time
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
// Allow the entire command to take up to 30 seconds
cmd.CommandTimeout = 30;
// Execute the command.
// We do not need to specify retry logic because MultiShardDataReader will internally retry until the CommandTimeout expires.
using (MultiShardDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetFieldValue <int>(0);
var cityId = reader.GetFieldValue <int>(1);
var callNumber = reader.GetFieldValue <string>(2);
var phone = reader.GetFieldValue <string>(3);
var unmaskedPhone = reader.GetFieldValue <string>(4);
var title = reader.GetFieldValue <string>(5);
var receivedTime = reader.GetFieldValue <DateTime>(6);
var address = reader.GetFieldValue <string>(7);
var reportingParty = reader.GetFieldValue <string>(8);
var unmaskedReportingParty = reader.GetFieldValue <string>(9);
var description = reader.GetFieldValue <string>(10);
var updateDescription = reader.GetFieldValue <string>(11);
var longitude = reader.GetFieldValue <double>(12);
var latitude = reader.GetFieldValue <double>(13);
var isHighPriority = reader.GetFieldValue <bool>(14);
var incidentCategory = reader.GetFieldValue <IncidentType>(15);
var incident = new IncidentModel
{
Id = id,
CityId = cityId,
CallNumber = callNumber,
Phone = phone,
UnmaskedPhone = unmaskedPhone,
Title = title,
ReceivedTime = receivedTime,
Address = address,
ReportingParty = reportingParty,
UnmaskedReportingParty = unmaskedReportingParty,
Description = description,
UpdateDescription = updateDescription,
Longitude = longitude,
Latitude = latitude,
IsHighPriority = isHighPriority,
IncidentCategory = incidentCategory,
SearchAreaId = null
};
result.Add(incident);
}
}
}
}
return(result);
}
