TSQL.APP Framework Complete Training Manual

• For Personal Login: Use your registered email address and password
• For Demo login: Use demo@t-sql.app with password: demo-2411

Table of Contents

1. Foundation
2. Core Components
3. UI Elements Library
4. Data Handling
5. Modal System
6. Advanced Features
7. Best Practices
8. Practical Exercises
9. Troubleshooting Guide
10. Reference

1. Foundation

1.1 What is TSQL.APP?

• No-code/low-code skeleton web application framework
• Built on Microsoft SQL Server
• Uses T-SQL as primary programming language
• Generates UI directly from database operations

1.2 Architecture Overview

Client Layer (ReactJS)
      ↕
API Layer (.NET Core)
      ↕
Database Layer (MSSQL)

1.3 Key Concepts

1. Action Scripts: T-SQL code that defines UI and behavior
2. Modal System: Manages UI state and user interactions
3. Stored Procedures: Over 400 built-in UI procedures
4. Cards: Visual representations of database tables

2. Core Components

2.1 Variable Synchronization (CRITICAL)

-- ALWAYS synchronize variables with modal state
DECLARE @UserInput NVARCHAR(MAX);
EXEC sp_api_modal_get_value 
    @name='@UserInput',   -- Must match UI element name
    @value=@UserInput OUT -- Variable to synchronize

2.2 Parameter Passing Rules (MANDATORY)

-- ✅ CORRECT: Prepare values before passing
DECLARE @Message NVARCHAR(MAX) = 'Hello, ' + @UserName;
EXEC sp_api_modal_text @text=@Message;

-- ❌ INCORRECT: Never pass calculations directly
EXEC sp_api_modal_text @text='Hello, ' + @UserName;

2.3 Basic Modal Structure

-- 1. Declare ALL variables
DECLARE @Input NVARCHAR(MAX);
DECLARE @Submit NVARCHAR(MAX);

-- 2. Synchronize ALL variables
EXEC sp_api_modal_get_value @name='@Input', @value=@Input OUT;
EXEC sp_api_modal_get_value @name='@Submit', @value=@Submit OUT;

-- 3. Create UI elements
EXEC sp_api_modal_text @text='Enter data:';
EXEC sp_api_modal_input @name='@Input', @value=@Input OUT;
EXEC sp_api_modal_button 
    @name='@Submit', 
    @value='Submit', 
    @valueout=@Submit OUT;

-- 4. Handle interactions
IF @Submit IS NOT NULL
BEGIN
    -- Process submission
    EXEC sp_api_modal_clear;
END

3. UI Elements Library

3.1 Text Elements

-- Basic Text
DECLARE @Title NVARCHAR(100) = 'Welcome';
EXEC sp_api_modal_text @text=@Title;

-- HTML Content
DECLARE @HTML NVARCHAR(MAX) = '<h1>Welcome</h1>';
EXEC sp_api_modal_html @html=@HTML;

-- Markdown
DECLARE @MD NVARCHAR(MAX) = '## Welcome';
EXEC sp_api_modal_text @text=@MD, @markdown=1;

DEMO3-1:

• login with demo@t-sql.app / demo-2411, or use your own registered email address / password

3.2 Input Elements

-- Text Input
EXEC sp_api_modal_input 
    @name='@TextInput',
    @value=@TextInput OUT,
    @placeholder='Enter text';

-- Numeric Input
EXEC sp_api_modal_input_decimal
    @name='@Amount',
    @value=@Amount OUT,
    @precision=18,
    @scale=2;

-- Date Input
EXEC sp_api_modal_date
    @name='@Date',
    @value=@Date OUT;

3.3 Control Elements

-- Button
EXEC sp_api_modal_button
    @name='@Submit',
    @value='Submit',
    @valueout=@Submit OUT,
    @class='btn-primary',
    @key='Enter';

-- Switch
EXEC sp_api_modal_switch
    @name='@Toggle',
    @value=@Toggle OUT,
    @label='Enable Feature',
    @direct_post=1;

-- Choose
EXEC sp_api_modal_choose
    @name='@Choice',
    @value=@Choice OUT,
    @list='Option1,Option2,Option3';

4. Data Handling

4.1 Temporary Tables (Essential Pattern)

-- Always clean up first
DROP TABLE IF EXISTS #Data;

-- Create with column suffixes
CREATE TABLE #Data (
    [Category*] NVARCHAR(100),  -- Group by
    [Amount@:2] DECIMAL(18,2),  -- Sum with 2 decimals
    [Date] DATE
);

-- Display
EXEC sp_api_modal_table 
    @tmptable='#Data',
    @print=1,
    @excel=1;

-- Clean up
DROP TABLE IF EXISTS #Data;

4.2 Column Suffixes

• *: Group by this column
• @: Calculate totals
• :n: Display n decimal places
• ~col-sm-n: Set column width

4.3 File Handling

-- Single File Upload
EXEC sp_api_modal_file
    @name='@File',
    @to_file_context='Documents',
    @to_file_context_id=@ID;

-- Multi-File Upload
DECLARE @UploadComplete BIT;
EXEC sp_api_modal_file_multi
    @name='@Files',
    @to_file_context='Documents',
    @to_file_context_id=@ID,
    @upload_finished=@UploadComplete OUT;

5. Modal System

