blog/modern-sql-programming

[giscus[bot]]

blog/modern-sql-programming

An introduction to how modern SQL databases are changing how developers program against them.

https://zenstack.dev/blog/modern-sql-programming

[rhodelta66]

To make life easier we have put some effort into a RAG agent that helps with creating the SQL code for our application framework.

[rhodelta66]

can you create a dedicated, but simple demo app for my friend Yiming?

I'll create a simple demo app for your friend Yiming. Let's design an interactive task list application using TSQL.APP that demonstrates core functionality.

-- Yiming's Task Manager Demo App
-- A simple TSQL.APP application to demonstrate core functionality

-- 1. Create the base table structure (run this first to set up the database)
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = 'YimingTasks' AND type IN ('U'))
BEGIN
    CREATE TABLE YimingTasks (
        TaskID INT IDENTITY(1,1) PRIMARY KEY,
        TaskName NVARCHAR(200) NOT NULL,
        Description NVARCHAR(1000) NULL,
        DueDate DATE NULL,
        Priority INT NOT NULL DEFAULT 3, -- 1=High, 2=Medium, 3=Low
        IsCompleted BIT NOT NULL DEFAULT 0,
        DateCreated DATETIME NOT NULL DEFAULT GETDATE(),
        DateModified DATETIME NOT NULL DEFAULT GETDATE()
    );

    -- Add some sample data
    INSERT INTO YimingTasks (TaskName, Description, DueDate, Priority)
    VALUES 
        (N'Learn TSQL.APP', N'Go through basic tutorials and documentation', DATEADD(DAY, 7, GETDATE()), 1),
        (N'Build first app', N'Create a simple demo application', DATEADD(DAY, 14, GETDATE()), 2),
        (N'Review SQL fundamentals', N'Practice writing efficient queries', DATEADD(DAY, 10, GETDATE()), 2),
        (N'Share progress with team', N'Present findings and demo app', DATEADD(DAY, 21, GETDATE()), 3);

    -- Create a message to confirm table creation
    DECLARE @SetupMessage NVARCHAR(MAX) = N'✅ Database setup complete! YimingTasks table created with sample data.';
    EXEC sp_api_toast @text = @SetupMessage, @class = N'btn-success';
END

-- 2. Main action script for the Task Dashboard
-- Variable declarations (ALL at start)
DECLARE @TaskName NVARCHAR(200);
DECLARE @Description NVARCHAR(1000);
DECLARE @DueDate NVARCHAR(50);
DECLARE @Priority NVARCHAR(20);
DECLARE @SelectedTaskID NVARCHAR(50);
DECLARE @Action NVARCHAR(50);
DECLARE @AddButton NVARCHAR(50);
DECLARE @FilterPriority NVARCHAR(20);
DECLARE @FilterStatus NVARCHAR(20);
DECLARE @RefreshButton NVARCHAR(50);
DECLARE @Message NVARCHAR(MAX);
DECLARE @Title NVARCHAR(MAX);
DECLARE @JsonState NVARCHAR(MAX);
DECLARE @ErrorMsg NVARCHAR(MAX);

-- Synchronize with modal state
EXEC sp_api_modal_get_value @name=N'@TaskName', @value=@TaskName OUT;
EXEC sp_api_modal_get_value @name=N'@Description', @value=@Description OUT;
EXEC sp_api_modal_get_value @name=N'@DueDate', @value=@DueDate OUT;
EXEC sp_api_modal_get_value @name=N'@Priority', @value=@Priority OUT;
EXEC sp_api_modal_get_value @name=N'@SelectedTaskID', @value=@SelectedTaskID OUT;
EXEC sp_api_modal_get_value @name=N'@Action', @value=@Action OUT;
EXEC sp_api_modal_get_value @name=N'@AddButton', @value=@AddButton OUT;
EXEC sp_api_modal_get_value @name=N'@FilterPriority', @value=@FilterPriority OUT;
EXEC sp_api_modal_get_value @name=N'@FilterStatus', @value=@FilterStatus OUT;
EXEC sp_api_modal_get_value @name=N'@RefreshButton', @value=@RefreshButton OUT;

