Data Manipulation Language (DML)

This guide covers the Create, Update, and Delete operations in Velocity. For querying and retrieval operations, see the Query documentation.

Overview

Velocity provides several methods for manipulating data in your database:

• AddRecord - Insert a single record and return the auto-generated identity value
• AddRecordNoIdentity - Insert a single record without returning an identity (faster for tables without auto-generated keys)
• AddRecords - Insert multiple records from a subquery (INSERT INTO ... SELECT FROM)
• UpdateRecords - Update existing records based on filter criteria
• DeleteRecords - Delete records based on filter criteria

All DML operations are performed through the Manager class, which must be initialized with a database connection.

---

Creating Records

For full documentation on building records, setting field values, using subqueries and functions as values, and data binding, see Working with Records.

The three insert methods are:

• AddRecord — inserts a record and returns the auto-generated identity value as long
• AddRecordNoIdentity — inserts a record without returning an identity; use this for tables without auto-generated keys or when the identity is not needed
• AddRecords — bulk-inserts rows from a subquery (INSERT INTO ... SELECT)

AddRecordNoIdentity

Use AddRecordNoIdentity when you don't need the auto-generated identity value. This is more efficient for bulk operations or when working with tables that don't have auto-generated keys.

using (Manager manager = new Manager(connection))
{
    Table logs = schema["Logs"];

    Record record = new Record(logs);
    record.SetFieldVarChar("Message", "User logged in");
    record.SetFieldDateTime("Timestamp", DateTime.Now);
    record.SetFieldInteger("UserId", 42);

    manager.AddRecordNoIdentity(record);
}

AddRecords (INSERT INTO ... SELECT)

The AddRecords method inserts multiple records into a table using the results from a subquery. This is equivalent to SQL's INSERT INTO ... SELECT statement.

Basic Example

using (Manager manager = new Manager(connection))
{
    Table archive = schema["OrdersArchive"];
    Table orders = schema["Orders"];

    // Create query to select orders older than 1 year
    Query query = new Query()
        .Select([
            orders["OrderID"],
            orders["CustomerID"],
            orders["OrderDate"],
            orders["Total"]
        ])
        .From(orders)
        .Where(new Criterion<DateTime>(orders["OrderDate"],
            ConditionalType.LessThan,
            DateTime.Now.AddYears(-1)));

    // Specify target columns (must match query select items in order)
    List<Column> targetColumns = new List<Column>
    {
        archive["OrderID"],
        archive["CustomerID"],
        archive["OrderDate"],
        archive["Total"]
    };

    // Insert matching records
    manager.AddRecords(archive, targetColumns, query);
}

Using Expressions in INSERT SELECT

You can use expressions to transform data during insertion:

Query query = new Query()
    .Select([
        sourceTable["IntegerColumn"],
        sourceTable["FloatColumn"],
        // Add a literal value for all inserted records
        new Expression("Status", new Literal<string>("Migrated"))
    ])
    .From(sourceTable);

List<Column> columns = new List<Column>
{
    targetTable["IntegerColumn"],
    targetTable["FloatColumn"],
    targetTable["Status"]
};

manager.AddRecords(targetTable, columns, query);

---

Data Binding for Inserts

For full documentation on binding strategies (Attribute, CamelCase, DirectMap, PascalCase, SnakeCase) and typed record examples, see Working with Records.

---

Updating Records

The UpdateRecords method modifies existing records that match specified filter criteria.

Basic Update

using (Manager manager = new Manager(connection))
{
    Table users = schema["Users"];

    Update update = new Update(users,
        new Criterion<DateTime>(users["LastLogin"],
            ConditionalType.LessThan,
            DateTime.Now.AddDays(-90)));
    update.SetFieldBoolean("IsActive", false);
    update.SetFieldDateTime("LastModified", DateTime.Now);

    manager.UpdateRecords(update);
}

Update with Multiple Conditions

Table products = schema["Products"];

// Update products that are low stock AND in a specific category
Update update = new Update(products, [
    new Criterion<int>(products["StockQuantity"], ConditionalType.LessThan, 10),
    new BooleanItem(BooleanType.And),
    new Criterion<string>(products["Category"], ConditionalType.Equals, "Electronics")
]);
update.SetFieldDecimal("Price", 9.99M);
update.SetFieldBoolean("OnSale", true);

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

Update Using Subquery as Value

Set a column's value based on a subquery result:

Table orders = schema["Orders"];
Table customers = schema["Customers"];

// Subquery to get customer's preferred shipping method
Query shippingQuery = new Query()
    .Select([customers["PreferredShipping"]])
    .From(customers)
    .Where(new Criterion<int>(customers["CustomerID"], ConditionalType.Equals, 42));

Update update = new Update(orders,
    new Criterion<int>(orders["CustomerID"], ConditionalType.Equals, 42));
update.SetFieldAsQuery("ShippingMethod", shippingQuery);
update.SetFieldDateTime("UpdatedAt", DateTime.Now);

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

Update with Subquery in WHERE Clause

Use a subquery as part of the filter criteria:

Table products = schema["Products"];
Table orders = schema["Orders"];

// Subquery to find products ordered in the last 30 days
Query recentOrdersQuery = new Query()
    .Select([orders["ProductID"]])
    .From(orders)
    .Where(new Criterion<DateTime>(orders["OrderDate"],
        ConditionalType.GreaterThan,
        DateTime.Now.AddDays(-30)));

Update update = new Update(products,
    new Criterion<Query>(products["ProductID"], ConditionalType.In, recentOrdersQuery));
update.SetFieldBoolean("RecentlyOrdered", true);

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

Update Using BETWEEN

Table inventory = schema["Inventory"];

// Update items with quantity between 5 and 20
Update update = new Update(inventory,
    new Criterion<int>(inventory["Quantity"],
        ConditionalType.Between,
        new List<int> { 5, 20 }));
update.SetFieldVarChar("Status", "Reorder Required");

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

Update Using NOT IN

Table users = schema["Users"];

// Don't update users with specific roles
Update update = new Update(users,
    new Criterion<string>(users["Role"],
        ConditionalType.NotIn,
        new List<string> { "Admin", "Moderator", "System" }));
update.SetFieldBoolean("SendNotifications", false);

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

---

Deleting Records

The DeleteRecords method removes records that match specified filter criteria.

Warning: Always include a WHERE clause when deleting records. To remove all records from a table, use manager.EmptyTable(table) instead, which makes the intent explicit.

Basic Delete

using (Manager manager = new Manager(connection))
{
    Table sessions = schema["Sessions"];

    // Delete expired sessions
    Delete delete = new Delete(sessions,
        new Criterion<DateTime>(sessions["ExpiresAt"],
            ConditionalType.LessThan,
            DateTime.Now));

    manager.DeleteRecords(delete);
}

Delete with Multiple Conditions

Table logs = schema["Logs"];

// Delete old info-level logs (keep errors and warnings longer)
Delete delete = new Delete(logs, [
    new Criterion<DateTime>(logs["CreatedAt"],
        ConditionalType.LessThan,
        DateTime.Now.AddMonths(-6)),
    new BooleanItem(BooleanType.And),
    new Criterion<string>(logs["Level"], ConditionalType.Equals, "Info")
]);

using (Manager manager = new Manager(connection))
{
    manager.DeleteRecords(delete);
}

Delete Using Subquery

Delete records based on a subquery condition:

Table users = schema["Users"];
Table orders = schema["Orders"];

// Subquery to find users who have placed an order in the last year
Query activeUsersQuery = new Query()
    .Select([orders["UserID"]])
    .From(orders)
    .Where(new Criterion<DateTime>(orders["OrderDate"],
        ConditionalType.GreaterThan,
        DateTime.Now.AddYears(-1)));

// Delete users NOT in the active users list
Delete delete = new Delete(users,
    new Criterion<Query>(users["UserID"],
        ConditionalType.NotIn,
        activeUsersQuery));

using (Manager manager = new Manager(connection))
{
    manager.DeleteRecords(delete);
}

Delete by ID

Table products = schema["Products"];

Delete delete = new Delete(products,
    new Criterion<int>(products["ProductID"], ConditionalType.Equals, 12345));

using (Manager manager = new Manager(connection))
{
    manager.DeleteRecords(delete);
}

Empty Table

To delete all records from a table (equivalent to TRUNCATE in some databases):

using (Manager manager = new Manager(connection))
{
    Table tempData = schema["TempData"];
    manager.EmptyTable(tempData);
}

---

Complete Example

Here's a comprehensive example showing Create, Update, and Delete operations together:

using YndigoBlue.Velocity;
using YndigoBlue.Velocity.Enums;

