Data Binding for Relational Tables

Every application that talks to a database contains a layer whose only job is moving values between two shapes. Rows come back from a query as a collection of named fields. Objects hold the same information as typed properties, so something has to copy one into the other. That code is uninteresting to write, tedious to review, and it is where a surprising proportion of data access bugs live, because a mistyped column name or a forgotten field produces a value that is quietly wrong rather than an error.

Velocity offers three ways to move rows in and out of a database. AddRecord inserts, Retrieve returns a result set held in memory, and Search returns a forward-only reader. All three can work a field at a time. That is explicit, readable, and entirely reasonable until the number of fields grows. Binding is the alternative: describe the class once, and let the framework perform the copy in both directions.

This article covers what binding does, the five ways it can match a property to a column, what to do when the names do not agree, and how the same mechanism behaves when a result set is too large to hold in memory.

What problem does binding actually solve?

The cost of writing that copy manually is easiest to see against a table of ordinary size, so consider the following one, defined in Velocity's schema format:

<table name="customer">
    <columns>
        <column type="long" autogenerate="true" notnull="true">customer_id</column>
        <column type="varchar" size="255" notnull="true">email</column>
        <column type="varchar" size="100" notnull="true">first_name</column>
        <column type="varchar" size="100" notnull="true">last_name</column>
        <column type="boolean">confirmed</column>
        <column type="datetime">creation_stamp</column>
        <column type="boolean" notnull="true">subscription_active</column>
        <column type="integer" notnull="true">total_licenses</column>
        <column type="date">join_date</column>
    </columns>
</table>

Inserting a row into it without binding means naming every column and choosing the setter that matches its type:

using var manager = new Manager(connection);

Table customerTable = schema["customer"];
Record record = new Record(customerTable);

record.SetFieldVarChar("email", "john.smith@example.com");
record.SetFieldVarChar("first_name", "John");
record.SetFieldVarChar("last_name", "Smith");
record.SetFieldBoolean("confirmed", true);
record.SetFieldDateTime("creation_stamp", DateTime.Now);
record.SetFieldBoolean("subscription_active", true);
record.SetFieldInteger("total_licenses", 5);
record.SetFieldDate("join_date", DateOnly.FromDateTime(DateTime.Now));

long customerId = manager.AddRecord(record);

There is nothing wrong with that code. It states exactly what it does, and the typed setters catch an entire category of mistake before anything runs. For a table of three columns it is the clearest thing to write.

The difficulty is proportional. Eight columns produce eight lines, and in real applications those values almost always originate from an object the caller is already holding, which makes the block a manual copy from one shape into another. Adding a column then requires an edit in two places, and omitting the second produces no compiler error, since the column takes its declared default instead. The resulting defect surfaces only when somebody queries the data and finds the field unpopulated.

How do you insert an object?

Describing the shape once, as a class, removes the field-by-field copy entirely, and the class itself needs nothing unusual about it. No base type, no interface, and no attributes are required for a table whose column names follow a convention:

class Customer
{
    public long CustomerId { get; set; }
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool? Confirmed { get; set; }
    public DateTime? CreationStamp { get; set; }
    public bool SubscriptionActive { get; set; }
    public int TotalLicenses { get; set; }
    public DateOnly? JoinDate { get; set; }
}

The insert then becomes three lines regardless of how many columns the table has:

var customer = new Customer
{
    Email = "john.smith@example.com",
    FirstName = "John",
    LastName = "Smith",
    Confirmed = true,
    CreationStamp = DateTime.Now,
    SubscriptionActive = true,
    TotalLicenses = 5,
    JoinDate = DateOnly.FromDateTime(DateTime.Now)
};

Record<Customer> record = new Record<Customer>(customerTable, BindingType.SnakeCase);

long customerId = manager.AddRecord(record.Bind(customer));

BindingType.SnakeCase is what connects FirstName to first_name, and it is the whole of the configuration. There is no mapping file, no fluent configuration block, and no attributes on the class, which means the class remains an ordinary object that other parts of the application can use without knowing a database exists.

Bind returns the record it was given, so it chains directly into AddRecord, which still hands back the generated key. The pattern reads as one statement because it is one statement.

What happens to the auto-generated key?

Notice that CustomerId never appears in the object initializer above. Its absence causes no difficulty, and that is deliberate rather than incidental.