5.1 Modal Lifecycle

1. Initial State
2. User Input
3. Server Processing
4. State Update
5. UI Refresh

5.2 State Management

-- Store State
DECLARE @Step INT = 1;
DECLARE @StepData NVARCHAR(MAX);

-- Handle Multi-step Process
IF @Step = 1
BEGIN
    -- Step 1 UI
    EXEC sp_api_modal_text 'Step 1';
    IF @NextClicked IS NOT NULL
    BEGIN
        SET @Step = 2;
        EXEC sp_api_modal_restart;
        RETURN;
    END
END

5.3 Modal Navigation

-- Clear and Exit
EXEC sp_api_modal_clear;

-- Restart Current Action
EXEC sp_api_modal_restart;

-- Navigate to Another Page
DECLARE @Path NVARCHAR(1000) = '/cards/detail/123';
EXEC sp_api_goto @path=@Path;

6. Advanced Features

6.1 Charts and Visualizations

-- Create Chart Data
CREATE TABLE #ChartData (
    Label NVARCHAR(100),
    Value INT
);

-- Display Chart
EXEC sp_api_modal_chart
    @name='@Chart',
    @tmptable='#ChartData',
    @type='bar';

6.2 Report Generation

-- Generate PDF Report
DECLARE @ReportData NVARCHAR(MAX);
EXEC sp_api_report
    @report_id=@ReportID,
    @record_id=@RecordID,
    @base64data=@ReportData OUTPUT;

6.3 API Integration

-- External API Call
DECLARE @Response NVARCHAR(MAX);
EXEC sp_api_fetch
    @url='https://api.example.com/data',
    @method='GET',
    @response=@Response OUTPUT;

7. Best Practices

7.1 Code Organization

1. Group declarations at top
2. Synchronize all variables
3. Handle all possible interactions
4. Clean up resources
5. Use consistent naming

7.2 Error Handling

BEGIN TRY
    -- Your code here
END TRY
BEGIN CATCH
    DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
    EXEC sp_api_modal_text @ErrorMsg, @class='error';
    RETURN;
END CATCH

7.3 Performance

1. Use appropriate data types
2. Clean up temporary tables
3. Index when needed
4. Avoid unnecessary calculations
5. Batch operations when possible

8. Practical Exercises

8.1 Basic Form

Create a form with:

• Text input
• Date input
• Submit button
• Success message

-- Solution Template
DECLARE @Name NVARCHAR(100);
DECLARE @Date DATE;
DECLARE @Submit NVARCHAR(10);

EXEC sp_api_modal_get_value @name='@Name', @value=@Name OUT;
EXEC sp_api_modal_get_value @name='@Date', @value=@Date OUT;
EXEC sp_api_modal_get_value @name='@Submit', @value=@Submit OUT;

EXEC sp_api_modal_input @name='@Name', @value=@Name OUT;
EXEC sp_api_modal_date @name='@Date', @value=@Date OUT;
EXEC sp_api_modal_button @name='@Submit', @value='Submit', @valueout=@Submit OUT;

IF @Submit IS NOT NULL
BEGIN
    EXEC sp_api_toast 'Form submitted successfully';
    EXEC sp_api_modal_clear;
END

8.2 Data Report

Create a report showing:

• Grouped data
• Totals
• Print option
• Excel export

-- Solution Template
DROP TABLE IF EXISTS #Report;

CREATE TABLE #Report (
    [Department*] NVARCHAR(100),
    [Sales@:2] DECIMAL(18,2),
    [Date] DATE
);

INSERT INTO #Report VALUES
('Sales', 1000.00, '2024-01-01'),
('Sales', 2000.00, '2024-01-02');

EXEC sp_api_modal_table
    @tmptable='#Report',
    @print=1,
    @excel=1;

DROP TABLE IF EXISTS #Report;

9. Troubleshooting Guide

9.1 Common Issues

1. Modal Not Updating

   • Check variable synchronization
   • Verify parameter passing
   • Inspect transaction state

2. Data Not Displaying

   • Verify temp table exists
   • Check column names
   • Validate suffixes

3. Buttons Not Responding

   • Confirm variable synchronization
   • Check button handling code
   • Verify transaction state

9.2 Debugging

-- Display Debug Info
EXEC sp_api_toast @text=@DebugInfo;

-- Log Error
EXEC apilog @message=@ErrorInfo;

-- Check Variable State
DECLARE @Debug NVARCHAR(MAX) = (
    SELECT @Variable as debug_value
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);
EXEC sp_api_modal_text @Debug;

10. Reference

10.1 Essential Procedures

• sp_api_modal_text
• sp_api_modal_input
• sp_api_modal_button
• sp_api_modal_table
• sp_api_modal_clear
• sp_api_toast

10.2 Important Tables

• api_files
• api_card
• api_file_category

10.3 Column Suffix Reference

* (asterisk)  - Group by column
@ (at)        - Calculate totals
:n (colon-n)  - Decimal places
~col-sm-n     - Bootstrap column width

10.4 Best Practices Checklist

• Variables declared and synchronized
• Parameters passed correctly
• Resources cleaned up
• Errors handled
• User feedback provided
• Code organized logically
• Performance considered
• Security implemented

This manual provides a comprehensive foundation for TSQL.APP development. Practice each concept thoroughly and refer back to this guide as needed.