Content
# Tool List
## Text2Sql.Net - .NET Implementation of Natural Language to SQL
### 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 and automatically generate corresponding SQL query statements
- Multi-Database Support: Compatible with SQL Server, MySQL, PostgreSQL, and SQLite
- Intelligent Context Understanding: Understand user query intent 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 using SQLite or PgSQL to run, 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 inputs 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 obtain 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 is successful}
N -->|Success| O[Return query result]
N -->|Failure| P[SQL optimization]
P --> Q[Use error information to optimize SQL]
Q --> R[Re-execute optimized SQL]
R --> S[Return final result]
M --> T[Prompt manual execution]
O --> U[Save response to chat history]
S --> U
T --> U
U --> V[Display result 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[Obtain table/column/foreign key information]
D --> E[Generate table description text]
E --> F[Text vectorization]
F --> G[Store in 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`: Obtain all database connection configurations
- `get_database_schema`: Obtain database table structure information
- `generate_sql`: Generate SQL queries based on natural language
- `execute_sql`: Execute SQL query statements
- `get_chat_history`: Obtain chat history records
- `get_table_structure`: Obtain detailed structure of a specified table
- `get_all_tables`: Obtain 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 configuration, you can directly use natural language to interact with the database in the IDE:
- "Display the structure of all user tables"
- "Query order data from 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**: 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 to improve 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
- **Classification Organization**: Support classification of basic queries, complex queries, aggregate queries, etc.
- **Usage Statistics**: Track example usage frequency and effectiveness
- **Batch Operations**: Support batch enabling, disabling, and deletion of examples
#### Example Classification Description
- **Basic Query**: Simple SELECT statements and basic filtering
- **Complex Query**: Multi-table association, subqueries, and other complex scenarios
- **Aggregate Query**: Contains GROUP BY, SUM, COUNT, and other aggregate functions
- **Join Query**: Multi-table JOIN operations
- **Correction Example**: Examples generated from corrected error SQL
#### 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**: Directly add Q&A pairs in the management interface
2. **Correction Generation**: Automatically create examples from corrected SQL errors
3. **Batch Import**: Support generating examples in batches from existing query history
## More Rag Scenarios Can Be Viewed at AntSK
Project Address: [AntSK](https://github.com/AIDotNet/AntSK)
Demo Environment:
[Demo Address](https://demo.antsk.cn)
Username: test
Password: test
