Table of Contents

Basic Queries

This guide covers the fundamentals of querying data in Velocity using the fluent query syntax.

Overview

Velocity provides a fluent, type-safe API for building SQL SELECT queries through the Query class. All queries start with new Query() and use method chaining to build complex SQL statements.

Key Components:

  • Query - The main query builder class
  • Table - Represents database tables
  • Column - Represents table columns
  • Schema - Container for tables

Creating Your First Query

Basic SELECT

Select specific columns from a table:

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

// Get table reference from schema
Table users = schema["users"];

// Build query using fluent syntax
Query query = new Query()
    .Select([
        users["user_id"],
        users["username"],
        users["email"]
    ])
    .From(users);

// Execute query
using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    foreach (Result row in results)
    {
        int id = row.GetFieldInteger("user_id");
        string username = row.GetFieldString("username");
        string email = row.GetFieldString("email");

        Console.WriteLine($"{id}: {username} ({email})");
    }
}

Generated SQL:

SELECT user_id, username, email
FROM users

FROM Clause

Single Table

Specify the table to query from:

Table products = schema["products"];

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

Generated SQL:

SELECT product_name, price
FROM products

Multiple Tables (Implicit Cross Join)

Query from multiple tables without an explicit JOIN creates a cross product:

Table categories = schema["categories"];
Table products = schema["products"];

Query query = new Query()
    .Select([
        categories["category_name"],
        products["product_name"]
    ])
    .From([categories, products]);

Generated SQL:

SELECT categories.category_name, products.product_name
FROM categories, products

Note: For explicit joins with ON conditions, see the Joins documentation.

SELECT * (All Columns)

All Columns from Single Table

Use SelectAll() to retrieve all columns:

Table customers = schema["customers"];

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

Generated SQL:

SELECT *
FROM customers

All Columns from Multiple Tables

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

Query query = new Query()
    .SelectAll([orders, customers]);

Generated SQL:

SELECT *
FROM orders, customers

Column Aliases

Rename columns in the result set using aliases:

Basic Alias

Table users = schema["users"];

Query query = new Query()
    .Select([
        (users["username"], "user_name"),
        (users["email"], "email_address")
    ])
    .From(users);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    foreach (Result row in results)
    {
        // Access by alias
        string name = row.GetFieldString("user_name");
        string email = row.GetFieldString("email_address");
    }
}

Generated SQL:

SELECT username AS user_name, email AS email_address
FROM users

Multiple Aliases

Use tuple syntax for multiple aliased columns:

Query query = new Query()
    .Select([
        (users["user_id"], "id"),
        (users["username"], "name"),
        (users["email"], "contact_email"),
        (users["created_at"], "registration_date")
    ])
    .From(users);

Generated SQL:

SELECT user_id AS id,
       username AS name,
       email AS contact_email,
       created_at AS registration_date
FROM users

DISTINCT

Remove duplicate rows from results:

DISTINCT on All Selected Columns

Table orders = schema["orders"];

Query query = new Query()
    .Select(orders["customer_id"])
    .From(orders)
    .Distinct();

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    Console.WriteLine($"Unique customers: {results.Count}");
}

Generated SQL:

SELECT DISTINCT customer_id
FROM orders

DISTINCT with Multiple Columns

Table orders = schema["orders"];

Query query = new Query()
    .Select([
        orders["customer_id"],
        orders["status"]
    ])
    .From(orders)
    .Distinct();

Generated SQL:

SELECT DISTINCT customer_id, status
FROM orders

Note: DISTINCT applies to the entire row, not individual columns. The above query returns unique combinations of customer_id and status.

Limit

Cap the number of rows returned using Limit():

Table products = schema["products"];

Query query = new Query()
    .SelectAll(products)
    .OrderBy(products["price"])
    .Limit(10);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    // results.Count <= 10
    foreach (Result row in results)
    {
        Console.WriteLine(row.GetFieldString("product_name"));
    }
}

Velocity translates Limit() to the appropriate native SQL syntax for each database:

Database Generated SQL
SQL Server SELECT TOP 10 ...
Teradata SELECT TOP 10 ...
MySQL ... LIMIT 10
PostgreSQL ... LIMIT 10
SQLite ... LIMIT 10
DB2 ... FETCH FIRST 10 ROWS ONLY
Oracle ... FETCH FIRST 10 ROWS ONLY

Note: Without an OrderBy() clause, the rows returned are not guaranteed to be in a consistent order. Combine Limit() with OrderBy() for deterministic results.

Retrieving Data

