Database Multi Database Serenity - serenity-is/Serenity GitHub Wiki

Sample of multi-database Serenity with change at logon screen:

Using the following changes:

Create a SQL view in all the databases:

CREATE VIEW [dbo].[V_PRJ_Empresas] AS
SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS ID, NAME AS 'Codigo', E.IDNome AS 'Descricao'
From 
	sys.databases AS s
	LEFT JOIN PRIEMPRE.DBO.EMPRESAS E
		ON E.Codigo=RIGHT(NAME, LEN(NAME)-3)
Where 
	s.name LIKE 'PRI%' AND name NOT LIKE 'PRIEMPRE%'

Create a ServiceEndpoint descendant class:

public class AdvancedServiceEndpoint : ServiceEndpoint
{
	public AdvancedServiceEndpoint()
	{
		#region Reconfigura a ligação ao SQL Server
		if (Authorization.UserDefinition != null)
		{
			((UserDefinition)Authorization.UserDefinition).AtualizaLigacaoSQL();
		}
		#endregion Reconfigura a ligação ao SQL Server
	}
}

Change all Enpoints:

Example:
	public class LanguageController : AdvancedServiceEndpoint

LoginRequest.cs:

public class LoginRequest : ServiceRequest
{                        
	//[Placeholder("Escolha a empresa")]
	[DisplayName("Escolha a empresa")]
	[Required(true)]
	[LookupEditor("Tabelas.VPrjEmpresas"), Width(200)]

	// ...
}

UserDefinition.cs:

public class UserDefinition : IUserDefinition
{
	public string Id { get { return UserId.ToInvariant(); } }
	public string DisplayName { get; set; }
	public string Email { get; set; }
	public string UserImage { get; set; }
	public short IsActive { get; set; }
	public int UserId { get; set; }
	public string Username { get; set; }
	public string PasswordHash { get; set; }
	public string PasswordSalt { get; set; }
	public string Source { get; set; }
	public DateTime? UpdateDate { get; set; }
	public DateTime? LastDirectoryUpdate { get; set; }

	//Fica com a Empresa Escolhida
	public string Company { get; set; }

	public void AtualizaLigacaoSQL()
	{
		#region Reconfigura a ligação ao SQL Server
		//Configura a nova Ligação à Base de dados
		var cs = SqlConnections.GetConnectionString("Default");
		var cb = cs.ProviderFactory.CreateConnectionStringBuilder();
		cb.ConnectionString = cs.ConnectionString;

		cb["Initial Catalog"] = Company;
		SqlConnections.SetConnection("Default", cb.ConnectionString, cs.ProviderName);
		#endregion Reconfigura a ligação ao SQL Server
	}
	// ...

AccountPage.cs:

[RoutePrefix("Account"), Route("{action=index}")]
public partial class AccountController : Controller
{
	[HttpPost, JsonFilter]
	public Result<ServiceResponse> Login(LoginRequest request)
	{
		return this.ExecuteMethod(() =>
		{
			request.CheckNotNull();

			var company = request.Company;

			if (string.IsNullOrWhiteSpace(request.Company))
				throw new ArgumentNullException("company");

			//Configura a nova Ligação à Base de dados
			var cs = SqlConnections.GetConnectionString("Default");
			var cb = cs.ProviderFactory.CreateConnectionStringBuilder();
			cb.ConnectionString = cs.ConnectionString;

			//Verificar se Empresa existe na instância
			string empresaSQL = string.Empty;
			using (var serverConnection = SqlConnections.New(cb.ConnectionString, cs.ProviderName))
			{
				try
				{
					serverConnection.Open();

					string databasesQuery = "SELECT Codigo FROM V_PRJ_Empresas WHERE Codigo = @name";

					empresaSQL = serverConnection.Query<string>(databasesQuery, new { name = company }).FirstOrDefault();

					//Se for o código
					empresaSQL = company;
				}
				catch (SqlException ex)
				{
					empresaSQL = string.Empty;
				}
			}

			//Se Empresa não existe
			if (string.IsNullOrEmpty(empresaSQL))
			{
				throw new Exception("A empresa escolhida não existe");
			}
			else
			{
				cb["Initial Catalog"] = empresaSQL;
			}

			if (string.IsNullOrEmpty(request.Username))
				throw new ArgumentNullException("username");

			var username = request.Username;

			//Atribui a Ligação do SQL Server
			SqlConnections.SetConnection("Default", cb.ConnectionString, cs.ProviderName);


			var user = Dependency.Resolve<UserRetrieveService>().ByUsername(username) as UserDefinition;
			if (user != null)
			{
				user.Company = empresaSQL;
			}
			else
			{
				throw new ValidationError("UserDoesntExist", Texts.Validation.UserDoesntExist);
			}



			if (WebSecurityHelper.Authenticate(ref username, request.Password, false))
			{
				//Atribui a Ligação do SQL Server
				return new ServiceResponse();
			}

			throw new ValidationError("AuthenticationError", Texts.Validation.AuthenticationError);
		});
	}
	// ...
}

I've got a more improved scenario of this behaviour but in general it's like this:

Database Multi Database Serenity - serenity-is/Serenity GitHub Wiki

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️