// Enter a new shard - i.e. an empty database - to the shard map, allocate a first tenant to it
// and kick off EF intialization of the database to deploy schema
// public void RegisterNewShard(string server, string database, string user, string pwd, string appname, int key)
public void RegisterNewShard(string server, string database, string connstr, int key)
{
Shard shard;
ShardLocation shardLocation = new ShardLocation(server, database);
if (!this.ShardMap.TryGetShard(shardLocation, out shard))
{
shard = this.ShardMap.CreateShard(shardLocation);
}
SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder(connstr);
connStrBldr.DataSource = server;
connStrBldr.InitialCatalog = database;
// Go into a DbContext to trigger migrations and schema deployment for the new shard.
// This requires an un-opened connection.
using (var db = new ElasticScaleContext<int>(connStrBldr.ConnectionString))
{
// Run a query to engage EF migrations
(from b in db.Blogs
select b).Count();
}
// Register the mapping of the tenant to the shard in the shard map.
// After this step, DDR on the shard map can be used
PointMapping<int> mapping;
if (!this.ShardMap.TryGetMappingForKey(key, out mapping))
{
this.ShardMap.CreatePointMapping(key, shard);
}
}
protected override void Seed(ElasticScaleContext <int> context)
{
// This method will be called after migrating to the latest version.
// You can use the DbSet<T>.AddOrUpdate() helper extension method
// to avoid creating duplicate seed data. E.g.
//
// context.People.AddOrUpdate(
// p => p.FullName,
// new Person { FullName = "Andrew Peters" },
// new Person { FullName = "Brice Lambson" },
// new Person { FullName = "Rowan Miller" }
// );
}
public static void Main()
{
SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder
{
UserID = s_userName,
Password = s_password,
ApplicationName = s_applicationName
};
// 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(s_server, s_shardmapmgrdb, connStrBldr.ConnectionString);
sharding.RegisterNewShard(s_server, s_shard1, connStrBldr.ConnectionString, s_tenantId1);
sharding.RegisterNewShard(s_server, s_shard2, connStrBldr.ConnectionString, s_tenantId2);
// 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, s_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, s_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}:", s_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, s_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}:", s_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, s_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, s_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}:", s_tenantId2);
foreach (var item in query)
{
Console.WriteLine(item.Name);
}
}
});
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
}
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 Main()
{
SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder
{
UserID = s_userName,
Password = s_password,
ApplicationName = s_applicationName
};
// 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(s_server, s_shardmapmgrdb, connStrBldr.ConnectionString);
sharding.RegisterNewShard(s_server, s_shard1, connStrBldr.ConnectionString, s_tenantId1);
sharding.RegisterNewShard(s_server, s_shard2, connStrBldr.ConnectionString, s_tenantId2);
sharding.RegisterNewShard(s_server, s_shard1, connStrBldr.ConnectionString, s_tenantId3);
sharding.RegisterNewShard(s_server, s_shard2, connStrBldr.ConnectionString, s_tenantId4);
// Using Entity Framework and LINQ, create a new blog and then display all blogs for each tenant
Console.WriteLine("\n--\n\nCreate a new blog for each tenant, then list the blogs belonging to that tenant.");
Console.WriteLine("If row-level security has not been enabled, then the blogs for all tenants on the shard database will be listed.");
int[] tenants = new int[] { s_tenantId1, s_tenantId2, s_tenantId3, s_tenantId4 };
foreach (int tenantId in tenants)
{
Console.Write("\nEnter a name for a new Blog for TenantId {0}: ", tenantId);
var name = Console.ReadLine();
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext<int>(sharding.ShardMap, tenantId, connStrBldr.ConnectionString))
{
var blog = new Blog { Name = name, TenantId = tenantId }; // must specify TenantId unless using default constraints to auto-populate
db.Blogs.Add(blog);
db.SaveChanges();
// If Row-Level Security is enabled, tenants will only display their own blogs
// Otherwise, tenants will see blogs for all tenants on the shard db
var query = from b in db.Blogs
orderby b.Name
select b;
Console.WriteLine("All blogs for TenantId {0}:", tenantId);
foreach (var item in query)
{
Console.WriteLine(item.Name);
}
}
});
}
// Example query via ADO.NET SqlClient
// If Row-Level Security is enabled, only Tenant 4's blogs will be listed
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
// Note: We are using a wrapper function OpenDDRConnection that automatically set SESSION_CONTEXT with the specified TenantId.
// This is a best practice to ensure that SESSION_CONTEXT is always set before executing a query.
using (SqlConnection conn = ElasticScaleContext<int>.OpenDDRConnection(sharding.ShardMap, s_tenantId4, connStrBldr.ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT * FROM Blogs";
Console.WriteLine("\n--\n\nAll blogs for TenantId {0} (using ADO.NET SqlClient):", s_tenantId4);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}", reader["Name"]);
}
}
});
// Because of the RLS block predicate, attempting to insert a row for the wrong tenant will throw an error.
Console.WriteLine("\n--\n\nTrying to create a new Blog for TenantId {0} while connected as TenantId {1}: ", s_tenantId2, s_tenantId3);
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext<int>(sharding.ShardMap, s_tenantId3, connStrBldr.ConnectionString))
{
// Verify that block predicate prevents Tenant 3 from inserting rows for Tenant 2
try
{
var bad_blog = new Blog { Name = "BAD BLOG", TenantId = s_tenantId2 };
db.Blogs.Add(bad_blog);
db.SaveChanges();
Console.WriteLine("No error thrown - make sure your security policy has a block predicate on this table in each shard database.");
}
catch (DbUpdateException)
{
Console.WriteLine("Can't insert blog for incorrect tenant.");
}
}
});
Console.WriteLine("\n--\n\nPress any key to exit...");
Console.ReadKey();
}
