Connection Helpers Reference

RDBMSHelpers Overview

The RDBMSHelpers group provides a comprehensive toolkit for interacting with relational databases across more than twenty database platforms. All public API surfaces are exposed through two static facade classes — RDBMSHelper and DatabaseDMLHelper — which delegate to focused, single-responsibility helpers.

DatabaseFeatureHelper

Provides enumerations for optional database capabilities and utility methods for generating engine-specific sequence / identity SQL.

Enumerations

Sequence Queries

Per-Engine Reference

// Get next sequence value for PostgreSQL
string sql = DatabaseFeatureHelper.GenerateFetchNextSequenceValueQuery(
    DataSourceType.Postgre, "order_id_seq");
// → "SELECT nextval('order_id_seq')"

// Get last identity value for SQL Server
string idSql = DatabaseFeatureHelper.GenerateFetchLastIdentityQuery(
    DataSourceType.SqlServer);
// → "SELECT SCOPE_IDENTITY()"

Schema & Metadata Queries

DatabaseSchemaQueryHelper and the corresponding methods on RDBMSHelper generate engine-specific SQL for discovering schemas/databases and inspecting table structure.

Facade Methods (RDBMSHelper)

Schema Discovery SQL — Per Engine

// Safe version — no exception on unsupported engine
var (sql, ok, err) = RDBMSHelper.GetSchemasorDatabasesSafe(
    DataSourceType.Postgre, "appuser");
if (ok)
    await connection.ExecuteAsync(sql);

// Quick table-exists check
string existsQuery = RDBMSHelper.GetTableExistsQuery(
    DataSourceType.SqlServer, "Orders", "dbo");

DDL Generation

DatabaseObjectCreationHelper generates data-definition SQL (CREATE TABLE, primary keys, indexes, DROP) from EntityStructure instances or explicit parameters.

Table Creation

Primary Key

Indexes

// CREATE TABLE from an EntityStructure
var (createSql, ok, err) = DatabaseObjectCreationHelper.GenerateCreateTableSQL(entity);
if (!ok) Console.WriteLine(err);

// ADD PRIMARY KEY — SQL Server style
string pkSql = DatabaseObjectCreationHelper.GeneratePrimaryKeyQuery(
    DataSourceType.SqlServer, "Orders", "OrderId", "INT");
// → "ALTER TABLE Orders ADD OrderId INT PRIMARY KEY IDENTITY"

// ADD PRIMARY KEY from entity (multi-key aware)
var (pkFromEntity, success, msg) =
    DatabaseObjectCreationHelper.GeneratePrimaryKeyFromEntity(entity);

// CREATE UNIQUE INDEX
string idxSql = DatabaseObjectCreationHelper.GenerateCreateIndexQuery(
    DataSourceType.Postgre, "Orders", "idx_orders_customer",
    new[] { "CustomerId", "OrderDate" });

Query Repository

DatabaseQueryRepositoryHelper provides a static, lazily-initialized cache of pre-built SQL queries keyed by (DataSourceType, Sqlcommandtype). The cache is populated at class-initialisation time from CreateQuerySqlRepos().

API

// Check if a pre-built query exists, then fetch it
if (DatabaseQueryRepositoryHelper.QueryExists(
        DataSourceType.SqlServer, Sqlcommandtype.getTable))
{
    string sql = DatabaseQueryRepositoryHelper.GetQuery(
        DataSourceType.SqlServer, Sqlcommandtype.getTable);
}

// Get all pre-built queries for MySQL
var mysqlQueries = DatabaseQueryRepositoryHelper.GetQueriesForDatabase(
    DataSourceType.Mysql);

// Basic validation guard before executing user-supplied SQL
bool valid = DatabaseQueryRepositoryHelper.IsSqlStatementValid(userInputSql);

DML Helpers

DatabaseDMLHelper is the top-level DML facade. It exposes all data-manipulation query generators without requiring callers to know which specialised sub-class to use. All methods are static.

Basic Operations

Bulk Operations

Advanced Query Generation

Parameterized Queries

// Basic INSERT
string insertSql = DatabaseDMLHelper.GenerateInsertQuery(
    DataSourceType.Mysql, "Orders",
    new Dictionary<string, object> { ["CustomerId"] = 42, ["Total"] = 99.99 });

