Content
# Tool List
## Text2Sql.Net - Natural Language to SQL .NET Implementation
### Project Background
Text2Sql.Net is a .NET-based natural language to SQL tool 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, 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: Support semantic similarity search
- Syntax Verification: Automatically check generated SQL syntax correctness
- **MCP Protocol Support**: Seamlessly integrate with IDE tools (Cursor, Trae, etc.)
- **Intelligent Q&A 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": "your_secret_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 requires setting, sqlite can be omitted
}
```
## Core Modules
**Database Adapter Layer**
**Vector Database Integration**
- Implement 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 database-specific dialect SQL statements
## 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 to Get 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 Safety Inspection]
K -->|Query Statement| L[Automatically Execute SQL]
K -->|Operation Statement| M[Generate SQL Only<br/>Do Not Execute Automatically]
L --> N{Execution Successful}
N -->|Successful| O[Return Query Results]
N -->|Failed| 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 Process
```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 Completed]
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/>Q&A 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/>Q&A Example Repository]
end
subgraph "External Services"
P[OpenAI API<br/>LLM Service]
Q[Vector Database<br/>SQLite/PostgreSQL]
R[Business Database<br/>Multi-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 serve as an MCP server to provide Text2SQL capabilities 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 detailed structure of a specified table
- `get_all_tables`: Get all table information
#### IDE Integration Configuration
In MCP-supported 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, and other IDEs.",
"isActive": true,
"url": "http://localhost:5000/mcp/sse?connectionId=xxxxxx"
}
}
}
```
After completing the configuration, you can use natural language to interact with the database directly in the IDE:
- "Show the structure of all user tables"
- "Query order data from the last week"
- "Count the number of products by category"
### MCP Usage Scenarios
1. **Code Development**: Quickly generate data query code in the IDE
2. **Data Analysis**: Rapidly explore data through natural language
3. **Report Generation**: Quickly build complex statistical queries
4. **System Integration**: Integrate Text2SQL capabilities into other toolchains
## 📚 Intelligent Q&A Example System
### Q&A Example Function
Text2Sql.Net provides an intelligent Q&A example management system that improves SQL generation accuracy by learning and accumulating examples.
#### Core Features
- **Example Management**: Support manual creation and correction of generated Q&A examples
- **Semantic Search**: Match relevant examples based on vector similarity
- **Category Organization**: Support categorization of basic queries, complex queries, aggregate queries, etc.
- **Usage Statistics**: Track example usage frequency and effectiveness
- **Batch Operations**: Support batch enablement, disablement, and deletion of examples
#### Example Category Description
- **Basic Query**: Simple SELECT statements and basic filtering
- **Complex Query**: Multi-table associations, subqueries, and other complex scenarios
- **Aggregate Query**: Includes GROUP BY, SUM, COUNT, and other aggregate functions
- **Join Query**: Multi-table JOIN operations
- **Corrected Examples**: Examples generated from corrected SQL errors
#### Intelligent Matching Mechanism
When a user inputs a query, the system will:
1. Vectorize the user's question
2. Perform 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": "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": "Count the number of independent users with activity records in the last 30 days"
}
```
### Example Creation Methods
1. **Manual Creation**: Add Q&A pairs directly in the management interface
2. **Correction Generation**: Automatically create examples from corrected SQL errors
3. **Batch Import**: Support batch generation of examples from existing query histories
## More Rag Scenarios Can Be Seen in AntSK
Project Address: [AntSK](https://github.com/AIDotNet/AntSK)
Experience Environment:
[Demo Address](https://demo.antsk.cn)
Username: test
Password: test
