Data Binding

This guide covers binding query results to C# objects using the fluent query syntax.

---

Overview

Data binding automatically maps query results to strongly-typed C# objects, eliminating manual field extraction. Velocity provides:

• BindColumn<T>() - Extract a single column as a typed collection
• BindResults<T>() - Map entire rows to C# objects
• BindingType - Naming convention strategies
• VelocityField - Custom field mapping

---

Choosing a Binding Approach

Velocity offers three ways to retrieve and bind query results, each with different tradeoffs:

Approach	Disposal	Returns	Re-bindable	Streaming
manager.Retrieve<T>(query)	None required	IList<T>	No	No
manager.Retrieve(query) + BindResults<T>()	using required	IList<T>	Yes	No
manager.Search(query) + BindResults<T>()	using required	IEnumerable<T>	No	Yes (lazy)

Use Retrieve<T>() for the simplest cases where you need one type of binding and do not want to manage disposal. Use Retrieve() directly when you need to bind the same in-memory result set to multiple types or with different BindingType values. Use Search() for large datasets where streaming row-by-row matters and memory usage must be kept low.

---

Binding Types

BindingType enumeration provides naming convention strategies. Each binding type expects a specific C# property naming convention and derives the database column name from it using a one-way transformation.

BindingType	C# Property Format	DB Column Format	Example
Attribute	Any	Specified in [VelocityField]	[VelocityField("col")]
CamelCase	snake_case	camelCase	first_name → firstName
DirectMap	Any	Same as C# property	FirstName → FirstName
PascalCase	snake_case	PascalCase	first_name → FirstName
SnakeCase	PascalCase	snake_case	FirstName → first_name

Conversion Matrix

C# Property	BindingType	DB Column
FirstName (PascalCase)	SnakeCase	first_name
first_name (snake_case)	PascalCase	FirstName
first_name (snake_case)	CamelCase	firstName
FirstName (any)	DirectMap	FirstName (unchanged)
(any)	Attribute	Value of [VelocityField(...)]

The expected C# naming convention differs by binding type: SnakeCase expects PascalCase properties (normal .NET convention), while PascalCase and CamelCase expect snake_case properties so the converter can derive the correct output.

---

BindColumn - Single Column Binding

Extract a single column as a typed collection:

Basic BindColumn

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

Table users = schema["users"];

Query query = new Query()
    .Select(users["username"])
    .From(users);

// Disconnected - returns IList<string>
using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    IList<string> usernames = results.BindColumn<string>("username");

    Console.WriteLine($"Total Users: {usernames.Count}");

    foreach (string username in usernames)
    {
        Console.WriteLine($"  - {username}");
    }
}

BindColumn by Index

// Access column by index instead of name
IList<string> usernames = results.BindColumn<string>(0);  // First column

BindColumn with Different Types

Table products = schema["products"];

Query query = new Query()
    .Select([
        products["product_id"],
        products["price"],
        products["stock_quantity"]
    ])
    .From(products);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    // Extract different typed columns
    IList<int> productIds = results.BindColumn<int>("product_id");
    IList<decimal> prices = results.BindColumn<decimal>("price");
    IList<int> quantities = results.BindColumn<int>("stock_quantity");

    for (int i = 0; i < productIds.Count; i++)
    {
        Console.WriteLine($"Product {productIds[i]}: ${prices[i]:F2} ({quantities[i]} in stock)");
    }
}

BindColumn with Search (Lazy Evaluation)

Query query = new Query()
    .Select(users["email"])
    .From(users);

// Connected - returns IEnumerable<string> (lazy evaluation)
using (Manager manager = new Manager(connection))
using (ResultReader results = manager.Search(query))
{
    IEnumerable<string> emails = results.BindColumn<string>("email");

    // Each email processed as it arrives from database
    foreach (string email in emails)
    {
        SendEmail(email);  // Process one at a time
    }
}

---

BindResults - Object Binding

Map entire rows to C# objects:

SnakeCase Binding

Database uses snake_case, C# uses PascalCase:

