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