// Paginated SELECT
string pageSql = DatabaseDMLHelper.GenerateSelectQuery(
    DataSourceType.Postgre, "Orders",
    columns: new[] { "Id", "CustomerId", "Total" },
    whereClause: "Total > 50",
    orderBy: "Id",
    pageNumber: 2, pageSize: 25);

// Parameterized INSERT — safe against SQL injection
string paramSql = DatabaseDMLHelper.GenerateParameterizedInsertQuery(
    DataSourceType.SqlServer, "Orders",
    new[] { "CustomerId", "Total", "CreatedAt" });
// SQL Server → INSERT INTO Orders (CustomerId, Total, CreatedAt)
//              VALUES (@CustomerId, @Total, @CreatedAt)

// UPSERT
string upsertSql = DatabaseDMLHelper.GenerateUpsertQuery(
    DataSourceType.Postgre, "Products",
    keyColumns:    new[] { "Id" },
    updateColumns: new[] { "Name", "Price" },
    insertColumns: new[] { "Id", "Name", "Price" });

Entity Helpers

DatabaseEntityHelper is the facade for all entity-oriented operations — SQL generation from EntityStructure, structural validation, quality analysis, and field-factory helpers. It delegates to specialised sub-classes.

SQL Generation (via DatabaseEntitySqlGenerator)

Validation (via DatabaseEntityValidator)

Analysis (via DatabaseEntityAnalyzer)

Field Factory (via DatabaseEntityTypeHelper)

// Validate before persisting schema changes
var (isValid, errors) = DatabaseEntityHelper.ValidateEntityStructure(entity);
if (!isValid)
    foreach (var e in errors) Console.WriteLine(e);

// Generate a parameterized INSERT and execute it
var (sql, parameters, ok, err) = DatabaseEntityHelper.GenerateInsertWithValues(
    entity,
    new Dictionary<string, object>
    {
        ["CustomerId"] = 42,
        ["OrderDate"] = DateTime.UtcNow,
        ["Total"] = 199.99
    });
if (ok)
    await connection.ExecuteAsync(sql, parameters);

// Get improvement suggestions for a schema review
var suggestions = DatabaseEntityHelper.SuggestEntityImprovements(entity);
foreach (var suggestion in suggestions)
    Console.WriteLine(suggestion);

// Build a field programmatically
var pk = DatabaseEntityHelper.CreateBasicField("OrderId", "INT", allowNull: false, isKey: true);
entity.Fields.Add(pk);

UniversalDataSourceHelpers Overview

The UniversalDataSourceHelpers framework is the next-generation abstraction layer for BeepDM, replacing the RDBMS-only legacy helpers with a datasource-agnostic pattern that covers 40+ datasource types through a single consistent interface.

Folder Map

IDataSourceHelper Interface

Every datasource helper implements IDataSourceHelper. Callers code against this interface and never need to reference a specific helper class directly.

Identity & Capabilities

Schema Operations

DDL Operations

Constraint Operations

Transaction Control

DML Operations

Utility Methods

DataSourceHelperFactory

Instantiates and returns the correct IDataSourceHelper for any DataSourceType. The factory covers 60+ registered datasource types across RDBMS, NoSQL, Key-Value, Column-Family, Graph, Search, Time-Series, Vector, Streaming, Big-Data, and File categories.

Direct Usage

// Inject IDMEEditor via DI, then create the factory
var factory = new DataSourceHelperFactory(dmeEditor);
IDataSourceHelper helper = factory.CreateHelper(DataSourceType.MongoDB);

// All operations go through IDataSourceHelper
var (sql, ok) = helper.GetSchemaQuery(userName: null);
var (insertSql, parms, success, err) = helper.GenerateInsertSql(
    "Orders",
    new Dictionary<string, object> { ["CustomerId"] = 42, ["Total"] = 99.99 });

GeneralDataSourceHelper — Runtime Delegate

GeneralDataSourceHelper wraps the factory: it accepts a DataSourceType in its constructor, resolves the correct helper internally, and forwards every IDataSourceHelper call to it. Use this when the datasource type is only known at runtime.

// Type resolved at runtime
var helper = new GeneralDataSourceHelper(DataSourceType.Redis, dmeEditor);
string quoteChar = helper.QuoteIdentifier("myKey"); // uses RedisHelper internally

Helper-to-Engine Mapping

DataSourceCapabilityMatrix

A static matrix that maps every registered DataSourceType to its DataSourceCapabilities flags. Use it to check at runtime whether a feature is available before attempting to generate SQL that relies on it.

