Working with Records

Introduction

The Record class is the primary mechanism for inserting and updating data in Velocity. A Record represents a single row of data that will be added to or used to update a database table, providing type-safe access to column values and support for advanced features like subqueries and functions.

Basic Record Operations

Creating a Record

Records are created from a Table object and automatically know about all columns defined in that table:

using (Manager manager = new Manager(connection))
{
    manager.LoadSchema("my_schema");
    Schema schema = manager.GetSchema("my_schema");
    Table usersTable = schema["users"];

    // Create a new record for the users table
    Record record = new Record(usersTable);

    // Set field values
    record.SetFieldVarChar("username", "john_doe");
    record.SetFieldVarChar("email", "john@example.com");
    record.SetFieldInteger("age", 30);
    record.SetFieldDateTime("created_at", DateTime.UtcNow);

    // Insert the record
    manager.AddRecord(record);
}

Field Setters by Type

The Record class inherits from Datum and provides type-specific setter methods for each data type:

Method	.NET Type	DataType	Use Case
`SetFieldBlob(field, value)`	`byte[]`	`Blob`	Binary data
`SetFieldBoolean(field, value)`	`bool`	`Boolean`	True/false values
`SetFieldByte(field, value)`	`byte`	`Byte`	Small unsigned integers (0-255)
`SetFieldChar(field, value)`	`string`	`Char`	Fixed-length text
`SetFieldClob(field, value)`	`string`	`Clob`	Large text
`SetFieldDate(field, value)`	`DateOnly`	`Date`	Date only (no time)
`SetFieldDateTime(field, value)`	`DateTime`	`DateTime`	Date and time
`SetFieldDecimal(field, value)`	`decimal`	`Decimal`	Fixed-precision decimals
`SetFieldDouble(field, value)`	`double`	`Double`	Double-precision floating point
`SetFieldFloat(field, value)`	`float`	`Float`	Single-precision floating point
`SetFieldGeography(field, value)`	`Geography`	`Geography`	Geographic spatial data
`SetFieldGeometry(field, value)`	`Geometry`	`Geometry`	Planar spatial data
`SetFieldGuid(field, value)`	`Guid`	`VarChar`	GUIDs (stored as VarChar)
`SetFieldInteger(field, value)`	`int`	`Integer`	32-bit integers
`SetFieldInterval(field, value)`	`TimeSpan`	`Interval`	Duration/elapsed time
`SetFieldLong(field, value)`	`long`	`Long`	64-bit integers
`SetFieldShort(field, value)`	`short`	`Short`	16-bit integers
`SetFieldTime(field, value)`	`TimeOnly`	`Time`	Time of day
`SetFieldTimestamp(field, value)`	`DateTimeOffset`	`Timestamp`	Date, time, and timezone offset
`SetFieldVarChar(field, value)`	`string`	`VarChar`	Variable-length text

Field Parameter Options:

You can specify fields by name (string) or by ISelectItem (typically a Column object):

// By name
record.SetFieldVarChar("username", "john_doe");

// By Column object
Column usernameColumn = usersTable["username"];
record.SetFieldVarChar(usernameColumn, "john_doe");

Auto-Generated Columns

Velocity automatically handles auto-generated columns (typically auto-incrementing primary keys). You don't need to set values for these columns:

Table productsTable = schema.CreateTable("products");
productsTable.CreateColumn("product_id", DataType.Long, autoGenerate: true, notNull: true);
productsTable.CreateColumn("product_name", DataType.VarChar, size: 200);
productsTable.CreateColumn("price", DataType.Decimal, precision: 10, scale: 2);

// Create a record - no need to set product_id
Record product = new Record(productsTable);
product.SetFieldVarChar("product_name", "Widget");
product.SetFieldDecimal("price", 19.99m);

manager.AddRecord(product);
// The database will automatically generate the product_id value

Advanced Record Features

Subqueries as Field Values

Records support using subqueries to populate field values, which is useful for copying data from other tables or performing calculations:

// Create a query to get the max price from products
Query maxPriceQuery = new Query()
    .Select([new Max(productsTable["price"])])
    .From(productsTable);

// Use the subquery result as a field value
Record priceRecord = new Record(settingsTable);
priceRecord.SetFieldVarChar("setting_name", "max_price");
priceRecord.SetFieldAsQuery("setting_value", maxPriceQuery);

manager.AddRecord(priceRecord);

This generates SQL like:

INSERT INTO settings (setting_name, setting_value)
VALUES ('max_price', (SELECT MAX(price) FROM products))

Subquery Methods:

Method	Description
`GetFieldAsQuery(field)`	Retrieve the subquery for a field
`HasFieldAsQuery(field)`	Check if a field is set to a subquery
`SetFieldAsQuery(field, query)`	Set a field value from a Query result

