Rick Hoek

Posted on Jan 31

ℹ️REVOLUTION!ℹ️ T-SQL.APP Framework Podcast🎙️

#sqlserver #sql

T-SQL Dot App: Comprehensive Framework Guide and Analysis

Introduction

Think of T-SQL Dot App as a universal toolbox for web development - like having a single tool that can handle nearly every job when building a house. Instead of constantly switching between different tools and approaches, T-SQL Dot App unifies most development work into one environment (SQL Server) while automating much of the heavy lifting.

INSTANT RESULTS

The New Paradigm

Traditional web applications treat SQL Server merely as a data store - like a chef who only supplies ingredients. T-SQL Dot App inverts this approach, making SQL Server the primary engine of your application - now the chef also handles plating, presentation, and final touches, reducing the need for multiple "cooks" in different kitchens (front-end, back-end, etc.).

Real-World Validation

T-SQL Dot App has proven itself in demanding enterprise environments:

Powers operations for large import/export firms (€100M+ turnover)
Successfully running ERP and WMS solutions for 4+ years
Handles complex, high-volume transactions in perishable goods industry
Demonstrates enterprise-grade reliability and scalability

Core Features

1. SQL-First Development Approach

Build complete applications using primarily SQL Server and T-SQL
Create full-featured applications directly from database
Eliminate separate frontend/backend codebases
Centralize business logic and data integrity

2. Built-in UI Generation

Think of this like a 3D printer for your user interface:

Automatic conversion of database tables to "cards" (UI views)
UI elements controlled through stored procedures
400+ built-in UI stored procedures
Auto-generated forms, CRUD operations, and modals
Get a working, modern, mobile-responsive interface instantly
Save enormous time typically spent on building frontend from scratch

3. Enterprise-Ready Features

Role-based access control
Business process management
Audit trailing
Data change tracking
Multi-language support
External API integrations

4. Modern Architecture

Think of it as having a classic, reliable engine (SQL Server) under the hood, but with all the modern comforts in the driver's seat:

ReactJS frontend for modern UI/UX
.NET Core backend enabling cross-platform deployment
Cross-platform support (Windows/Linux)
Mobile-responsive design out of the box
REST API support for integration needs
Best of both worlds: traditional reliability with modern capabilities

Industry-Specific Achievements

Perishable Goods Sector

Successfully manages unique requirements in fruit and vegetable import/export:

Traceability:
- Built-in audit trails
- Data change tracking
- Compliance with food safety regulations
Rapid Adaptation:
- Quick response to market changes
- Tariff adjustment handling
- Supply chain disruption management
Global Operations:
- Multi-language support
- International trade documentation
- Cross-border compliance

Enterprise Resource Planning (ERP) Implementation

Demonstrates capability in complex business systems:

Robustness:
- Complex business rules handling
- Multi-tenant data management
- Real-time updates
Performance:
- Large dataset management
- Perishable goods tracking
- Cross-border compliance
Integration Capabilities:
- Customs API connectivity
- IoT sensor integration (cold chain)
- Third-party system compatibility

Implementation Example: Multi-Step Forms

Key Implementation Patterns

State Management
- JSON-based state handling
- Use of sp_api_modal_restart procedure
- FOR JSON PATH pattern for state transitions
Navigation
- Bidirectional navigation (forward/back)
- State preservation across steps
- Seamless user experience
Modal Components
- sp_api_modal_text for headings/messages
- sp_api_modal_input for form fields
- sp_api_modal_button for navigation
- sp_api_toast for user feedback

Example Code

-- T-SQL Dot App Multi-Step Forms Implementation


DECLARE @Step INT;
DECLARE @Name NVARCHAR(100);
DECLARE @Email NVARCHAR(100);
DECLARE @Confirm BIT;
DECLARE @NextButton NVARCHAR(100);
DECLARE @BackButton NVARCHAR(100);
DECLARE @Title NVARCHAR(MAX) = N'User Registration';
DECLARE @StepTitle NVARCHAR(MAX);
DECLARE @Message NVARCHAR(MAX);
DECLARE @SubmitButton NVARCHAR(100);

EXEC sp_api_modal_get_value @name='@Step', @value=@Step OUT;
EXEC sp_api_modal_get_value @name='@Name', @value=@Name OUT;
EXEC sp_api_modal_get_value @name='@Email', @value=@Email OUT;
EXEC sp_api_modal_get_value @name='@Confirm', @value=@Confirm OUT;

IF @Step IS NULL SET @Step = 1;

