/// <summary>
/// Adds a customer to the customers table (or updates the customer if that id already exists).
/// </summary>
private static void AddCustomer(
ShardMap shardMap,
string credentialsConnectionString,
int customerId,
string name,
int regionId)
{
// Open and execute the command with retry for transient faults. Note that if the command fails, the connection is closed, so
// the entire block is wrapped in a retry. This means that only one command should be executed per block, since if we had multiple
// commands then the first command may be executed multiple times if later commands fail.
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
// Looks up the key in the shard map and opens a connection to the shard
using (SqlConnection conn = shardMap.OpenConnectionForKey(customerId, credentialsConnectionString))
{
// Create a simple command that will insert or update the customer information
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
IF EXISTS (SELECT 1 FROM Customers WHERE CustomerId = @customerId)
UPDATE Customers
SET Name = @name, RegionId = @regionId
WHERE CustomerId = @customerId
ELSE
INSERT INTO Customers (CustomerId, Name, RegionId)
VALUES (@customerId, @name, @regionId)";
cmd.Parameters.AddWithValue("@customerId", customerId);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@regionId", regionId);
cmd.CommandTimeout = 60;
// Execute the command
cmd.ExecuteNonQuery();
}
});
}
/// <summary>
/// Helper function to do validation,this will be called using TFH retry policy.
/// </summary>
/// <param name="sm">Shard map for a mapping to validate</param>
/// <param name="key">Key to lookup and validate</param>
private static void ValidateImpl(ShardMap sm, int key)
{
try
{
using (SqlConnection conn = sm.OpenConnectionForKey(key,
Globals.ShardUserConnectionString,
ConnectionOptions.Validate))
{
}
}
catch (SqlException e)
{
// Error Number 3980: The request failed to run because the batch is aborted, this can be caused by abort signal sent from client, or another request is running in the same session, which makes the session busy.
// Error Number = 0, Class = 20, Message = The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection.
// Error Number = 0, Class = 11, Message = A severe error occurred on the current command. The results, if any, should be discarded.
switch (e.Number)
{
case 0:
if (e.Class != 20 && e.Class != 11)
{
throw;
}
break;
case 3980:
break;
default:
throw;
}
}
}
/// <summary>
/// Wrapper function for ShardMap.OpenConnectionForKey() that automatically sets CONTEXT_INFO to the correct
/// tenantId before returning a connection. As a best practice, you should only open connections using this
/// method to ensure that CONTEXT_INFO is always set before executing a query.
/// </summary>
public static SqlConnection OpenDDRConnection(ShardMap shardMap, T shardingKey, string connectionStr)
{
// No initialization
Database.SetInitializer <ElasticScaleContext <T> >(null);
// Ask shard map to broker a validated connection for the given key
SqlConnection conn = null;
try
{
conn = shardMap.OpenConnectionForKey(shardingKey, connectionStr, ConnectionOptions.Validate);
// Set CONTEXT_INFO to shardingKey to enable Row-Level Security filtering
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SET CONTEXT_INFO @shardingKey";
cmd.Parameters.AddWithValue("@shardingKey", shardingKey);
cmd.ExecuteNonQuery();
return(conn);
}
catch (Exception)
{
if (conn != null)
{
conn.Dispose();
}
throw;
}
}
/// <summary>
/// Adds an order to the orders table for the customer.
/// </summary>
private static void AddOrder(
ShardMap shardMap,
string credentialsConnectionString,
int customerId,
int productId)
{
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
// Looks up the key in the shard map and opens a connection to the shard
using (SqlConnection conn = shardMap.OpenConnectionForKey(customerId, credentialsConnectionString))
{
// Create a simple command that will insert a new order
SqlCommand cmd = conn.CreateCommand();
// Create a simple command
cmd.CommandText = @"INSERT INTO dbo.Orders (CustomerId, OrderDate, ProductId)
VALUES (@customerId, @orderDate, @productId)";
cmd.Parameters.AddWithValue("@customerId", customerId);
cmd.Parameters.AddWithValue("@orderDate", DateTime.Now.Date);
cmd.Parameters.AddWithValue("@productId", productId);
cmd.CommandTimeout = 60;
// Execute the command
cmd.ExecuteNonQuery();
}
});
ConsoleUtils.WriteInfo("Inserted order for customer ID: {0}", customerId);
}
/// <summary>
/// Adds a customer to the customers table (or updates the customer if that id already exists).
/// </summary>
private static void AddCustomer(
ShardMap shardMap,
string credentialsConnectionString,
int customerId,
string name,
int regionId)
{
//失敗的話重試
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
// 依據 Key 判斷要取回哪個 Shard
using (SqlConnection conn = shardMap.OpenConnectionForKey(customerId, credentialsConnectionString))
{
// 產生 Command
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
IF EXISTS (SELECT 1 FROM Customers WHERE CustomerId = @customerId)
UPDATE Customers
SET Name = @name, RegionId = @regionId
WHERE CustomerId = @customerId
ELSE
INSERT INTO Customers (CustomerId, Name, RegionId)
VALUES (@customerId, @name, @regionId)";
cmd.Parameters.AddWithValue("@customerId", customerId);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@regionId", regionId);
cmd.CommandTimeout = 60;
// Execute the command
cmd.ExecuteNonQuery();
}
});
}
/// <summary>
/// Adds a customer to the customers table (or updates the customer if that id already exists).
/// </summary>
private static void AddCustomer(
ShardMap shardMap,
string credentialsConnectionString,
int customerId,
string name,
int regionId)
{
// Open and execute the command with retry for transient faults. Note that if the command fails, the connection is closed, so
// the entire block is wrapped in a retry. This means that only one command should be executed per block, since if we had multiple
// commands then the first command may be executed multiple times if later commands fail.
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
// Looks up the key in the shard map and opens a connection to the shard
using (SqlConnection conn = shardMap.OpenConnectionForKey(customerId, credentialsConnectionString))
{
// Create a simple command that will insert or update the customer information
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
IF EXISTS (SELECT 1 FROM Customers WHERE CustomerId = @customerId)
UPDATE Customers
SET Name = @name, RegionId = @regionId
WHERE CustomerId = @customerId
ELSE
INSERT INTO Customers (CustomerId, Name, RegionId)
VALUES (@customerId, @name, @regionId)";
cmd.Parameters.AddWithValue("@customerId", customerId);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@regionId", regionId);
cmd.CommandTimeout = 60;
// Execute the command
cmd.ExecuteNonQuery();
}
});
}
/// <summary>
/// Creates the DDR (Data Dependent Routing) connection.
/// </summary>
/// <param name="shardMap">The shard map.</param>
/// <param name="shardingKey">The sharding key.</param>
/// <param name="connectionStr">The connection string.</param>
/// <returns></returns>
private static DbContextOptions CreateDdrConnection(ShardMap shardMap, int shardingKey, string connectionStr)
{
// Ask shard map to broker a validated connection for the given key
SqlConnection sqlConn = shardMap.OpenConnectionForKey(shardingKey, connectionStr);
var optionsBuilder = new DbContextOptionsBuilder <TenantDbContext>();
var options = optionsBuilder.UseSqlServer(sqlConn).Options;
return(options);
}
// Only static methods are allowed in calls into base class c'tors
private static DbConnection CreateDDRConnection(ShardMap shardMap, T shardingKey, string connectionStr)
{
// No initialization
Database.SetInitializer <ElasticScaleContext <T> >(null);
// Ask shard map to broker a validated connection for the given key
SqlConnection conn = shardMap.OpenConnectionForKey <T>(shardingKey, connectionStr, ConnectionOptions.Validate);
return(conn);
}
private static DbConnection CreateDdrConnection(ShardMap shardMap, int shardingKey, string connectionStr)
{
// No initialization
Database.SetInitializer <TenantContext>(null);
// Ask shard map to broker a validated connection for the given key
SqlConnection sqlConn = shardMap.OpenConnectionForKey(shardingKey, connectionStr);
return(sqlConn);
}
/// <summary>
/// Creates the DDR (Data Dependent Routing) connection.
/// </summary>
/// <param name="shardMap">The shard map.</param>
/// <param name="shardingKey">The sharding key.</param>
/// <param name="connectionStr">The connection string.</param>
/// <returns></returns>
private static DbContextOptions CreateDdrConnection(ShardMap shardMap, int shardingKey, string connectionStr)
{
// Ask shard map to broker a validated connection for the given key
SqlConnection sqlConn = shardMap.OpenConnectionForKey(shardingKey, connectionStr);
// Set TenantId in SESSION_CONTEXT to shardingKey to enable Row-Level Security filtering
SqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = @"exec sp_set_session_context @key=N'TenantId', @value=@shardingKey";
cmd.Parameters.AddWithValue("@shardingKey", shardingKey);
cmd.ExecuteNonQuery();
var optionsBuilder = new DbContextOptionsBuilder<TenantDbContext>();
var options = optionsBuilder.UseSqlServer(sqlConn).Options;
return options;
}
private static DbConnection CreateDdrConnection(ShardMap shardMap, int shardingKey, string connectionStr)
{
// No initialization
Database.SetInitializer <TenantContext>(null);
// Ask shard map to broker a validated connection for the given key
SqlConnection sqlConn = shardMap.OpenConnectionForKey(shardingKey, connectionStr);
// Set TenantId in SESSION_CONTEXT to shardingKey to enable Row-Level Security filtering
SqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = @"exec sp_set_session_context @key=N'TenantId', @value=@shardingKey";
cmd.Parameters.AddWithValue("@shardingKey", shardingKey);
cmd.ExecuteNonQuery();
return(sqlConn);
}
/// <summary>
/// Creates the DDR (Data Dependent Routing) connection.
/// Only static methods are allowed in calls into base class constructors
/// </summary>
/// <param name="shardMap">The shard map.</param>
/// <param name="shardingKey">The sharding key.</param>
/// <param name="connectionStr">The connection string.</param>
/// <param name="metadataWorkSpaceConnectionString">The metadata work space connection string.</param>
/// <returns></returns>
private static DbConnection CreateDdrConnection(ShardMap shardMap, byte[] shardingKey, string connectionStr, string metadataWorkSpaceConnectionString)
{
// No initialization
Database.SetInitializer <TenantEntities>(null);
//convert key from byte[] to int as OpenConnectionForKey requires int
int key = BitConverter.ToInt32(shardingKey, 0);
// Ask shard map to broker a validated connection for the given key
SqlConnection sqlConn = shardMap.OpenConnectionForKey(key, connectionStr);
//convert into Entity Connection since we are using EF
var efConnection = new EntityConnection(metadataWorkSpaceConnectionString);
// Create Entity connection that holds the sharded SqlConnection and metadata workspace
var workspace = efConnection.GetMetadataWorkspace();
var entCon = new EntityConnection(workspace, sqlConn, true);
return(entCon);
}
/// <summary>
/// Adds an order to the orders table for the customer.
/// </summary>
private static void AddOrder(
ShardMap shardMap,
string credentialsConnectionString,
int customerId,
int productId)
{
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
// Looks up the key in the shard map and opens a connection to the shard
using (SqlConnection conn = shardMap.OpenConnectionForKey(customerId, credentialsConnectionString))
{
// Create a simple command that will insert a new order
SqlCommand cmd = conn.CreateCommand();
// Create a simple command
cmd.CommandText = @"INSERT INTO dbo.Orders (CustomerId, OrderDate, ProductId)
VALUES (@customerId, @orderDate, @productId)";
cmd.Parameters.AddWithValue("@customerId", customerId);
cmd.Parameters.AddWithValue("@orderDate", DateTime.Now.Date);
cmd.Parameters.AddWithValue("@productId", productId);
cmd.CommandTimeout = 60;
// Execute the command
cmd.ExecuteNonQuery();
}
});
ConsoleUtils.WriteInfo("Inserted order for customer ID: {0}", customerId);
}
/// <summary>
/// Helper function to do validation,this will be called using TFH retry policy.
/// </summary>
/// <param name="sm">Shard map for a mapping to validate</param>
/// <param name="key">Key to lookup and validate</param>
private static void ValidateImpl(ShardMap sm, int key)
{
try
{
using (SqlConnection conn = sm.OpenConnectionForKey(key,
Globals.ShardUserConnectionString,
ConnectionOptions.Validate))
{
}
}
catch (SqlException e)
{
// Error Number 3980: The request failed to run because the batch is aborted, this can be caused by abort signal sent from client, or another request is running in the same session, which makes the session busy.
// Error Number = 0, Class = 20, Message = The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection.
// Error Number = 0, Class = 11, Message = A severe error occurred on the current command. The results, if any, should be discarded.
switch (e.Number)
{
case 0:
if (e.Class != 20 && e.Class != 11)
throw;
break;
case 3980:
break;
default:
throw;
}
}
}