/// <summary>
/// ExecuteMultiShardQuery method to get the database records using MultiShardConnection,
/// MultiShardCommand,multiShardDataReader class
/// </summary>
/// <param name="shardMap"></param>
/// <param name="credentialsConnectionString"></param>
public static void ExecuteMultiShardQuery(RangeShardMap <int> shardMap)
{
// Get the Shards from Shard Map manager
MultiShardConfiguration.Shards = shardMap.GetShards();
TestParameterValue testParameterValue
= new TestParameterValue(
"MultiShard", "ExecMultiShard", "SelectAll_DR",
"SqlDbWithMultiShard" + "%"
+ "individual" + "%"
+ "-",
new MyUserInfo("MultiShard", "MultiShard"));
// 分離レベルの設定
DbEnum.IsolationLevelEnum iso = DbEnum.IsolationLevelEnum.NoTransaction;
// B層を生成
LayerB myBusiness = new LayerB();
// 業務処理を実行
TestReturnValue testReturnValue =
(TestReturnValue)myBusiness.DoBusinessLogic(
(BaseParameterValue)testParameterValue, iso);
string strErrorMsg = "";
if (testReturnValue.ErrorFlag == true)
{
// 結果（業務続行可能なエラー）
strErrorMsg = "ErrorMessageID:" + testReturnValue.ErrorMessageID + "\r\n";
strErrorMsg += "ErrorMessage:" + testReturnValue.ErrorMessage + "\r\n";
strErrorMsg += "ErrorInfo:" + testReturnValue.ErrorInfo + "\r\n";
Console.WriteLine("Inserted failed for Error message : {0}", strErrorMsg);
}
else
{
//Converts Return value object to dataTable data to display the data in screen
DataTable dtTable = (DataTable)testReturnValue.Obj;
int rows = 0;
// Get the column names
TableFormatter formatter = new TableFormatter(ShardManagementUtils.GetColumnNames(dtTable).ToArray());
foreach (DataRow dr in dtTable.Rows)
{
// Extract just database name from the $ShardLocation pseudocolumn to make the output formater cleaner.
// Note that the $ShardLocation pseudocolumn is always the last column
int shardLocationOrdinal = dr.ItemArray.Length - 1;
dr.ItemArray[shardLocationOrdinal] = ShardManagementUtils.ExtractDatabaseName(dr.ItemArray[shardLocationOrdinal].ToString());
// Add values to output formatter
formatter.AddRow(dr.ItemArray);
rows++;
}
Console.WriteLine(formatter.ToString());
Console.WriteLine("({0} rows returned)", rows);
}
}
public static void ExecuteMultiShardQuery(RangeShardMap<int> shardMap, string credentialsConnectionString)
{
// Get the shards to connect to
IEnumerable<Shard> shards = shardMap.GetShards();
// Create the multi-shard connection
using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString))
{
// Create a simple command
using (MultiShardCommand cmd = conn.CreateCommand())
{
// Because this query is grouped by CustomerID, which is sharded,
// we will not get duplicate rows.
cmd.CommandText = @"
SELECT
c.CustomerId,
c.Name AS CustomerName,
COUNT(o.OrderID) AS OrderCount
FROM
dbo.Customers AS c INNER JOIN
dbo.Orders AS o
ON c.CustomerID = o.CustomerID
GROUP BY
c.CustomerId,
c.Name
ORDER BY
OrderCount";
// 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())
{
// Get the column names
TableFormatter formatter = new TableFormatter(GetColumnNames(reader).ToArray());
int rows = 0;
while (reader.Read())
{
// Read the values using standard DbDataReader methods
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
// Extract just database name from the $ShardLocation pseudocolumn to make the output formater cleaner.
// Note that the $ShardLocation pseudocolumn is always the last column
int shardLocationOrdinal = values.Length - 1;
values[shardLocationOrdinal] = ExtractDatabaseName(values[shardLocationOrdinal].ToString());
// Add values to output formatter
formatter.AddRow(values);
rows++;
}
Console.WriteLine(formatter.ToString());
Console.WriteLine("({0} rows returned)", rows);
}
}
}
}
/// <summary>
/// ExecuteMultiShardQuery method to get the database records using MultiShardConnection,
/// MultiShardCommand,ultiShardDataReader class
/// </summary>
/// <param name="shardMap"></param>
/// <param name="credentialsConnectionString"></param>
public static void ExecuteMultiShardQuery(RangeShardMap <int> shardMap, string credentialsConnectionString)
{
// Get the shards to connect to
IEnumerable <Shard> shards = shardMap.GetShards();
// Create the multi-shard connection
using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString))
{
// Create a simple command
using (MultiShardCommand cmd = conn.CreateCommand())
{
// Because this query is grouped by CustomerID, which is sharded,
// we will not get duplicate rows.
cmd.CommandText = @"
SELECT
c.CustomerId,
c.Name AS CustomerName,
COUNT(o.OrderID) AS OrderCount
FROM
dbo.Customers AS c INNER JOIN
dbo.Orders AS o
ON c.CustomerID = o.CustomerID
GROUP BY
c.CustomerId,
c.Name
ORDER BY
OrderCount";
// 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())
{
// Get the column names
TableFormatter formatter = new TableFormatter(GetColumnNames(reader).ToArray());
int rows = 0;
while (reader.Read())
{
// Read the values using standard DbDataReader methods
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
// Extract just database name from the $ShardLocation pseudocolumn to make the output formater cleaner.
// Note that the $ShardLocation pseudocolumn is always the last column
int shardLocationOrdinal = values.Length - 1;
values[shardLocationOrdinal] = ExtractDatabaseName(values[shardLocationOrdinal].ToString());
// Add values to output formatter
formatter.AddRow(values);
rows++;
}
Console.WriteLine(formatter.ToString());
Console.WriteLine("({0} rows returned)", rows);
}
}
}
}
