LocalDB DataSource

Overview

ILocalDB extends IDataSource with file-based database management capabilities. It provides control over database file creation, copying, deletion, and entity management.

LocalDB datasources are used for embedded database engines like SQLite, providing a self-contained data store in a single file.

Supported Operations

• Create - Create database files with optional in-memory mode
• Copy - Clone database files to backup locations
• Delete - Remove the entire database file
• Drop - Remove individual entities (tables)

ILocalDB Interface

public interface ILocalDB
{
    bool CanCreateLocal { get; set; }
    bool InMemory { get; set; }
    string Extension { get; set; }

    // Database file operations
    bool CreateDB();
    bool CreateDB(bool inMemory);
    bool CreateDB(string filepathandname);
    bool DeleteDB();
    bool CopyDB(string DestDbName, string DesPath);

    // Entity operations
    IErrorsInfo DropEntity(string EntityName);
}

Properties

Methods

Creating Databases

var localDb = (ILocalDB)editor.GetDataSource("LocalSQLite");

// Basic creation using connection string
bool created = localDb.CreateDB();

// In-memory mode
bool createdMem = localDb.CreateDB(inMemory: true);

// Specific path
string customPath = Path.Combine(AppContext.BaseDirectory, "Data", "app.db");
bool createdPath = localDb.CreateDB(customPath);

// With custom extension
localDb.Extension = ".db";
bool created2 = localDb.CreateDB();

File Path Resolution

The default path comes from the datasource's ConnectionProperties.ConnectionString. The extension (e.g., .db, .sqlite) is appended if missing.

File Operations

Copy Database

// Copy to backup folder
var backupPath = Path.Combine(AppContext.BaseDirectory, "Backups");
bool copied = localDb.CopyDB("app_backup.db", backupPath);

// Ensure connection is closed before copy
editor.CloseDataSource("LocalSQLite");
copied = localDb.CopyDB("snapshot.db", snapshotsPath);

Delete Database

// Close first, then delete
localDb.Closeconnection();
bool deleted = localDb.DeleteDB();

// Check result
if (!deleted)
{
    var error = localDb.ErrorObject;
    Console.WriteLine($"Delete failed: {error.Message}");
}

Schema Management

// Drop a table
var result = localDb.DropEntity("TempData");
if (result.Flag == Errors.Ok)
    Console.WriteLine("Table dropped");
else
    Console.WriteLine($"Drop failed: {result.Message}");

// Check entity exists before dropping
if (localDb.CheckEntityExist("TempData"))
{
    var drop = localDb.DropEntity("TempData");
}

Lifecycle

Pitfalls

• File handles — Not releasing file handles before copy/delete causes IOException
• Missing extension — Creates unexpected filenames without correct extension
• Directory missing — CreateDB() fails silently if parent directory doesn't exist; ensure the path directory is created first via EnvironmentService.CreateAppfolder()
• Connection status — Always check ConnectionStatus == Closed before file copy or delete
• DropEntity safety — DropEntity is destructive; verify entity name before calling
• InMemory mode — Data is lost when connection closes; don't use for persistent data

Integration Patterns

With ETL Operations

// Create local DB, load from external source via ETL
localDb.CreateDB();
ds.Openconnection();

var copier = new ETLDataCopier(editor);
var result = await copier.CopyDataAsync(
    sourceDs, "Customers",
    ds, "Customers",
    progress: new Progress<PassedArgs>(p =>
        Console.WriteLine($"Copying: {p.Messege}")));

With Migration

// Apply migration to local DB
var migration = new Migration
{
    Name = "CreateIndexes",
    Version = "1.0.0",
    Up = async () =>
    {
        await ds.ExecuteSql("CREATE INDEX idx_name ON Customers(Name)");
    }
};
await migrationManager.ApplyAsync(migration);

Multi-Application Shared Database

// Multiple apps share a local DB
var sharedPath = Path.Combine(
    EnvironmentService.BeepDataPath, "Shared", "app.db");

var sharedDb = new ConnectionProperties
{
    ConnectionName = "SharedDB",
    ConnectionString = sharedPath,
    DatabaseType = DataSourceType.SqlLite
};

// App1 and App2 both connect to same file
editor.ConfigEditor.AddDataConnection(sharedDb);
var ds = editor.GetDataSource("SharedDB");
ds.Openconnection();

Usage Examples

Complete Workflow

// Setup connection
var props = new ConnectionProperties
{
    ConnectionName = "AppDB",
    ConnectionString = "./Data/",
    DatabaseType = DataSourceType.SqlLite,
    Category = DatasourceCategory.RDBMS
};
editor.ConfigEditor.AddDataConnection(props);

var ds = editor.GetDataSource("AppDB");
var localDb = (ILocalDB)ds;

// Ensure directory exists
EnvironmentService.CreateAppfolder("MyApp", "Data");

// Create database
localDb.Extension = ".db";
localDb.CreateDB();

// Open and use
ds.Openconnection();
var entities = ds.GetEntitesList();

// Close and backup
ds.Closeconnection();
localDb.CopyDB("backup_2024.db", Path.Combine(backupDir, "Snapshots"));

In-Memory Testing

// Create in-memory for tests
localDb.InMemory = true;
localDb.CreateDB(inMemory: true);

// Test operations
ds.Openconnection();
ds.InsertEntity("Test", testData);
var result = ds.GetEntity("Test", null);

// Dispose (no file to clean up)
ds.Closeconnection();

Safe Delete

// Check connection state
if (localDb.ConnectionStatus == ConnectionState.Open)
{
    Console.WriteLine("Close connection first");
    return;
}

// Verify file exists
string dbPath = Path.Combine(connectionString, filename);
if (!File.Exists(dbPath))
{
    Console.WriteLine("File not found");
    return;
}

// Delete
bool deleted = localDb.DeleteDB();
if (deleted)
    Console.WriteLine("Database deleted successfully");