Functions as Field Values

You can also use database Functions to calculate field values:

// Use UPPER() function to convert text to uppercase
Upper upperFunction = new Upper(new Literal("john doe"));
record.SetFieldAsFunction("username", upperFunction);

// Use NOW() or CURRENT_TIMESTAMP for timestamps
Now nowFunction = new Now();
record.SetFieldAsFunction("created_at", nowFunction);

manager.AddRecord(record);

Function Methods:

Method	Description
`GetFieldAsFunction(field)`	Retrieve the function for a field
`HasFieldAsFunction(field)`	Check if a field is set to a function
`SetFieldAsFunction(field, function)`	Set a field value using a database function

Typed Records with Data Binding

The Record<T> generic class enables automatic data binding between C# objects and database records, eliminating the need to manually set each field.

Binding Types

Velocity supports three binding strategies:

Binding Type	Description	Use Case
Attribute	Uses VelocityField attributes to map properties	Custom mapping
CamelCase	Converts C# PascalCase to camelCase	`UserName` → `userName`
DirectMap	Direct property name to column name mapping	`username` → `username`
SnakeCase	Converts C# PascalCase/camelCase to snake_case	`UserName` → `user_name`

Attribute-Based Binding

For complete control over mapping:

using YndigoBlue.Velocity.Attributes;

public class Employee
{
    [VelocityField("emp_id")]
    public int EmployeeId { get; set; }

    [VelocityField("first_name")]
    public string FirstName { get; set; }

    [VelocityField("last_name")]
    public string LastName { get; set; }

    [VelocityField("hire_date")]
    public DateTime HireDate { get; set; }

    // This property won't be mapped to any column
    [VelocityField(ignore: true)]
    public string FullName => $"{FirstName} {LastName}";
}

Record<Employee> record = new Record<Employee>(employeesTable, BindingType.Attribute);
record.Bind(employee);

Camel Case Binding

Useful for JavaScript-style naming conventions:

public class Product
{
    public string ProductName { get; set; }
    public decimal UnitPrice { get; set; }
}

// Maps ProductName → productName, UnitPrice → unitPrice
Record<Product> record = new Record<Product>(productsTable, BindingType.CamelCase);
record.Bind(product);

Direct Map Binding

When property names exactly match column names:

public class Order
{
    public string orderid { get; set; }
    public string customerid { get; set; }
    public decimal total { get; set; }
}

// Direct 1:1 mapping
Record<Order> record = new Record<Order>(ordersTable, BindingType.DirectMap);
record.Bind(order);

Snake Case Binding

Snake case is the most common convention for database column names:

// C# class with PascalCase properties
public class User
{
    public string UserName { get; set; }
    public string EmailAddress { get; set; }
    public int AccountAge { get; set; }
    public DateTime CreatedAt { get; set; }
}

// Database table with snake_case columns
Table usersTable = schema.CreateTable("users");
usersTable.CreateColumn("user_name", DataType.VarChar, size: 100);
usersTable.CreateColumn("email_address", DataType.VarChar, size: 200);
usersTable.CreateColumn("account_age", DataType.Integer);
usersTable.CreateColumn("created_at", DataType.DateTime);

// Create and bind
var user = new User
{
    UserName = "john_doe",
    EmailAddress = "john@example.com",
    AccountAge = 30,
    CreatedAt = DateTime.UtcNow
};

// Automatically maps UserName → user_name, EmailAddress → email_address, etc.
Record<User> record = new Record<User>(usersTable, BindingType.SnakeCase);
record.Bind(user);

manager.AddRecord(record);

Bulk Inserts

When inserting multiple records, use a loop or collection:

List<User> users = GetUsersFromApi();

using (Manager manager = new Manager(connection))
{
    manager.LoadSchema("my_schema");
    Schema schema = manager.GetSchema("my_schema");
    Table usersTable = schema["users"];

    foreach (var user in users)
    {
        Record<User> record = new Record<User>(usersTable, BindingType.SnakeCase);
        record.Bind(user);
        manager.AddRecord(record);
    }
}

Performance Tip: For large bulk inserts, consider using transactions:

using (Manager manager = new Manager(connection))
{
    manager.BeginTransaction();

    try
    {
        foreach (var user in users)
        {
            Record<User> record = new Record<User>(usersTable, BindingType.SnakeCase);
            record.Bind(user);
            manager.AddRecord(record);
        }

        manager.CommitTransaction();
    }
    catch
    {
        manager.RollbackTransaction();
        throw;
    }
}

Record Fields and Metadata

Accessing Record Fields