Use manager.Retrieve(query) to load all results into memory as a ResultSet, or manager.Search(query) to stream results row-by-row as a ResultReader. See Retrieve vs Search for a full comparison, decision matrix, and examples.

Reading Field Values

The Result class provides type-safe methods for reading values:

Integer Types

foreach (Result row in results)
{
    byte flags = row.GetFieldByte("flags");
    short smallNumber = row.GetFieldShort("small_number");
    int id = row.GetFieldInteger("user_id");
    long bigNumber = row.GetFieldLong("big_number");
}

Floating Point and Decimal Types

foreach (Result row in results)
{
    float ratio = row.GetFieldFloat("ratio");
    double percentage = row.GetFieldDouble("percentage");
    decimal price = row.GetFieldDecimal("price");
}

Text Types

foreach (Result row in results)
{
    string username = row.GetFieldString("username");    // General-purpose string accessor
    string code = row.GetFieldChar("country_code");     // Fixed-length CHAR column
    string email = row.GetFieldVarChar("email");        // VARCHAR column
    string notes = row.GetFieldClob("notes");           // Large text / CLOB column
}

Date and Time Types

foreach (Result row in results)
{
    DateOnly birthDate = row.GetFieldDate("birth_date");            // Date only
    TimeOnly startTime = row.GetFieldTime("start_time");            // Time only
    DateTime createdAt = row.GetFieldDateTime("created_at");        // Date and time
    DateTimeOffset lastLogin = row.GetFieldTimestamp("last_login"); // Date, time, and timezone
    TimeSpan duration = row.GetFieldInterval("duration");           // Time interval
}

Boolean

foreach (Result row in results)
{
    bool isActive = row.GetFieldBoolean("is_active");
}

Binary

foreach (Result row in results)
{
    byte[] data = row.GetFieldBlob("file_data");
}

GUID

foreach (Result row in results)
{
    Guid trackingId = row.GetFieldGuid("tracking_id");
}

Checking for NULL

foreach (Result row in results)
{
    if (row.IsNull("optional_field"))
    {
        Console.WriteLine("Field is NULL");
    }
    else
    {
        string value = row.GetFieldString("optional_field");
    }
}

Complete Examples

Example 1: User List

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

// Get table reference
Table users = schema["users"];

// Build query
Query query = new Query()
    .Select([
        users["user_id"],
        users["username"],
        users["email"],
        users["created_at"]
    ])
    .From(users);

// Execute and display results
using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    Console.WriteLine($"Total users: {results.Count}\n");

    foreach (Result row in results)
    {
        int id = row.GetFieldInteger("user_id");
        string username = row.GetFieldString("username");
        string email = row.GetFieldString("email");
        DateTime created = row.GetFieldDateTime("created_at");

        Console.WriteLine($"User #{id}");
        Console.WriteLine($"  Username: {username}");
        Console.WriteLine($"  Email: {email}");
        Console.WriteLine($"  Joined: {created:d}");
        Console.WriteLine();
    }
}

Example 2: Product Catalog with Aliases

Table products = schema["products"];

// Use aliases for cleaner result access
Query query = new Query()
    .Select([
        (products["product_id"], "id"),
        (products["product_name"], "name"),
        (products["unit_price"], "price"),
        (products["stock_quantity"], "stock")
    ])
    .From(products);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    foreach (Result row in results)
    {
        int id = row.GetFieldInteger("id");
        string name = row.GetFieldString("name");
        decimal price = row.GetFieldDecimal("price");
        int stock = row.GetFieldInteger("stock");

        Console.WriteLine($"{name} (${price:F2}) - {stock} in stock");
    }
}

Example 3: Unique Values

Table orders = schema["orders"];

// Get list of unique order statuses
Query query = new Query()
    .Select(orders["status"])
    .From(orders)
    .Distinct();

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    Console.WriteLine("Order Statuses:");
    foreach (Result row in results)
    {
        Console.WriteLine($"- {row.GetFieldString("status")}");
    }
}

Best Practices

  1. Always use using statements

  2. Select only needed columns

    • Avoid SELECT * in production code
    • Reduces network traffic and memory usage
    • Makes code more maintainable

  3. Use appropriate data types

    • Use the correct GetField* method for each column type
    • Prevents runtime errors and type conversion issues

  4. Check for NULL values

    • Always check IsNull() before accessing nullable columns
    • Prevents unexpected exceptions

  5. Choose the right retrieval method

    • Use Retrieve() for small result sets or when you need random access
    • Use Search() for large result sets or streaming scenarios

Next Steps