DEV Community: Rick Hoek

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

Rick Hoek — Fri, 31 Jan 2025 14:49:40 +0000

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.

Turn SQL Server into an Application Server with "TSQL.APP"(hot🌡️)

Rick Hoek — Fri, 24 Jan 2025 22:08:34 +0000

T-SQL dot app: A Revolutionary Approach to Web Application Development

Introduction

T-SQL dot app is an innovative skeleton web application with a built-in Integrated Development Environment (IDE), designed to work entirely data-driven based on T-SQL, Microsoft SQL Server's programming language. The platform enables developers to build complete web applications by writing T-SQL code exclusively, eliminating the need to master multiple programming languages and frameworks.

Here you can listen to the podcast that will give you a complete picture of the unique power of our TSQL.APP framework in just 10 minutes.

Listen to the Podcast here

What is T-SQL dot app?

Core Features

Fully Data-driven Development: All application logic and user interfaces are defined within the database, enabling direct and efficient data access.
Built-in IDE with Monaco Editor: Write, test, and debug T-SQL code directly in the browser with advanced coding features like syntax highlighting and code auto-completion.
Data-driven React Client: A fully functional frontend built with React, automatically generated based on data and T-SQL code.
Comprehensive Functionality: Built-in support for authentication, authorization, role management, server-side processing, task processing, mail server, FTP, and file management.
Rich Set of Stored Procedures and Functions: Over 400 built-in procedures and functions capable of fulfilling virtually any business need via action code.
Custom GPT Support: A custom-trained GPT model helps developers generate action code and provides support within the platform, accessible via https://t-sql.app.

What can T-SQL dot app be used for?

1. Business Applications

Enterprise Resource Planning (ERP): Manage business processes such as purchasing, sales, inventory management, and finance within one integrated system.
Customer Relationship Management (CRM): Manage customer data, interactions, and sales opportunities to improve customer relationships.
Inventory Management: Real-time tracking of inventory levels, orders, and deliveries for efficient supply chain management.

2. Data Analysis and Reporting

Internal Dashboards: Create interactive dashboards for real-time monitoring of business performance.
Reporting Tools: Generate custom reports and visualizations directly from the database without additional BI tools.

3. Automation and Workflow

Task Automation: Automate repetitive tasks and processes with the built-in task server.
Workflow Management: Define and manage business processes to promote efficiency and consistency.

4. Industry-Specific Solutions

Produce Trading Sector: Proven applications in the potato, vegetable, and fruit sector, with successful implementations at large companies.
Logistics and Transport: Management of complex logistic processes, including route planning and tracking.

5. Internal Tools and Utilities

File Management: Manage documents and files with built-in FTP and file manager functionality.
User and Role Management: Manage user accounts, roles, and access rights for enhanced security.

Why is T-SQL dot app a Game Changer?

1. Simplicity and Efficiency

Single Language Development: Both frontend and backend can be developed using only T-SQL, reducing the learning curve and development time.
Faster Implementation: By eliminating the need for multiple technologies, applications can be developed and deployed more quickly.

2. Cost Savings

Lower Development and Maintenance Costs: Less dependency on different technologies leads to reduced overhead and lower costs.
Resource Efficiency: Centralization of development within the database reduces the need for additional infrastructure.

3. Complete Data Control

Direct Data Access: All logic is executed within the database, ensuring fast and efficient data and transaction processes.
Enhanced Security: Managing data and application logic within one environment strengthens security and simplifies regulatory compliance.

4. Extensive Out-of-the-Box Functionality

Built-in Business Functions: Features such as authentication, authorization, mail server, and task processing are available by default.
Data-driven UI Generation: User interfaces are automatically generated based on data and T-SQL code, promoting consistency and efficiency.

5. Proven Success in Practice

Large Enterprise Implementations: Multiple large companies in the Dutch produce trading sector use T-SQL dot app, processing hundreds of thousands of transactions and realizing annual revenue exceeding 200 million euros.
Scalability and Reliability: The platform has proven capable of handling large volumes and critical business processes.

6. Innovative Approach to Application Development

Custom GPT Support: The custom-trained GPT model assists developers in writing code, increasing productivity.
Development Centralization: Managing both frontend and backend within the database significantly reduces development process complexity.

Looking for Collaboration and Further Development

T-SQL dot app has proven its value and is now poised to grow further and transform the market. To achieve this, we are looking for interested parties who want to contribute to the further development and commercialization of this groundbreaking technology.

Potential Collaboration Areas

Technical Development: Contribute to expanding functionality, optimizing performance, and improving user experience.
Commercial Exploitation: Develop business models for offering T-SQL dot app as a commercial product or service.
Implementation Partners: Companies wanting to integrate T-SQL dot app into their own infrastructure or provide solutions to their customers.
Support and Training: Provide services such as technical support, training, and consultancy to stimulate the use and adoption of T-SQL dot app.

Why Join?

Innovation Leadership Role: Participate in the forefront of technological innovation in web application development.
Market Potential: Benefit from a growing need for efficient and cost-effective development solutions.
Collaboration with Experienced Teams: Work with experts who have already achieved success with T-SQL dot app in critical business environments.
Industry Impact: Help redefine industry standards and best practices.