using YndigoBlue.Velocity.Enums;

// C# model with PascalCase properties
public class User
{
    public int UserId { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
    public bool IsActive { get; set; }
}

// Database columns: user_id, username, email, created_at, is_active
Table users = schema["users"];

Query query = new Query()
    .SelectAll(users);

// Disconnected - returns IList<User>
using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    IList<User> userList = results.BindResults<User>(BindingType.SnakeCase);

    Console.WriteLine($"Total Users: {userList.Count}\n");

    foreach (User user in userList)
    {
        Console.WriteLine($"User #{user.UserId}");
        Console.WriteLine($"  Username: {user.Username}");
        Console.WriteLine($"  Email: {user.Email}");
        Console.WriteLine($"  Created: {user.CreatedAt:d}");
        Console.WriteLine($"  Active: {user.IsActive}");
        Console.WriteLine();
    }
}

Mapping:

• UserId → user_id
• Username → username
• Email → email
• CreatedAt → created_at
• IsActive → is_active

Re-binding the Same Result Set to Multiple Types

Because Retrieve() loads all results into memory, you can call BindResults<T>() multiple times on the same ResultSet with different target types or different BindingType values:

// Retrieve once, bind to multiple types
using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    IList<UserDto> dtos = results.BindResults<UserDto>(BindingType.SnakeCase);
    IList<UserSummary> summaries = results.BindResults<UserSummary>(BindingType.SnakeCase);
    // Both lists are populated from the same in-memory result set
}

CamelCase Binding

Database uses camelCase, C# uses snake_case:

// C# model with snake_case properties
public class Product
{
    public int product_id { get; set; }
    public string product_name { get; set; }
    public decimal unit_price { get; set; }
    public int stock_quantity { get; set; }
}

// Database columns: productId, productName, unitPrice, stockQuantity
Table products = schema["products"];

Query query = new Query()
    .SelectAll(products);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    IList<Product> productList = results.BindResults<Product>(BindingType.CamelCase);

    foreach (Product product in productList)
    {
        Console.WriteLine($"{product.product_name}: ${product.unit_price:F2}");
    }
}

Mapping:

• product_id → productId
• product_name → productName
• unit_price → unitPrice
• stock_quantity → stockQuantity

PascalCase Binding

Database uses PascalCase, C# uses snake_case:

// C# model with snake_case properties
public class Employee
{
    public int employee_id { get; set; }
    public string first_name { get; set; }
    public string last_name { get; set; }
    public string department_name { get; set; }
    public decimal base_salary { get; set; }
}

// Database columns: EmployeeId, FirstName, LastName, DepartmentName, BaseSalary
Table employees = schema["Employees"];

Query query = new Query()
    .SelectAll(employees);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    IList<Employee> employeeList = results.BindResults<Employee>(BindingType.PascalCase);

    foreach (Employee employee in employeeList)
    {
        Console.WriteLine($"{employee.first_name} {employee.last_name} — {employee.department_name}");
    }
}

Mapping:

• employee_id → EmployeeId
• first_name → FirstName
• last_name → LastName
• department_name → DepartmentName
• base_salary → BaseSalary

DirectMap Binding

Property names exactly match column names:

// Property names match database columns exactly
public class Order
{
    public int OrderID { get; set; }
    public int CustomerID { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
    public string Status { get; set; }
}

// Database columns: OrderID, CustomerID, OrderDate, Total, Status
Table orders = schema["Orders"];

Query query = new Query()
    .SelectAll(orders);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    IList<Order> orderList = results.BindResults<Order>(BindingType.DirectMap);

    foreach (Order order in orderList)
    {
        Console.WriteLine($"Order #{order.OrderID}");
        Console.WriteLine($"  Customer: {order.CustomerID}");
        Console.WriteLine($"  Date: {order.OrderDate:d}");
        Console.WriteLine($"  Total: ${order.Total:F2}");
        Console.WriteLine($"  Status: {order.Status}");
        Console.WriteLine();
    }
}

Attribute Binding

Use attributes for custom column mapping:

