Get started

dotnet --version

dotnet new console -n ArxSample -f net10.0
cd ArxSample

dotnet add package ARX.SDK.SQL --version 1.0.0-rc.1

docker run --name arx-samples-mssql -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=Your-password1 -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest
docker exec arx-samples-mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P Your-password1 -C -Q "CREATE DATABASE arx_samples; ALTER DATABASE arx_samples SET ALLOW_SNAPSHOT_ISOLATION ON"

macOS and Linux (bash, zsh)

docker exec -i arx-samples-mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P Your-password1 -C -I -b -d arx_samples < sample-schema.sql

Windows PowerShell

Get-Content sample-schema.sql -Raw | docker exec -i arx-samples-mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P Your-password1 -C -I -b -d arx_samples

The sample schema the samples run against.

-- Schema for the ARX.SDK SQL Server documentation samples: a small CRM and product catalogue, and a table of every
-- data type. Run it in a database of its own (arx_samples). Re-runnable: it drops, by name, only the tables, views and
-- routines it creates below, and the reporting schema once nothing else is left in it, so running it again starts over
-- and nothing else in the database is touched. Batches are separated by GO lines (a tool's separator, not T-SQL). Run it with sqlcmd or any SQL tool against the
-- samples' database, for example:
--   sqlcmd -S localhost,11433 -U sa -d arx_samples -C -i sample-schema.sql
--
-- ARX conventions used here: the table name is the entity name, and a lookup column stores the referenced row's id.
-- Every table's id column is declared in SampleCatalog.cs: most are a generated bigint "id"; country and device show a
-- natural id and a generated uniqueidentifier; price_list and activity_log have no id column (NoIdColumn). Selection
-- set columns hold the option's code itself.

-- The routines and views this script creates, and no other: only these names, only in dbo and reporting.
DECLARE @drop nvarchar(max) = (
    SELECT STRING_AGG(CONCAT(CASE o.type WHEN 'P' THEN N'DROP PROCEDURE ' WHEN 'V' THEN N'DROP VIEW ' ELSE N'DROP FUNCTION ' END,
                             QUOTENAME(SCHEMA_NAME(o.schema_id)), N'.', QUOTENAME(o.name)), N'; ')
    FROM sys.objects o
    WHERE o.type IN ('P', 'FN', 'IF', 'TF', 'V') AND SCHEMA_NAME(o.schema_id) IN ('dbo', 'reporting')
      AND o.name IN (
        N'fn_account_totals', N'fn_accounts_with_status', N'fn_contact_count', N'fn_contacts_named', N'fn_contacts_of_account', N'fn_order_count',
        N'fn_products_like', N'sp_account_report', N'sp_account_totals', N'sp_import_progress', N'sp_order_with_lines', N'sp_place_order',
        N'sp_products', N'sp_products_page', N'sp_recalculate_order_totals', N'sp_recalculate_totals', N'sp_rename_account', N'sp_reserve_credit',
        N'sp_slow', N'vw_account_overview', N'vw_account_sales_summary', N'vw_contact_directory', N'vw_key_accounts', N'vw_product_catalogue'));
IF @drop IS NOT NULL EXEC (@drop);
GO

IF OBJECT_ID('account') IS NOT NULL ALTER TABLE account DROP CONSTRAINT IF EXISTS account_primary_contact_fkey;
DROP TABLE IF EXISTS legacy_customer, web_visit, activity_log, price_list, device, country, order_line, sales_order, data_type_showcase, product, product_category, contact, account;
-- The reporting schema is the script's own: it goes only when nothing else is left in it.
IF SCHEMA_ID('reporting') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE schema_id = SCHEMA_ID('reporting')) EXEC ('DROP SCHEMA reporting');
GO

-- Customers. industry is an Integer selection set built from a .NET enum; status a String set the CHECK also guards;
-- segments a multiple-choice String set stored as delimited text (SQL Server has no arrays).
CREATE TABLE account (
    id                 bigint            IDENTITY(1, 1) PRIMARY KEY,
    guid               uniqueidentifier  NOT NULL DEFAULT NEWID(),
    name               nvarchar(200)     NOT NULL,
    account_number     varchar(20)       NULL,
    industry           smallint,
    status             varchar(10)       NOT NULL DEFAULT 'ACTIVE' CONSTRAINT account_status_check CHECK (status IN ('ACTIVE', 'ON_HOLD', 'CLOSED')),
    segments           nvarchar(200),
    website            varchar(200),
    annual_revenue     decimal(18, 2),
    employee_count     int               CONSTRAINT account_employee_count_check CHECK (employee_count >= 0),
    credit_limit       decimal(18, 2),
    is_key_account     bit               NOT NULL DEFAULT 0,
    founded_on         date,
    parent_account_id  bigint            REFERENCES account (id),
    primary_contact_id bigint,           -- references contact (added after contact below; the preload samples follow it)
    created_on         datetimeoffset    NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    modified_on        datetimeoffset
);
GO