Binding inspects the table for an auto-increment column and omits it from the insert, whatever value the property happens to hold. The natural mistake is to leave the property at its default of zero, which would otherwise insert a zero into a column the database intends to populate itself. The identity value stays the database's responsibility, and the generated key comes back as the return value of AddRecord rather than being read back out of the object.

This holds for every binding mode rather than being specific to one, so it is not something to remember per query.

How does it know which column is which?

Five modes are available, and the one you choose depends entirely on the naming convention the database already uses.

BindingType	FirstName maps to
SnakeCase	first_name
CamelCase	firstName
PascalCase	FirstName
DirectMap	FirstName, matched exactly as written
Attribute	whatever the property's VelocityField declares

SnakeCase is the common case, since most relational schemas separate words with underscores. Most examples in this article use it. PascalCase and DirectMap differ subtly: the first converts a property name into Pascal case before matching, whereas the second performs no conversion at all and compares the names as written. For a property already in Pascal case the two behave identically, which is why the distinction rarely comes up until a property name contains something the conversion would alter.

The mode is supplied per operation rather than configured globally. One application can therefore read from a legacy table using one convention and write to a new one using another, with no special handling.

What if the names do not follow a convention?

Real schemas contain columns that no convention will reach. Tables inherited from an older system, columns named after a business term that has since changed, and abbreviations that were obvious to somebody in 2009 are all common, and a convention-based mapping cannot help with any of them.

BindingType.Attribute moves the decision onto the class, one property at a time:

class Customer
{
    [VelocityField("cust_id")]
    public long CustomerId { get; set; }

    [VelocityField("email_addr")]
    public string Email { get; set; }

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

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

Record<Customer> record = new Record<Customer>(customerTable, BindingType.Attribute);

long customerId = manager.AddRecord(record.Bind(customer));

The attribute takes a second argument for the case where the class describes a column the database generates:

[VelocityField("cust_id", autoGenerate: true)]
public long CustomerId { get; set; }

Declaring it that way excludes the property from inserts in the same manner as an auto-increment column detected from the schema, which is useful when the class is being bound against something the framework cannot inspect for itself.

How do you exclude a property entirely?

Classes frequently carry properties that have no column behind them. Computed values, presentation helpers, and fields populated from elsewhere in the application all fall into that category, and binding needs to be told to leave them alone rather than searching for a column that does not exist.

The same attribute handles it, using the constructor that takes a single boolean:

class Customer
{
    public long CustomerId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [VelocityField(true)]
    public string DisplayName => $"{FirstName} {LastName}";