using YndigoBlue.Velocity.Attributes;

public class Customer
{
    [VelocityField("customer_id")]
    public int Id { get; set; }

    [VelocityField("company_name")]
    public string CompanyName { get; set; }

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

    [VelocityField("phone_number")]
    public string Phone { get; set; }

    [VelocityField("created_date")]
    public DateTime CreatedDate { get; set; }

    // Property not mapped to a column (computed/transient)
    public string DisplayName => $"{CompanyName} ({Email})";
}

// Database columns: customer_id, company_name, contact_email, phone_number, created_date
Table customers = schema["customers"];

Query query = new Query()
    .SelectAll(customers);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    IList<Customer> customerList = results.BindResults<Customer>(BindingType.Attribute);

    foreach (Customer customer in customerList)
    {
        Console.WriteLine(customer.DisplayName);
        Console.WriteLine($"  Phone: {customer.Phone}");
        Console.WriteLine($"  Created: {customer.CreatedDate:d}");
        Console.WriteLine();
    }
}

---

Binding with Aliases

Use aliases to control result column names:

public class OrderSummary
{
    public int OrderId { get; set; }
    public string CustomerName { get; set; }
    public DateTime Date { get; set; }
    public decimal Amount { get; set; }
}

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

var join = new Join(orders, customers, "customer_id");

Query query = new Query()
    .Select([
        (orders["order_id"], "OrderId"),
        (customers["customer_name"], "CustomerName"),
        (orders["order_date"], "Date"),
        (orders["total"], "Amount")
    ])
    .From(join);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    // Use DirectMap since aliases match property names
    IList<OrderSummary> summaries = results.BindResults<OrderSummary>(BindingType.DirectMap);

    foreach (OrderSummary summary in summaries)
    {
        Console.WriteLine($"Order #{summary.OrderId} - {summary.CustomerName}");
        Console.WriteLine($"  Date: {summary.Date:d}");
        Console.WriteLine($"  Amount: ${summary.Amount:F2}");
    }
}

---

Binding with Aggregates

Bind aggregated results:

public class CustomerStats
{
    public int CustomerId { get; set; }
    public int OrderCount { get; set; }
    public decimal TotalSpent { get; set; }
    public decimal AvgOrderValue { get; set; }
    public DateTime LastOrderDate { get; set; }
}

Table orders = schema["orders"];

Expression orderCount = new Expression("OrderCount",
    new Aggregate(AggregateType.Count, new ArithmeticOperator(ArithmeticType.All))
);

Expression totalSpent = new Expression("TotalSpent",
    new Aggregate(AggregateType.Sum, orders["total"])
);

Expression avgOrderValue = new Expression("AvgOrderValue",
    new Aggregate(AggregateType.Average, orders["total"])
);

Expression lastOrderDate = new Expression("LastOrderDate",
    new Aggregate(AggregateType.Max, orders["order_date"])
);

Query query = new Query()
    .Select(orders["customer_id"], "CustomerId")
    .Select([orderCount, totalSpent, avgOrderValue, lastOrderDate])
    .From(orders)
    .GroupBy(orders["customer_id"]);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    IList<CustomerStats> stats = results.BindResults<CustomerStats>(BindingType.DirectMap);

    foreach (CustomerStats stat in stats)
    {
        Console.WriteLine($"Customer #{stat.CustomerId}");
        Console.WriteLine($"  Orders: {stat.OrderCount}");
        Console.WriteLine($"  Total Spent: ${stat.TotalSpent:N2}");
        Console.WriteLine($"  Avg Order: ${stat.AvgOrderValue:F2}");
        Console.WriteLine($"  Last Order: {stat.LastOrderDate:d}");
        Console.WriteLine();
    }
}

---

Binding with Search (Streaming)

Use binding with Search for lazy evaluation:

Warning

The IEnumerable<T> returned by BindResults<T>() on a ResultReader is lazy — rows are read one at a time as you iterate. You must complete iteration inside the using (ResultReader ...) block. Storing the IEnumerable<T> and iterating after the block closes will throw an ObjectDisposedException.