-- A unique account number that several accounts may leave empty (a UNIQUE constraint allows one NULL only).
CREATE UNIQUE INDEX account_account_number_key ON account (account_number) WHERE account_number IS NOT NULL;
GO

-- People at a customer. account_id is a lookup (ArxEntityReference); email is unique. preferred_channel, lead_source
-- and rating are selection sets (lead_source and rating come from the catalog's JSON).
CREATE TABLE contact (
    id                bigint            IDENTITY(1, 1) PRIMARY KEY,
    guid              uniqueidentifier  NOT NULL DEFAULT NEWID(),
    account_id        bigint            REFERENCES account (id),
    first_name        nvarchar(100)     NOT NULL,
    last_name         nvarchar(100)     NOT NULL,
    job_title         nvarchar(100),
    email             varchar(254),
    phone             varchar(30),
    preferred_channel char(1),
    lead_source       nvarchar(50),
    rating            smallint,
    birthday          date,
    do_not_email      bit               NOT NULL DEFAULT 0,
    consent_given_at  datetimeoffset,
    notes             nvarchar(max),
    created_on        datetimeoffset    NOT NULL DEFAULT SYSDATETIMEOFFSET()
);
GO

CREATE UNIQUE INDEX contact_email_key ON contact (email) WHERE email IS NOT NULL;

-- account.primary_contact_id: a contact of the account, the reference a preload follows to add contact.
ALTER TABLE account ADD CONSTRAINT account_primary_contact_fkey FOREIGN KEY (primary_contact_id) REFERENCES contact (id);

CREATE TABLE product_category (
    id    bigint         IDENTITY(1, 1) PRIMARY KEY,
    name  nvarchar(100)  NOT NULL CONSTRAINT product_category_name_key UNIQUE
);

-- The catalogue. attributes is JSON text (ArxJsonB; no JSON type in SQL Server 2022, ISJSON checks it); image is binary.
-- tags is delimited text.
CREATE TABLE product (
    id              bigint            IDENTITY(1, 1) PRIMARY KEY,
    guid            uniqueidentifier  NOT NULL DEFAULT NEWID(),
    sku             varchar(32)       NOT NULL CONSTRAINT product_sku_key UNIQUE,
    name            nvarchar(200)     NOT NULL,
    description     nvarchar(max),
    category_id     bigint            REFERENCES product_category (id),
    unit_price      decimal(12, 2)    NOT NULL CONSTRAINT product_unit_price_check CHECK (unit_price >= 0),
    cost            decimal(12, 4),
    weight_kg       float,
    stock_quantity  int               NOT NULL DEFAULT 0,
    is_active       bit               NOT NULL DEFAULT 1,
    tags            nvarchar(400),
    attributes      nvarchar(max)     CONSTRAINT product_attributes_check CHECK (attributes IS NULL OR ISJSON(attributes) = 1),
    image           varbinary(max),
    launched_on     date,
    created_on      datetimeoffset    NOT NULL DEFAULT SYSDATETIMEOFFSET()
);

-- An order and its lines, for the "one process, one transaction" samples.
CREATE TABLE sales_order (
    id           bigint          IDENTITY(1, 1) PRIMARY KEY,
    order_number varchar(20)     NOT NULL CONSTRAINT sales_order_order_number_key UNIQUE,
    account_id   bigint          NOT NULL REFERENCES account (id),
    ordered_on   datetimeoffset  NOT NULL,
    total        decimal(14, 2)  NOT NULL DEFAULT 0
);

CREATE TABLE order_line (
    id          bigint          IDENTITY(1, 1) PRIMARY KEY,
    order_id    bigint          NOT NULL REFERENCES sales_order (id),
    product_id  bigint          NOT NULL REFERENCES product (id),
    quantity    int             NOT NULL CONSTRAINT order_line_quantity_check CHECK (quantity > 0),
    unit_price  decimal(12, 2)  NOT NULL
);

-- One column for every kind of value the SQL Server provider writes (README, "Type mapping").
CREATE TABLE data_type_showcase (
    id                     bigint            IDENTITY(1, 1) PRIMARY KEY,
    text_value             nvarchar(max),
    varchar_value          varchar(50),
    char_value             nchar(3),
    tinyint_value          tinyint,
    smallint_value         smallint,
    int_value              int,
    bigint_value           bigint,
    decimal_value          decimal(30, 6),
    money_value            money,
    real_value             real,
    float_value            float,
    bit_value              bit,
    uniqueidentifier_value uniqueidentifier,
    datetimeoffset_value   datetimeoffset,
    datetime2_value        datetime2,
    datetime_value         datetime,
    date_value             date,
    time_value             time,
    varbinary_value        varbinary(max),
    json_value             nvarchar(max)     CONSTRAINT data_type_showcase_json_check CHECK (json_value IS NULL OR ISJSON(json_value) = 1),
    mood                   nvarchar(10)      CONSTRAINT data_type_showcase_mood_check CHECK (mood IN (N'happy', N'neutral', N'sad')),
    account_id             bigint            REFERENCES account (id),
    industry               smallint
);

-- Ids that aren't a generated bigint: a natural text id and a generated uniqueidentifier. Then two tables with no id
-- column: a price identified by region and SKU together, and a log whose rows may repeat. All declared in
-- SampleCatalog.cs: ARX reads ids and keys from the database only when asked to preload them.
CREATE TABLE country (
    code  char(2)        PRIMARY KEY,
    name  nvarchar(100)  NOT NULL
);

CREATE TABLE device (
    id             uniqueidentifier  PRIMARY KEY DEFAULT NEWID(),
    serial_number  varchar(50)       NOT NULL CONSTRAINT device_serial_number_key UNIQUE,
    account_id     bigint            REFERENCES account (id)
);

CREATE TABLE price_list (
    region      char(2)         NOT NULL REFERENCES country (code),
    sku         varchar(32)     NOT NULL,
    unit_price  decimal(12, 2)  NOT NULL CONSTRAINT price_list_unit_price_check CHECK (unit_price >= 0),
    valid_from  date            NOT NULL,
    PRIMARY KEY (region, sku)
);

-- No primary key at all: the same action can be logged twice.
CREATE TABLE activity_log (
    account_id  bigint          REFERENCES account (id),
    action      nvarchar(200)   NOT NULL,
    logged_at   datetimeoffset  NOT NULL DEFAULT SYSDATETIMEOFFSET()
);

-- A table made outside ARX, its columns named outside ARX's name rule: used through aliases (Create Advanced10).
CREATE TABLE legacy_customer (
    [Customer Code]  nvarchar(20)    NOT NULL PRIMARY KEY,
    [first-name]     nvarchar(100)   NOT NULL,
    [last-name]      nvarchar(100)   NULL,
    [order.total]    decimal(12, 2)  NULL
);

-- Web visits (declared NoIdColumn in SampleCatalog.cs).
CREATE TABLE web_visit (
    account_id  bigint         REFERENCES account (id),
    page        nvarchar(200)  NOT NULL,
    visited_on  date           NOT NULL
);
GO

-- Naming: views vw_, functions fn_, procedures sp_; tables have no prefix. The name says what an object is where only
-- the name is visible (a query, a log, an error message), and ARX reads them all by name.

-- Retrieve: a view an application reads like a table, and a function that returns a table.
CREATE VIEW vw_account_overview AS
SELECT a.id, a.name, a.account_number, a.status, COUNT_BIG(c.id) AS contacts
FROM account a
LEFT JOIN contact c ON c.account_id = a.id
GROUP BY a.id, a.name, a.account_number, a.status;
GO

CREATE FUNCTION fn_contacts_of_account(@p_account_id bigint) RETURNS TABLE
AS RETURN SELECT id, first_name, last_name, email FROM contact WHERE account_id = @p_account_id;
GO

-- A view in another schema: read as "reporting.vw_product_catalogue".
IF SCHEMA_ID('reporting') IS NULL EXEC ('CREATE SCHEMA reporting');
GO

CREATE VIEW reporting.vw_product_catalogue AS
SELECT p.id, p.sku, p.name, p.unit_price, c.name AS category
FROM product p
LEFT JOIN product_category c ON c.id = p.category_id
WHERE p.is_active = 1;
GO

-- An account summary, computed at each read (an indexed view would store it instead).
CREATE VIEW vw_account_sales_summary AS
SELECT a.id AS account_id, a.name, a.account_number, COUNT_BIG(o.id) AS orders, COALESCE(SUM(o.total), 0) AS revenue
FROM account a
LEFT JOIN sales_order o ON o.account_id = a.id
GROUP BY a.id, a.name, a.account_number;
GO

-- Function shapes: a table's rows, one row of totals (a multi-statement function), one value (a scalar function).
CREATE FUNCTION fn_accounts_with_status(@p_status varchar(10)) RETURNS TABLE
AS RETURN SELECT * FROM account WHERE status = @p_status;
GO

CREATE FUNCTION fn_account_totals() RETURNS @totals TABLE (accounts bigint, contacts bigint)
AS
BEGIN
    INSERT INTO @totals SELECT (SELECT COUNT_BIG(*) FROM account), (SELECT COUNT_BIG(*) FROM contact);
    RETURN;
END;
GO

CREATE FUNCTION fn_order_count(@p_account_id bigint) RETURNS bigint
AS BEGIN RETURN (SELECT COUNT_BIG(*) FROM sales_order WHERE account_id = @p_account_id); END;
GO

-- A procedure: not a row source (Execute's, not Retrieve's).
CREATE PROCEDURE sp_recalculate_order_totals
AS UPDATE o SET total = COALESCE((SELECT SUM(l.quantity * l.unit_price) FROM order_line l WHERE l.order_id = o.id), 0) FROM sales_order o;
GO

-- Views in depth: a directory of contacts without their private columns (email, phone, birthday, notes), and a view
-- built on another view.
CREATE VIEW vw_contact_directory AS
SELECT id, first_name, last_name, job_title, account_id
FROM contact;
GO

CREATE VIEW vw_key_accounts AS
SELECT id, name, account_number, contacts
FROM vw_account_overview
WHERE contacts >= 2;
GO

-- Execute: procedures (sp_) and functions (fn_) the samples call.

-- Rename an account (no result); its totals for a year (IN, IN/OUT and OUTPUT values).
CREATE PROCEDURE sp_rename_account @p_account_id bigint, @p_name nvarchar(200), @p_reason nvarchar(200) = N'not given'
AS UPDATE account SET name = @p_name, modified_on = SYSDATETIMEOFFSET() WHERE id = @p_account_id;
GO

CREATE PROCEDURE sp_account_totals @p_account_id bigint, @p_year int OUTPUT, @p_orders bigint OUTPUT, @p_revenue decimal(18, 2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @p_year = COALESCE(@p_year, YEAR(SYSDATETIMEOFFSET()));
    SELECT @p_orders = COUNT_BIG(*), @p_revenue = COALESCE(SUM(total), 0)
    FROM sales_order WHERE account_id = @p_account_id AND YEAR(ordered_on) = @p_year;
END;
GO

-- A value; rows.
CREATE FUNCTION fn_contact_count(@p_account_id bigint) RETURNS bigint
AS BEGIN RETURN (SELECT COUNT_BIG(*) FROM contact WHERE account_id = @p_account_id); END;
GO

CREATE FUNCTION fn_contacts_named(@p_last_name nvarchar(100)) RETURNS TABLE
AS RETURN SELECT id, first_name, job_title FROM contact WHERE last_name = @p_last_name;
GO

-- Place an order: writes sales_order and order_line, returns the new id and the total. The SKUs are comma-separated.
CREATE PROCEDURE sp_place_order @p_account_id bigint, @p_order_number varchar(20), @p_skus nvarchar(max), @p_quantity int, @p_order_id bigint OUTPUT, @p_total decimal(14, 2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO sales_order (order_number, account_id, ordered_on) VALUES (@p_order_number, @p_account_id, SYSDATETIMEOFFSET());
    SET @p_order_id = SCOPE_IDENTITY();
    INSERT INTO order_line (order_id, product_id, quantity, unit_price)
        SELECT @p_order_id, p.id, @p_quantity, p.unit_price FROM product p WHERE p.sku IN (SELECT TRIM(value) FROM STRING_SPLIT(@p_skus, ','));
    SET @p_total = (SELECT COALESCE(SUM(quantity * unit_price), 0) FROM order_line WHERE order_id = @p_order_id);
    UPDATE sales_order SET total = @p_total WHERE id = @p_order_id;
END;
GO

-- Tables from a procedure: an order and its lines; an account report with three tables (result sets, in order).
CREATE PROCEDURE sp_order_with_lines @p_order_id bigint
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.order_number, o.ordered_on, o.total, a.name AS account FROM sales_order o JOIN account a ON a.id = o.account_id WHERE o.id = @p_order_id;
    SELECT p.sku, l.quantity, l.unit_price FROM order_line l JOIN product p ON p.id = l.product_id WHERE l.order_id = @p_order_id ORDER BY p.sku;
END;
GO

CREATE PROCEDURE sp_account_report @p_account_id bigint
AS
BEGIN
    SET NOCOUNT ON;
    SELECT first_name, last_name, job_title FROM contact WHERE account_id = @p_account_id ORDER BY first_name;
    SELECT order_number, total FROM sales_order WHERE account_id = @p_account_id ORDER BY order_number;
    SELECT (SELECT COUNT_BIG(*) FROM contact WHERE account_id = @p_account_id) AS contacts,
           (SELECT COUNT_BIG(*) FROM sales_order WHERE account_id = @p_account_id) AS orders,
           (SELECT COALESCE(SUM(total), 0) FROM sales_order WHERE account_id = @p_account_id) AS revenue;
END;
GO

-- Paging: a procedure that pages itself; a function ARX pages; a procedure's result set ARX pages.
CREATE PROCEDURE sp_products_page @p_prefix varchar(32), @p_page int, @p_page_size int, @p_total bigint = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @p_total = (SELECT COUNT_BIG(*) FROM product WHERE sku LIKE @p_prefix + '%');
    SELECT sku, name, unit_price FROM product WHERE sku LIKE @p_prefix + '%' ORDER BY sku
    OFFSET (@p_page - 1) * @p_page_size ROWS FETCH NEXT @p_page_size ROWS ONLY;
END;
GO

CREATE FUNCTION fn_products_like(@p_prefix varchar(32)) RETURNS TABLE
AS RETURN SELECT sku, name, unit_price FROM product WHERE sku LIKE @p_prefix + '%';
GO

CREATE PROCEDURE sp_products @p_prefix varchar(32)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT sku, name, unit_price FROM product WHERE sku LIKE @p_prefix + '%' ORDER BY sku;
END;
GO

-- Business rules the database enforces: an account on hold (its own error number) and a credit limit (THROW).
CREATE PROCEDURE sp_reserve_credit @p_account_id bigint, @p_amount decimal(18, 2)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @limit decimal(18, 2), @status varchar(10), @message nvarchar(400);
    SELECT @limit = credit_limit, @status = status FROM account WHERE id = @p_account_id;

    IF @status = 'ON_HOLD'
    BEGIN
        SET @message = CONCAT(N'account ', @p_account_id, N' is on hold: release the hold first');
        THROW 50101, @message, 1;
    END;

    IF @p_amount > COALESCE(@limit, 0)
    BEGIN
        SET @message = CONCAT(N'credit limit exceeded: limit ', @limit, N', asked ', @p_amount, N'; reserve ', @limit, N' or less');
        THROW 50102, @message, 1;
    END;

    UPDATE account SET credit_limit = credit_limit - @p_amount, modified_on = SYSDATETIMEOFFSET() WHERE id = @p_account_id;
END;
GO

-- A nightly job that commits each batch and reports progress; a slow procedure.
CREATE PROCEDURE sp_recalculate_totals @p_prefix varchar(20), @p_batch int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @done int = 0, @rows int = 1;

    WHILE @rows > 0
    BEGIN
        BEGIN TRANSACTION;
        UPDATE o SET total = COALESCE((SELECT SUM(l.quantity * l.unit_price) FROM order_line l WHERE l.order_id = o.id), 0)
        FROM sales_order o
        WHERE o.id IN (SELECT TOP (@p_batch) id FROM sales_order WHERE order_number LIKE @p_prefix + '%' AND total < 0 ORDER BY id);
        SET @rows = @@ROWCOUNT;
        COMMIT TRANSACTION;

        IF @rows > 0
        BEGIN
            SET @done += @rows;
            RAISERROR(N'recalculated %d orders', 10, 1, @done) WITH NOWAIT;
        END;
    END;

    PRINT CONCAT(N'done: ', @done, N' orders');
END;
GO

CREATE PROCEDURE sp_import_progress @p_rows int
AS
BEGIN
    DECLARE @i int = 250;

    WHILE @i <= @p_rows
    BEGIN
        RAISERROR(N'imported %d of %d rows', 10, 1, @i, @p_rows) WITH NOWAIT;
        SET @i += 250;
    END;

    IF @p_rows > 900 RAISERROR(N'a large import: %d rows', 10, 1, @p_rows);
END;
GO

CREATE PROCEDURE sp_slow @p_seconds int
AS
BEGIN
    DECLARE @delay datetime = DATEADD(second, @p_seconds, 0);
    WAITFOR DELAY @delay;
END;
GO

macOS and Linux (bash, zsh)

export ARX_SAMPLES_SQLSERVER="Server=localhost,1433;Database=arx_samples;User Id=sa;Password=Your-password1;TrustServerCertificate=True"

Windows PowerShell

$env:ARX_SAMPLES_SQLSERVER = "Server=localhost,1433;Database=arx_samples;User Id=sa;Password=Your-password1;TrustServerCertificate=True"

dotnet run