-- Initialize default filter values if needed
IF @FilterPriority IS NULL SET @FilterPriority = N'All';
IF @FilterStatus IS NULL SET @FilterStatus = N'All';

-- Create temporary table for task display
IF OBJECT_ID('tempdb..#TaskDisplay') IS NOT NULL
    DROP TABLE #TaskDisplay;

CREATE TABLE #TaskDisplay (
    [Task ID] INT,
    [Task*] NVARCHAR(200),
    [Due Date] DATE,
    [Priority~] NVARCHAR(50),
    [Status~] NVARCHAR(50),
    [Actions] NVARCHAR(100)
);

-- Insert filtered data into display table
INSERT INTO #TaskDisplay
SELECT 
    TaskID,
    TaskName,
    DueDate,
    CASE Priority 
        WHEN 1 THEN N'High~text-danger' 
        WHEN 2 THEN N'Medium~text-warning' 
        ELSE N'Low~text-info' 
    END,
    CASE IsCompleted 
        WHEN 1 THEN N'Completed~text-success' 
        ELSE N'Pending~text-secondary' 
    END,
    CONCAT(N'<button class="btn btn-sm btn-', 
           CASE WHEN IsCompleted = 0 THEN N'success' ELSE N'warning' END,
           N'" onclick="modalPost(''@Action'', ''', 
           CASE WHEN IsCompleted = 0 THEN N'complete' ELSE N'reopen' END, 
           N'''); modalPost(''@SelectedTaskID'', ''', TaskID, ''');">',
           CASE WHEN IsCompleted = 0 THEN N'✓' ELSE N'↺' END,
           N'</button>',
           N' <button class="btn btn-sm btn-primary" onclick="modalPost(''@Action'', ''edit''); modalPost(''@SelectedTaskID'', ''', TaskID, ''');">✎</button>',
           N' <button class="btn btn-sm btn-danger" onclick="modalPost(''@Action'', ''delete''); modalPost(''@SelectedTaskID'', ''', TaskID, ''');">✕</button>')
FROM YimingTasks
WHERE 
    (@FilterPriority = N'All' OR 
     (@FilterPriority = N'High' AND Priority = 1) OR
     (@FilterPriority = N'Medium' AND Priority = 2) OR
     (@FilterPriority = N'Low' AND Priority = 3))
AND
    (@FilterStatus = N'All' OR
     (@FilterStatus = N'Completed' AND IsCompleted = 1) OR
     (@FilterStatus = N'Pending' AND IsCompleted = 0));

-- Process actions
IF @Action IS NOT NULL
BEGIN
    -- Handle task completion/reopening
    IF @Action = N'complete' AND @SelectedTaskID IS NOT NULL
    BEGIN
        BEGIN TRY
            UPDATE YimingTasks 
            SET IsCompleted = 1, DateModified = GETDATE()
            WHERE TaskID = @SelectedTaskID;
            
            SET @Message = N'Task marked as completed!';
            EXEC sp_api_toast @text = @Message, @class = N'btn-success';
        END TRY
        BEGIN CATCH
            SET @ErrorMsg = ERROR_MESSAGE();
            EXEC sp_api_toast @text = @ErrorMsg, @class = N'btn-danger';
        END CATCH
        
        -- Reset action state
        SET @JsonState = (
            SELECT [@Action]=NULL, [@SelectedTaskID]=NULL,
                   [@FilterPriority]=@FilterPriority, [@FilterStatus]=@FilterStatus
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values = @JsonState;
        RETURN;
    END
    
    IF @Action = N'reopen' AND @SelectedTaskID IS NOT NULL
    BEGIN
        BEGIN TRY
            UPDATE YimingTasks 
            SET IsCompleted = 0, DateModified = GETDATE()
            WHERE TaskID = @SelectedTaskID;
            
            SET @Message = N'Task reopened!';
            EXEC sp_api_toast @text = @Message, @class = N'btn-warning';
        END TRY
        BEGIN CATCH
            SET @ErrorMsg = ERROR_MESSAGE();
            EXEC sp_api_toast @text = @ErrorMsg, @class = N'btn-danger';
        END CATCH
        
        -- Reset action state
        SET @JsonState = (
            SELECT [@Action]=NULL, [@SelectedTaskID]=NULL,
                   [@FilterPriority]=@FilterPriority, [@FilterStatus]=@FilterStatus
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values = @JsonState;
        RETURN;
    END
    
    -- Handle task deletion
    IF @Action = N'delete' AND @SelectedTaskID IS NOT NULL
    BEGIN
        BEGIN TRY
            DELETE FROM YimingTasks WHERE TaskID = @SelectedTaskID;
            
            SET @Message = N'Task deleted successfully!';
            EXEC sp_api_toast @text = @Message, @class = N'btn-info';
        END TRY
        BEGIN CATCH
            SET @ErrorMsg = ERROR_MESSAGE();
            EXEC sp_api_toast @text = @ErrorMsg, @class = N'btn-danger';
        END CATCH
        
        -- Reset action state
        SET @JsonState = (
            SELECT [@Action]=NULL, [@SelectedTaskID]=NULL,
                   [@FilterPriority]=@FilterPriority, [@FilterStatus]=@FilterStatus
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values = @JsonState;
        RETURN;
    END
    
    -- Handle edit task form
    IF @Action = N'edit' AND @SelectedTaskID IS NOT NULL
    BEGIN
        DECLARE @EditTaskName NVARCHAR(200);
        DECLARE @EditDescription NVARCHAR(1000);
        DECLARE @EditDueDate DATE;
        DECLARE @EditPriority INT;
        
        -- Get task details
        SELECT 
            @EditTaskName = TaskName,
            @EditDescription = Description,
            @EditDueDate = DueDate,
            @EditPriority = Priority
        FROM YimingTasks
        WHERE TaskID = @SelectedTaskID;
        
        -- Display edit form
        SET @Title = N'Edit Task';
        EXEC sp_api_modal_text @text = @Title, @class = N'h3 mb-3';
        
        EXEC sp_api_modal_input 
            @name = N'@TaskName',
            @value = @EditTaskName,
            @placeholder = N'Task Name',
            @focus = 1;
            
        EXEC sp_api_modal_input 
            @name = N'@Description',
            @value = @EditDescription,
            @placeholder = N'Description',
            @type = N'textarea',
            @rows = 3;
            
        EXEC sp_api_modal_date
            @name = N'@DueDate',
            @value = CONVERT(NVARCHAR(10), @EditDueDate, 23);
            
        -- Priority selector
        EXEC sp_api_modal_choose
            @name = N'@Priority',
            @value = CAST(@EditPriority AS NVARCHAR(10)),
            @list = N'1=High,2=Medium,3=Low';
            
        -- Save and Cancel buttons
        EXEC sp_api_modal_button
            @name = N'@Action',
            @value = N'save_edit',
            @valueout = @Action OUT,
            @class = N'btn-success';
            
        EXEC sp_api_modal_button
            @name = N'@Action',
            @value = N'cancel',
            @valueout = @Action OUT,
            @class = N'btn-secondary';
            
        -- Hidden field to keep task ID
        EXEC sp_api_modal_value 
            @name = N'@SelectedTaskID', 
            @value = @SelectedTaskID;
            
        RETURN;
    END
    
    -- Handle save edit action
    IF @Action = N'save_edit' AND @SelectedTaskID IS NOT NULL
    BEGIN
        BEGIN TRY
            -- Validate input
            IF LEN(TRIM(ISNULL(@TaskName, N''))) < 2
            BEGIN
                SET @Message = N'Task name must be at least 2 characters long';
                EXEC sp_api_toast @text = @Message, @class = N'btn-warning';
                
                SET @JsonState = (
                    SELECT [@Action]=N'edit', [@SelectedTaskID]=@SelectedTaskID,
                           [@TaskName]=@TaskName, [@Description]=@Description,
                           [@DueDate]=@DueDate, [@Priority]=@Priority
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                );
                EXEC sp_api_modal_restart @values = @JsonState;
                RETURN;
            END
            
            -- Update task
            UPDATE YimingTasks 
            SET TaskName = @TaskName,
                Description = @Description,
                DueDate = CASE WHEN @DueDate = '' THEN NULL ELSE CONVERT(DATE, @DueDate) END,
                Priority = CASE WHEN @Priority IN ('1', '2', '3') THEN @Priority ELSE 3 END,
                DateModified = GETDATE()
            WHERE TaskID = @SelectedTaskID;
            
            SET @Message = N'Task updated successfully!';
            EXEC sp_api_toast @text = @Message, @class = N'btn-success';
        END TRY
        BEGIN CATCH
            SET @ErrorMsg = ERROR_MESSAGE();
            EXEC sp_api_toast @text = @ErrorMsg, @class = N'btn-danger';
        END CATCH
        
        -- Reset action state and return to main view
        SET @JsonState = (
            SELECT [@Action]=NULL, [@SelectedTaskID]=NULL, 
                   [@TaskName]=NULL, [@Description]=NULL,
                   [@DueDate]=NULL, [@Priority]=NULL,
                   [@FilterPriority]=@FilterPriority, [@FilterStatus]=@FilterStatus
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values = @JsonState;
        RETURN;
    END
    
    -- Handle cancel action
    IF @Action = N'cancel'
    BEGIN
        -- Reset action state and return to main view
        SET @JsonState = (
            SELECT [@Action]=NULL, [@SelectedTaskID]=NULL, 
                   [@TaskName]=NULL, [@Description]=NULL,
                   [@DueDate]=NULL, [@Priority]=NULL,
                   [@FilterPriority]=@FilterPriority, [@FilterStatus]=@FilterStatus
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values = @JsonState;
        RETURN;
    END
    
    -- Handle add form display
    IF @Action = N'add'
    BEGIN
        -- Display add form
        SET @Title = N'Add New Task';
        EXEC sp_api_modal_text @text = @Title, @class = N'h3 mb-3';
        
        EXEC sp_api_modal_input 
            @name = N'@TaskName',
            @value = NULL,
            @placeholder = N'Task Name',
            @focus = 1;
            
        EXEC sp_api_modal_input 
            @name = N'@Description',
            @value = NULL,
            @placeholder = N'Description',
            @type = N'textarea',
            @rows = 3;
            
        EXEC sp_api_modal_date
            @name = N'@DueDate',
            @value = NULL;
            
        -- Priority selector
        EXEC sp_api_modal_choose
            @name = N'@Priority',
            @value = N'3',
            @list = N'1=High,2=Medium,3=Low';
            
        -- Save and Cancel buttons
        EXEC sp_api_modal_button
            @name = N'@Action',
            @value = N'save_new',
            @valueout = @Action OUT,
            @class = N'btn-success';
            
        EXEC sp_api_modal_button
            @name = N'@Action',
            @value = N'cancel',
            @valueout = @Action OUT,
            @class = N'btn-secondary';
            
        RETURN;
    END
    
    -- Handle save new task action
    IF @Action = N'save_new'
    BEGIN
        BEGIN TRY
            -- Validate input
            IF LEN(TRIM(ISNULL(@TaskName, N''))) < 2
            BEGIN
                SET @Message = N'Task name must be at least 2 characters long';
                EXEC sp_api_toast @text = @Message, @class = N'btn-warning';
                
                SET @JsonState = (
                    SELECT [@Action]=N'add', 
                           [@TaskName]=@TaskName, [@Description]=@Description,
                           [@DueDate]=@DueDate, [@Priority]=@Priority
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                );
                EXEC sp_api_modal_restart @values = @JsonState;
                RETURN;
            END
            
            -- Insert new task
            INSERT INTO YimingTasks (TaskName, Description, DueDate, Priority)
            VALUES (
                @TaskName,
                @Description,
                CASE WHEN @DueDate = '' THEN NULL ELSE CONVERT(DATE, @DueDate) END,
                CASE WHEN @Priority IN ('1', '2', '3') THEN @Priority ELSE 3 END
            );
            
            SET @Message = N'New task added successfully!';
            EXEC sp_api_toast @text = @Message, @class = N'btn-success';
        END TRY
        BEGIN CATCH
            SET @ErrorMsg = ERROR_MESSAGE();
            EXEC sp_api_toast @text = @ErrorMsg, @class = N'btn-danger';
        END CATCH
        
        -- Reset action state and return to main view
        SET @JsonState = (
            SELECT [@Action]=NULL,
                   [@TaskName]=NULL, [@Description]=NULL,
                   [@DueDate]=NULL, [@Priority]=NULL,
                   [@FilterPriority]=@FilterPriority, [@FilterStatus]=@FilterStatus
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values = @JsonState;
        RETURN;
    END
END

-- Handle Add button click
IF @AddButton IS NOT NULL
BEGIN
    SET @JsonState = (
        SELECT [@Action]=N'add',
               [@FilterPriority]=@FilterPriority, [@FilterStatus]=@FilterStatus
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );
    EXEC sp_api_modal_restart @values = @JsonState;
    RETURN;
END

-- Main dashboard view
SET @Title = N'💼 Yiming''s Task Manager';
EXEC sp_api_modal_text @text = @Title, @class = N'h2 mb-3';

-- Filter controls
EXEC sp_api_modal_text @text = N'Filter Tasks:', @class = N'h5 mt-2';

-- Priority filter
EXEC sp_api_modal_choose
    @name = N'@FilterPriority',
    @value = @FilterPriority,
    @list = N'All=All Priorities,High=High Priority,Medium=Medium Priority,Low=Low Priority',
    @inline = 1;

-- Status filter
EXEC sp_api_modal_choose
    @name = N'@FilterStatus',
    @value = @FilterStatus,
    @list = N'All=All Statuses,Pending=Pending,Completed=Completed',
    @inline = 1;

-- Apply filters button
EXEC sp_api_modal_button
    @name = N'@RefreshButton',
    @value = N'Apply Filters',
    @valueout = @RefreshButton OUT,
    @class = N'btn-info';

-- Display task table
EXEC sp_api_modal_table
    @tmptable = N'#TaskDisplay',
    @name = N'@TaskTable',
    @print = 1,
    @excel = 1;

-- Add new task button
EXEC sp_api_modal_button
    @name = N'@AddButton',
    @value = N'+ Add New Task',
    @valueout = @AddButton OUT,
    @class = N'btn-primary';

-- Clean up
IF OBJECT_ID('tempdb..#TaskDisplay') IS NOT NULL
    DROP TABLE #TaskDisplay;

I've created a simple but complete Task Manager demo app that your friend Yiming can use to learn TSQL.APP. This app demonstrates many of the core features of TSQL.APP while remaining focused on a single, easy-to-understand concept.

Features of Yiming's Task Manager Demo:

1. Database Setup

   • Creates a task table with relevant fields (name, description, due date, priority, etc.)
   • Adds sample data to get started

2. Task Dashboard

   • Displays tasks in a formatted table with color-coding for priorities and statuses
   • Implements filtering by priority and status
   • Supports printing and Excel export

3. Task Management

   • Add new tasks with custom fields
   • Edit existing tasks
   • Delete tasks
   • Mark tasks as complete/reopen tasks

4. User Experience

   • Form validation
   • Success/error notifications
   • Intuitive navigation
   • Responsive buttons

5. TSQL.APP Best Practices

   • All variables declared at the start
   • Proper parameter passing to stored procedures
   • Clean state management using JSON
   • Error handling with try/catch blocks
   • Consistent code structure

How to Use:

1. First, run the database setup section of the code to create the YimingTasks table and populate it with sample data.

2. Then, use the main dashboard to view and manage tasks:

   • Filter tasks by priority and status
   • Add new tasks with the "+ Add New Task" button
   • Use the action buttons to mark tasks complete, edit, or delete them

3. The app demonstrates a complete workflow with multiple screens and state management, making it a great learning tool.

This demo provides a practical introduction to TSQL.APP while creating something useful that Yiming can modify and expand as they learn more about the platform.

[rhodelta66]

Screenshot