/// <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);
                    }
                }
            }
        }
Ejemplo n.º 3
0
        /// <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);
                    }
                }
            }
        }