// Load schema
Schema schema;
using (Manager manager = new Manager())
{
    schema = manager.ReadSchemaFromFile("database_schema.xml", ConfigType.Xml);
}

// Create connection
var connection = new PostgreSqlDatasourceConnection(
    hostname: "localhost",
    port: 5432,
    database: "myapp",
    username: "appuser",
    password: "secret123"
);

using (Manager manager = new Manager(connection))
{
    Table users = schema["users"];
    Table orders = schema["orders"];

    // CREATE: Insert a new user using binding
    var userRecord = new Record<User>(users, BindingType.SnakeCase);
    userRecord.Bind(new User
    {
        Username = "jane.doe",
        Email = "jane@example.com",
        FirstName = "Jane",
        LastName = "Doe",
        CreatedAt = DateTime.Now,
        IsActive = true
    });

    long userId = manager.AddRecord(userRecord);
    Console.WriteLine($"Created user ID: {userId}");

    // CREATE: Insert an order manually
    Record orderRecord = new Record(orders);
    orderRecord.SetFieldLong("UserID", userId);
    orderRecord.SetFieldDateTime("OrderDate", DateTime.Now);
    orderRecord.SetFieldDecimal("Total", 149.99M);
    orderRecord.SetFieldVarChar("Status", "Pending");

    long orderId = manager.AddRecord(orderRecord);

    // UPDATE: Mark the order as shipped
    Update updateOrder = new Update(orders,
        new Criterion<long>(orders["OrderID"], ConditionalType.Equals, orderId));
    updateOrder.SetFieldVarChar("Status", "Shipped");
    updateOrder.SetFieldDateTime("ShippedDate", DateTime.Now);

    manager.UpdateRecords(updateOrder);

    // UPDATE: Deactivate users with no orders
    Query noOrdersQuery = new Query()
        .Select([orders["UserID"]])
        .From(orders);

    Update deactivateUsers = new Update(users,
        new Criterion<Query>(users["UserID"], ConditionalType.NotIn, noOrdersQuery));
    deactivateUsers.SetFieldBoolean("IsActive", false);

    manager.UpdateRecords(deactivateUsers);

    // DELETE: Remove cancelled orders older than 90 days
    Delete deleteCancelled = new Delete(orders, [
        new Criterion<string>(orders["Status"], ConditionalType.Equals, "Cancelled"),
        new BooleanItem(BooleanType.And),
        new Criterion<DateTime>(orders["OrderDate"],
            ConditionalType.LessThan,
            DateTime.Now.AddDays(-90))
    ]);

    manager.DeleteRecords(deleteCancelled);
}

---

Filter Operators

Velocity supports the following conditional operators for WHERE clauses:

Operator	Description	Example
Between	Value between two values (inclusive)	ConditionalType.Between
Equals	Equal to	ConditionalType.Equals
GreaterThan	Greater than	ConditionalType.GreaterThan
GreaterThanOrEqualTo	Greater than or equal	ConditionalType.GreaterThanOrEqualTo
In	Value in list	ConditionalType.In
IsNotNull	Value is not NULL	ConditionalType.IsNotNull
IsNull	Value is NULL	ConditionalType.IsNull
LessThan	Less than	ConditionalType.LessThan
LessThanOrEqualTo	Less than or equal	ConditionalType.LessThanOrEqualTo
Like	Pattern matching	ConditionalType.Like
NotEquals	Not equal to	ConditionalType.NotEquals
NotIn	Value not in list	ConditionalType.NotIn

---

Best Practices

1. Always use using statements - The Manager class manages database connections and should always be disposed properly

2. Use typed methods - Use SetFieldInteger(), SetFieldVarChar(), etc. instead of generic methods for type safety

3. Use binding for complex objects - For objects with many properties, use Record<T> with binding instead of manually setting each field

4. Include WHERE clauses for safety - Always specify WHERE clauses when updating or deleting to avoid accidentally modifying all records

5. Use transactions for related operations - When performing multiple DML operations that should succeed or fail together, use database transactions

6. Use AddRecordNoIdentity for bulk inserts - When inserting many records and you don't need the identity values, use AddRecordNoIdentity for better performance

7. Prefer AddRecords for bulk data - When copying or transforming data from one table to another, use AddRecords with a query instead of looping with AddRecord

---

Next Steps

• Learn about querying data
• Explore schema management
• Understand data types and conversions