    [VelocityField(true)]
    public bool IsRecentSignup { get; set; }
}

Properties marked this way are excluded from every binding operation in both directions, so they are neither written on insert nor populated on read. The alternative designs are both worse. A class permitted to contain nothing extra forces a parallel type maintained purely for persistence. A framework that silently ignores unmatched properties cannot tell a deliberate exclusion from a typo.

What about property names ending in digits?

One conversion detail is worth knowing before it produces a confusing failure. Under SnakeCase, a property called InterestedDb2 becomes interested_db_2, because the converter treats a trailing digit as a separate word and inserts an underscore before it. Where the column is really named interested_db2, the mapping finds nothing.

The behaviour is controlled per property:

[PreserveNumbers(false)]
public bool InterestedDb2 { get; set; }

The same switch, PreserveNumbers, exists for a whole class as a third argument to the Record<T> constructor, which is the better choice when a table uses that convention throughout:

Record<Customer> record = new Record<Customer>(customerTable, BindingType.SnakeCase, preserveNumbers: false);

Which .NET types can be bound?

Binding decides what a value is from the property's declared type rather than from anything in the query, so the set of types it recognizes determines what a class is allowed to contain. That set is wider than the obvious primitives.

Property type	Column type
bool	Boolean
byte, sbyte	Byte
short, ushort	Short
int	Integer
long, uint, ulong	Long
float	Float
double	Double
decimal	Decimal
string	String
byte[]	Blob
Guid	Guid
DateOnly	Date
TimeOnly	Time
DateTime	DateTime
DateTimeOffset	Timestamp
TimeSpan	Interval
Geography, Geometry	Geography, Geometry

Several of those are worth drawing attention to, because they remove conversions applications frequently write by hand.

Dates and times map to the type that actually describes them. A column holding a date with no time becomes DateOnly rather than a DateTime whose time component everybody agrees to ignore, and a time of day becomes TimeOnly. Intervals map to TimeSpan, so a duration stored in the database arrives as a duration rather than as a number of seconds requiring interpretation at every use.

Binary columns map to byte[], which means a stored document, image or serialized payload round-trips without a base64 step in either direction. Spatial columns map to Geography and Geometry, so a class describing a table with a location column holds that location as a spatial value ready to pass into a spatial query rather than as text needing parsing.

The unsigned integer types are accepted and widened, so uint and ulong both map to Long and ushort maps to Short. That is a convenience for classes generated from or shared with code that uses them, rather than an invitation to prefer them.

What happens to null values?

Nullability is expressed in the property type, which makes the choice between bool and bool? a decision about correctness rather than about style.

Every value type in the table above has a nullable counterpart that maps to the same column type, so int and int? both bind to an Integer column. What differs is what happens when the column contains null. Nullable properties receive null and represent the database's state accurately, whereas non-nullable ones cannot, so they receive the type's default, and zero, false or DateTime.MinValue then stand in for a value that was actually absent.

That distinction matters most where the absence itself means something. Consider a confirmed column that is null because nobody has answered yet, which is a different state from one that is false because somebody declined, and note that a bool property collapses the two into one. Declaring the property bool? keeps them apart:

public bool? Confirmed { get; set; }
public DateTime? CreationStamp { get; set; }
public DateOnly? JoinDate { get; set; }

public bool SubscriptionActive { get; set; }
public int TotalLicenses { get; set; }

The rule worth following is to match the property's nullability to the column's. Columns declared notnull take plain value types, and everything else takes the nullable form. Doing so makes the class a faithful description of the table, which means an unexpected null surfaces as a null rather than as a plausible-looking zero.

Strings are nullable already, so a null in a text column arrives as a null reference rather than as an empty string. Code treating the two as equivalent should say so explicitly rather than relying on binding to normalize them, because it does not.

How do you read rows back into objects?

Reading saves the most effort. The alternative is a loop extracting every field by name and type, repeated for every query in the application.

Query query = new Query()
    .SelectAll(customerTable)
    .Where(new Criterion<bool>(customerTable["subscription_active"], true));

using var manager = new Manager(connection);

IList<Customer> customers = manager.Retrieve<Customer>(query, BindingType.SnakeCase);

That is the entire round trip. Retrieve<T> executes the query, binds each row, disposes the underlying result set internally, and returns a plain list holding no connection to the database, which is why there is no using on the result.

An asynchronous form, RetrieveAsync<T>, takes the same arguments and a cancellation token:

IList<Customer> customers = await manager.RetrieveAsync<Customer>(
    query, BindingType.SnakeCase, cancellationToken);

Where the query returns a single value rather than rows, GetScalar<T> skips binding altogether and returns the first column of the first row:

Query countQuery = new Query()
    .Select(new Expression("total", new Count(customerTable["customer_id"])))
    .From(customerTable);

long total = manager.GetScalar<long>(countQuery);

Why did my object come back empty?

One default deserves attention ahead of the others. The failure it produces is silent rather than loud, which costs considerably more time to diagnose than an exception would.

BindingType defaults to DirectMap on the read path. Against a snake_case table, that means every property fails to find a matching column, and because the read path populates a property only when it finds a column of that name in the result, the outcome is a list of objects with every property left at its default value. No exception is raised, since a result set containing no column called FirstName is a perfectly legal result set.

IList<Customer> customers = manager.Retrieve<Customer>(query);

That line compiles, runs, and returns the right number of objects, all of them empty. The fix is to state the mode:

IList<Customer> customers = manager.Retrieve<Customer>(query, BindingType.SnakeCase);

Whenever a bound result comes back with the correct row count and no data, the binding mode is the first thing to check, ahead of the query itself.

What if the result set is too large to hold?

Retrieve materializes every row before returning. That suits the result sizes most queries produce, and does not suit exports, migrations or batch processing.

Search returns a forward-only reader instead, and binding works against it one row at a time:

using var manager = new Manager(connection);
using ResultReader reader = manager.Search(query);

foreach (Customer customer in reader.BindResults<Customer>(BindingType.SnakeCase))
{
    Console.WriteLine(customer.Email);
}

BindResults on a reader returns IEnumerable<T> rather than IList<T>, constructing each object as its row arrives rather than building the whole collection first. Memory consumption stays flat regardless of how many rows the query matches.

The asynchronous counterpart behaves the same way and yields an async sequence:

await using ResultReader reader = await manager.SearchAsync(query, cancellationToken);

await foreach (Customer customer in reader.BindResultsAsync<Customer>(BindingType.SnakeCase, cancellationToken))
{
    await ProcessAsync(customer, cancellationToken);
}

Both forms take the binding mode as an explicit argument rather than defaulting it, which removes the silent-empty-object problem described above for the streaming path.

How do you read a single column?

Queries returning a single column per row occur frequently enough that defining a class for each of them would add types without adding information, so binding provides a direct path for that case.

Query emailQuery = new Query()
    .Select(customerTable["email"])
    .From(customerTable)
    .Where(new Criterion<bool>(customerTable["subscription_active"], true));

using ResultSet results = manager.Retrieve(emailQuery);

IList<string> emails = results.BindColumn<string>();

BindColumn<T> takes the first column when called with no arguments, and accepts either a column name or a zero-based index where the query selects more than one:

IList<long> ids = results.BindColumn<long>("customer_id");
IList<string> names = results.BindColumn<string>(1);

The reader provides the same three overloads, returning IEnumerable<T> for streaming and IAsyncEnumerable<T> through BindColumnAsync<T>. The common application is collecting a set of identifiers to feed into a subsequent query, and obtaining them this way rather than selecting whole rows and projecting them afterwards transfers considerably less data.

How do you bind a computed column?

Queries frequently return values that exist in no table, such as aggregates, expressions and results of database functions, and those bind exactly as stored columns do because binding matches on the name in the result set rather than on the schema.

An aliased expression supplies that name, so the alias and the property name are what have to agree:

class RegionSummary
{
    public string RegionName { get; set; }
    public int CustomerCount { get; set; }
    public decimal TotalRevenue { get; set; }
}

Query query = new Query()
    .Select([
        regionTable["region_name"],
        new Expression("customer_count", new Count(customerTable["customer_id"])),
        new Expression("total_revenue", new Aggregate(AggregateType.Sum, orderTable["total"]))
    ])
    .From(join)
    .GroupBy([ regionTable["region_id"], regionTable["region_name"] ]);

IList<RegionSummary> summary = manager.Retrieve<RegionSummary>(query, BindingType.SnakeCase);

The aliases customer_count and total_revenue are converted by the same snake case rule that handles ordinary columns, so CustomerCount and TotalRevenue are populated without anything further being said. Choosing aliases that follow the schema's naming convention therefore keeps one rule in play across the whole class rather than requiring attributes for the computed members and conventions for the rest.

This is the reason reporting and summary types need no separate treatment. They are ordinary classes describing the shape of a result, and the fact that no table has that shape makes no difference to how they are populated.

How do you bind the result of a join?

Joins produce rows wider than any single table, and the natural class for such a row is one that extends the table's class with the additional columns.

Inheritance handles this without further configuration:

class PartnerRequest
{
    public long PartnerRequestId { get; set; }
    public long UserId { get; set; }
    public DateTime RequestDatetime { get; set; }
    public bool? Applied { get; set; }
}

class PartnerRequestDetail : PartnerRequest
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string CompanyName { get; set; }
}