public class LogEntry
{
    public long LogId { get; set; }
    public DateTime Timestamp { get; set; }
    public string Level { get; set; }
    public string Message { get; set; }
}

Table logs = schema["logs"];

Filter recentErrors = new Filter([
    new Criterion<DateTime>(
        logs["timestamp"],
        ConditionalType.GreaterThan,
        DateTime.Now.AddDays(-1)
    ),
    new BooleanItem(BooleanType.And),
    new Criterion<string>(
        logs["level"],
        ConditionalType.Equals,
        "ERROR"
    )
]);

Query query = new Query()
    .SelectAll(logs)
    .Where(recentErrors)
    .OrderBy(logs["timestamp"], OrderClauseType.Descending);

// Connected - returns IEnumerable<LogEntry> (lazy evaluation)
using (Manager manager = new Manager(connection))
using (ResultReader reader = manager.Search(query))
{
    IEnumerable<LogEntry> errors = reader.BindResults<LogEntry>(BindingType.SnakeCase);

    // Process each error as it arrives (low memory footprint)
    // Must complete iteration here, inside the using block
    foreach (LogEntry error in errors)
    {
        Console.WriteLine($"[{error.Timestamp}] {error.Level}: {error.Message}");

        // Could send alert, write to file, etc.
        // Only one row in memory at a time
    }
}

---

Complete Examples

Example 1: Product Inventory Report

public class ProductInventory
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public string Category { get; set; }
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }
    public decimal InventoryValue { get; set; }
}

Table products = schema["products"];

Expression inventoryValue = new Expression("InventoryValue", [
    products["price"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    products["stock_quantity"]
]);

Query query = new Query()
    .Select([
        (products["product_id"], "ProductId"),
        (products["product_name"], "ProductName"),
        (products["category"], "Category"),
        (products["price"], "Price"),
        (products["stock_quantity"], "StockQuantity"),
        inventoryValue
    ])
    .From(products)
    .OrderBy(inventoryValue, OrderClauseType.Descending);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    IList<ProductInventory> inventory = results.BindResults<ProductInventory>(BindingType.DirectMap);

    decimal totalValue = inventory.Sum(p => p.InventoryValue);

    Console.WriteLine("Product Inventory Report");
    Console.WriteLine("========================\n");

    foreach (ProductInventory product in inventory)
    {
        Console.WriteLine($"{product.ProductName} ({product.Category})");
        Console.WriteLine($"  Price: ${product.Price:F2}");
        Console.WriteLine($"  Stock: {product.StockQuantity} units");
        Console.WriteLine($"  Value: ${product.InventoryValue:N2}");
        Console.WriteLine();
    }

    Console.WriteLine("========================");
    Console.WriteLine($"Total Inventory Value: ${totalValue:N2}");
}

Example 2: Customer Analysis with Joins

public class CustomerAnalysis
{
    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
    public string Email { get; set; }
    public int TotalOrders { get; set; }
    public decimal LifetimeValue { get; set; }
    public DateTime? FirstOrder { get; set; }
    public DateTime? LastOrder { get; set; }
}

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

var join = new Join(customers, orders, "customer_id", JoinType.LeftOuter);

Expression orderCount = new Expression("TotalOrders",
    new Aggregate(AggregateType.Count, orders["order_id"])
);

Expression lifetimeValue = new Expression("LifetimeValue",
    new Aggregate(AggregateType.Sum, orders["total"])
);

Expression firstOrder = new Expression("FirstOrder",
    new Aggregate(AggregateType.Min, orders["order_date"])
);

Expression lastOrder = new Expression("LastOrder",
    new Aggregate(AggregateType.Max, orders["order_date"])
);

