Content
| [English](./README.en.md)
## Text2Sql.Net - .NET Implementation of Natural Language to SQL
### 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-based searches
- Syntax Validation: 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
<img src="https://github.com/AIDotNet/Text2Sql.Net/blob/main/doc/index.png?raw=true" alt="index">
<img src="https://github.com/AIDotNet/Text2Sql.Net/blob/main/doc/db.png?raw=true" alt="db">
<img src="https://github.com/AIDotNet/Text2Sql.Net/blob/main/doc/schecm.png?raw=true" alt="schecm">
<img src="https://github.com/AIDotNet/Text2Sql.Net/blob/main/doc/demo.png?raw=true" alt="demo">
<img src="https://github.com/AIDotNet/Text2Sql.Net/blob/main/doc/demo1.png?raw=true" alt="demo1">
Configuration file. The project supports running with sqlite or pgsql, and supports configuring SqlService, MySql, PgSql, and Sqlite for Text2Sql.
```json
"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
- Defines standard operations through the IDatabaseProvider interface
- Dynamically loads corresponding database drivers (SQLite/Postgres/MySql/SqlServer)
- Automatically generates SQL statements in the specific dialect of the database
## Core Processing Flow
```mermaid
flowchart TD
A[用户输入自然语言查询] --> B{选择数据库连接}
B -->|未选择| C[提示选择数据库]
B -->|已选择| D[保存用户消息到聊天历史]
D --> E[语义搜索获取相关Schema]
E --> F[向量数据库查询]
F --> G[相关性评分与表关联推断]
G --> H[构建Schema上下文]
H --> I[调用LLM生成SQL]
I --> J[使用Semantic Kernel插件]
J --> K[SQL安全检查]
K -->|查询语句| L[自动执行SQL]
K -->|操作语句| M[仅生成SQL<br/>不自动执行]
L --> N{执行是否成功}
N -->|成功| O[返回查询结果]
N -->|失败| P[SQL优化]
P --> Q[使用错误信息优化SQL]
Q --> R[重新执行优化后SQL]
R --> S[返回最终结果]
M --> T[提示手动执行]
O --> U[保存响应到聊天历史]
S --> U
T --> U
U --> V[显示结果给用户]
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[数据库连接配置] --> B[Schema训练服务]
B --> C[提取数据库表结构]
C --> D[获取表/列/外键信息]
D --> E[生成表描述文本]
E --> F[文本向量化]
F --> G[存储到向量数据库]
G --> H{向量存储类型}
H -->|SQLite| I[SQLiteMemoryStore]
H -->|PostgreSQL| J[PostgresMemoryStore with pgvector]
I --> K[Schema训练完成]
J --> K
K --> L[等待用户查询]
L --> M[语义搜索]
M --> N[相关性匹配]
N --> O[返回相关表结构]
style A fill:#e3f2fd
style F fill:#f3e5f5
style G fill:#e8f5e8
style M fill:#fff3e0
```
## System Architecture Diagram
```mermaid
flowchart LR
subgraph "用户界面层"
A[Blazor前端页面]
B[数据库连接选择]
C[聊天输入框]
D[SQL结果展示]
end
subgraph "服务层"
E[ChatService<br/>聊天服务]
F[SchemaTrainingService<br/>Schema训练服务]
G[SemanticService<br/>语义服务]
H[SqlExecutionService<br/>SQL执行服务]
I[QAExampleService<br/>问答示例服务]
J[McpServer<br/>MCP协议服务器]
end
subgraph "数据访问层"
K[DatabaseConnectionRepository<br/>数据库连接仓储]
L[ChatMessageRepository<br/>聊天消息仓储]
M[DatabaseSchemaRepository<br/>Schema仓储]
N[SchemaEmbeddingRepository<br/>向量嵌入仓储]
O[QAExampleRepository<br/>问答示例仓储]
end
subgraph "外部服务"
P[OpenAI API<br/>LLM服务]
Q[向量数据库<br/>SQLite/PostgreSQL]
R[业务数据库<br/>多种数据库支持]
S[MCP客户端<br/>IDE工具集成]
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 serve 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": "智能Text2SQL服务 - 。支持自然语言转SQL查询。兼容Cursor、Trae等IDE。",
"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
- **Categorized Organization**: Supports categories such as basic queries, complex queries, and aggregate queries
- **Usage Statistics**: Tracks the usage frequency and effectiveness 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 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 examples (default relevance threshold 0.7)
4. Provide relevant examples as context to the LLM
5. Update example usage statistics
#### Example Format
```json
{
"question": "查询最近一个月的活跃用户数量",
"sqlQuery": "SELECT COUNT(DISTINCT user_id) FROM user_activities WHERE activity_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)",
"category": "aggregate",
"description": "统计最近30天内有活动记录的独立用户数量"
}
```
### 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.