API

Selected Capability Names

// Check before generating JOIN SQL
bool canJoin = DataSourceCapabilityMatrix.Supports(
    DataSourceType.Cassandra, "SupportsJoins"); // false

// Get all FTS-capable datasources
var ftsSources = DataSourceCapabilityMatrix
    .GetDatasourcesSupportingCapability("SupportsFullTextSearch");
// → [ElasticSearch, Solr, MongoDB, PostgreSQL, ClickHouse, …]

// Graceful degradation
var caps = DataSourceCapabilityMatrix.GetCapabilities(type);
string query = caps.SupportsJoins
    ? GenerateJoinSql()
    : GenerateDenormalizedSql();

NoSQL Helpers

The document, key-value, and column-family helpers all implement IDataSourceHelper but translate every call into the native command syntax of their engine rather than SQL.

MongoDBHelper

Covers MongoDB, CouchDB, RavenDB, Couchbase, ArangoDB, LiteDB, Firebase, DynamoDB.

RedisHelper

Covers Redis, Memcached, Hazelcast, GridGain, ApacheIgnite, in-memory caches.

CassandraHelper

Covers Cassandra and ClickHouse.

// MongoDB via the universal interface
IDataSourceHelper mongo = factory.CreateHelper(DataSourceType.MongoDB);
var (insertCmd, parms, ok, err) = mongo.GenerateInsertSql(
    "Orders",
    new Dictionary<string, object> { ["customerId"] = 42, ["total"] = 99.99 });
// insertCmd → JSON document for insertOne

// Redis — check exists, then SET
IDataSourceHelper redis = factory.CreateHelper(DataSourceType.Redis);
var (existsCmd, _) = redis.GetTableExistsQuery("session:abc123");
// existsCmd → "EXISTS session:abc123"

Specialized Store Helpers

Helpers for graph databases, search engines, time-series databases, vector databases, and streaming platforms. These stores have fundamentally different semantics from RDBMS; the helpers reflect that by returning engine-native query fragments or indicating that DDL is managed by the engine itself.

// Graph — Neo4j via Cypher (CREATE CONSTRAINT)
IDataSourceHelper graph = factory.CreateHelper(DataSourceType.Neo4j);
var (cypher, ok, err) = graph.GenerateAddColumnSql("Person", ageField);
// cypher → "CREATE CONSTRAINT ON (n:Person) ASSERT n.Age IS NOT NULL"

// Vector — Weaviate property add
IDataSourceHelper vector = factory.CreateHelper(DataSourceType.Weaviate);
var (json, ok2, err2) = vector.GenerateAddColumnSql("Product", descField);
// json → Weaviate PUT /v1/schema/Product/properties JSON

// Check capability before generating time-series query
bool hasTTL = DataSourceCapabilityMatrix.Supports(
    DataSourceType.InfluxDB, "SupportsTTL"); // true

Analysis Helpers

The Analysis/ sub-folder contains two static helpers for inspecting POCO type graphs and inferring relational structure — a prerequisite when converting C# object models to EntityStructure instances.

NavigationPropertyDetector

RelationshipInferencer

Determines RelationshipCardinality from POCO property graphs using a four-tier priority: explicit attributes → naming conventions → type analysis → bidirectional detection.

Inference Priority

1. Explicit attributes — [ForeignKey], [InverseProperty]
2. Naming conventions — properties ending in Id / ID
3. Type analysis — ICollection<T> on one side implies One-to-Many
4. Bidirectional detection — matching navigation property on the target type

// Check whether Order.Customer is a collection or single ref
var prop = typeof(Order).GetProperty("Customer");
bool isCollection = NavigationPropertyDetector.IsCollectionNavigation(prop);
// false — single reference

// Infer cardinality between Order and Customer
var card = RelationshipInferencer.InferCardinality(
    typeof(Order).GetProperty("Customer"),
    typeof(Order),
    typeof(Customer));
// → RelationshipCardinality.ManyToOne

POCO-to-Entity Conversion

PocoToEntityConverter (in Conversion/) uses reflection and DataAnnotations to automatically build a fully-configured EntityStructure from any C# class — eliminating the need to hand-craft entity definitions.

Key Detection Strategy

Supported DataAnnotations

Circular Reference Guard

The converter tracks visited types and halts recursion when a cycle is detected, preventing infinite loops in deeply nested object graphs.