Query query = new Query()
    .Select([
        (customers["customer_id"], "CustomerId"),
        (customers["customer_name"], "CustomerName"),
        (customers["email"], "Email")
    ])
    .Select([orderCount, lifetimeValue, firstOrder, lastOrder])
    .From(join)
    .GroupBy([
        customers["customer_id"],
        customers["customer_name"],
        customers["email"]
    ])
    .OrderBy(lifetimeValue, OrderClauseType.Descending);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    IList<CustomerAnalysis> analysis = results.BindResults<CustomerAnalysis>(BindingType.DirectMap);

    Console.WriteLine("Customer Analysis Report");
    Console.WriteLine("========================\n");

    foreach (CustomerAnalysis customer in analysis)
    {
        Console.WriteLine($"{customer.CustomerName} ({customer.Email})");
        Console.WriteLine($"  Customer ID: {customer.CustomerId}");
        Console.WriteLine($"  Total Orders: {customer.TotalOrders}");

        if (customer.TotalOrders > 0)
        {
            Console.WriteLine($"  Lifetime Value: ${customer.LifetimeValue:N2}");
            Console.WriteLine($"  First Order: {customer.FirstOrder:d}");
            Console.WriteLine($"  Last Order: {customer.LastOrder:d}");
        }
        else
        {
            Console.WriteLine($"  No orders yet");
        }

        Console.WriteLine();
    }
}

Example 3: Streaming Large Dataset

public class Transaction
{
    public long TransactionId { get; set; }
    public DateTime TransactionDate { get; set; }
    public decimal Amount { get; set; }
    public string Status { get; set; }
    public string AccountNumber { get; set; }
}

Table transactions = schema["transactions"];

Filter recentFilter = new Filter(
    new Criterion<DateTime>(
        transactions["transaction_date"],
        ConditionalType.GreaterThan,
        DateTime.Now.AddDays(-30)
    )
);

Query query = new Query()
    .SelectAll(transactions)
    .Where(recentFilter)
    .OrderBy(transactions["transaction_date"], OrderClauseType.Descending);

decimal totalAmount = 0;
int count = 0;

// Stream millions of records without loading all into memory
using (Manager manager = new Manager(connection))
using (ResultReader reader = manager.Search(query))
{
    IEnumerable<Transaction> transactionStream = reader.BindResults<Transaction>(BindingType.SnakeCase);

    foreach (Transaction txn in transactionStream)
    {
        // Process each transaction
        totalAmount += txn.Amount;
        count++;

        // Log high-value transactions
        if (txn.Amount > 10000)
        {
            Console.WriteLine($"High-value transaction: ${txn.Amount:N2} on {txn.TransactionDate:d}");
        }

        // Could write to file, send to API, etc.
    }
}

Console.WriteLine($"\nProcessed {count:N0} transactions");
Console.WriteLine($"Total Amount: ${totalAmount:N2}");
Console.WriteLine($"Average: ${(totalAmount / count):F2}");

---

Best Practices

1. Choose the right binding approach

   • Use Retrieve<T>() for simple one-shot cases with no disposal management
   • Use Retrieve() when you need to bind the same result set to multiple types
   • Use Search() for large datasets where streaming matters

2. Choose the right binding type

   • Use SnakeCase for most modern databases
   • Use DirectMap when column names match exactly
   • Use Attribute for complex or legacy mappings

3. Use Search for large datasets

   • Streaming prevents memory issues
   • Process one row at a time
   • Essential for millions of records

4. Alias columns for DirectMap

   • Makes queries self-documenting
   • Easier to match C# property names
   • Reduces mapping errors

5. Handle nullable types appropriately

   • Use DateTime? for nullable date columns
   • Use decimal? for nullable numeric columns
   • Check IsNull() before accessing

6. Keep models simple

   • One model per query result structure
   • Avoid complex inheritance
   • Use composition when needed

7. Validate mapped properties

   • Ensure all required columns are selected
   • Test with various data scenarios
   • Check for NULL handling

8. Consider performance

   • BindColumn is faster than manual loops
   • BindResults eliminates repetitive code
   • Streaming reduces memory usage

9. Document custom mappings

   • Comment [ColumnName] attributes
   • Explain naming conventions
   • Note any special handling

---

Next Steps

• Basic Queries - Query data to bind
• Joins - Bind joined query results
• Grouping and Aggregation - Bind aggregate results