Getting Started with DbLocator
DbLocator is a library designed to simplify database interactions for multi-database tenant applications on SQL Server. This guide will help you get started with DbLocator in your .NET application.
Prerequisites
- .NET 9.0 (required)
- SQL Server 2016 or later
Installation
Add the DbLocator NuGet package to your project:
dotnet add package DbLocator
Key Features
- Multi-tenant Database Management: Manage multiple databases for different tenants with ease
- Role-Based Access Control: Implement fine-grained access control using SQL Server database roles
- Database Server Management: Support for multiple server identification methods (hostname, FQDN, IP)
- Connection Management: Secure connection handling with SQL Server authentication
- Distributed Caching: Optional caching support for improved performance
- Data Encryption: Built-in encryption for sensitive connection information
Basic Setup
First, ensure you have a SQL Server instance running. For local development, you have several options:
Option 1: Using Docker (Recommended for Development)
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourStrong@Passw0rd" -p 1433:1433 --name sql1 --hostname sql1 -d mcr.microsoft.com/mssql/server:2022-latest
Option 2: Local Installation
- Download SQL Server 2022 Developer Edition from Microsoft's website
- Install SQL Server Management Studio (SSMS) for database management
- Ensure the SQL Server service is running and accessible
Initialize DbLocator with your connection string:
using DbLocator;
// Basic initialization
var dbLocator = new Locator("YourConnectionString");
// With encryption (recommended for production)
var dbLocator = new Locator("YourConnectionString", "YourEncryptionKey");
// With caching (for better performance)
var dbLocator = new Locator("YourConnectionString", "YourEncryptionKey", cache);
Setting Up a Multi-tenant Environment
Here's a complete example of setting up a tenant with a database, user, and role-based access:
// Add a tenant
var tenantId = await dbLocator.AddTenant(
"Acme Corp", // Name
"acme", // Code
Status.Active // Status
);
// Add a database type
var databaseTypeId = await dbLocator.AddDatabaseType("Client");
// Add a database server
var databaseServerId = await dbLocator.CreateDatabaseServer(
"Local SQL Server", // Name
"localhost", // HostName
"127.0.0.1", // IP Address
"localhost.local", // Fully Qualified Domain Name
false // Is Linked Server
);
// Add a database
var databaseId = await dbLocator.CreateDatabase(
"Acme_Client", // Database name
databaseServerId, // Server ID
databaseTypeId, // Database type ID
Status.Active, // Status
true // Auto-create database
);
// Create a database user
var userId = await dbLocator.CreateDatabaseUser(
new[] { databaseId }, // Database IDs
"acme_user", // Username
"Strong@Passw0rd", // Password
true // Create user on database server
);
// Assign roles to the user
await dbLocator.CreateDatabaseUserRole(
userId, // User ID
DatabaseRole.DataReader, // Role
true // Update user on database server
);
// Get a SqlConnection with specific role
using var connection = await dbLocator.GetConnection(
tenantId,
databaseTypeId,
new[] { DatabaseRole.DataReader } // Required roles
);
Available Database Roles
DbLocator supports the following SQL Server database roles:
- DataReader: Read-only access to all user tables
- DataWriter: Can insert, update, and delete data in all user tables
- DdlAdmin: Can create, modify, and drop database objects
- BackupOperator: Can perform backup and restore operations
- SecurityAdmin: Can manage database security settings
- DbOwner: Full control over the database
Connection String Management
DbLocator handles connection strings in several ways:
Basic Connection: Uses SQL Server authentication
var connection = await dbLocator.GetConnection(tenantId, databaseTypeId);
Role-Based Connection: Ensures user has specific roles
var connection = await dbLocator.GetConnection( tenantId, databaseTypeId, new[] { DatabaseRole.DataReader, DatabaseRole.DataWriter } );
Trusted Connection: Uses Windows authentication
var databaseId = await dbLocator.CreateDatabase( "MyDatabase", serverId, typeId, useTrustedConnection: true );
Security Considerations
When setting up DbLocator, consider these security best practices:
- Use encryption for sensitive connection information
- Follow the principle of least privilege:
- Use
dbcreator
role if you need to create databases - Use
securityadmin
role if you need to create logins - Use no server roles if you're just mapping to existing databases
- Use
- Use trusted connections when possible
- Implement proper password policies for database users
- Regularly audit database access and permissions
Common Use Cases
- Multi-tenant Applications: Manage separate databases for each tenant while maintaining centralized control
- Database Sharding: Distribute databases across multiple servers for better scalability
- Role-Based Access: Implement different access levels for different user types
Next Steps
- Learn about Advanced Configuration for more complex scenarios
- Check out the Examples for common usage patterns
- Review the API Reference for detailed method documentation