public class Product
{
    [Key]
    public int ProductId { get; set; }

    [Required, StringLength(100)]
    public string Name { get; set; }

    [Range(0, double.MaxValue)]
    public decimal Price { get; set; }

    public string Description { get; set; }
}

// Convert to EntityStructure in one call
EntityStructure entity = PocoToEntityConverter.ConvertPocoToEntity<Product>(
    strategy: PocoToEntityConverter.KeyDetectionStrategy.AttributeThenConvention,
    entityName: "Products");

// entity.EntityName  → "Products"
// entity.Fields[0]   → ProductId  (IsKey=true, AllowDBNull=false)
// entity.Fields[1]   → Name       (AllowDBNull=false, FieldSize=100)
// entity.Fields[2]   → Price      (range metadata attached)
// entity.Fields[3]   → Description (nullable)

// Then feed directly into any helper
IDataSourceHelper helper = new GeneralDataSourceHelper(
    DataSourceType.Postgre, dmeEditor);
var (createSql, ok, err) = helper.GenerateCreateTableSql(entity);

File & Folder Helpers

FileHelper

public static class FileHelper
{
    // Driver/file extension discovery
    public static string GetFileExtensions(IDMEEditor dmeEditor);
    public static List<ConnectionDriversConfig> GetFileDataSources(IDMEEditor dmeEditor);
    public static DataSourceType GetDataSourceTypeFromExtension(string fileExtension, IDMEEditor dmeEditor);

    // File operations
    public static bool IsFileLocked(string filePath);
    public static string GetFileHash(string filePath, HashAlgorithmName algorithm);
    public static string ComputeFileChecksum(string filePath);

    // Path helpers
    public static string GetRelativePath(string basePath, string fullPath);
    public static string EnsureAbsolutePath(string basePath, string relativePath);
    public static string NormalizePath(string path);
    public static string SanitizeFileName(string fileName);
}

FileOperationHelper

public static class FileOperationHelper
{
    // Safe file operations with locking
    public static bool SafeDelete(string filePath);
    public static bool SafeMove(string sourcePath, string destPath, bool overwrite = false);
    public static bool SafeCopy(string sourcePath, string destPath, bool overwrite = false);

    // Directory operations
    public static void EnsureDirectoryExists(string directoryPath);
    public static void ClearDirectory(string directoryPath, bool recursive = true);

    // Compression
    public static Task CompressDirectoryAsync(string sourceDir, string destZipPath);
    public static Task DecompressArchiveAsync(string archivePath, string destDir);

    // JSON/XML helpers
    public static T LoadJsonFile<T>(string filePath);
    public static Task SaveJsonFileAsync<T>(string filePath, T data, bool indent = true);
    public static XDocument LoadXmlFile(string filePath);
    public static Task SaveXmlFileAsync(string filePath, XDocument doc);
}

Project & Library Helpers

6 helpers in ProjectandLibraryHelpers/ for project management and metadata.

ProjectCreationHelper

public static class ProjectCreationHelper
{
    public static Tuple<IErrorsInfo, RootFolder> CreateProject(
        IDMEEditor dmeEditor, string folderpath, ProjectFolderType folderType = ProjectFolderType.Files);
}

ProjectMetadataHelper

public static class ProjectMetadataHelper
{
    public static ProjectMetadata LoadProjectMetadata(string projectPath);
    public static void SaveProjectMetadata(string projectPath, ProjectMetadata metadata);
    public static ProjectMetadata CreateDefaultMetadata(string projectName, string rootPath);
}

ProjectRetrievalHelper

public static class ProjectRetrievalHelper
{
    public static List<RootFolder> GetProjects(IDMEEditor dmeEditor, string rootPath);
    public static RootFolder LoadProject(IDMEEditor dmeEditor, string projectPath);
}

ProjectSynchronizationHelper

public static class ProjectSynchronizationHelper
{
    public static void SyncProjectDataSources(RootFolder project, IDMEEditor dmeEditor);
    public static void SyncProjectAssemblies(RootFolder project, IAssemblyHandler handler);
}

ProjectLifecycleHelper

public static class ProjectLifecycleHelper
{
    public static bool InitializeProject(RootFolder project, IDMEEditor dmeEditor);
    public static bool ValidateProjectIntegrity(RootFolder project);
    public static void ArchiveProject(RootFolder project, string archivePath);
    public static void RestoreProject(string archivePath, string targetPath);
}

