示例#1
0
        public static void Main(string[] args)
        {
            try
            {
                // Parse command line arguments
                s_commandLine = new CommandLine(args);
                if (!s_commandLine.IsValid)
                {
                    s_commandLine.WriteUsage();
                    return;
                }

                // Get Shard Map Manager
                ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
                    GetConnectionString(), ShardMapManagerLoadPolicy.Eager);
                Console.WriteLine("Connected to Shard Map Manager");

                // Get Shard Map
                ShardMap map = smm.GetShardMap(s_commandLine.ShardMap);
                Console.WriteLine("Found {0} shards", map.GetShards().Count());

                // Create connection string for MultiShardConnection
                string connectionString = GetCredentialsConnectionString();

                // REPL
                Console.WriteLine();
                while (true)
                {
                    // Read command from console
                    string commandText = GetCommand();
                    if (commandText == null)
                    {
                        // Exit requested
                        break;
                    }

                    // Evaluate command
                    string output;
                    using (MultiShardConnection conn = new MultiShardConnection(map.GetShards(), connectionString))
                    {
                        output = ExecuteCommand(conn, commandText);
                    }

                    // Print output
                    Console.WriteLine(output);
                }
            }
            catch (Exception e)
            {
                // Print exception and exit
                Console.WriteLine(e);
                return;
            }
        }
        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);
                    }
                }
            }
        }
示例#3
0
        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();
        }
示例#4
0
        /// <summary>
        /// Executes the SQL command and returns the output in text format.
        /// </summary>
        private static string ExecuteCommand(MultiShardConnection conn, string commandText)
        {
            try
            {
                StringBuilder output = new StringBuilder();
                output.AppendLine();

                int rowsAffected = 0;

                using (MultiShardCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    cmd.CommandTimeout = s_commandLine.QueryTimeout;
                    cmd.CommandTimeoutPerShard = s_commandLine.QueryTimeout;

                    // Execute command and time with a stopwatch
                    Stopwatch stopwatch = Stopwatch.StartNew();
                    cmd.ExecutionPolicy = s_commandLine.ExecutionPolicy;
                    cmd.ExecutionOptions = s_commandLine.ExecutionOptions;
                    using (MultiShardDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
                    {
                        stopwatch.Stop();

                        // Get column names
                        IEnumerable<string> columnNames = GetColumnNames(reader).ToArray();

                        // Create table formatter
                        TableFormatter tableFormatter = new TableFormatter(columnNames.ToArray());

                        // Read results from db
                        while (reader.Read())
                        {
                            rowsAffected++;

                            // Add the row to the table formatter
                            object[] values = new object[reader.FieldCount];
                            reader.GetValues(values);
                            tableFormatter.AddRow(values);
                        }

                        // Write formatter output
                        output.AppendLine(tableFormatter.ToString());
                    }

                    output.AppendLine();
                    output.AppendFormat("({0} rows affected - {1:hh}:{1:mm}:{1:ss} elapsed)", rowsAffected, stopwatch.Elapsed);
                    output.AppendLine();
                }

                return output.ToString();
            }
            catch (MultiShardAggregateException e)
            {
                return e.ToString();
            }
        }
 public DataSet MultiShardQuery(string commandText)
 {
     DataSet _ds = new DataSet();
     try
     {
         RetryPolicy.DefaultExponential.ExecuteAction(() =>
         {
             using (MultiShardCommand cmd = new MultiShardConnection(lsm.GetShards(), shardConnectionString).CreateCommand())
             {
                 cmd.CommandText = commandText;
                 cmd.CommandType = CommandType.Text;
                 cmd.ExecutionOptions = MultiShardExecutionOptions.None;
                 //04/23/2015 by Mark Berman - switched to Partial results versus CompleteResults as timeout exception was being thrown
                 cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
                 using (MultiShardDataReader sdr = cmd.ExecuteReader())
                 {
                     if (sdr.Read())
                     {
                         // the multi-shard query does not return a dataset or datarow.
                         // we have to manually re-create a table structure, then fill it with data.
                         object[] sqlValues = new object[sdr.FieldCount];
                         sdr.GetSqlValues(sqlValues);
                         DataTable dtValues = new DataTable();
                         foreach (var column in sqlValues)
                             dtValues.Columns.Add(new DataColumn { DataType = column.GetType() });
                         _ds.Tables.Add(dtValues);
                         _ds.Tables[0].Rows.Add(sqlValues);
                         while (sdr.Read())
                         {
                             sdr.GetSqlValues(sqlValues);
                             _ds.Tables[0].Rows.Add(sqlValues);
                         }
                     }
                 }
             }
         });
     }
     catch { _ds = null; }
     return _ds;
 }