RDBMS Helpers Reference

Overview

RDBMS helpers provide dialect-aware SQL generation and schema operations across 30+ database systems. The facade delegates to specialized helpers:

• Schema queries — Table existence, column info, schema listing, constraint queries
• Object creation — DDL for tables, indexes, constraints, views, procedures
• DML — INSERT, UPDATE, DELETE, SELECT generation with provider-specific syntax
• Entity helpers — Object relation mapping, CRUD, caching, parameter generation
• Feature detection — Capability checks, version detection, SQL dialect rules
• Query repository — Pre-built query templates for each database type
• Validation — Entity schema validation against target database

Supported Database Types

SqlServer, Oracle, MySQL, PostgreSQL, SQLite, DB2, Sybase, 
Informix, Firebird, Teradata, Netezza, ODBC, OleDb, 
MsAccess, Excel, Csv, Xml, Json, MongoDB, Redis, 
Cassandra, CosmosDB, DocumentDB, Hive, Impala, 
Spark, Neo4j, ArangoDB, ElasticSearch, Cassandra, Couchbase

RDBMSHelper Facade

namespace TheTechIdea.Beep.Helpers.RDBMSHelpers
{
    public static partial class RDBMSHelper
    {
        // Schema & Metadata (→ DatabaseSchemaQueryHelper)
        public static string GetSchemasorDatabases(DataSourceType rdbms, string userName);
        public static (string, bool, string) GetSchemasorDatabasesSafe(DataSourceType, string, bool);
        public static QueryValidationResult ValidateSchemaQuery(DataSourceType, string, string);
        public static string GetTableExistsQuery(DataSourceType, string tableName, string schema = null);
        public static string GetColumnInfoQuery(DataSourceType, string tableName, string schema = null);

        // Object Creation (→ DatabaseObjectCreationHelper)
        public static string CreateTableSqlString(EntityStructure entity, DataSourceType dsType);
        public static List<string> CreateAlterSqlStrings(EntityStructure, DataSourceType);
        public static string DropTableSqlString(EntityStructure entity, DataSourceType dsType);

        // DML (→ DMLHelpers)
        public static string InsertSqlString(DataSourceType, EntityStructure, string tableName, params[]);
        public static string UpdateSqlString(DataSourceType, EntityStructure, string tableName, params[]);
        public static string DeleteSqlString(DataSourceType, EntityStructure, string tableName, params[]);
        public static string SelectSqlString(DataSourceType, EntityStructure, string tableName, params[]);

        // Feature Detection (→ DatabaseFeatureHelper)
        public static bool SupportsTransaction(DataSourceType dsType);
        public static bool SupportsBatching(DataSourceType dsType);
        public static string GetIdentitySql(DataSourceType dsType);

        // Query Repository (→ DatabaseQueryRepositoryHelper)
        public static List<QueryConfig> CreateQuerySqlRepos();
        public static QueryConfig GetQueryByType(DataSourceType dsType, string queryType);
    }
}

Schema & Metadata Queries

DatabaseSchemaQueryHelper generates dialect-aware schema introspection queries:

// List accessible schemas or databases
string sql = RDBMSHelper.GetSchemasorDatabases(DataSourceType.SqlServer, "sa");
// Returns: SELECT name FROM sys.databases (SQL Server)
// Returns: SELECT schema_name FROM information_schema.schemata (PostgreSQL)
// Returns: SHOW DATABASES (MySQL)

// Table existence check
string checkSql = RDBMSHelper.GetTableExistsQuery(
    DataSourceType.Postgresql, "customers", schemaName: "public");
// Returns: SELECT EXISTS (SELECT FROM information_schema.tables 
//          WHERE table_name = 'customers' AND table_schema = 'public')

// Column info
string colsSql = RDBMSHelper.GetColumnInfoQuery(
    DataSourceType.MySql, "orders");
// Returns: SHOW COLUMNS FROM orders (MySQL)
// Returns: sp_columns 'orders' (SQL Server)

// Safe query with error handling
var (query, success, error) = RDBMSHelper.GetSchemasorDatabasesSafe(
    DataSourceType.Oracle, "SCOTT", throwOnError: false);
if (!success) Console.WriteLine(error);

Object Creation (DDL)

// Create table
var entity = new EntityStructure
{
    EntityName = "Customers",
    Fields = new List<EntityField>
    {
        new EntityField { FieldName = "Id", FieldType = "int", IsKey = true },
        new EntityField { FieldName = "Name", FieldType = "string", MaxLength = 100 },
        new EntityField { FieldName = "Email", FieldType = "string" }
    }
};

