// 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);
}
}
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 sets CONTEXT_INFO to the specified TenantId.
// This is a best practice to ensure that CONTEXT_INFO 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"]);
}
}
});
// If you created a check constraint in addition to RLS, 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 check constraint blocks 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 you have created check constraints in the shard databases.");
}
catch (DbUpdateException)
{
Console.WriteLine("Can't insert blog for incorrect tenant.");
}
}
});
Console.WriteLine("\n--\n\nPress any key to exit...");
Console.ReadKey();
}