Records provide read-only access to their field structure:

Record record = new Record(usersTable);

// Get all fields (columns)
IReadOnlyCollection<ISelectItem> fields = record.Fields;

// Get field count
int fieldCount = record.FieldCount;

// Get the table
Table table = record.Table;

// Check if a field exists
bool hasUsername = record.HasField("username");

Field Information

Since Record inherits from Datum, you can query field metadata:

// Get field data type
DataType nameType = record.GetFieldDataType("username");

// Check if field is null
bool isNull = record.IsNull("email");

// Check if field has been set
bool hasEmail = record.HasField("email");

Null Handling

To store NULL, pass null to any typed setter. To check whether a field is NULL, use IsNull():

Record record = new Record(usersTable);

// Set a field to NULL by passing null to the typed setter
record.SetFieldVarChar("middle_name", null);

// Check if a field is NULL
if (record.IsNull("middle_name"))
{
    Console.WriteLine("Middle name is not set");
}

// Conditional setting
string middleName = user.MiddleName;
if (string.IsNullOrEmpty(middleName))
{
    record.SetFieldVarChar("middle_name", null);
}
else
{
    record.SetFieldVarChar("middle_name", middleName);
}

Common Patterns

Insert with Timestamp

Record record = new Record(usersTable);
record.SetFieldVarChar("username", "john_doe");
record.SetFieldVarChar("email", "john@example.com");
record.SetFieldDateTime("created_at", DateTime.UtcNow);
record.SetFieldDateTime("updated_at", DateTime.UtcNow);

manager.AddRecord(record);

Copy Data Between Tables

// Query source table
Query sourceQuery = new Query()
    .From(sourceTable)
    .Where(new Criterion<string>(sourceTable["status"], ConditionalType.Equals, "active"));

using (ResultSet results = manager.Retrieve(sourceQuery))
{
    foreach (Result row in results)
    {
        Record record = new Record(targetTable);
        record.SetFieldVarChar("name", row.GetFieldVarChar("name"));
        record.SetFieldVarChar("email", row.GetFieldVarChar("email"));
        record.SetFieldDateTime("migrated_at", DateTime.UtcNow);

        manager.AddRecord(record);
    }
}

Conditional Field Setting

public Record CreateUserRecord(User user, Table usersTable)
{
    Record record = new Record(usersTable);

    record.SetFieldVarChar("username", user.Username);
    record.SetFieldVarChar("email", user.Email);

    // Only set optional fields if they have values
    if (!string.IsNullOrEmpty(user.PhoneNumber))
    {
        record.SetFieldVarChar("phone_number", user.PhoneNumber);
    }
    else
    {
        record.SetFieldVarChar("phone_number", null);
    }

    if (user.BirthDate.HasValue)
    {
        record.SetFieldDate("birth_date", user.BirthDate.Value);  // DateOnly?
    }
    else
    {
        record.SetFieldDate("birth_date", null);
    }

    return record;
}

Working with Temporal Types

Velocity provides distinct setter methods for each temporal data type. Use the correct method to match the column's DataType:

var eventsTable = schema.CreateTable("events");
eventsTable.CreateColumn("id", DataType.Integer, autoGenerate: true);
eventsTable.CreateColumn("title", DataType.VarChar, size: 200);
eventsTable.CreateColumn("event_date", DataType.Date);
eventsTable.CreateColumn("start_time", DataType.Time);
eventsTable.CreateColumn("event_at", DataType.DateTime);
eventsTable.CreateColumn("published_at", DataType.Timestamp);
eventsTable.CreateColumn("duration", DataType.Interval);

Record eventRecord = new Record(eventsTable);
eventRecord.SetFieldVarChar("title", "Team Meeting");

// Date only — use DateOnly
eventRecord.SetFieldDate("event_date", new DateOnly(2026, 6, 15));

// Time of day — use TimeOnly
eventRecord.SetFieldTime("start_time", new TimeOnly(14, 30, 0));

// Date and time — use DateTime
eventRecord.SetFieldDateTime("event_at", new DateTime(2026, 6, 15, 14, 30, 0, DateTimeKind.Utc));

// Date, time, and timezone offset — use DateTimeOffset
eventRecord.SetFieldTimestamp("published_at", DateTimeOffset.UtcNow);

// Duration/elapsed time — use TimeSpan
eventRecord.SetFieldInterval("duration", TimeSpan.FromHours(1.5));

manager.AddRecord(eventRecord);

Note: SetFieldDate accepts DateOnly, not DateTime. SetFieldTime accepts TimeOnly, not TimeSpan. SetFieldInterval accepts TimeSpan. SetFieldTimestamp accepts DateTimeOffset.

Table of Contents