string createSql = RDBMSHelper.CreateTableSqlString(entity, DataSourceType.SqlServer);
// Returns: CREATE TABLE Customers (Id INT PRIMARY KEY, Name NVARCHAR(100), Email NVARCHAR(MAX))

string createPgSql = RDBMSHelper.CreateTableSqlString(entity, DataSourceType.Postgresql);
// Returns: CREATE TABLE Customers (Id SERIAL PRIMARY KEY, Name VARCHAR(100), Email TEXT)

// Alter table
var alteredEntity = /* modified entity structure */;
var alterStatements = RDBMSHelper.CreateAlterSqlStrings(alteredEntity, DataSourceType.SqlServer);

// Drop table
string dropSql = RDBMSHelper.DropTableSqlString(entity, DataSourceType.SqlLite);
// Returns: DROP TABLE IF EXISTS Customers

DML Operations

// INSERT
string insertSql = RDBMSHelper.InsertSqlString(
    DataSourceType.SqlServer, entity, "Customers",
    "Id", "Name", "Email");
// Returns: INSERT INTO Customers (Id, Name, Email) VALUES (@Id, @Name, @Email)

// PostgreSQL INSERT
string pgInsert = RDBMSHelper.InsertSqlString(
    DataSourceType.Postgresql, entity, "Customers",
    "Id", "Name", "Email");
// Returns: INSERT INTO Customers (Id, Name, Email) VALUES ($1, $2, $3)

// UPDATE
string updateSql = RDBMSHelper.UpdateSqlString(
    DataSourceType.MySql, entity, "Customers",
    "Name", "Email");
// Returns: UPDATE Customers SET Name = @Name, Email = @Email WHERE Id = @Id

// DELETE
string deleteSql = RDBMSHelper.DeleteSqlString(
    DataSourceType.Oracle, entity, "Customers", "Id");
// Returns: DELETE FROM Customers WHERE Id = :Id

// SELECT
string selectSql = RDBMSHelper.SelectSqlString(
    DataSourceType.SqlLite, entity, "Customers",
    "Id", "Name", "Email");
// Returns: SELECT Id, Name, Email FROM Customers

Provider-Specific Differences

Entity Helpers

// Entity CRUD with providers
var entityHelper = new RDBMSEntityHelper(DataSourceType.SqlServer);

// Generate INSERT with entity values
var entity = new EntityStructure { /* ... */ };
var data = new Dictionary<string, object>
{
    ["Id"] = 1, ["Name"] = "John", ["Email"] = "john@example.com"
};

string sql = entityHelper.GenerateInsert(entity, "Customers", data);

// Generate parameters
var parameters = entityHelper.GenerateParameters(entity, data);

// Caching
entityHelper.CacheEntityStructure("Customers", entity);
var cached = entityHelper.GetCachedStructure("Customers");

// Clear cache
entityHelper.ClearEntityCache();

Feature Detection

// Capability checks
bool supportsXact = RDBMSHelper.SupportsTransaction(DataSourceType.SqlServer); // true
bool supportsBatch = RDBMSHelper.SupportsBatching(DataSourceType.SqlServer);   // true
bool supportsXact = RDBMSHelper.SupportsTransaction(DataSourceType.Csv);       // false

// Identity SQL per provider
string identity = RDBMSHelper.GetIdentitySql(DataSourceType.SqlServer);      // SELECT SCOPE_IDENTITY()
string identityPg = RDBMSHelper.GetIdentitySql(DataSourceType.Postgresql);   // SELECT lastval()
string identityMy = RDBMSHelper.GetIdentitySql(DataSourceType.MySql);        // SELECT LAST_INSERT_ID()

// Feature detection matrix
var features = DatabaseFeatureHelper.GetSupportedFeatures(DataSourceType.SqlServer);
foreach (var (feature, supported) in features)
    Console.WriteLine($"{feature}: {supported}");

Feature Matrix

Query Repository

// Get all query templates for all databases
var allQueries = RDBMSHelper.CreateQuerySqlRepos();
// Returns: List<QueryConfig> with pre-built queries per database type

// Each QueryConfig contains:
// - DataSourceType (SqlServer, PostgreSQL, etc.)
// - QueryType (SchemaQuery, TableQuery, ColumnQuery, etc.)
// - QuerySQL (the actual SQL template)
// - IsDefault (whether this is the default query)

// Get specific query type
var schemaQuery = RDBMSHelper.GetQueryByType(
    DataSourceType.Postgresql, "SchemaQuery");
