Chained CRUD - Examples & Best Practices

This document contains practical examples, comparison with traditional SQL, FAQ, and best practices. For method details, see the sub-pages:

• Condition Methods
• Sort/Group/Join/Selection
• Limit & Pagination
• Terminal Methods
• Aggregate Methods

---

Comprehensive Examples

Example 1: User Management System

' Query active user list (sort + pagination)
If db.Table("users") _
    .Where("status=?", "active") _
    .WhereNotNull("email") _
    .OrderByDesc("last_login") _
    .Page(1, 20) _
    .RowRead Then
    
    Do Until db.Rs.EOF
        Debug.Print db.Rs!name & " | " & db.Rs!email
        db.Rs.MoveNext
    Loop
End If

' Search users (fuzzy match + OR condition)
db.Table("users") _
    .WhereLike("name", "%" & keyword & "%") _
    .OrWhereLike("email", "%" & keyword & "%") _
    .OrderBy("name") _
    .RowRead

' Create new user
With db.Table("users")
    .Field "name", txtName.Text
    .Field "age", CLng(txtAge.Text)
    .Field "email", txtEmail.Text
    .Field "status", "active"
    .RowCreate
    newId = db.LastInsertId
End With

' Update user info
With db.Table("users").Where("id=?", userId)
    .Field "name", txtName.Text
    .Field "email", txtEmail.Text
    .RowUpdate
End With

' Delete expired users
db.Table("users").Where("status=?", "expired").WhereNull("email").RowDelete

Example 2: Report Statistics

' Department count statistics
db.Table("users") _
    .Columns("dept,COUNT(*) AS cnt") _
    .GroupBy("dept") _
    .Having("cnt>5") _
    .OrderByDesc("cnt") _
    .RowRead

' Monthly sales summary
db.Table("orders") _
    .Columns("YEAR(created_at) AS y,MONTH(created_at) AS m,SUM(amount) AS total") _
    .GroupBy("YEAR(created_at)") _
    .GroupBy("MONTH(created_at)") _
    .WhereBetween("created_at", "2026-01-01", "2026-12-31") _
    .OrderBy("y") _
    .OrderBy("m") _
    .RowRead

' Quick statistics
Dim totalUsers As Long
totalUsers = db.Table("users").RowCount

Dim avgSalary As Variant
avgSalary = db.Table("users").Where("dept=?", "IT").RowAvg("salary")

Dim maxOrder As Variant
maxOrder = db.Table("orders").RowMax("amount")

' Get all department names list
Dim depts As Collection
Set depts = db.Table("users").Distinct.Columns("dept").RowPluck("dept")

Example 3: Multi-Table Join Query

' Order details (orders + users + products)
db.Table("orders") _
    .Columns("orders.id,users.name AS user_name,products.title AS product,orders.amount") _
    .Join("users", "orders.user_id=users.id") _
    .LeftJoin("products", "orders.product_id=products.id") _
    .Where("orders.amount>?", 100) _
    .WhereBetween("orders.created_at", "2026-01-01", "2026-06-30") _
    .OrderByDesc("orders.amount") _
    .Limit(50) _
    .RowRead

Example 4: Check Existence and Extract List

' Check if email exists before registration
If db.Table("users").Where("email=?", newEmail).RowExists Then
    MsgBox "This email is already registered"
    Exit Sub
End If

' Get admin ID list
Dim adminIds As Collection
Set adminIds = db.Table("users").WhereIn("role", "admin,superadmin").RowPluck("id")

' Batch operation: mark all admins as online
With db.Table("users").WhereIn("id", "1,2,3,5")
    .Field "online_status", True
    .RowUpdate
End With

---

Comparison with Traditional SQL

Query Comparison

' ── Traditional SQL Approach ──
db.Sql("SELECT id,name FROM users WHERE age > 18 AND dept IN ('IT','HR') ORDER BY age DESC LIMIT 10").Fetch

' ── Chained Builder Approach ──
db.Table("users") _
    .Columns("id,name") _
    .Where("age>?", 18) _
    .WhereIn("dept", "IT,HR") _
    .OrderByDesc("age") _
    .Limit(10) _
    .RowRead

Insert Comparison

' ── Traditional SQL Approach ──
db.Sql("INSERT INTO users (name,age) VALUES ('Zhang San',25)").Exec

' ── Chained Builder Approach ──
db.Table("users").Field("name", "Zhang San").Field("age", 25).RowCreate

Update Comparison

' ── Traditional SQL Approach ──
db.Sql("UPDATE users SET name='Li Si' WHERE id=1").Exec

' ── Chained Builder Approach ──
db.Table("users").Where("id=?", 1).Field("name", "Li Si").RowUpdate

