Examples

This page contains practical examples of DbLocator usage in common scenarios.

Basic Tenant Setup

// Initialize DbLocator with encryption (recommended for production)
var dbLocator = new Locator(
    "YourConnectionString",
    "YourEncryptionKey"
);

// Create a new tenant
var tenantId = await dbLocator.AddTenant(
    "Acme Corp",     // Name
    "acme",          // Code
    Status.Active    // Status
);

// Set up their client database
var clientDbTypeId = await dbLocator.AddDatabaseType("Client");

// Add a database server with all identification methods
var serverId = await dbLocator.CreateDatabaseServer(
    "Local SQL",     // Name
    "localhost",     // HostName
    "127.0.0.1",    // IP Address
    "localhost.local", // FQDN
    false           // Is Linked Server
);

// Create the database
var dbId = await dbLocator.CreateDatabase(
    "Acme_Client",   // Database name
    serverId,        // Server ID
    clientDbTypeId,  // Database type ID
    Status.Active,   // Status
    autoCreateDatabase: true,           // Auto-create database (creates the database if it doesn't exist)
    useTrustedConnection: false           // Use Windows authentication (false = SQL Server authentication)
);

// Create a database user
var userId = await dbLocator.CreateDatabaseUser(
    new[] { dbId },  // Database IDs
    "acme_user",     // Username
    "Strong@Passw0rd", // Password
    true            // Create user on database server (creates the login and user)
);

// Assign roles to the user
await dbLocator.CreateDatabaseUserRole(
    userId,          // User ID
    DatabaseRole.DataReader, // Role
    true            // Update user on database server (grants the role)
);

// Get a SqlConnection with specific role
using var connection = await dbLocator.GetConnection(
    tenantId, 
    clientDbTypeId,
    new[] { DatabaseRole.DataReader }
);

Multi-Database Tenant

// Set up multiple database types for a tenant
var clientDbTypeId = await dbLocator.AddDatabaseType("Client");
var biDbTypeId = await dbLocator.AddDatabaseType("BI");
var reportingDbTypeId = await dbLocator.AddDatabaseType("Reporting");

// Add databases for each type
var clientDbId = await dbLocator.CreateDatabase(
    "Acme_Client", 
    serverId, 
    clientDbTypeId, 
    Status.Active, 
    autoCreateDatabase: true,           // Auto-create database
    useTrustedConnection: false           // Use SQL Server authentication
);

var biDbId = await dbLocator.CreateDatabase(
    "Acme_BI", 
    serverId, 
    biDbTypeId, 
    Status.Active, 
    autoCreateDatabase: true,           // Auto-create database
    useTrustedConnection: false           // Use SQL Server authentication
);

var reportingDbId = await dbLocator.CreateDatabase(
    "Acme_Reporting", 
    serverId, 
    reportingDbTypeId, 
    Status.Active, 
    autoCreateDatabase: true,           // Auto-create database
    useTrustedConnection: false           // Use SQL Server authentication
);

// Create a user with access to all databases
var userId = await dbLocator.CreateDatabaseUser(
    new[] { clientDbId, biDbId, reportingDbId },
    "acme_user",
    "Strong@Passw0rd",
    true            // Create user on database server
);

// Assign different roles for different databases
await dbLocator.CreateDatabaseUserRole(userId, DatabaseRole.DataReader, true);  // Client DB
await dbLocator.CreateDatabaseUserRole(userId, DatabaseRole.DataWriter, true);  // BI DB
await dbLocator.CreateDatabaseUserRole(userId, DatabaseRole.DbOwner, true);     // Reporting DB

// Get connections to different databases with appropriate roles
using var clientConnection = await dbLocator.GetConnection(
    tenantId, 
    clientDbTypeId,
    new[] { DatabaseRole.DataReader }
);

using var biConnection = await dbLocator.GetConnection(
    tenantId, 
    biDbTypeId,
    new[] { DatabaseRole.DataWriter }
);

using var reportingConnection = await dbLocator.GetConnection(
    tenantId, 
    reportingDbTypeId,
    new[] { DatabaseRole.DbOwner }
);

Role-Based Access

// Get a connection with specific database roles
using var readerConnection = await dbLocator.GetConnection(
    tenantId, 
    clientDbTypeId, 
    new[] { DatabaseRole.DataReader }
);

// Get a connection with multiple roles
using var writerConnection = await dbLocator.GetConnection(
    tenantId, 
    clientDbTypeId, 
    new[] { DatabaseRole.DataReader, DatabaseRole.DataWriter }
);

// Get a connection with administrative access
using var adminConnection = await dbLocator.GetConnection(
    tenantId, 
    clientDbTypeId, 
    new[] { DatabaseRole.DbOwner }
);

Using with Dependency Injection

// In Program.cs
builder.Services.AddSingleton<Locator>(sp =>
{
    var configuration = sp.GetRequiredService<IConfiguration>();
    return new Locator(
        configuration.GetConnectionString("DbLocator"),
        configuration["DbLocator:EncryptionKey"]
    );
});

// In your service
public class TenantService(Locator dbLocator, ILogger<TenantService> logger)
{
    private readonly Locator _dbLocator = dbLocator;
    private readonly ILogger<TenantService> _logger = logger;

    public async Task<SqlConnection> GetTenantConnectionAsync(
        string tenantCode,
        string databaseType,
        DatabaseRole[] roles
    )
    {
        try
        {
            var tenant = await _dbLocator.GetTenant(tenantCode);
            return await _dbLocator.GetConnection(tenant.Id, databaseType, roles);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to get connection for tenant {TenantCode}", tenantCode);
            throw;
        }
    }
}