Console.WriteLine(schemaQuery.QuerySQL);
// SELECT schema_name FROM information_schema.schemata
// WHERE schema_name NOT IN ('information_schema', 'pg_catalog')

// Query categories:
// SchemaQuery    → List schemas/databases
// TableQuery     → List tables in schema
// ColumnQuery    → List columns in table
// PrimaryKeyQuery → List primary key columns
// ForeignKeyQuery → List foreign keys
// IndexQuery     → List indexes
// ConstraintQuery → List constraints

Entity Validation

// Validate entity structure against target database
var validator = new RDBMSEntityValidator(DataSourceType.SqlServer);

var entity = new EntityStructure
{
    EntityName = "Customers",
    Fields = new List<EntityField>
    {
        new EntityField { FieldName = "Id", FieldType = "int", IsKey = true },
        new EntityField { FieldName = "CreditLimit", FieldType = "decimal", MaxLength = 18 }
    }
};

var result = validator.ValidateEntity(entity);
if (!result.IsValid)
{
    foreach (var error in result.Errors)
        Console.WriteLine($"{error.FieldName}: {error.Message}");
}

// Validation checks:
// - Reserved word conflicts
// - Field name length limits
// - Data type compatibility
// - Maximum column count
// - Index name length limits
// - Constraint naming conventions

Universal Helpers

The universal helper system provides instance-based helpers via IUniversalHelperFactory:

// Get helper via factory per DataSourceType
var factory = editor.GetUniversalHelperFactory();
var helper = factory.GetHelper(DataSourceType.SqlServer);

// Same dialect-aware API, instance-based
var (sql, success, error) = helper.GenerateCreateTableSql(entityStructure);

// Helper categories:
// - RdbmsHelper         → SQL generation, schema, DML, features
// - MongoDBHelper        → Document operations
// - RestApiHelper        → HTTP request building
// - VectorHelper         → Vector DB operations
// - GraphHelper          → Graph DB (Cypher, Gremlin)
// - SearchHelper         → Full-text search
// - StreamingHelper      → Stream processing
// - TimeSeriesHelper     → Time-series operations
// - RedisHelper          → Key-value operations
// - CassandraHelper      → Wide-column operations
// - FileHelper           → File-based data source ops
// - AnalysisHelper       → Data analysis utilities

Usage Examples

Cross-Database Schema Script

var entity = new EntityStructure { EntityName = "Products", Fields = { ... } };

// Same entity definition, different SQL per provider
var sqlServerSql = RDBMSHelper.CreateTableSqlString(entity, DataSourceType.SqlServer);
var postgreSql = RDBMSHelper.CreateTableSqlString(entity, DataSourceType.Postgresql);
var oracleSql = RDBMSHelper.CreateTableSqlString(entity, DataSourceType.Oracle);
var mySql = RDBMSHelper.CreateTableSqlString(entity, DataSourceType.MySql);

// Apply to different environments
editor.GetDataSource("DevDB").ExecuteSql(postgreSql);
editor.GetDataSource("ProdDB").ExecuteSql(sqlServerSql);

Full CRUD Generation

var dsType = DataSourceType.Postgresql;
var entity = GetEntityStructure("Orders");

// Schema
var create = RDBMSHelper.CreateTableSqlString(entity, dsType);
var check = RDBMSHelper.GetTableExistsQuery(dsType, "Orders");

// CRUD
var insert = RDBMSHelper.InsertSqlString(dsType, entity, "Orders", fields);
var update = RDBMSHelper.UpdateSqlString(dsType, entity, "Orders", fields);
var delete = RDBMSHelper.DeleteSqlString(dsType, entity, "Orders", keyField);
var select = RDBMSHelper.SelectSqlString(dsType, entity, "Orders", fields);

// Execute
var ds = editor.GetDataSource("AppDB");
ds.ExecuteSql(create);
ds.ExecuteSql(insert);
ds.ExecuteSql(update);

Feature-Gated Operations

if (RDBMSHelper.SupportsTransaction(dbType))
{
    using var tx = ds.BeginTransaction();
    try
    {
        ds.ExecuteSql(RDBMSHelper.InsertSqlString(dbType, entity, "Orders", fields));
        ds.ExecuteSql(RDBMSHelper.UpdateSqlString(dbType, entity, "Inventory", fields));
        tx.Commit();
    }
    catch { tx.Rollback(); throw; }
}
else
{
    // Fallback: execute without transaction
    ds.ExecuteSql(insertSql);
    ds.ExecuteSql(updateSql);
}