Delete Comparison

' ── Traditional SQL Approach ──
db.Sql("DELETE FROM users WHERE status='expired'").Exec

' ── Chained Builder Approach ──
db.Table("users").Where("status=?", "expired").RowDelete

Chained Advantages

Dimension	Traditional SQL	Chained Builder
Readability	Long string concatenation, hard to read	Method chain with clear semantics
SQL Injection Risk	Manual value concatenation, easy to miss escaping	Automatic parameter escaping
Cross-Database	Need to write different pagination/limit syntax	Auto-adapts MySQL/MSSQL/Access
Maintainability	Changing conditions requires rewriting entire SQL	Add/remove chained methods
Debugging	Need to print complete SQL	Each step corresponds to a SQL clause

---

FAQ

Q1: Can the chained builder and Sql() method be mixed?

Not recommended. The chained builder (Table + Where + ...) and the traditional approach (Sql) use different internal states. If Sql() has been called, chained conditions won't apply; and vice versa. Choose one approach:

' Approach 1: Chained builder (recommended for standard CRUD)
db.Table("users").Where("id=?", 1).RowRead

' Approach 2: Traditional SQL (suitable for complex queries, subqueries, etc.)
db.Sql("SELECT * FROM users WHERE id = 1").Fetch

Q2: What if complex queries don't fit the chained approach?

For advanced SQL like subqueries, UNION, window functions that the chained builder cannot cover, use the traditional Sql() approach:

' Subquery - use Sql() approach
db.Sql("SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000)").Fetch

' UNION query - use Sql() approach
db.Sql("SELECT name FROM users UNION SELECT name FROM admins").Fetch

Q3: Can Where and WhereLike be used together?

Yes, they are both AND conditions and will be automatically merged:

db.Table("users") _
    .Where("dept=?", "IT") _
    .WhereLike("name", "%Zhang%") _
    .RowRead
' Generated: WHERE dept='IT' AND name LIKE '%Zhang%'

Q4: How does OrWhere priority work?

OrWhere is wrapped in parentheses to ensure correct combination with AND conditions:

db.Table("users").Where("age>?", 18).OrWhere("role='VIP'").RowRead
' Generated: WHERE age > 18 OR (role='VIP')

Q5: Can Rs editing continue after RowCreate?

Yes. RowCreate does not automatically reset the builder, allowing you to continue operating on Rs:

With db.Table("users").RowCreate
    .Rs!name = "Zhang San"
    If someCondition Then
        .Rs!email = "zhang@test.com"
    End If
    .Rs.Update
End With

Q6: What's the difference between Page and Limit/Offset?

Method	Use Case	Implementation
Page(N,M)	Standard pagination	ADO PageSize/AbsolutePage, universal for all databases
Limit(M)	Simple truncation	SQL LIMIT M, MySQL/MSSQL syntax adaptation
Offset(N)	Offset with Limit	SQL OFFSET N, MySQL/MSSQL syntax adaptation

Do not use Page and Limit/Offset simultaneously - choose one.

Q7: Is builder state preserved after chained method execution?

No. Terminal methods (RowRead, RowCreate, RowUpdate, RowDelete, RowCount, etc.) automatically call ResetBuilder to clear all internal state after execution. Next chained call must start again from Table().

---

Best Practices

1. Use With to Simplify Chained Calls

' Recommended: Clear With structure
With db.Table("users").Where("id=?", userId)
    .Field "name", newName
    .Field "age", newAge
    .RowUpdate
End With

' Not recommended: Scattered writing
db.Table "users"
db.Where "id=?", userId
db.Field "name", newName
db.Field "age", newAge
db.RowUpdate

2. Prefer Chained Parameter Escaping

' Recommended: Where placeholder auto-escaping
db.Table("users").Where("name=?", userName).RowRead

' Not recommended: Manual value concatenation
db.Table("users").Where("name='" & userName & "'").RowRead

3. Always Set Conditions for Update/Delete

' Safe: Must have Where condition
db.Table("users").Where("id=?", 1).RowUpdate
db.Table("users").Where("status=?", "expired").RowDelete

' Dangerous: No condition will error (builder protection)
db.Table("users").RowUpdate  ' Error: no condition set

4. Use Sql() for Complex Queries

' Chained: Standard CRUD, simple conditions, sorting, pagination
db.Table("users").Where("age>?", 18).OrderByDesc("id").Page(1, 10).RowRead

' Sql(): Subqueries, UNION, window functions, complex expressions
db.Sql("SELECT * FROM (SELECT id,name FROM users UNION SELECT id,name FROM admins) t").Fetch

---

Last Updated: 2026-06-26