Text2Sql.Net

[English](./README.en.md) ## Text2Sql.Net - Natural Language to SQL .NET Implementation ### Project Background Text2Sql.Net is a natural language to SQL tool based on the .NET platform, designed to help developers and data analysts quickly generate database query statements through simple natural language descriptions. The project combines large language models (LLM) and traditional SQL parsing techniques, supporting multiple mainstream databases. ### Core Features - Natural Language to SQL: Input daily language descriptions and automatically generate corresponding SQL query statements - Multi-Database Support: Compatible with SQL Server, MySQL, PostgreSQL, and SQLite - Intelligent Context Understanding: Understand user query intentions based on chat history - Vector Search Integration: Supports semantic similarity search - Syntax Verification: Automatically checks the correctness of generated SQL syntax - **MCP Protocol Support**: Seamless integration with IDE tools (Cursor, Trae, etc.) - **Intelligent Question Answering Example System**: Improve SQL generation accuracy through example learning ## Technical Architecture      Configuration file. The project supports running with sqlite or pgsql, and supports configuring SqlService, MySql, PgSql, and Sqlite for Text2Sql ``` "Text2SqlOpenAI": { "Key": "你的秘钥", "EndPoint": "https://api.antsk.cn/", "ChatModel": "gpt-4o", "EmbeddingModel": "text-embedding-ada-002" }, "Text2SqlConnection": { "DbType": "Sqlite", //PostgreSQL "DBConnection": "Data Source=text2sql.db", "VectorConnection": "text2sqlmem.db", "VectorSize": 1536 //PostgreSQL需要设置，sqlite可以不设置 } ``` You are also welcome to join our WeChat exchange group. You can add my WeChat: **xuzeyu91** and send a message to join the group. ### Core Modules **Database Adapter Layer** **Vector Database Integration** - Implemented multi-database support based on the strategy pattern - Define standard operations through the IDatabaseProvider interface - Dynamically load corresponding database drivers (SQLite/Postgres/MySql/SqlServer) - Automatically generate SQL statements in database-specific dialects ## Core Processing Flow ```mermaid flowchart TD A[User Input Natural Language Query] --> B{Select Database Connection} B -->|Not Selected| C[Prompt to Select Database] B -->|Selected| D[Save User Message to Chat History] D --> E[Semantic Search for Relevant Schema] E --> F[Vector Database Query] F --> G[Relevance Scoring and Table Association Inference] G --> H[Build Schema Context] H --> I[Call LLM to Generate SQL] I --> J[Use Semantic Kernel Plugin] J --> K[SQL Security Check] K -->|Query Statement| L[Automatically Execute SQL] K -->|Operation Statement| M[Generate SQL Only<br/>Do Not Execute Automatically] L --> N{Execution Successful?} N -->|Success| O[Return Query Results] N -->|Failure| P[SQL Optimization] P --> Q[Optimize SQL Using Error Information] Q --> R[Re-execute Optimized SQL] R --> S[Return Final Results] M --> T[Prompt Manual Execution] O --> U[Save Response to Chat History] S --> U T --> U U --> V[Display Results to User] style A fill:#e1f5fe style V fill:#e8f5e8 style K fill:#fff3e0 style P fill:#fce4ec ``` ## Schema Training and Vector Search Flow ```mermaid flowchart TD A[Database Connection Configuration] --> B[Schema Training Service] B --> C[Extract Database Table Structure] C --> D[Get Table/Column/Foreign Key Information] D --> E[Generate Table Description Text] E --> F[Text Vectorization] F --> G[Store to Vector Database] G --> H{Vector Storage Type} H -->|SQLite| I[SQLiteMemoryStore] H -->|PostgreSQL| J[PostgresMemoryStore with pgvector] I --> K[Schema Training Complete] J --> K K --> L[Wait for User Query] L --> M[Semantic Search] M --> N[Relevance Matching] N --> O[Return Relevant Table Structure] style A fill:#e3f2fd style F fill:#f3e5f5 style G fill:#e8f5e8 style M fill:#fff3e0 ``` ## System Architecture Diagram ```mermaid flowchart LR subgraph "User Interface Layer" A[Blazor Front-End Page] B[Database Connection Selection] C[Chat Input Box] D[SQL Result Display] end subgraph "Service Layer" E[ChatService<br/>Chat Service] F[SchemaTrainingService<br/>Schema Training Service] G[SemanticService<br/>Semantic Service] H[SqlExecutionService<br/>SQL Execution Service] I[QAExampleService<br/>QA Example Service] J[McpServer<br/>MCP Protocol Server] end subgraph "Data Access Layer" K[DatabaseConnectionRepository<br/>Database Connection Repository] L[ChatMessageRepository<br/>Chat Message Repository] M[DatabaseSchemaRepository<br/>Schema Repository] N[SchemaEmbeddingRepository<br/>Vector Embedding Repository] O[QAExampleRepository<br/>QA Example Repository] end subgraph "External Services" P[OpenAI API<br/>LLM Service] Q[Vector Database<br/>SQLite/PostgreSQL] R[Business Database<br/>Multiple Database Support] S[MCP Client<br/>IDE Tool Integration] end A --> E B --> K C --> E D --> H E --> F E --> G E --> H E --> L E --> I F --> M F --> N G --> Q H --> K H --> R I --> O J --> S E --> P G --> P style A fill:#e1f5fe style E fill:#f3e5f5 style P fill:#fff3e0 style Q fill:#e8f5e8 style J fill:#fce4ec style I fill:#e3f2fd ``` ## 🔧 MCP Protocol Integration ### Model Context Protocol (MCP) Support Text2Sql.Net integrates the Model Context Protocol and can be used as an **MCP Server** to provide Text2SQL functionality for various AI development tools. #### Supported MCP Tools - `get_database_connections`: Get all database connection configurations - `get_database_schema`: Get database table structure information - `generate_sql`: Generate SQL queries based on natural language - `execute_sql`: Execute SQL query statements - `get_chat_history`: Get chat history records - `get_table_structure`: Get the detailed structure of the specified table - `get_all_tables`: Get all table information #### IDE Integration Configuration In MCP-enabled IDEs (such as Cursor, Trae, etc.), you can connect to Text2Sql.Net through the following configuration: ```json { "mcpServers": { "text2sql": { "name": "Text2Sql.Net - sqlserver", "type": "sse", "description": "Intelligent Text2SQL service. Supports natural language to SQL queries. Compatible with Cursor, Trae, etc.", "isActive": true, "url": "http://localhost:5000/mcp/sse?connectionId=xxxxxx" } } } ``` After the configuration is complete, you can directly interact with the database using natural language in the IDE: - "Show the structure of all user tables" - "Query order data for the last week" - "Count the number of products in each category" ### MCP Usage Scenarios 1. **Code Development**: Quickly generate data query code in the IDE 2. **Data Analysis**: Quickly explore data through natural language 3. **Report Generation**: Quickly build complex statistical queries 4. **System Integration**: Integrate Text2SQL capabilities into other toolchains ## 📚 Intelligent Question Answering Example System ### QA Example Function Text2Sql.Net provides an intelligent question answering example management system, which improves the accuracy of SQL generation by learning and accumulating examples. #### Core Features - **Example Management**: Supports manually creating and correcting generated question answering examples - **Semantic Search**: Matches relevant examples based on vector similarity - **Classification Organization**: Supports classifications such as basic queries, complex queries, and aggregate queries - **Usage Statistics**: Tracks the usage frequency and effect of examples - **Batch Operations**: Supports batch enabling, disabling, and deleting examples #### Example Category Description - **Basic Queries**: Simple SELECT statements and basic filtering - **Complex Queries**: Complex scenarios such as multi-table joins and subqueries - **Aggregate Queries**: Includes aggregate functions such as GROUP BY, SUM, and COUNT - **Join Queries**: Multi-table JOIN operations - **Corrected Examples**: Examples generated after correcting from incorrect SQL #### Intelligent Matching Mechanism When a user enters a query, the system will: 1. Vectorize the user's question 2. Perform a semantic search in the example library 3. Return the most relevant example (default relevance threshold 0.7) 4. Provide the relevant example as context to the LLM 5. Update example usage statistics #### Example Format ```json { "question": "Query the number of active users in the last month", "sqlQuery": "SELECT COUNT(DISTINCT user_id) FROM user_activities WHERE activity_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)", "category": "aggregate", "description": "Statistics on the number of unique users with activity records in the last 30 days" } ``` ### Example Creation Methods 1. **Manual Creation**: Directly add question answering pairs in the management interface 2. **Corrected Generation**: When SQL generation is incorrect, automatically create an example after the user corrects it 3. **Batch Import**: Supports batch generation of examples from existing query history ## More Rag Scenarios Can Be Viewed at AntSK Project address: [AntSK](https://github.com/AIDotNet/AntSK) Experience environment: [Demo Address](https://demo.antsk.cn) Account: test Password: test You are also welcome to join our WeChat exchange group. You can add my WeChat: **antskpro** and send a message to join the group.