Chained CRUD Operations

This document details the chained query builder of the cDataBase class, providing an elegant ORM-like API without writing complex SQL manually.

Detailed Documentation

Topic	Description
Condition Methods	Where, OrWhere, WhereIn, WhereLike, WhereNull, etc.
Sort/Group/Join/Selection	OrderBy, GroupBy, Having, Join, Columns, Distinct
Limit & Pagination	Limit, Offset, Page
Terminal Methods	RowCreate, RowRead, RowUpdate, RowDelete, Field
Aggregate Methods	RowCount, RowExists, RowPluck, RowMax/Min/Sum/Avg
Examples & Best Practices	Practical examples, SQL comparison, FAQ, best practices

---

Overview

What is Chained CRUD

Chained CRUD is an elegant database operation approach provided by the cDataBase class. It builds SQL statements progressively through Method Chaining, ultimately executing operations via terminal methods.

Core Advantages:

• High Readability - Each chained method corresponds to a SQL clause with clear semantics
• Type Safety - Method parameters are automatically escaped, reducing SQL injection risk
• Cross-Database Compatibility - Automatically adapts to MySQL / SQL Server / Access syntax differences
• Zero Handwritten SQL - Common operations like sorting, grouping, pagination, JOINs require no string concatenation

Design Philosophy

Chained Call Flow:

  Starting Method → Condition Methods(stackable) → Modifier Methods(stackable) → Terminal Method
  ─────────────   ───────────────   ───────────────   ──────────
  Table()         Where()           Columns()          RowRead
                  WhereIn()         OrderBy()           RowCreate
                  WhereLike()       GroupBy()           RowUpdate
                  OrWhere()         Join()              RowDelete
                  ...               Limit()             RowCount
                                    Page()              RowExists
                                                        RowPluck
                                                        RowMax/Min/Sum/Avg

---

Chained Builder Architecture

Internal State Variables

The chained builder accumulates each clause through a set of private variables. Terminal methods automatically reset the builder state after execution (ResetBuilder):

Variable	SQL Clause	Setting Method
pvTableName	FROM	Table()
pvSelectFields	SELECT column list	Columns()
pvIsDistinct	SELECT DISTINCT	Distinct()
pvWhereCondition	WHERE (AND conditions)	Where(), WhereLike()
pvOrWhereCondition	WHERE (OR conditions)	OrWhere()
pvWhereInClauses	WHERE IN/NOT IN	WhereIn(), WhereNotIn()
pvWhereBetweenClauses	WHERE BETWEEN	WhereBetween()
pvWhereNullClauses	WHERE IS NULL	WhereNull(), WhereNotNull()
pvOrderByClause	ORDER BY	OrderBy(), OrderByDesc(), OrderByRaw()
pvGroupByClause	GROUP BY	GroupBy()
pvHavingClause	HAVING	Having()
pvJoinClauses	JOIN	Join(), LeftJoin(), RightJoin()
pvLimitValue	LIMIT	Limit()
pvOffsetValue	OFFSET	Offset()
pvPageNum/Limit	ADO Pagination	Page()
pvFields	INSERT/UPDATE values	Field()

SQL Assembly Logic

Terminal methods call BuildSelectSql() to assemble the complete SQL:

SELECT [DISTINCT] {columns} FROM {table} {JOIN clauses}
WHERE {Where + WhereIn + WhereBetween + WhereNull} [OR (OrWhere)]
GROUP BY {group} HAVING {filter}
ORDER BY {sort}
LIMIT/OFFSET or ADO pagination

---

Starting Methods

Table - Set Operation Table

Sets the table name for the current operation. The starting point for all chained CRUD operations.

Syntax

Function Table(ByVal TableName As String) As cDataBase

Example

' Basic usage
db.Table("users").RowRead

' With Where query
db.Table("users").Where("age>?", 18).RowRead

' With Field insert
db.Table("users").Field("name", "Zhang San").Field("age", 25).RowCreate

---

Complete Method Reference

Chained Methods (return cDataBase, allowing continued chaining)

Category	Method	Description	SQL Clause
Start	Table(table)	Set operation table	FROM
Condition	Where(cond,params...)	AND condition	WHERE
	OrWhere(cond,params...)	OR condition	WHERE ... OR (...)
	WhereIn(field,values)	IN condition	WHERE field IN (...)
	WhereNotIn(field,values)	NOT IN condition	WHERE field NOT IN (...)
	WhereBetween(field,V1,V2)	BETWEEN condition	WHERE field BETWEEN V1 AND V2
	WhereNotBetween(field,V1,V2)	NOT BETWEEN	WHERE field NOT BETWEEN ...
	WhereLike(field,pattern)	LIKE fuzzy match	WHERE field LIKE 'pattern'
	WhereNotLike(field,pattern)	NOT LIKE	WHERE field NOT LIKE 'pattern'
	WhereNull(field)	IS NULL	WHERE field IS NULL
	WhereNotNull(field)	IS NOT NULL	WHERE field IS NOT NULL
Sort	OrderBy(field)	ASC sorting	ORDER BY field ASC
	OrderByDesc(field)	DESC sorting	ORDER BY field DESC
	OrderByRaw(clause)	Custom sorting	ORDER BY clause
Group	GroupBy(field)	Grouping	GROUP BY field
	Having(condition)	Group filter	HAVING condition
Join	Join(table,on)	INNER JOIN	INNER JOIN table ON on
	LeftJoin(table,on)	LEFT JOIN	LEFT JOIN table ON on
	RightJoin(table,on)	RIGHT JOIN	RIGHT JOIN table ON on
Select	Columns(cols...)	Specify query columns	SELECT columns
	Distinct()	Distinct	SELECT DISTINCT
Value	Field(field,value)	Set field value	INSERT/UPDATE values
Limit	Limit(N)	Limit count	LIMIT N
	Offset(N)	Offset	OFFSET N
	Page(num,perpage)	ADO pagination	Rs.PageSize + AbsolutePage

Terminal Methods (execute operation, reset builder)

Method	Return	Description
RowCreate	cDataBase	Create new row
RowRead	cDataBase	Query rows (results in Rs)
RowUpdate	cDataBase	Update row
RowDelete	cDataBase	Delete row
RowCount	Long	Count rows
RowExists	Boolean	Check if exists
RowPluck(field)	Collection	Extract single column values
RowMax(field)	Variant	Aggregate MAX
RowMin(field)	Variant	Aggregate MIN
RowSum(field)	Variant	Aggregate SUM
RowAvg(field)	Variant	Aggregate AVG

---

Last Updated: 2026-06-26