var join = new Join(partnerRequestTable, userTable, "user_id");

Query query = new Query()
    .Select([
        partnerRequestTable["partner_request_id"],
        partnerRequestTable["user_id"],
        partnerRequestTable["request_datetime"],
        partnerRequestTable["applied"],
        userTable["first_name"],
        userTable["last_name"],
        userTable["email"],
        userTable["company_name"]
    ])
    .From(join);

IList<PartnerRequestDetail> details = manager.Retrieve<PartnerRequestDetail>(query, BindingType.SnakeCase);

Binding walks the inherited properties alongside the declared ones. The derived type therefore collects columns from both tables, with no attributes and no separate mapping. The base class remains usable on its own for queries that do not need the join, which keeps one type per table rather than one type per query shape.

Can one class serve more than one query?

Applications rarely select every column every time. List views need three fields, detail views need twenty, and export routines need something different again, which raises the question of whether each of those shapes requires its own type.

It does not. The read path populates a property only when the result set contains a matching column, leaving the rest at their defaults. One class describing the whole table therefore serves a narrower query perfectly well:

Query listQuery = new Query()
    .Select([
        customerTable["customer_id"],
        customerTable["first_name"],
        customerTable["last_name"]
    ])
    .From(customerTable);

IList<Customer> customers = manager.Retrieve<Customer>(listQuery, BindingType.SnakeCase);

