public static string GetTenantConnectionString(int tenantId)
{
try
{
return(_csMap.GetOrAdd(tenantId, (id) =>
{
EFDAL.Entity.TenantMaster item = null;
using (var context = new MultiTenantEntities(DatabaseHelper.MasterConnectionString))
{
item = context.TenantMaster.FirstOrDefault(x => x.TenantId == id);
if (item == null)
{
throw new Exception("Tenant not found");
}
}
var builder = new SqlConnectionStringBuilder(MasterConnectionString);
builder.UserID = item.DbUserName;
builder.Password = SecurityDomain.Decrypt(item.DbPassword);
builder.IntegratedSecurity = false;
return builder.ToString();
}));
}
catch (Exception ex)
{
Logger.LogError(ex);
return(null);
}
}
public static bool CreateTenant(string tenantName)
{
//Create a new tenant if the name does not exist
using (var context = new MultiTenantEntities(DatabaseHelper.MasterConnectionString))
{
//Check for tenant existence
if (context.TenantMaster.Any(x => x.TenantName == tenantName))
{
throw new Exception("The tenant already exists.");
}
//Create a SQL database user and store the creds for this tenant
var newUser = Utilities.GetRandomString();
var password = Utilities.GetRandomString();
var passwordCipher = SecurityDomain.Crypt(password);
var newItem = new TenantMaster
{
TenantName = tenantName,
DbUserName = newUser,
DbPassword = passwordCipher,
};
context.Add(newItem);
context.SaveChanges();
//Create database permissions
//Note: cannot use parameters as these are not data queries. They are DDL
var sb = new StringBuilder();
sb.AppendLine($"CREATE LOGIN [{newItem.DbUserName}] WITH PASSWORD=N'{password}'");
sb.AppendLine($"if not exists(select * from sys.sysusers where name = '{newItem.DbUserName}')");
sb.AppendLine($"CREATE USER [{newItem.DbUserName}] FOR LOGIN [{newItem.DbUserName}];");
//The "TenantRole" was created in a custom script in the SQL installer in the "6_UserDefinedFinalize\Always" folder
sb.AppendLine("GRANT DELETE TO [TenantRole];");
sb.AppendLine("GRANT INSERT TO [TenantRole];");
sb.AppendLine("GRANT SELECT TO [TenantRole];");
sb.AppendLine("GRANT UPDATE TO [TenantRole];");
sb.AppendLine($"ALTER ROLE [TenantRole] ADD MEMBER [{newItem.DbUserName}];");
DatabaseHelper.ExecuteSql(sb.ToString());
}
return(true);
}
static void Main(string[] args)
{
//****************************************
//The EFDAL project is generated
//The Installer project is generated
//Teh API project is hand written containing a few utilities to create tenants
//The Console app
//For now this will store the tenant ID in a variable.
//In production you would use a session variable or some other login management technique.
var tenant1Id = 0;
var tenant2Id = 0;
//Connect to the database with the master connection string
//There is no tenant information in this connection string
using (var context = new MultiTenantEntities(DatabaseHelper.MasterConnectionString))
{
var tenant1Name = "Tenant1";
var tenant2Name = "Tenant2";
#region Create Tenants IF EMPTY
if (!context.TenantMaster.Any())
{
//Create Tenant 1
TenantDomain.CreateTenant(tenant1Name);
Console.WriteLine($"Created {tenant1Name}");
//Create Tenant 2
TenantDomain.CreateTenant(tenant2Name);
Console.WriteLine($"Created {tenant2Name}");
}
#endregion
//Pull each Tenant ID from the database for testing
tenant1Id = context.TenantMaster.FirstOrDefault(x => x.TenantName == tenant1Name).TenantId;
tenant2Id = context.TenantMaster.FirstOrDefault(x => x.TenantName == tenant2Name).TenantId;
}
//Now connect with the connection string for Tenant1
using (var context = new MultiTenantEntities(DatabaseHelper.GetTenantConnectionString(tenant1Id)))
{
//Add "Project 1" through "Project 10"
for (var ii = 1; ii <= 10; ii++)
{
var newProject = new Project
{
Name = $"Project {ii}",
ProjectTypeValue = ProjectTypeConstants.CSharp
};
context.AddItem(newProject);
}
context.SaveChanges();
}
//Now connect with the connection string for Tenant2
using (var context = new MultiTenantEntities(DatabaseHelper.GetTenantConnectionString(tenant2Id)))
{
//Add "Project 11" through "Project 20"
for (var ii = 11; ii <= 20; ii++)
{
var newProject = new Project
{
Name = $"Project {ii}",
ProjectTypeValue = ProjectTypeConstants.Java
};
context.AddItem(newProject);
}
context.SaveChanges();
}
//Now select ALL Projects using the Tenant1 connection string
using (var context = new MultiTenantEntities(DatabaseHelper.GetTenantConnectionString(tenant1Id)))
{
var list = context.Project.ToList();
Console.WriteLine($"Tenant 1 Project Count={list.Count}");
foreach (var item in list)
{
Console.WriteLine($"Tenant 1, Project ={item.Name}");
}
//Notice that there are only 10 items even with no Where clause
//The framework limits the selection to only items for Tenant 1.
//There is never any data leak between tenants
}
//Now select ALL Projects using the Tenant2 connection string
using (var context = new MultiTenantEntities(DatabaseHelper.GetTenantConnectionString(tenant2Id)))
{
var list = context.Project.ToList();
Console.WriteLine($"Tenant 2 Project Count={list.Count}");
foreach (var item in list)
{
Console.WriteLine($"Tenant 2, Project ={item.Name}");
}
//Notice that there are only 10 items even with no Where clause
//The framework limits the selection to only items for Tenant 2.
//There is never any data leak between tenants
}
}
