One unified API for cross-vendor .NET database access

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Model;

// Manager is the entry point and owns the connection for its lifetime
using var manager = new Manager(connection);

// Compose a type-safe query with no raw SQL strings in application code
// Multiple criteria are combined in an array with BooleanItem connectors
var query = new Query()
    .Select([Product.Table["product_id"],
             Product.Table["product_name"],
             Product.Table["unit_price"],
             Product.Table["category"]])
    .From(Product.Table)
    .Where([
        new Criterion<decimal>(Product.Table["unit_price"], ConditionalType.GreaterThan, 50.00m),
        new BooleanItem(BooleanType.And),
        new Criterion<string>(Product.Table["category"], "Electronics")
    ])
    .OrderBy(Product.Table["unit_price"], OrderClauseType.Descending);

// BindResults maps each result row to a typed object via [VelocityField]
IList<Product> products = manager
    .Retrieve(query)
    .BindResults<Product>(BindingType.Attribute);

// Update a record: pass the criterion to the constructor and use the typed Set methods
var update = new Update(Product.Table,
    new Criterion<int>(Product.Table["product_id"], 42));
update.SetFieldDecimal("unit_price", 45.00m);
manager.UpdateRecords(update);

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Model;

var orderTable = Datastore.Schema["order"];
var customerTable = Datastore.Schema["customer"];

// Aggregates: count, sum, avg, min, max
var revenueExpr = new Expression("total_revenue", new Aggregate(AggregateType.Sum, orderTable["amount"]));
var countExpr = new Expression("order_count", new Aggregate(AggregateType.Count, orderTable["order_id"]));

// Join on customer_id and group by customer. The constructor defaults to an inner
// equality join; other overloads take the join type and operator explicitly.
var query = new Query()
    .Select([customerTable["name"], revenueExpr, countExpr])
    .From(new Join(orderTable, customerTable, "customer_id"))
    .GroupBy(customerTable["name"])
    .OrderBy(revenueExpr);

// Bind to a dedicated result model
IList<CustomerRevenue> topCustomers = manager
    .Retrieve(query)
    .BindResults<CustomerRevenue>(BindingType.Attribute);

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Enums;
using YndigoBlue.Velocity.Model;

// Load schema from a versioned XML file and cache it globally
Datastore.Schema = manager.ReadSchemaFromFile("Config/myapp-v3.xml", ConfigType.Xml);

// Verify the live database matches, and fall back to the previous version if not
Schema liveSchema = manager.LoadSchema("myapp");
if (!liveSchema.Equals(Datastore.Schema))
{
    Datastore.Schema = manager.ReadSchemaFromFile("Config/myapp-v2.xml", ConfigType.Xml);
}

// Deploy a schema update (e.g. new columns, new tables) in a transaction
string newSchemaXml = File.ReadAllText("Config/myapp-v4.xml");
Datastore.Schema = manager.ReadSchema(newSchemaXml, ConfigType.Xml);

manager.BeginTransaction();
manager.UpdateSchema(Datastore.Schema);
manager.CommitTransaction();

// Rebuild the schema from scratch, dropping and recreating every table
manager.BuildSchema(Datastore.Schema, overwrite: true);

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Model;

// ── CSV import ──
// ImportConfig takes the target table, date format, and whether to retain identity values from file
var config = new ImportConfig(Product.Table, DateFormatType.Iso, keepIdentity: true);
manager.ImportData(Product.Table, "data/products.csv", config);

// ── Export a schema's definition and every table's data to a folder ──
manager.ExportSchema(
    Datastore.Schema,
    ConfigType.Xml,
    "data/export/",
    new ExportConfig(DateFormatType.Iso));

// ── Insert a single record using typed field setters ──
var record = new Record(Product.Table);
record.SetFieldInteger("product_id", 1001);
record.SetFieldVarChar("product_name", "Widget Pro");
record.SetFieldDecimal("unit_price", 49.99m);
record.SetFieldVarChar("category", "Electronics");
record.SetFieldDateTime("created_at", DateTime.UtcNow);
manager.AddRecord(record);

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Enums;
using YndigoBlue.Velocity.Functions;
using YndigoBlue.Velocity.Model;

// ── Store a geography value ──
Geography london = GeospatialUtils.GeographyFromText("POINT (-0.1276 51.5074)");

var record = new Record(Location.Table);
record.SetFieldVarChar("name", "London");
record.SetFieldGeography("coordinates", london);
manager.AddRecord(record);

// ── Proximity query: find all locations within 50 km of a reference point ──
Geography origin = GeospatialUtils.GeographyFromText("POINT (-1.8904 52.4862)");

// SpatialDistance computes ST_Distance between the column and the reference point
var distanceFn = new SpatialDistance(
    Location.Table["coordinates"],
    origin,
    UnitOfMeasure.Kilometer);

// Wrap in an Expression so it can appear in both SELECT and WHERE
var distanceExpr = new Expression("distance_km", distanceFn);

var query = new Query()
    .Select([Location.Table["name"], Location.Table["coordinates"], distanceExpr])
    .From(Location.Table)
    .Where(new Criterion<double>(distanceExpr, ConditionalType.LessThan, 50.0))
    .OrderBy(distanceExpr, OrderClauseType.Ascending);

IList<Location> nearby = manager
    .Retrieve(query)
    .BindResults<Location>(BindingType.Attribute);