Those objects have their name and identifier populated and everything else at its default, which is exactly what the query asked for.

This is the same tolerance that produces the silent empty objects described earlier, so it repays being deliberate about. Populating a partial object is correct when the query deliberately selected a subset, and misleading when a caller further down assumes the whole record is present. Where that distinction matters, a purpose-built type for the narrow query states the contract in the type system rather than in a comment:

class CustomerListItem
{
    public long CustomerId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Both approaches work. The full class costs nothing to reuse and is the right answer for internal code where the query is close to its consumer. The narrow class is worth defining when the result travels far from the query that produced it, particularly across an API boundary, where a property that is empty because it was never selected is indistinguishable from one that is empty because the database says so.

What about updates?

Updates take a different form from inserts, because an update names a subset of columns and a condition rather than a whole row.

var update = new Update(customerTable,
    new Criterion<long>(customerTable["customer_id"], customerId));

update.SetFieldVarChar("email", "j.smith@example.com");
update.SetFieldBoolean("confirmed", true);
update.SetFieldInteger("total_licenses", 10);

manager.UpdateRecords(update);

The typed setters are the same family used on a record. Each value's type is stated at the call site, so a mismatch is a compile error rather than a runtime one. Writing every column an object holds is rarely what an update should do, since it overwrites values other processes may have changed in the interim, so naming the columns that actually changed is both safer and clearer about intent.

Putting it together

The full cycle, from an object to a row and back, is short enough to read in one piece.

Defining the class once, with the properties that have no column behind them excluded:

class Customer
{
    public long CustomerId { get; set; }
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool SubscriptionActive { get; set; }
    public int TotalLicenses { get; set; }
    public DateOnly? JoinDate { get; set; }

    [VelocityField(true)]
    public string DisplayName => $"{FirstName} {LastName}";
}

Inserting one:

using var manager = new Manager(connection);

Table customerTable = schema["customer"];

var customer = new Customer
{
    Email = "john.smith@example.com",
    FirstName = "John",
    LastName = "Smith",
    SubscriptionActive = true,
    TotalLicenses = 5,
    JoinDate = DateOnly.FromDateTime(DateTime.Now)
};

Record<Customer> record = new Record<Customer>(customerTable, BindingType.SnakeCase);

long customerId = manager.AddRecord(record.Bind(customer));

Reading them back:

Query query = new Query()
    .SelectAll(customerTable)
    .Where(new Criterion<bool>(customerTable["subscription_active"], true))
    .OrderBy(customerTable["join_date"], OrderClauseType.Descending);

IList<Customer> active = manager.Retrieve<Customer>(query, BindingType.SnakeCase);

Streaming them when there are too many to hold:

using ResultReader reader = manager.Search(query);

foreach (Customer c in reader.BindResults<Customer>(BindingType.SnakeCase))
{
    Process(c);
}

And updating one:

var update = new Update(customerTable,
    new Criterion<long>(customerTable["customer_id"], customerId));

update.SetFieldInteger("total_licenses", 10);

manager.UpdateRecords(update);

Every call above is identical on every supported engine, and the only line aware of which one is in use is the connection object. The class has no persistence framework in its type hierarchy, no base class to inherit, and no attributes at all unless the schema forced them, which means the same object can travel out to an API boundary or into a unit test without dragging a database along with it.

Where to start

The free Community Edition is published on nuget.org and covers MySQL and PostgreSQL, which is enough to work through everything in this article:

dotnet add package YndigoBlue.Velocity.Community

A reasonable first exercise is to take whichever table in your application has the most columns, describe it as a class, and replace a single insert and a single query with bound equivalents. The question worth asking afterwards is not how much shorter the code became, but how many places still name the same column more than once.