Conclusion

T-SQL dot app is more than a tool; it's a revolution in how web applications are developed and deployed. By harnessing the power of T-SQL and centralizing development within the database, the platform offers unparalleled efficiency, scalability, and cost savings.

With proven successes at large companies and a wide range of applications, T-SQL dot app is ready to transform the market. We invite you to be part of this exciting journey and help shape the future of application development together.

Interested?

Contact us today to discover how you can contribute to and benefit from the growth and opportunities that T-SQL dot app has to offer.

Contact Details:

Website: https://t-sql.app
Email: info@t-sql.app
Address: [Add physical address if desired]

Together we can redefine the future of web application development with T-SQL dot app as a catalyst for change.

/*
We have built it. Technically it is a data-driven stack containing MICROSOFT SQL SERVER, .NET, NODE AND REACT, But we have made it completely data-driven, so the ONLY language you have to use is T-SQL. For everything, UI Modal Dialogs CRUD apps, functions, actions, reports. like so:
*/

-- learn from the TSQL.APP Assistent at https://tsql.app
EXEC sp_api_modal_text @text=N'TSQL.APP Choose Example',@class='h2'
DECLARE @var01 
        NVARCHAR(MAX); EXEC sp_api_modal_get_value @name='@var01', @value=@var01 OUT;   
DECLARE @selected_ids nvarchar(128)
DECLARE @T nvarchar(max)

DECLARE @switch_1 nvarchar(max); EXEC sp_api_modal_get_value @name='@switch_1',@value = @switch_1 OUT 
EXEC sp_api_modal_switch @name = '@switch_1', @value = @switch_1 OUT,@label = N'Single / Multi', @direct_post = 1
DECLARE @multi bit = case when isnull(@switch_1,'false')='false' then 0 else 1 end

DECLARE @switch_2 nvarchar(max); EXEC sp_api_modal_get_value @name='@switch_2',@value = @switch_2 OUT 
EXEC sp_api_modal_switch @name = '@switch_2', @value = @switch_2 OUT,@label = N'List / Table', @direct_post = 1
DECLARE @use_table bit = case when isnull(@switch_2,'false')='false' then 0 else 1 end

--exit button (action ends)
DECLARE @exit nvarchar(max); EXEC sp_api_modal_get_value @name='@exit',@value = @exit OUT 
EXEC sp_api_modal_button @name='@exit',@value = 'Exit', @valueout = @exit OUT,@class='btn-danger',@key='Esc'
IF @exit is not NULL
BEGIN
    EXEC sp_api_modal_clear
END 
--prepare table and list
SELECT top 10 id=id, name=name, selected=0  INTO #tmptable FROM api_card order by id DESC
DECLARE @list nvarchar(128)=N'A,B,C,D'

if @use_table =0 -- gebruik dan de list !
BEGIN
    EXEC sp_api_modal_choose 
        @list = @list,
        @value = @var01 OUT 
        --,@name = '@button_choose'
        --,@class = 'btn-outline-danger'
        --,@class_updated = 'btn-danger'
        --,@startkey = 1 -- 0 = no keys or NULL = first character of text
        --,@keyprefix = 'F'
        ,@multi = @multi --1 --allow multiple values
        --,@tmptable = '#tmptable'
        --,@ids =@selected_ids OUT
        --,@orderby = 'ORDER BY name'
        --,@inline = 1
        --,@reducer = NULL
        --,@reset_values = N'@button,lastname,etc' -- onchange, reset other values
    SET @T=concat('selected: ',@var01)
    EXEC sp_api_modal_text @T
END

if @use_table =1 -- gebruik dan de table
BEGIN
    EXEC sp_api_modal_choose 
        --@list = @list,
        @value = @var01 OUT 
        --,@name = '@button_choose'
        --,@class = 'btn-outline-danger'
        --,@class_updated = 'btn-danger'
        --,@startkey = 1 -- 0 = no keys or NULL = first character of text
        --,@keyprefix = 'F'
        ,@multi = @multi --1 --allow multiple values
        ,@tmptable = '#tmptable'
        --,@ids =@selected_ids OUT
        --,@orderby = 'ORDER BY name'
        --,@inline = 1
        --,@reducer = NULL
        --,@reset_values = N'@button,lastname,etc' -- onchange, reset other values
    SET @T=concat('selected: ',@var01)
    EXEC sp_api_modal_text @T   
    EXEC sp_api_modal_table @tmptable=N'#tmptable'
END

-- Add 'Quit' button
exec sp_api_modal_text N'click Quit/Exit button to end the action.'
DECLARE @ButtonQuit nvarchar(128)
EXEC sp_api_modal_get_value @name='@ButtonQuit', @value=@ButtonQuit OUT;
EXEC sp_api_modal_button 
    @name='quit',
    @value='Quit this Action', 
    @class='btn-secondary', 
    @valueout=@ButtonQuit OUT;

IF @ButtonQuit IS NOT NULL
BEGIN
    --SET @Quit = 1;
    EXEC sp_api_modal_clear;
    RETURN;
END

Teaching my AI all about using our TSQL.APP Framework that turns SQL Server into an Application Server

Rick Hoek — Fri, 24 Jan 2025 21:09:55 +0000