IF @Step = 1
BEGIN
    SET @StepTitle = CONCAT(@Title, N' - Step 1');
    EXEC sp_api_modal_text @text=@StepTitle, @class='h3';
    EXEC sp_api_modal_input @name='@Name', @value=@Name OUT, @placeholder=N'Enter name';
    EXEC sp_api_modal_button @name='@NextButton', @value=N'Next', @valueout=@NextButton OUT;

    IF @NextButton IS NOT NULL AND @Name IS NOT NULL
    BEGIN
        SET @Message = CONCAT(N'Welcome ', @Name, N'! 👋');
        EXEC sp_api_toast @text=@Message, @class='btn-success';

        DECLARE @step2Json NVARCHAR(MAX) = (
            SELECT [@Step]=2, [@Name]=@Name 
                , [@Email]=@Email 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@step2Json;
        RETURN;
    END
END

IF @Step = 2
BEGIN
    SET @StepTitle = CONCAT(@Title, N' - Step 2');
    EXEC sp_api_modal_text @text=@StepTitle, @class='h3';
    EXEC sp_api_modal_input @name='@Email', @value=@Email OUT, @placeholder=N'Enter email';
    EXEC sp_api_modal_button @name='@BackButton', @value=N'Back', @valueout=@BackButton OUT;
    EXEC sp_api_modal_button @name='@NextButton', @value=N'Next', @valueout=@NextButton OUT;

    IF @BackButton IS NOT NULL
    BEGIN
        DECLARE @backToStep1Json NVARCHAR(MAX) = (
            SELECT [@Step]=1, [@Name]=@Name 
                , [@Email]=@Email 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@backToStep1Json;
        RETURN;
    END

    IF @NextButton IS NOT NULL AND @Email IS NOT NULL
    BEGIN
        SET @Message = N'Email saved! 📧';
        EXEC sp_api_toast @text=@Message, @class='btn-success';

        DECLARE @step3Json NVARCHAR(MAX) = (
            SELECT [@Step]=3, [@Name]=@Name, [@Email]=@Email 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@step3Json;
        RETURN;
    END
END

IF @Step = 3
BEGIN
    SET @StepTitle = CONCAT(@Title, N' - Review');
    EXEC sp_api_modal_text @text=@StepTitle, @class='h3';

    SET @Message = CONCAT(N'Name: ', @Name);
    EXEC sp_api_modal_text @text=@Message;

    SET @Message = CONCAT(N'Email: ', @Email);
    EXEC sp_api_modal_text @text=@Message;

    EXEC sp_api_modal_button @name='@BackButton', @value=N'Back', @valueout=@BackButton OUT;
    EXEC sp_api_modal_button @name='@SubmitButton', @value=N'Submit', @valueout=@SubmitButton OUT;

    IF @BackButton IS NOT NULL
    BEGIN
        DECLARE @backToStep2Json NVARCHAR(MAX) = (
            SELECT [@Step]=2, [@Name]=@Name, [@Email]=@Email 
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
        EXEC sp_api_modal_restart @values=@backToStep2Json;
        RETURN;
    END

    IF @SubmitButton IS NOT NULL
    BEGIN
        SET @Message = N'Registration complete! ✨';
        EXEC sp_api_toast @text=@Message, @class='btn-success';
        EXEC sp_api_modal_clear;
        RETURN;
    END
END


--Looking at this code, there are a few interesting patterns to note:

--State Management: The code demonstrates proper multi-step form handling in T-SQL Dot App using JSON state management. It maintains state between steps using the sp_api_modal_restart procedure with JSON values, following the required FOR JSON PATH pattern.
--Step Navigation: It implements bidirectional navigation (forward/back) while preserving form values, which is a good practice for user experience. Each step transition carries forward all relevant state data.
--Modal Dialog Components: The code uses several T-SQL Dot App modal components:


--sp_api_modal_text for headings and messages
--sp_api_modal_input for form fields
--sp_api_modal_button for navigation
--sp_api_toast for feedback messages


--Code Organization: It follows T-SQL Dot App mandated practices well:


--All variables declared at the start
--Clear step-based logical structure
--Proper state synchronization after declarations
--Consistent use of Unicode (N prefix)

--One notable pattern is how it handles the email value preservation during back navigation:
--sqlCopy[@Email]=@Email --bewaar de email waarde ook bij back
--This ensures the email field state persists even when going backwards, which is a thoughtful user experience detail.
--The code is a good example of proper T-SQL Dot App implementation, adhering to the framework's core principles while creating an interactive multi-step form interface.

Strategic Benefits

Cost Efficiency for Enterprises

Provides advantages over traditional ERP/WMS systems:

Lower licensing costs compared to proprietary platforms
Reduced development time through automation
Decreased maintenance overhead
Single-stack simplicity

Technical Debt Reduction

Eliminates common enterprise challenges:

No fragmented microservices
No frontend/backend sync issues
No ORM bottlenecks
Simplified architecture

Risk Mitigation

Compliance:
- Built-in auditing for regulatory requirements
- Comprehensive data tracking
- Security controls
Business Continuity:
- SQL Server reliability
- Modern frontend durability (React)
- Maintainable architecture

Best-Fit Scenarios

1. SQL Server-Dependent Organizations

Ideal for modernizing existing SQL Server investments
Leverages current team expertise
Minimizes retraining needs

2. Rapid Application Development (RAD)

Perfect for MVP creation
Efficient for internal tool development
Supports quick iteration cycles

3. Compliance-Driven Industries

Healthcare sector applications
Financial services systems
Government agency solutions
Import/export operations
Any compliance-heavy environment

Infrastructure Benefits

Runs efficiently on SQL Server Express
Tiny house approach - all necessities built in
No sprawling infrastructure needed
Simpler deployments and lower hosting costs
Fewer moving parts to maintain

Conclusion

T-SQL Dot App represents a paradigm shift in web application development, proven through real-world implementations in demanding enterprise environments. Its success in complex industries like perishable goods import/export validates its capability to handle mission-critical operations while maintaining compliance and performance requirements.

The framework's unique approach offers:

Proven enterprise capability
Operational efficiency
Cost effectiveness
Technical simplicity
Rapid development
Regulatory compliance

For organizations looking to modernize their operations while leveraging existing SQL Server expertise, T-SQL Dot App provides a compelling alternative to traditional multi-tier architectures and expensive ERP systems. Its track record in handling complex, high-volume operations in regulated industries demonstrates its maturity and reliability as an enterprise-grade platform.

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →

DEV Community