/// <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); } } } }