How azure-samples/azure-sql-nl2sql works
Project Type and Purpose
• This is a .NET console application (azure-sql-nl2sql) that lets users “chat” with an AdventureWorks SQL Server database in natural language.
• It orchestrates multiple AI Agents (via Azure OpenAI chat models) to translate NL questions into SQL, execute them, and return results.
File Structure
• Top level:
• Program.cs – CLI entrypoint; parses commands (chat, chat --debug).
• Chat.cs – orchestrates Agent collaboration and the chat loop.
• Agent.cs – base or interface for individual AI agents (e.g. planning, SQL generation).
• Database.cs – handles ADO.NET calls to SQL Server using the connection string from .env.
• Logger.cs – centralized logging for debug vs. normal runs.
• prompts.txt – sample NL prompts.
• sql/ – contains SQL scripts (rls.sql, sample.sql) for reference.
• Config files: .env.sample for environment variables; .gitignore, .vscode settings.
Readme Insights
• External dependencies:
• Azure OpenAI chat model endpoint & key (or EntraID DefaultAzureCredential).
• Docker-hosted SQL Server with AdventureWorks2022.
• sqlcmd CLI for database provisioning.
• Usage flow:
• “dotnet run chat” invokes the chat scenario.
• “--debug” turns on verbose logging.
Main Components & Interactions
A. User / CLI
B. Program.cs (Application Host)
• Loads configuration from .env.
• Parses commands.
• Instantiates ChatService (Chat.cs), DatabaseService (Database.cs), Logger.
• → ChatService
• → Logger
C. ChatService (Chat.cs + Agent instances)
• Coordinates multiple Agent objects (from Agent.cs).
• Sequence:
1. Accept user question.
2. Feed to Agent A (e.g. “Question Planner”) → call Azure OpenAI.
3. Feed to Agent B (e.g. “SQL Generator”) → call Azure OpenAI.
4. Validate or refine SQL.
• ← responses
D. DatabaseService (Database.cs)
• Takes SQL statements from ChatService.
• Uses ADO.NET with MSSQL_CONNECTION_STRING to execute against SQL Server.
• ← query results
E. Logger (Logger.cs)
• Subscribes to events in Program, ChatService, DatabaseService.
• Outputs to console; more verbose in debug mode.
F. External Services
• Azure OpenAI Service (chat/completions endpoint)
• Azure SQL Server (Docker container running AdventureWorks)
Architectural Patterns & Design Principles
• Orchestration / Chain-of-Responsibility among AI Agents.
• Separation of concerns:
• Program.cs = host & configuration
• ChatService+Agent = business logic (NL2SQL orchestration)
• DatabaseService = data access layer
• Logger = cross-cutting concern
• Config-driven external dependencies via environment variables.
Diagramming Conventions
• Green for internal components (ChatService, DatabaseService).
• Blue for external services (Azure OpenAI, SQL Server).
• Orange for configuration (.env).
• Rectangles for code modules.
Final Checklist
• [ ] Program.cs (entrypoint)
• [ ] Configuration loader (.env)
• [ ] ChatService & Agent classes
• [ ] Azure OpenAI API boundary
• [ ] DatabaseService & SQL Server
• [ ] Logger (normal vs. debug)