ProjectManagementHelper

public static class ProjectManagementHelper
{
    public static void AddDataSourceToProject(RootFolder project, string datasourceName, IDMEEditor editor);
    public static void RemoveDataSourceFromProject(RootFolder project, string datasourceName, IDMEEditor editor);
    public static List<string> GetProjectDataSources(RootFolder project);
    public static Dictionary<string, int> GetProjectFileStats(RootFolder project);
}

General Utility Helpers

ValidationHelper

public static class ValidationHelper
{
    // ValidationResult class
    public class ValidationResult
    {
        public bool IsValid { get; set; }
        public List<string> Errors { get; set; }
        public List<string> Warnings { get; set; }

        public void AddError(string message);
        public void AddWarning(string message);
        public string ToSummary();
    }

    // Core validation methods
    public static ValidationResult ValidateConnectionProperties(IConnectionProperties props);
    public static ValidationResult ValidateEntityStructure(EntityStructure entity);
    public static ValidationResult ValidateDataType(string dataType, DataSourceType dsType);
    public static ValidationResult ValidateEntityName(string entityName, DataSourceType dsType, bool allowReserved = false);
    public static ValidationResult ValidateColumnName(string columnName, DataSourceType dsType, bool allowReserved = false);
    public static ValidationResult ValidateConnectionString(string connectionString, DataSourceType dsType);

    // Data validation
    public static ValidationResult ValidateDataValue(object value, EntityField field);
    public static ValidationResult ValidatePrimaryKey(object value, EntityStructure entity);
    public static ValidationResult ValidateForeignKey(object value, RelationShipKeys fk);

    // Regex helpers
    public static bool IsValidEmail(string email);
    public static bool IsValidUrl(string url);
    public static bool IsValidIpAddress(string ip);
}

ErrorHandlingHelper

public static class ErrorHandlingHelper
{
    // Structured error handling
    public static void HandleException(Exception ex, string context, IDMEEditor editor,
        bool includeStackTrace = true);
    public static void LogError(string message, string context, IDMEEditor editor,
        Exception ex = null);

    // Error categorization
    public static ErrorCategory CategorizeError(Exception ex);
    public static string GetUserFriendlyMessage(Exception ex);
    public static bool IsTransientError(Exception ex);     // network, timeout
    public static bool IsConfigurationError(Exception ex);  // bad config, missing driver

    public enum ErrorCategory
    {
        Unknown, Validation, Configuration, Connection,
        Query, DataMapping, Security, Permission,
        ResourceNotFound, Timeout, Concurrency,
        Serialization, OutOfMemory
    }
}

StringExtensions

public static class StringExtensions
{
    // String manipulation extensions
    public static string ToCamelCase(this string value);
    public static string ToPascalCase(this string value);
    public static string ToSnakeCase(this string value);
    public static string ToKebabCase(this string value);

    // Splitting and parsing
    public static string[] SplitAndTrim(this string value, char separator = ',');
    public static bool EqualsIgnoreCase(this string value, string other);
    public static bool ContainsIgnoreCase(this string value, string substring);
    public static string Truncate(this string value, int maxLength, string suffix = "...");

    // Encoding/path helpers
    public static string ToSafeFileName(this string value);
    public static string ToUrlSafeString(this string value);
    public static string RemoveSpecialCharacters(this string value, string allowedChars = null);
    public static string ToSqlSafeString(this string value);
    public static string ToXmlSafeString(this string value);
}

VectorDatabaseHelper

public static class VectorDatabaseHelper
{
    public static float[] NormalizeVector(float[] vector);
    public static float CosineSimilarity(float[] a, float[] b);
    public static float EuclideanDistance(float[] a, float[] b);
    public static float DotProduct(float[] a, float[] b);
    public static List<(int Index, float Similarity)> FindNearest(
        float[] query, List<(int Id, float[] Vector)> vectors, int topK = 10);
}

DataSourceLifecycleHelper

public static class DataSourceLifecycleHelper
{
    public static IErrorsInfo OpenDataSource(IDataSource ds);
    public static IErrorsInfo CloseDataSource(IDataSource ds);
    public static ConnectionState RefreshConnection(IDataSource ds);
    public static bool IsHealthy(IDataSource ds);
    public static Task<bool> PingAsync(IDataSource ds, int timeoutMs = 5000, CancellationToken ct = default);
}

Complete Helpers Source File Map (100+ files)