# Directory Structure
```
├── .config
│ └── dotnet-tools.json
├── .gitattributes
├── .github
│ ├── dependabot.yml
│ └── workflows
│ ├── pr_validation.yml
│ └── publish_docker.yml
├── .gitignore
├── akkalogo.png
├── build.ps1
├── coverlet.runsettings
├── Directory.Build.props
├── Directory.Packages.props
├── global.json
├── LICENSE
├── MSSQL.MCP.sln.DotSettings
├── MSSQL.MCP.slnx
├── NuGet.Config
├── README.md
├── RELEASE_NOTES.md
├── scripts
│ ├── bumpVersion.ps1
│ └── getReleaseNotes.ps1
├── src
│ └── MSSQL.MCP
│ ├── Actors
│ │ └── DatabaseValidationActor.cs
│ ├── Configuration
│ │ └── DatabaseOptions.cs
│ ├── Database
│ │ ├── ISqlConnectionFactory.cs
│ │ └── SqlConnectionFactory.cs
│ ├── MSSQL.MCP.csproj
│ ├── Program.cs
│ ├── Tools
│ │ └── SqlExecutionTool.cs
│ └── Usings.cs
└── tests
└── MSSQL.MCP.IntegrationTests
├── Configuration
│ └── DatabaseOptionsTests.cs
├── Database
│ └── SqlConnectionFactoryTests.cs
├── Infrastructure
│ ├── DatabaseTestCollection.cs
│ └── DatabaseTestFixture.cs
├── MSSQL.MCP.IntegrationTests.csproj
└── Tools
└── SqlExecutionToolTests.cs
```
# Files
--------------------------------------------------------------------------------
/.gitattributes:
--------------------------------------------------------------------------------
```
###############################################################################
# Set default behavior to automatically normalize line endings.
###############################################################################
* text=auto
*.csv -text
###############################################################################
# Set default behavior for command prompt diff.
#
# This is need for earlier builds of msysgit that does not have it on by
# default for csharp files.
# Note: This is only used by command line
###############################################################################
#*.cs diff=csharp
###############################################################################
# Set the merge driver for project and solution files
#
# Merging from the command prompt will add diff markers to the files if there
# are conflicts (Merging from VS is not affected by the settings below, in VS
# the diff markers are never inserted). Diff markers may cause the following
# file extensions to fail to load in VS. An alternative would be to treat
# these files as binary and thus will always conflict and require user
# intervention with every merge. To do so, just uncomment the entries below
###############################################################################
#*.sln merge=binary
#*.csproj merge=binary
#*.vbproj merge=binary
#*.vcxproj merge=binary
#*.vcproj merge=binary
#*.dbproj merge=binary
#*.fsproj merge=binary
#*.lsproj merge=binary
#*.wixproj merge=binary
#*.modelproj merge=binary
#*.sqlproj merge=binary
#*.wwaproj merge=binary
###############################################################################
# behavior for image files
#
# image files are treated as binary by default.
###############################################################################
#*.jpg binary
#*.png binary
#*.gif binary
###############################################################################
# diff behavior for common document formats
#
# Convert binary document formats to text before diffing them. This feature
# is only available from the command line. Turn it on by uncommenting the
# entries below.
###############################################################################
#*.doc diff=astextplain
#*.DOC diff=astextplain
#*.docx diff=astextplain
#*.DOCX diff=astextplain
#*.dot diff=astextplain
#*.DOT diff=astextplain
#*.pdf diff=astextplain
#*.PDF diff=astextplain
#*.rtf diff=astextplain
#*.RTF diff=astextplain
```
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
## Ignore Visual Studio temporary files, build results, and
## files generated by popular Visual Studio add-ons.
##
## Get latest from https://github.com/github/gitignore/blob/main/VisualStudio.gitignore
# User-specific files
*.rsuser
*.suo
*.user
*.userosscache
*.sln.docstates
# JetBrains
.idea/
# User-specific files (MonoDevelop/Xamarin Studio)
*.userprefs
# Mono auto generated files
mono_crash.*
# Build results
[Dd]ebug/
[Dd]ebugPublic/
[Rr]elease/
[Rr]eleases/
x64/
x86/
[Ww][Ii][Nn]32/
[Aa][Rr][Mm]/
[Aa][Rr][Mm]64/
bld/
[Bb]in/
[Oo]bj/
[Ll]og/
[Ll]ogs/
# Visual Studio 2015/2017 cache/options directory
.vs/
# Uncomment if you have tasks that create the project's static files in wwwroot
#wwwroot/
# Visual Studio 2017 auto generated files
Generated\ Files/
# MSTest test Results
[Tt]est[Rr]esult*/
[Bb]uild[Ll]og.*
# NUnit
*.VisualState.xml
TestResult.xml
nunit-*.xml
# Build Results of an ATL Project
[Dd]ebugPS/
[Rr]eleasePS/
dlldata.c
# Benchmark Results
BenchmarkDotNet.Artifacts/
# .NET Core
project.lock.json
project.fragment.lock.json
artifacts/
# ASP.NET Scaffolding
ScaffoldingReadMe.txt
# StyleCop
StyleCopReport.xml
# Files built by Visual Studio
*_i.c
*_p.c
*_h.h
*.ilk
*.meta
*.obj
*.iobj
*.pch
*.pdb
*.ipdb
*.pgc
*.pgd
*.rsp
# but not Directory.Build.rsp, as it configures directory-level build defaults
!Directory.Build.rsp
*.sbr
*.tlb
*.tli
*.tlh
*.tmp
*.tmp_proj
*_wpftmp.csproj
*.log
*.tlog
*.vspscc
*.vssscc
.builds
*.pidb
*.svclog
*.scc
# Chutzpah Test files
_Chutzpah*
# Visual C++ cache files
ipch/
*.aps
*.ncb
*.opendb
*.opensdf
*.sdf
*.cachefile
*.VC.db
*.VC.VC.opendb
# Visual Studio profiler
*.psess
*.vsp
*.vspx
*.sap
# Visual Studio Trace Files
*.e2e
# TFS 2012 Local Workspace
$tf/
# Guidance Automation Toolkit
*.gpState
# ReSharper is a .NET coding add-in
_ReSharper*/
*.[Rr]e[Ss]harper
*.DotSettings.user
# TeamCity is a build add-in
_TeamCity*
# DotCover is a Code Coverage Tool
*.dotCover
# AxoCover is a Code Coverage Tool
.axoCover/*
!.axoCover/settings.json
# Coverlet is a free, cross platform Code Coverage Tool
coverage*.json
coverage*.xml
coverage*.info
# Visual Studio code coverage results
*.coverage
*.coveragexml
# NCrunch
_NCrunch_*
.*crunch*.local.xml
nCrunchTemp_*
# MightyMoose
*.mm.*
AutoTest.Net/
# Web workbench (sass)
.sass-cache/
# Installshield output folder
[Ee]xpress/
# DocProject is a documentation generator add-in
DocProject/buildhelp/
DocProject/Help/*.HxT
DocProject/Help/*.HxC
DocProject/Help/*.hhc
DocProject/Help/*.hhk
DocProject/Help/*.hhp
DocProject/Help/Html2
DocProject/Help/html
# Click-Once directory
publish/
# Publish Web Output
*.[Pp]ublish.xml
*.azurePubxml
# Note: Comment the next line if you want to checkin your web deploy settings,
# but database connection strings (with potential passwords) will be unencrypted
*.pubxml
*.publishproj
# Microsoft Azure Web App publish settings. Comment the next line if you want to
# checkin your Azure Web App publish settings, but sensitive information contained
# in these scripts will be unencrypted
PublishScripts/
# NuGet Packages
*.nupkg
# NuGet Symbol Packages
*.snupkg
# The packages folder can be ignored because of Package Restore
**/[Pp]ackages/*
# except build/, which is used as an MSBuild target.
!**/[Pp]ackages/build/
# Uncomment if necessary however generally it will be regenerated when needed
#!**/[Pp]ackages/repositories.config
# NuGet v3's project.json files produces more ignorable files
*.nuget.props
*.nuget.targets
# Microsoft Azure Build Output
csx/
*.build.csdef
# Microsoft Azure Emulator
ecf/
rcf/
# Windows Store app package directories and files
AppPackages/
BundleArtifacts/
Package.StoreAssociation.xml
_pkginfo.txt
*.appx
*.appxbundle
*.appxupload
# Visual Studio cache files
# files ending in .cache can be ignored
*.[Cc]ache
# but keep track of directories ending in .cache
!?*.[Cc]ache/
# Others
ClientBin/
~$*
*~
*.dbmdl
*.dbproj.schemaview
*.jfm
*.pfx
*.publishsettings
orleans.codegen.cs
# Including strong name files can present a security risk
# (https://github.com/github/gitignore/pull/2483#issue-259490424)
#*.snk
# Since there are multiple workflows, uncomment next line to ignore bower_components
# (https://github.com/github/gitignore/pull/1529#issuecomment-104372622)
#bower_components/
# RIA/Silverlight projects
Generated_Code/
# Backup & report files from converting an old project file
# to a newer Visual Studio version. Backup files are not needed,
# because we have git ;-)
_UpgradeReport_Files/
Backup*/
UpgradeLog*.XML
UpgradeLog*.htm
ServiceFabricBackup/
*.rptproj.bak
# SQL Server files
*.mdf
*.ldf
*.ndf
# Business Intelligence projects
*.rdl.data
*.bim.layout
*.bim_*.settings
*.rptproj.rsuser
*- [Bb]ackup.rdl
*- [Bb]ackup ([0-9]).rdl
*- [Bb]ackup ([0-9][0-9]).rdl
# Microsoft Fakes
FakesAssemblies/
# GhostDoc plugin setting file
*.GhostDoc.xml
# Node.js Tools for Visual Studio
.ntvs_analysis.dat
node_modules/
# Visual Studio 6 build log
*.plg
# Visual Studio 6 workspace options file
*.opt
# Visual Studio 6 auto-generated workspace file (contains which files were open etc.)
*.vbw
# Visual Studio 6 auto-generated project file (contains which files were open etc.)
*.vbp
# Visual Studio 6 workspace and project file (working project files containing files to include in project)
*.dsw
*.dsp
# Visual Studio 6 technical files
*.ncb
*.aps
# Visual Studio LightSwitch build output
**/*.HTMLClient/GeneratedArtifacts
**/*.DesktopClient/GeneratedArtifacts
**/*.DesktopClient/ModelManifest.xml
**/*.Server/GeneratedArtifacts
**/*.Server/ModelManifest.xml
_Pvt_Extensions
# Paket dependency manager
.paket/paket.exe
paket-files/
# FAKE - F# Make
.fake/
# CodeRush personal settings
.cr/personal
# Python Tools for Visual Studio (PTVS)
__pycache__/
*.pyc
# Cake - Uncomment if you are using it
# tools/**
# !tools/packages.config
# Tabs Studio
*.tss
# Telerik's JustMock configuration file
*.jmconfig
# BizTalk build output
*.btp.cs
*.btm.cs
*.odx.cs
*.xsd.cs
# OpenCover UI analysis results
OpenCover/
# Azure Stream Analytics local run output
ASALocalRun/
# MSBuild Binary and Structured Log
*.binlog
# NVidia Nsight GPU debugger configuration file
*.nvuser
# MFractors (Xamarin productivity tool) working folder
.mfractor/
# Local History for Visual Studio
.localhistory/
# Visual Studio History (VSHistory) files
.vshistory/
# BeatPulse healthcheck temp database
healthchecksdb
# Backup folder for Package Reference Convert tool in Visual Studio 2017
MigrationBackup/
# Ionide (cross platform F# VS Code tools) working folder
.ionide/
# Fody - auto-generated XML schema
FodyWeavers.xsd
# VS Code files for those working on multiple tools
.vscode/*
!.vscode/settings.json
!.vscode/tasks.json
!.vscode/launch.json
!.vscode/extensions.json
*.code-workspace
# Local History for Visual Studio Code
.history/
# Windows Installer files from build outputs
*.cab
*.msi
*.msix
*.msm
*.msp
# JetBrains Rider
*.sln.iml
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# mssql-mcp
A .NET-powered Model Context Protocol (MCP) server for Microsoft SQL Server.
## Abstract
Why does this exist? Because the other MCP solutions in market for this are generally janky pieces of shit that don't work - certainly not on Windows.
This MCP server provides AI agents with robust, reliable access to Microsoft SQL Server databases through a clean, well-architected .NET application using Akka.NET for internal coordination and the official MCP C# SDK for protocol compliance.
## Features
- **Schema Discovery**: AI agents can explore database structure without writing complex SQL
- **Query Execution**: Full SQL support for SELECT, INSERT, UPDATE, DELETE, and DDL operations
- **Connection Validation**: Automatic database connectivity validation on startup
- **Error Handling**: Comprehensive error handling with clear, actionable error messages
- **Table Formatting**: Query results formatted in readable tables for AI consumption
- **Docker Support**: Easy deployment with built-in .NET Docker tooling
## Available Tools
| Tool | Description |
|------|-------------|
| `execute_sql` | Execute any SQL query against the database |
| `list_tables` | List all tables with schema, name, type, and row count |
| `list_schemas` | List all available schemas/databases in the SQL Server instance |
## Configuration
### Required Environment Variables
The MCP server requires a single environment variable:
- **`MSSQL_CONNECTION_STRING`**: Complete SQL Server connection string
#### Example Connection Strings
**Windows Authentication:**
```
MSSQL_CONNECTION_STRING="Server=localhost;Database=MyDatabase;Trusted_Connection=true;"
```
**SQL Server Authentication:**
```
MSSQL_CONNECTION_STRING="Server=localhost;Database=MyDatabase;User Id=myuser;Password=mypassword;"
```
**Azure SQL Database:**
```
MSSQL_CONNECTION_STRING="Server=myserver.database.windows.net;Database=mydatabase;User Id=myuser;Password=mypassword;Encrypt=true;"
```
## Running the MCP Server
### Option 1: Docker (Recommended)
The easiest way to run the MCP server is using Docker with .NET's built-in container support.
#### Build and Run with Docker
Clone the repository
```bash
# Clone the repository
git clone https://github.com/Aaronontheweb/mssql-mcp.git
cd mssql-mcp
```
Build the Docker image
```bash
dotnet publish --os linux --arch x64 /t:PublishContainer
```
You can run the container directly if you wish, but it's **probably best** to let the MCP server spin up the client:
```bash
# Run the container
docker run -it --rm \
-e MSSQL_CONNECTION_STRING="Server=host.docker.internal;Database=MyDB;Trusted_Connection=true;" \
mssql-mcp:latest
```
## MCP Client Configuration
### Cursor IDE
Add to your Cursor settings (`Cursor Settings > Features > Model Context Protocol`):
```json
{
"mcpServers": {
"mssql": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"-e",
"MSSQL_CONNECTION_STRING",
"mssql-mcp:latest"
],
"env": {
"MSSQL_CONNECTION_STRING": "Server=host.docker.internal,1533; Database=MyDb; User Id=myUser; Password=My(!)Password;TrustServerCertificate=true;"
}
}
}
}
```
### Claude Desktop
Add to your Claude Desktop configuration file:
* **Windows:** `%APPDATA%\Claude\claude_desktop_config.json`
* **macOS:** `~/Library/Application Support/Claude/claude_desktop_config.json`
```json
{
"mcpServers": {
"mssql": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"-e",
"MSSQL_CONNECTION_STRING",
"mssql-mcp:latest"
],
"env": {
"MSSQL_CONNECTION_STRING": "Server=host.docker.internal,1533; Database=MyDb; User Id=myUser; Password=My(!)Password;TrustServerCertificate=true;"
}
}
}
}
```
You might need to create that file and restart Claude Desktop for the changes to take effect.
#### Understanding Your Claude Desktop MCP Server Configuration
This JSON configuration is for **Claude Desktop's Model Context Protocol (MCP) servers**. It essentially teaches Claude how to connect to and use a custom
"tool" that interacts with a **Microsoft SQL Server (MSSQL)** database.
Let's break down each part:
##### `mcpServers`
This is the top-level section where you define all your custom MCP servers. You can set up multiple servers here, each with its own unique name.
##### `"mssql"`
This is the **unique name** you've chosen for this particular SQL Server integration. Claude will use this name to refer to this database connection.
##### `"command": "docker"`
This line tells Claude Desktop to launch the MCP server using **Docker**. This means the actual server software runs inside an isolated container,
and you'll need **Docker Desktop** installed and running on your Windows/mac/Linux machine for this to work. Alternatively, you can use remote Docker server
using [custon context](https://docs.docker.com/engine/manage-resources/contexts/).
##### `"args": [...]`
These are the **arguments** Claude Desktop passes to the `docker` command when starting the container:
* `"run"`: This standard Docker command creates and starts a new container.
* `"-i"`: Stands for "interactive," keeping the standard input open for communication between the MCP server and Claude Desktop.
* `"--rm"`: This important argument tells Docker to **automatically remove the container** when it stops. This helps keep your Docker environment tidy.
* `"-e", "MSSQL_CONNECTION_STRING"`: This passes an **environment variable** named `MSSQL_CONNECTION_STRING` into the Docker container.
* `"mssql-mcp:latest"`: This specifies the **Docker image** to use. This image (`mssql-mcp` with the `latest` tag) contains the actual MCP server application
designed to interact with SQL Server. You'll need to ensure this image is available (either built locally or pulled from a Docker registry).
##### `"env": {...}`
This section defines the **environment variables** that will be set when Docker executes the command.
* `"MSSQL_CONNECTION_STRING": "Server=host.docker.internal,1533; Database=MyDb; User Id=myUser; Password=My(!)Password;TrustServerCertificate=true;"`
* This is the **SQL Server connection string** that the `mssql-mcp` Docker container will use to connect to your database.
* `Server=host.docker.internal,1533`: `host.docker.internal` is a special Docker DNS name that lets the container reach your **host machine's IP address**.
This is how the MCP server inside Docker can connect to your SQL Server instance, which is presumably running directly on your machine. `1533` is the
port your SQL Server is listening on.
* `Database=MyDb`: The name of the specific database you want to connect to.
* `User Id=myUser; Password=My(!)Password;`: The credentials for a user (`myUser`) to log into your SQL Server.
* `TrustServerCertificate=true;`: This tells the client to **skip validating the server's SSL/TLS certificate**. While convenient for development or when
using self-signed certificates, be aware this reduces security by making you vulnerable to man-in-the-middle attacks in production environments.
---
##### In a Nutshell:
This configuration enables Claude Desktop to run a SQL Server-specific MCP server inside a Docker container. This server then uses the provided connection
string to establish a connection to your SQL Server database, allowing Claude to interact with your data through this custom tool.
### Local Binary Configuration
If running the built binary directly instead of Docker:
```json
{
"mcpServers": {
"mssql": {
"command": "/path/to/mssql-mcp/src/MSSQL.MCP/bin/Release/net9.0/MSSQL.MCP",
"env": {
"MSSQL_CONNECTION_STRING": "Server=localhost;Database=MyDB;Trusted_Connection=true;"
}
}
}
}
```
## Docker Networking Issues
### Understanding the Problem
When running the MCP server as a Docker container, you'll encounter networking challenges when trying to connect to SQL Server instances running on your host machine or in other containers. Docker containers are isolated from the host network by default, making `localhost` connections impossible.
### Solutions by Scenario
#### Scenario 1: SQL Server Running on Host Machine
**Problem**: Your SQL Server is installed directly on Windows/macOS/Linux, and you want the containerized MCP server to connect to it.
**Solution**: Use `host.docker.internal` instead of `localhost` in your connection string.
```bash
# ❌ This won't work - localhost refers to the container itself
docker run -it --rm \
-e MSSQL_CONNECTION_STRING="Server=localhost;Database=MyDB;User Id=sa;Password=YourPassword123!;" \
mssql-mcp:latest
# ✅ This works - host.docker.internal refers to the host machine
docker run -it --rm \
-e MSSQL_CONNECTION_STRING="Server=host.docker.internal;Database=MyDB;User Id=sa;Password=YourPassword123!;" \
mssql-mcp:latest
```
**Updated MCP Client Configuration:**
```json
{
"mcpServers": {
"mssql": {
"command": "docker",
"args": [
"run", "-i", "--rm",
"-e", "MSSQL_CONNECTION_STRING=Server=host.docker.internal;Database=MyDB;User Id=sa;Password=YourPassword123!;",
"mssql-mcp:latest"
]
}
}
}
```
#### Scenario 2: SQL Server in Another Docker Container
**Solution**: Use Docker Compose with a custom network and reference containers by service name.
```yaml
version: '3.8'
networks:
sql-network:
driver: bridge
services:
mssql-mcp:
build: .
environment:
# Use the service name 'sqlserver' as the hostname
- MSSQL_CONNECTION_STRING=Server=sqlserver;Database=MyDatabase;User Id=sa;Password=YourPassword123!;
stdin_open: true
tty: true
networks:
- sql-network
depends_on:
- sqlserver
sqlserver:
image: mcr.microsoft.com/mssql/server:2022-latest
environment:
- ACCEPT_EULA=Y
- SA_PASSWORD=YourPassword123!
networks:
- sql-network
ports:
- "1433:1433" # Expose to host for external tools
```
#### Scenario 3: Linux with Host Network Mode
**Linux Only Solution**: Use Docker's host networking mode for direct host network access.
```bash
# Linux only - shares the host's network stack
docker run -it --rm --network host \
-e MSSQL_CONNECTION_STRING="Server=localhost;Database=MyDB;User Id=sa;Password=YourPassword123!;" \
mssql-mcp:latest
```
### Platform-Specific Considerations
| Platform | host.docker.internal | Host Network Mode | Recommended Solution |
|----------|---------------------|-------------------|---------------------|
| **Windows** | ✅ Works out of box | ❌ Not supported | Use `host.docker.internal` |
| **macOS** | ✅ Works out of box | ❌ Not supported | Use `host.docker.internal` |
| **Linux** | ⚠️ Requires `--add-host` | ✅ Supported | Use `--network host` or `host.docker.internal` |
**Linux `host.docker.internal` setup:**
```bash
docker run -it --rm \
--add-host=host.docker.internal:host-gateway \
-e MSSQL_CONNECTION_STRING="Server=host.docker.internal;Database=MyDB;User Id=sa;Password=YourPassword123!;" \
mssql-mcp:latest
```
### Testing Network Connectivity
To verify your container can reach the SQL Server:
```bash
# Test from inside a running container
docker exec -it <container_name> ping host.docker.internal
# Test SQL Server port specifically
docker run --rm -it mcr.microsoft.com/mssql-tools \
/bin/bash -c "sqlcmd -S host.docker.internal -U sa -P 'YourPassword123!' -Q 'SELECT @@VERSION'"
```
### Common Networking Troubleshooting
1. **Connection Refused**:
- Verify SQL Server is listening on all interfaces: `netstat -an | grep 1433`
- Check Windows Firewall allows Docker subnet access
2. **DNS Resolution**:
- Test: `docker run --rm busybox nslookup host.docker.internal`
- Ensure Docker Desktop is running (for Windows/macOS)
3. **Container-to-Container**:
- Verify both containers are on the same Docker network
- Use container service names, not localhost
4. **Port Conflicts**:
- Ensure port 1433 isn't already bound by another process
- Check with: `netstat -tlnp | grep 1433`
## Usage Examples
Once configured, AI agents can use natural language to interact with your database:
**"Show me all the tables in the database"**
→ Uses `list_tables` tool
**"Describe the structure of the Users table"**
→ Uses `execute_sql` with an INFORMATION_SCHEMA query
**"Find all users created in the last 30 days"**
→ Uses `execute_sql` with appropriate SELECT query
**"Create a new customer record"**
→ Uses `execute_sql` with INSERT statement
## Security Considerations
### ⚠️ Important Security Warnings
- **Database Permissions**: Only grant the minimum required permissions to the database user
- **Connection Security**: Use encrypted connections for production environments
- **Access Control**: This MCP server provides full SQL execution capabilities - ensure proper access controls
- **Audit Logging**: Consider enabling SQL Server audit logging for production use
- **Network Security**: Restrict network access to the database server appropriately
### Recommended Database Permissions
For read-only access:
```sql
-- Create a dedicated user with minimal permissions
CREATE LOGIN mcp_readonly WITH PASSWORD = 'SecurePassword123!';
CREATE USER mcp_readonly FOR LOGIN mcp_readonly;
-- Grant only necessary permissions
GRANT SELECT ON SCHEMA::dbo TO mcp_readonly;
GRANT VIEW DEFINITION ON SCHEMA::dbo TO mcp_readonly;
```
For read-write access:
```sql
-- Create a dedicated user
CREATE LOGIN mcp_readwrite WITH PASSWORD = 'SecurePassword123!';
CREATE USER mcp_readwrite FOR LOGIN mcp_readwrite;
-- Grant necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO mcp_readwrite;
GRANT VIEW DEFINITION ON SCHEMA::dbo TO mcp_readwrite;
```
## Troubleshooting
### Connection Issues
1. **Verify connection string**: Test with SQL Server Management Studio or Azure Data Studio
2. **Check firewall**: Ensure SQL Server port (default 1433) is accessible
3. **Enable TCP/IP**: Ensure TCP/IP protocol is enabled in SQL Server Configuration Manager
4. **Authentication mode**: Verify SQL Server is configured for the appropriate authentication mode
### Container Issues
1. **Network connectivity**: Use `host.docker.internal` instead of `localhost` when connecting from container to host
2. **Environment variables**: Ensure the connection string is properly escaped in Docker commands
3. **Logs**: Check container logs with `docker logs <container_id>`
## License
This software is licensed under Apache 2.0 and is available "as is" - this means that if you turbo-nuke your database because you gave an AI agent `sa` access through this MCP server, we're not responsible.
## Contributing
1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request
## Architecture
- **[Akka.NET](https://getakka.net/)**: Used for internal actor system coordination and database validation
- **[MCP C# SDK](https://github.com/modelcontextprotocol/csharp-sdk)**: Official Model Context Protocol implementation
- **Microsoft.Data.SqlClient**: High-performance SQL Server connectivity
```
--------------------------------------------------------------------------------
/global.json:
--------------------------------------------------------------------------------
```json
{
"sdk": {
"version": "9.0.300",
"rollForward": "major"
}
}
```
--------------------------------------------------------------------------------
/src/MSSQL.MCP/Usings.cs:
--------------------------------------------------------------------------------
```csharp
global using Akka.Actor;
global using Microsoft.Extensions.Options;
global using Microsoft.Extensions.Configuration;
global using Microsoft.Extensions.DependencyInjection;
```
--------------------------------------------------------------------------------
/NuGet.Config:
--------------------------------------------------------------------------------
```
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<solution>
<add key="disableSourceControlIntegration" value="true" />
</solution>
<packageSources>
<clear />
<add key="nuget.org" value="https://api.nuget.org/v3/index.json" />
</packageSources>
</configuration>
```
--------------------------------------------------------------------------------
/.github/dependabot.yml:
--------------------------------------------------------------------------------
```yaml
version: 2
updates:
- package-ecosystem: github-actions
directory: "/"
schedule:
interval: daily
- package-ecosystem: "dotnet-sdk"
directory: "/"
schedule:
interval: "weekly"
day: "wednesday"
- package-ecosystem: nuget
directory: "/"
schedule:
interval: daily
```
--------------------------------------------------------------------------------
/.config/dotnet-tools.json:
--------------------------------------------------------------------------------
```json
{
"version": 1,
"isRoot": true,
"tools": {
"incrementalist.cmd": {
"version": "1.0.0",
"commands": [
"incrementalist"
],
"rollForward": false
},
"docfx": {
"version": "2.78.3",
"commands": [
"docfx"
],
"rollForward": false
}
}
}
```
--------------------------------------------------------------------------------
/src/MSSQL.MCP/Database/ISqlConnectionFactory.cs:
--------------------------------------------------------------------------------
```csharp
using Microsoft.Data.SqlClient;
namespace MSSQL.MCP.Database;
public interface ISqlConnectionFactory
{
SqlConnection CreateConnection();
Task<SqlConnection> CreateOpenConnectionAsync(CancellationToken cancellationToken = default);
Task<bool> ValidateConnectionAsync(CancellationToken cancellationToken = default);
}
```
--------------------------------------------------------------------------------
/RELEASE_NOTES.md:
--------------------------------------------------------------------------------
```markdown
#### 0.1.1 June 11th 2025 ####
- Enhanced SqlExecutionTool with comprehensive query logging for better debugging and monitoring
- Added T-SQL syntax validation to prevent AI agents from sending invalid non-SQL inputs
- Improved tool descriptions with explicit T-SQL requirements and usage examples
- Added structured error messages to guide AI agents on proper query formatting
```
--------------------------------------------------------------------------------
/tests/MSSQL.MCP.IntegrationTests/Infrastructure/DatabaseTestCollection.cs:
--------------------------------------------------------------------------------
```csharp
using Xunit;
namespace MSSQL.MCP.IntegrationTests.Infrastructure;
/// <summary>
/// Test collection definition that ensures all integration tests share the same database fixture.
/// This improves performance by reusing the SQL Server container across tests.
/// </summary>
[CollectionDefinition("Database")]
public class DatabaseTestCollection : ICollectionFixture<DatabaseTestFixture>
{
// This class has no code, it exists solely to define the collection
}
```
--------------------------------------------------------------------------------
/build.ps1:
--------------------------------------------------------------------------------
```
. "$PSScriptRoot\scripts\getReleaseNotes.ps1"
. "$PSScriptRoot\scripts\bumpVersion.ps1"
######################################################################
# Step 1: Grab release notes and update solution metadata
######################################################################
$releaseNotes = Get-ReleaseNotes -MarkdownFile (Join-Path -Path $PSScriptRoot -ChildPath "RELEASE_NOTES.md")
# inject release notes into Directory.Buil
UpdateVersionAndReleaseNotes -ReleaseNotesResult $releaseNotes -XmlFilePath (Join-Path -Path $PSScriptRoot -ChildPath "Directory.Build.props")
Write-Output "Added release notes $releaseNotes"
```
--------------------------------------------------------------------------------
/src/MSSQL.MCP/Configuration/DatabaseOptions.cs:
--------------------------------------------------------------------------------
```csharp
using System.ComponentModel.DataAnnotations;
namespace MSSQL.MCP.Configuration;
public class DatabaseOptions
{
public const string SectionName = "Database";
[Required(ErrorMessage = "MSSQL_CONNECTION_STRING environment variable is required")]
public string ConnectionString { get; set; } = string.Empty;
}
public class DatabaseOptionsValidator : IValidateOptions<DatabaseOptions>
{
public ValidateOptionsResult Validate(string? name, DatabaseOptions options)
{
if (string.IsNullOrWhiteSpace(options.ConnectionString))
{
return ValidateOptionsResult.Fail("MSSQL_CONNECTION_STRING environment variable must be provided and cannot be empty");
}
return ValidateOptionsResult.Success;
}
}
```
--------------------------------------------------------------------------------
/scripts/bumpVersion.ps1:
--------------------------------------------------------------------------------
```
function UpdateVersionAndReleaseNotes {
param (
[Parameter(Mandatory=$true)]
[PSCustomObject]$ReleaseNotesResult,
[Parameter(Mandatory=$true)]
[string]$XmlFilePath
)
# Load XML
$xmlContent = New-Object XML
$xmlContent.Load($XmlFilePath)
# Update VersionPrefix and PackageReleaseNotes
$versionPrefixElement = $xmlContent.SelectSingleNode("//VersionPrefix")
$versionPrefixElement.InnerText = $ReleaseNotesResult.Version
$packageReleaseNotesElement = $xmlContent.SelectSingleNode("//PackageReleaseNotes")
$packageReleaseNotesElement.InnerText = $ReleaseNotesResult.ReleaseNotes
# Save the updated XML
$xmlContent.Save($XmlFilePath)
}
# Usage example:
# $notes = Get-ReleaseNotes -MarkdownFile "$PSScriptRoot\RELEASE_NOTES.md"
# $propsPath = Join-Path -Path (Get-Item $PSScriptRoot).Parent.FullName -ChildPath "Directory.Build.props"
# UpdateVersionAndReleaseNotes -ReleaseNotesResult $notes -XmlFilePath $propsPath
```
--------------------------------------------------------------------------------
/Directory.Packages.props:
--------------------------------------------------------------------------------
```
<Project>
<PropertyGroup>
<ManagePackageVersionsCentrally>true</ManagePackageVersionsCentrally>
<!-- Nuget package versions -->
<AkkaHostingVersion>1.5.46</AkkaHostingVersion>
</PropertyGroup>
<!-- App dependencies -->
<ItemGroup>
<PackageVersion Include="Akka.Hosting" Version="$(AkkaHostingVersion)" />
<PackageVersion Include="Microsoft.Data.SqlClient" Version="6.1.0" />
<PackageVersion Include="Microsoft.Extensions.Hosting" Version="9.0.8" />
<PackageVersion Include="ModelContextProtocol" Version="0.2.0-preview.3" />
</ItemGroup>
<!-- Test dependencies -->
<ItemGroup>
<PackageVersion Include="xunit" Version="2.9.3" />
<PackageVersion Include="xunit.runner.visualstudio" Version="3.1.3" />
<PackageVersion Include="Microsoft.NET.Test.Sdk" Version="17.14.1" />
<PackageVersion Include="Testcontainers.MSSql" Version="4.6.0" />
</ItemGroup>
<!-- SourceLink support for all Akka.NET projects -->
<ItemGroup>
<PackageVersion Include="Microsoft.SourceLink.GitHub" Version="8.0.0" />
</ItemGroup>
</Project>
```
--------------------------------------------------------------------------------
/.github/workflows/publish_docker.yml:
--------------------------------------------------------------------------------
```yaml
name: Publish NuGet
on:
push:
tags:
- '*'
jobs:
publish-nuget:
name: publish-nuget
runs-on: ${{ matrix.os }}
strategy:
matrix:
os: [ubuntu-latest]
steps:
- name: "Checkout"
uses: actions/[email protected]
with:
lfs: true
fetch-depth: 0
- name: "Install .NET SDK"
uses: actions/[email protected]
with:
global-json-file: "./global.json"
- name: "Restore .NET tools"
run: dotnet tool restore
- name: "Update release notes"
shell: pwsh
run: |
./build.ps1
- name: release
uses: actions/create-release@v1
id: create_release
with:
draft: false
prerelease: false
release_name: 'mssql-mcp ${{ github.ref_name }}'
tag_name: ${{ github.ref }}
body_path: RELEASE_NOTES.md
env:
GITHUB_TOKEN: ${{ github.token }}
# - name: Upload Release Asset
# uses: AButler/[email protected]
# with:
# repo-token: ${{ github.token }}
# release-tag: ${{ github.ref_name }}
# files: 'output/*.nupkg'
```
--------------------------------------------------------------------------------
/src/MSSQL.MCP/Database/SqlConnectionFactory.cs:
--------------------------------------------------------------------------------
```csharp
using Microsoft.Data.SqlClient;
using MSSQL.MCP.Configuration;
namespace MSSQL.MCP.Database;
public class SqlConnectionFactory(IOptions<DatabaseOptions> databaseOptions) : ISqlConnectionFactory
{
private readonly string _connectionString = databaseOptions.Value.ConnectionString;
public SqlConnection CreateConnection()
{
return new SqlConnection(_connectionString);
}
public async Task<SqlConnection> CreateOpenConnectionAsync(CancellationToken cancellationToken = default)
{
var connection = CreateConnection();
try
{
await connection.OpenAsync(cancellationToken);
return connection;
}
catch
{
connection.Dispose();
throw;
}
}
public async Task<bool> ValidateConnectionAsync(CancellationToken cancellationToken = default)
{
try
{
await using var connection = await CreateOpenConnectionAsync(cancellationToken);
await using var command = new SqlCommand("SELECT 1", connection);
await command.ExecuteScalarAsync(cancellationToken);
return true;
}
catch
{
return false;
}
}
}
```
--------------------------------------------------------------------------------
/scripts/getReleaseNotes.ps1:
--------------------------------------------------------------------------------
```
function Get-ReleaseNotes {
param (
[Parameter(Mandatory=$true)]
[string]$MarkdownFile
)
# Read markdown file content
$content = Get-Content -Path $MarkdownFile -Raw
# Split content based on headers
$sections = $content -split "####"
# Output object to store result
$outputObject = [PSCustomObject]@{
Version = $null
Date = $null
ReleaseNotes = $null
}
# Check if we have at least 3 sections (1. Before the header, 2. Header, 3. Release notes)
if ($sections.Count -ge 3) {
$header = $sections[1].Trim()
$releaseNotes = $sections[2].Trim()
# Extract version and date from the header
$headerParts = $header -split " ", 2
if ($headerParts.Count -eq 2) {
$outputObject.Version = $headerParts[0]
$outputObject.Date = $headerParts[1]
}
$outputObject.ReleaseNotes = $releaseNotes
}
# Return the output object
return $outputObject
}
# Call function example:
#$result = Get-ReleaseNotes -MarkdownFile "$PSScriptRoot\RELEASE_NOTES.md"
#Write-Output "Version: $($result.Version)"
#Write-Output "Date: $($result.Date)"
#Write-Output "Release Notes:"
#Write-Output $result.ReleaseNotes
```
--------------------------------------------------------------------------------
/.github/workflows/pr_validation.yml:
--------------------------------------------------------------------------------
```yaml
name: pr_validation
on:
push:
branches:
- master
- dev
- main
pull_request:
branches:
- master
- dev
- main
jobs:
test:
name: Test-${{matrix.os}}
runs-on: ${{matrix.os}}
strategy:
matrix:
os: [ubuntu-latest]
steps:
- name: "Checkout"
uses: actions/[email protected]
with:
lfs: true
fetch-depth: 0
- name: "Install .NET SDK"
uses: actions/[email protected]
with:
global-json-file: "./global.json"
- name: "Restore .NET tools"
run: dotnet tool restore
- name: "Update release notes"
shell: pwsh
run: |
./build.ps1
- name: "dotnet build"
run: dotnet build -c Release
# .NET Framework tests can't run reliably on Linux, so we only do .NET 8
- name: "dotnet test"
shell: bash
run: dotnet test -c Release
docker-build:
name: Docker Build Validation
runs-on: ubuntu-latest
steps:
- name: "Checkout"
uses: actions/[email protected]
with:
lfs: true
fetch-depth: 0
- name: "Install .NET SDK"
uses: actions/[email protected]
with:
global-json-file: "./global.json"
- name: "Build Docker image"
run: |
dotnet publish src/MSSQL.MCP/MSSQL.MCP.csproj \
--os linux \
--arch x64 \
-c Release \
/t:PublishContainer
- name: "Verify Docker image exists"
run: docker images | grep mssql-mcp
```
--------------------------------------------------------------------------------
/src/MSSQL.MCP/Program.cs:
--------------------------------------------------------------------------------
```csharp
using Akka.Hosting;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using MSSQL.MCP.Configuration;
using MSSQL.MCP.Database;
using MSSQL.MCP.Actors;
var hostBuilder = new HostBuilder();
hostBuilder
.ConfigureAppConfiguration((context, builder) =>
{
builder.AddEnvironmentVariables();
// Map MSSQL_CONNECTION_STRING to Database:ConnectionString
builder.AddInMemoryCollection([
new KeyValuePair<string, string?>("Database:ConnectionString",
Environment.GetEnvironmentVariable("MSSQL_CONNECTION_STRING"))
]);
})
.ConfigureServices((context, services) =>
{
// Configure logging to stderr for MCP protocol compatibility
services.AddLogging(builder =>
{
builder.AddConsole(consoleLogOptions =>
{
consoleLogOptions.LogToStandardErrorThreshold = Microsoft.Extensions.Logging.LogLevel.Trace;
});
});
// Configure Database options with validation
services.AddSingleton<IValidateOptions<DatabaseOptions>, DatabaseOptionsValidator>();
services.AddOptionsWithValidateOnStart<DatabaseOptions>()
.BindConfiguration("Database");
// Register SQL Connection Factory
services.AddSingleton<ISqlConnectionFactory, SqlConnectionFactory>();
// Add MCP Server
services.AddMcpServer()
.WithStdioServerTransport()
.WithToolsFromAssembly();
// Add Akka.NET
services.AddAkka("MSSQLMcpActorSystem", (builder, sp) =>
{
builder
.ConfigureLoggers(configBuilder =>
{
configBuilder.ClearLoggers();
configBuilder.AddLoggerFactory();
})
.WithActors((system, registry, resolver) =>
{
// Database validation actor - tests actual connection
var dbValidationActorProps = resolver.Props<DatabaseValidationActor>();
var dbValidationActor = system.ActorOf(dbValidationActorProps, "database-validation");
// We would normally register this actor in the registry, but since it dies immediately after validation,
// there's not much point in keeping it around.
});
});
});
var host = hostBuilder.Build();
await host.RunAsync();
```
--------------------------------------------------------------------------------
/Directory.Build.props:
--------------------------------------------------------------------------------
```
<Project>
<PropertyGroup>
<Copyright>Copyright © 2015-$([System.DateTime]::Now.Year) Akka.NET Team</Copyright>
<Authors>Akka.NET Team</Authors>
<Tags>akka, akka.streams, kafka, akkadotnet, akka streams, akka.streams.kafka</Tags>
<NoWarn>$(NoWarn);CS1591</NoWarn>
<LangVersion>latest</LangVersion>
<Nullable>enable</Nullable>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
<VersionPrefix>0.1.1</VersionPrefix>
<PackageReleaseNotes>- Enhanced SqlExecutionTool with comprehensive query logging for better debugging and monitoring
- Added T-SQL syntax validation to prevent AI agents from sending invalid non-SQL inputs
- Improved tool descriptions with explicit T-SQL requirements and usage examples
- Added structured error messages to guide AI agents on proper query formatting</PackageReleaseNotes>
</PropertyGroup>
<PropertyGroup>
<!-- Visual Studio C# settings -->
<TargetFramework>net9.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<Using Include="Akka.Event" />
</ItemGroup>
<!-- GitHub SourceLink -->
<ItemGroup>
<PackageReference Include="Microsoft.SourceLink.GitHub" PrivateAssets="All" />
</ItemGroup>
<!-- NuGet package properties -->
<ItemGroup>
<None Include="$(MSBuildThisFileDirectory)\akkalogo.png" Pack="true" PackagePath="\" />
<None Include="$(MSBuildThisFileDirectory)\README.md" Pack="true" PackagePath="\" />
</ItemGroup>
<!-- NuGet .nupkg options -->
<PropertyGroup>
<PackageReleaseNotes>1.5.25 June 17 2024
* [Updated Akka.NET to 1.5.25](https://github.com/akkadotnet/akka.net/releases/tag/1.5.25)</PackageReleaseNotes>
<PackageTags>akka;actors;actor model;Akka;concurrency;test</PackageTags>
<PackageProjectUrl>https://github.com/akkadotnet/Akka.MultiNodeTestRunner</PackageProjectUrl>
<PackageLicenseExpression>Apache-2.0</PackageLicenseExpression>
<PackageIcon>akkalogo.png</PackageIcon>
<PackageReadmeFile>README.md</PackageReadmeFile>
<PublishRepositoryUrl>true</PublishRepositoryUrl>
<!-- Optional: Embed source files that are not tracked by the source control manager in the PDB -->
<EmbedUntrackedSources>true</EmbedUntrackedSources>
<!-- Optional: Build symbol package (.snupkg) to distribute the PDB containing Source Link -->
<IncludeSymbols>true</IncludeSymbols>
<SymbolPackageFormat>snupkg</SymbolPackageFormat>
</PropertyGroup>
</Project>
```
--------------------------------------------------------------------------------
/src/MSSQL.MCP/Actors/DatabaseValidationActor.cs:
--------------------------------------------------------------------------------
```csharp
using MSSQL.MCP.Database;
namespace MSSQL.MCP.Actors;
/// <summary>
/// Validates the database connection at startup, so that the MCP server can only start if the database is accessible.
/// </summary>
public sealed class DatabaseValidationActor : ReceiveActor
{
private readonly ISqlConnectionFactory _connectionFactory;
private readonly ILoggingAdapter _logger;
public DatabaseValidationActor(ISqlConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
_logger = Context.GetLogger();
ReceiveAsync<ValidateDatabaseConnection>(_ => Handle());
}
protected override void PreStart()
{
_logger.Info("🔌 Starting database connection validation...");
Self.Tell(new ValidateDatabaseConnection());
}
private async Task Handle()
{
try
{
var isValid = await _connectionFactory.ValidateConnectionAsync();
if (isValid)
{
_logger.Info("✅ Database connection validated successfully! Ready to process MCP requests.");
}
else
{
_logger.Error("❌ Database connection validation failed: Unable to connect to the database");
_logger.Error("💡 Please verify that:");
_logger.Error(" • MSSQL_CONNECTION_STRING environment variable is set correctly");
_logger.Error(" • SQL Server instance is running and accessible");
_logger.Error(" • Database exists and credentials are valid");
_logger.Error(" • Network connectivity to the database server");
_logger.Error("🛑 Shutting down MCP server due to database connection failure");
_ = Context.System.Terminate();
}
}
catch (Exception ex)
{
_logger.Error(ex, "❌ Database connection validation failed with exception: {Message}", ex.Message);
_logger.Error("💡 Connection string format should be similar to:");
_logger.Error(" Server=localhost;Database=MyDatabase;Trusted_Connection=true;");
_logger.Error(" or");
_logger.Error(" Server=localhost;Database=MyDatabase;User Id=myuser;Password=mypassword;");
_logger.Error("🛑 Shutting down MCP server due to database connection error");
_ = Context.System.Terminate();
}
finally
{
// shut ourselves down once finished
Context.Stop(Self);
}
}
}
public record ValidateDatabaseConnection;
```
--------------------------------------------------------------------------------
/tests/MSSQL.MCP.IntegrationTests/Database/SqlConnectionFactoryTests.cs:
--------------------------------------------------------------------------------
```csharp
using MSSQL.MCP.IntegrationTests.Infrastructure;
using Microsoft.Data.SqlClient;
using Xunit;
namespace MSSQL.MCP.IntegrationTests.Database;
/// <summary>
/// Integration tests for SqlConnectionFactory to validate connection handling.
/// </summary>
[Collection("Database")]
public class SqlConnectionFactoryTests(DatabaseTestFixture fixture)
{
[Fact]
public void CreateConnection_ReturnsValidConnection()
{
// Act
using var connection = fixture.ConnectionFactory.CreateConnection();
// Assert
Assert.NotNull(connection);
Assert.IsType<SqlConnection>(connection);
Assert.Equal(fixture.ConnectionString, connection.ConnectionString);
}
[Fact]
public async Task CreateOpenConnectionAsync_ReturnsOpenConnection()
{
// Act
await using var connection = await fixture.ConnectionFactory.CreateOpenConnectionAsync();
// Assert
Assert.NotNull(connection);
Assert.Equal(System.Data.ConnectionState.Open, connection.State);
}
[Fact]
public async Task CreateOpenConnectionAsync_WithCancellation_HandlesCancellation()
{
using var cts = new CancellationTokenSource();
await cts.CancelAsync();
// Should handle cancellation token (TaskCanceledException inherits from OperationCanceledException)
await Assert.ThrowsAnyAsync<OperationCanceledException>(async () =>
{
await using var connection = await fixture.ConnectionFactory.CreateOpenConnectionAsync(cts.Token);
});
}
[Fact]
public async Task ValidateConnectionAsync_WithValidConnection_ReturnsTrue()
{
// Act
var isValid = await fixture.ConnectionFactory.ValidateConnectionAsync();
// Assert
Assert.True(isValid);
}
[Fact]
public async Task ValidateConnectionAsync_WithTimeout_CompletesQuickly()
{
// Act
var stopwatch = System.Diagnostics.Stopwatch.StartNew();
var isValid = await fixture.ConnectionFactory.ValidateConnectionAsync();
stopwatch.Stop();
// Assert
Assert.True(isValid);
Assert.True(stopwatch.ElapsedMilliseconds < 5000); // Should complete within 5 seconds
}
[Fact]
public async Task CreateOpenConnectionAsync_MultipleConnections_WorkCorrectly()
{
// Act & Assert - Test connection pooling
var tasks = new List<Task>();
for (int i = 0; i < 5; i++)
{
tasks.Add(Task.Run(async () =>
{
await using var connection = await fixture.ConnectionFactory.CreateOpenConnectionAsync();
await using var command = new SqlCommand("SELECT 1", connection);
var result = await command.ExecuteScalarAsync();
Assert.Equal(1, result);
}));
}
await Task.WhenAll(tasks);
}
}
```
--------------------------------------------------------------------------------
/tests/MSSQL.MCP.IntegrationTests/Configuration/DatabaseOptionsTests.cs:
--------------------------------------------------------------------------------
```csharp
using MSSQL.MCP.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Options;
using Xunit;
namespace MSSQL.MCP.IntegrationTests.Configuration;
/// <summary>
/// Tests for DatabaseOptions configuration validation.
/// </summary>
public class DatabaseOptionsTests
{
[Fact]
public void DatabaseOptionsValidator_WithValidConnectionString_ReturnsSuccess()
{
// Arrange
var validator = new DatabaseOptionsValidator();
var options = new DatabaseOptions
{
ConnectionString = "Server=localhost;Database=Test;Trusted_Connection=true;"
};
// Act
var result = validator.Validate(null, options);
// Assert
Assert.True(result.Succeeded);
}
[Fact]
public void DatabaseOptionsValidator_WithEmptyConnectionString_ReturnsFail()
{
// Arrange
var validator = new DatabaseOptionsValidator();
var options = new DatabaseOptions
{
ConnectionString = ""
};
// Act
var result = validator.Validate(null, options);
// Assert
Assert.True(result.Failed);
Assert.Contains("MSSQL_CONNECTION_STRING", result.FailureMessage);
}
[Fact]
public void DatabaseOptionsValidator_WithNullConnectionString_ReturnsFail()
{
// Arrange
var validator = new DatabaseOptionsValidator();
var options = new DatabaseOptions
{
ConnectionString = null!
};
// Act
var result = validator.Validate(null, options);
// Assert
Assert.True(result.Failed);
Assert.Contains("MSSQL_CONNECTION_STRING", result.FailureMessage);
}
[Fact]
public void DatabaseOptionsValidator_WithWhitespaceConnectionString_ReturnsFail()
{
// Arrange
var validator = new DatabaseOptionsValidator();
var options = new DatabaseOptions
{
ConnectionString = " "
};
// Act
var result = validator.Validate(null, options);
// Assert
Assert.True(result.Failed);
Assert.Contains("MSSQL_CONNECTION_STRING", result.FailureMessage);
}
[Fact]
public void DatabaseOptions_SectionName_IsCorrect()
{
// Assert
Assert.Equal("Database", DatabaseOptions.SectionName);
}
[Fact]
public void DatabaseOptions_DefaultValues_AreCorrect()
{
// Arrange & Act
var options = new DatabaseOptions();
// Assert
Assert.Equal(string.Empty, options.ConnectionString);
}
[Fact]
public void DatabaseOptions_Integration_WithDependencyInjection()
{
// Arrange
var services = new ServiceCollection();
// Simulate configuration
var connectionString = "Server=localhost;Database=Test;Trusted_Connection=true;";
services.Configure<DatabaseOptions>(options =>
{
options.ConnectionString = connectionString;
});
services.AddSingleton<IValidateOptions<DatabaseOptions>, DatabaseOptionsValidator>();
var serviceProvider = services.BuildServiceProvider();
// Act
var options = serviceProvider.GetRequiredService<IOptions<DatabaseOptions>>();
// Assert
Assert.NotNull(options);
Assert.Equal(connectionString, options.Value.ConnectionString);
}
}
```
--------------------------------------------------------------------------------
/tests/MSSQL.MCP.IntegrationTests/Infrastructure/DatabaseTestFixture.cs:
--------------------------------------------------------------------------------
```csharp
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using MSSQL.MCP.Configuration;
using MSSQL.MCP.Database;
using Testcontainers.MsSql;
using Xunit;
namespace MSSQL.MCP.IntegrationTests.Infrastructure;
/// <summary>
/// Test fixture that provides a SQL Server container for integration tests.
/// This fixture is shared across all tests in the collection to improve performance.
/// </summary>
public class DatabaseTestFixture : IAsyncLifetime
{
private MsSqlContainer? _container;
public string ConnectionString { get; private set; } = string.Empty;
public ISqlConnectionFactory ConnectionFactory { get; private set; } = null!;
public async Task InitializeAsync()
{
// Start SQL Server container
_container = new MsSqlBuilder()
.WithImage("mcr.microsoft.com/mssql/server:2022-latest")
.WithPassword("Test123!")
.WithCleanUp(true)
.Build();
await _container.StartAsync();
ConnectionString = _container.GetConnectionString();
// Create connection factory for tests
var services = new ServiceCollection();
services.Configure<DatabaseOptions>(options =>
{
options.ConnectionString = ConnectionString;
});
services.AddSingleton<ISqlConnectionFactory, SqlConnectionFactory>();
services.AddLogging(builder => builder.AddConsole());
var serviceProvider = services.BuildServiceProvider();
ConnectionFactory = serviceProvider.GetRequiredService<ISqlConnectionFactory>();
// Verify connection works
var isValid = await ConnectionFactory.ValidateConnectionAsync();
if (!isValid)
{
throw new InvalidOperationException("Failed to establish connection to test SQL Server container");
}
}
public async Task DisposeAsync()
{
if (_container != null)
{
await _container.DisposeAsync();
}
}
/// <summary>
/// Creates a test database with sample schema and data
/// </summary>
public async Task CreateTestDatabaseAsync()
{
await using var connection = await ConnectionFactory.CreateOpenConnectionAsync();
// Create test schema and tables
var setupSql = @"
-- Create test schema
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'TestSchema')
EXEC('CREATE SCHEMA TestSchema');
-- Create Users table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Users' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
CREATE TABLE dbo.Users (
Id INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Email NVARCHAR(255) UNIQUE NOT NULL,
CreatedDate DATETIME2 DEFAULT GETUTCDATE(),
IsActive BIT DEFAULT 1
);
END;
-- Create Orders table with foreign key
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Orders' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
CREATE TABLE dbo.Orders (
Id INT IDENTITY(1,1) PRIMARY KEY,
UserId INT NOT NULL,
OrderDate DATETIME2 DEFAULT GETUTCDATE(),
Total DECIMAL(10,2) NOT NULL,
Status NVARCHAR(50) DEFAULT 'Pending',
FOREIGN KEY (UserId) REFERENCES dbo.Users(Id)
);
END;
-- Create table in test schema
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Products' AND schema_id = SCHEMA_ID('TestSchema'))
BEGIN
CREATE TABLE TestSchema.Products (
Id INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
Category NVARCHAR(50),
InStock BIT DEFAULT 1
);
END;
-- Insert sample data
IF NOT EXISTS (SELECT * FROM dbo.Users)
BEGIN
INSERT INTO dbo.Users (Name, Email) VALUES
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]'),
('Bob Johnson', '[email protected]');
INSERT INTO dbo.Orders (UserId, Total, Status) VALUES
(1, 99.99, 'Completed'),
(1, 149.99, 'Pending'),
(2, 75.50, 'Completed'),
(3, 200.00, 'Cancelled');
INSERT INTO TestSchema.Products (Name, Price, Category) VALUES
('Laptop', 999.99, 'Electronics'),
('Mouse', 29.99, 'Electronics'),
('Book', 15.99, 'Education');
END;
";
await using var command = new Microsoft.Data.SqlClient.SqlCommand(setupSql, connection);
await command.ExecuteNonQueryAsync();
}
/// <summary>
/// Cleans up test data between tests
/// </summary>
public async Task CleanupTestDataAsync()
{
await using var connection = await ConnectionFactory.CreateOpenConnectionAsync();
var cleanupSql = @"
DELETE FROM dbo.Orders;
DELETE FROM dbo.Users;
DELETE FROM TestSchema.Products;
-- Reset identity seeds
DBCC CHECKIDENT ('dbo.Orders', RESEED, 0);
DBCC CHECKIDENT ('dbo.Users', RESEED, 0);
DBCC CHECKIDENT ('TestSchema.Products', RESEED, 0);
";
await using var command = new Microsoft.Data.SqlClient.SqlCommand(cleanupSql, connection);
await command.ExecuteNonQueryAsync();
}
}
```
--------------------------------------------------------------------------------
/tests/MSSQL.MCP.IntegrationTests/Tools/SqlExecutionToolTests.cs:
--------------------------------------------------------------------------------
```csharp
using Microsoft.Extensions.Logging.Abstractions;
using MSSQL.MCP.IntegrationTests.Infrastructure;
using MSSQL.MCP.Tools;
using Xunit;
namespace MSSQL.MCP.IntegrationTests.Tools;
/// <summary>
/// Integration tests for SqlExecutionTool that validate all MCP tools work correctly
/// with a real SQL Server database.
/// </summary>
[Collection("Database")]
public class SqlExecutionToolTests(DatabaseTestFixture fixture) : IAsyncLifetime
{
private readonly SqlExecutionTool _tool = new(fixture.ConnectionFactory, NullLogger<SqlExecutionTool>.Instance);
public async Task InitializeAsync()
{
await fixture.CreateTestDatabaseAsync();
}
public async Task DisposeAsync()
{
await fixture.CleanupTestDataAsync();
}
#region ExecuteSql Tests
[Fact]
public async Task ExecuteSql_SelectQuery_ReturnsFormattedResults()
{
// Act
var result = await _tool.ExecuteSql("SELECT Id, Name, Email FROM dbo.Users ORDER BY Id");
// Assert
Assert.NotNull(result);
Assert.Contains("John Doe", result);
Assert.Contains("[email protected]", result);
Assert.Contains("Bob Johnson", result);
// Should contain table headers
Assert.Contains("Id", result);
Assert.Contains("Name", result);
Assert.Contains("Email", result);
}
[Fact]
public async Task ExecuteSql_WithClause_ReturnsResults()
{
// Act
var result = await _tool.ExecuteSql(@"
WITH ActiveUsers AS (
SELECT Id, Name FROM dbo.Users WHERE IsActive = 1
)
SELECT * FROM ActiveUsers ORDER BY Id");
// Assert
Assert.NotNull(result);
Assert.Contains("John Doe", result);
Assert.Contains("Jane Smith", result);
Assert.Contains("Bob Johnson", result);
}
[Fact]
public async Task ExecuteSql_InsertQuery_ReturnsRowsAffected()
{
// Act
var result = await _tool.ExecuteSql("INSERT INTO dbo.Users (Name, Email) VALUES ('Test User', '[email protected]')");
// Assert
Assert.NotNull(result);
Assert.Contains("Rows affected: 1", result);
Assert.Contains("successfully", result);
}
[Fact]
public async Task ExecuteSql_UpdateQuery_ReturnsRowsAffected()
{
// Act
var result = await _tool.ExecuteSql("UPDATE dbo.Users SET Name = 'Updated Name' WHERE Id = 1");
// Assert
Assert.NotNull(result);
Assert.Contains("Rows affected: 1", result);
Assert.Contains("successfully", result);
}
[Fact]
public async Task ExecuteSql_DeleteQuery_ReturnsRowsAffected()
{
// Act
var result = await _tool.ExecuteSql("DELETE FROM dbo.Orders WHERE Status = 'Cancelled'");
// Assert
Assert.NotNull(result);
Assert.Contains("Rows affected: 1", result);
Assert.Contains("successfully", result);
}
[Fact]
public async Task ExecuteSql_CreateTable_ReturnsSuccess()
{
// Act
var result = await _tool.ExecuteSql(@"
CREATE TABLE dbo.TestTable (
Id INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50) NOT NULL
)");
// Assert
Assert.NotNull(result);
Assert.Contains("successfully", result);
}
[Fact]
public async Task ExecuteSql_EmptyQuery_ReturnsError()
{
// Act
var result = await _tool.ExecuteSql("");
// Assert
Assert.NotNull(result);
Assert.Contains("Error: SQL query cannot be empty", result);
}
[Fact]
public async Task ExecuteSql_InvalidQuery_ReturnsError()
{
// Act - Use valid T-SQL syntax but invalid operation to get SQL Server error
var result = await _tool.ExecuteSql("SELECT * FROM InvalidTable123");
// Assert
Assert.NotNull(result);
Assert.Contains("SQL Error:", result);
}
[Fact]
public async Task ExecuteSql_NonSqlInput_ReturnsValidationError()
{
// Act - Use invalid T-SQL syntax to trigger validation error
var result = await _tool.ExecuteSql("INVALID SQL QUERY");
// Assert
Assert.NotNull(result);
Assert.Contains("Error: Invalid T-SQL syntax", result);
Assert.Contains("This tool only accepts valid Microsoft SQL Server T-SQL statements", result);
}
[Fact]
public async Task ExecuteSql_SelectFromNonExistentTable_ReturnsError()
{
// Act
var result = await _tool.ExecuteSql("SELECT * FROM NonExistentTable");
// Assert
Assert.NotNull(result);
Assert.Contains("SQL Error:", result);
}
#endregion
#region ListTables Tests
[Fact]
public async Task ListTables_ReturnsAllTables()
{
// Act
var result = await _tool.ListTables();
// Assert
Assert.NotNull(result);
// Should contain our test tables
Assert.Contains("Users", result);
Assert.Contains("Orders", result);
Assert.Contains("Products", result);
// Should show schema information
Assert.Contains("dbo", result);
Assert.Contains("TestSchema", result);
// Should show table type
Assert.Contains("BASE TABLE", result);
// Should show row counts
Assert.Contains("3", result); // Users table should have 3 rows
Assert.Contains("4", result); // Orders table should have 4 rows
}
[Fact]
public async Task ListTables_ShowsCorrectColumns()
{
// Act
var result = await _tool.ListTables();
// Assert
Assert.NotNull(result);
// Should contain expected column headers
Assert.Contains("TABLE_SCHEMA", result);
Assert.Contains("TABLE_NAME", result);
Assert.Contains("TABLE_TYPE", result);
Assert.Contains("ROW_COUNT", result);
}
#endregion
#region ListSchemas Tests
[Fact]
public async Task ListSchemas_ReturnsAllSchemas()
{
// Act
var result = await _tool.ListSchemas();
// Assert
Assert.NotNull(result);
// Should contain standard schemas
Assert.Contains("dbo", result);
Assert.Contains("sys", result);
Assert.Contains("INFORMATION_SCHEMA", result);
// Should contain our test schema
Assert.Contains("TestSchema", result);
// Should show column headers
Assert.Contains("SCHEMA_NAME", result);
Assert.Contains("SCHEMA_OWNER", result);
}
#endregion
#region Error Handling Tests
[Fact]
public async Task ExecuteSql_WithCancellation_HandlesGracefully()
{
using var cts = new CancellationTokenSource();
// Start a long-running query
var task = _tool.ExecuteSql("WAITFOR DELAY '00:00:10'; SELECT 1", cts.Token);
// Cancel immediately
await cts.CancelAsync();
// Should complete without throwing
var result = await task;
Assert.NotNull(result);
}
[Fact]
public async Task ListTables_WithCancellation_HandlesGracefully()
{
using var cts = new CancellationTokenSource();
await cts.CancelAsync();
// Should handle cancellation gracefully
var result = await _tool.ListTables(cts.Token);
Assert.NotNull(result);
}
#endregion
#region Data Validation Tests
[Fact]
public async Task ExecuteSql_ComplexJoin_ReturnsCorrectData()
{
// Act
var result = await _tool.ExecuteSql(@"
SELECT
u.Name as UserName,
COUNT(o.Id) as OrderCount,
SUM(o.Total) as TotalAmount
FROM dbo.Users u
LEFT JOIN dbo.Orders o ON u.Id = o.UserId
WHERE u.IsActive = 1
GROUP BY u.Id, u.Name
ORDER BY u.Name");
// Assert
Assert.NotNull(result);
Assert.Contains("John Doe", result);
Assert.Contains("Jane Smith", result);
Assert.Contains("Bob Johnson", result);
// Should show aggregated data
Assert.Contains("249.98", result); // John's total orders
Assert.Contains("75.50", result); // Jane's total orders
Assert.Contains("200.00", result); // Bob's total orders
}
[Fact]
public async Task ExecuteSql_ParameterizedQuery_WorksCorrectly()
{
// Note: This test shows that regular parameterized queries work
// In a real MCP implementation, you might want to add parameter support
var result = await _tool.ExecuteSql("SELECT * FROM dbo.Users WHERE Id = 1");
Assert.NotNull(result);
Assert.Contains("John Doe", result);
Assert.DoesNotContain("Jane Smith", result);
}
#endregion
}
```
--------------------------------------------------------------------------------
/src/MSSQL.MCP/Tools/SqlExecutionTool.cs:
--------------------------------------------------------------------------------
```csharp
using System.ComponentModel;
using System.Text.RegularExpressions;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using ModelContextProtocol.Server;
using MSSQL.MCP.Database;
namespace MSSQL.MCP.Tools;
[McpServerToolType]
public class SqlExecutionTool(ISqlConnectionFactory connectionFactory, ILogger<SqlExecutionTool> logger)
{
// Regex to detect valid T-SQL keywords at the beginning of queries
private static readonly Regex ValidTSqlStartPattern = new(
@"^\s*(SELECT|INSERT|UPDATE|DELETE|WITH|CREATE|ALTER|DROP|GRANT|REVOKE|EXEC|EXECUTE|DECLARE|SET|USE|BACKUP|RESTORE|TRUNCATE|MERGE)\s+",
RegexOptions.IgnoreCase | RegexOptions.Compiled);
[McpServerTool, Description(@"Execute T-SQL queries against the connected Microsoft SQL Server database.
IMPORTANT: This tool ONLY accepts valid T-SQL (Transact-SQL) syntax for Microsoft SQL Server.
Supported operations:
- SELECT statements for data retrieval
- INSERT, UPDATE, DELETE for data modification
- CREATE, ALTER, DROP for schema changes
- WITH clauses for CTEs (Common Table Expressions)
- EXEC/EXECUTE for stored procedures
- And other valid T-SQL statements
Examples of valid T-SQL:
- SELECT * FROM Users WHERE Active = 1
- INSERT INTO Products (Name, Price) VALUES ('Widget', 19.99)
- UPDATE Customers SET Status = 'Active' WHERE ID = 123
- CREATE TABLE Orders (ID int PRIMARY KEY, CustomerID int)
The query parameter must contain ONLY the T-SQL statement - no explanations, markdown, or other text.")]
public async Task<string> ExecuteSql(
[Description(@"The T-SQL query to execute. Must be valid Microsoft SQL Server T-SQL syntax only.
Examples: 'SELECT * FROM Users', 'INSERT INTO Products VALUES (1, ''Name'')', 'CREATE TABLE Test (ID int)'
Do NOT include explanations, markdown formatting, or non-SQL text.")]
string query,
CancellationToken cancellationToken = default)
{
// Log the incoming query for debugging
logger.LogInformation("Received SQL execution request. Query length: {QueryLength} characters", query.Length );
logger.LogDebug("SQL Query received: {Query}", query);
if (string.IsNullOrWhiteSpace(query))
{
logger.LogWarning("Empty or null query received");
return "Error: SQL query cannot be empty";
}
// Validate that the query looks like T-SQL
var trimmedQuery = query.Trim();
if (!ValidTSqlStartPattern.IsMatch(trimmedQuery))
{
logger.LogWarning("Invalid T-SQL query received. Query does not start with valid T-SQL keyword: {QueryStart}",
trimmedQuery.Length > 50 ? trimmedQuery[..50] + "..." : trimmedQuery);
return @"Error: Invalid T-SQL syntax. This tool only accepts valid Microsoft SQL Server T-SQL statements.
Valid T-SQL statements must start with keywords like:
- SELECT (for data retrieval)
- INSERT, UPDATE, DELETE (for data modification)
- CREATE, ALTER, DROP (for schema changes)
- WITH (for CTEs)
- EXEC/EXECUTE (for stored procedures)
- And other valid T-SQL keywords
Examples:
✓ SELECT * FROM Users
✓ INSERT INTO Products (Name) VALUES ('Test')
✓ CREATE TABLE Orders (ID int)
✗ Please show me all users
✗ Can you create a table for orders?
✗ ```sql SELECT * FROM Users```
Please provide only the T-SQL statement without explanations or formatting.";
}
try
{
logger.LogInformation("Executing T-SQL query starting with: {QueryStart}",
trimmedQuery.Length > 30 ? trimmedQuery[..30] + "..." : trimmedQuery);
await using var connection = await connectionFactory.CreateOpenConnectionAsync(cancellationToken);
await using var command = new SqlCommand(query, connection);
// Determine if this is a SELECT query or a command
var isSelectQuery = trimmedQuery.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase) ||
trimmedQuery.StartsWith("WITH", StringComparison.OrdinalIgnoreCase);
if (isSelectQuery)
{
// Handle SELECT queries - return data
await using var reader = await command.ExecuteReaderAsync(cancellationToken);
var result = await FormatQueryResults(reader, cancellationToken);
logger.LogInformation("SELECT query executed successfully");
return result;
}
else
{
// Handle INSERT/UPDATE/DELETE/DDL - return affected rows
var rowsAffected = await command.ExecuteNonQueryAsync(cancellationToken);
var result = $"Query executed successfully. Rows affected: {rowsAffected}";
logger.LogInformation("Non-SELECT query executed successfully. Rows affected: {RowsAffected}", rowsAffected);
return result;
}
}
catch (SqlException ex)
{
logger.LogError(ex, "SQL execution failed with SQL error: {SqlErrorMessage}", ex.Message);
return $"SQL Error: {ex.Message}";
}
catch (Exception ex)
{
logger.LogError(ex, "SQL execution failed with general error: {ErrorMessage}", ex.Message);
return $"Error: {ex.Message}";
}
}
[McpServerTool, Description("List all tables in the database with basic information.")]
public async Task<string> ListTables(CancellationToken cancellationToken = default)
{
try
{
await using var connection = await connectionFactory.CreateOpenConnectionAsync(cancellationToken);
var query = @"
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
t.TABLE_TYPE,
ISNULL(p.rows, 0) as ROW_COUNT
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN (
SELECT
SCHEMA_NAME(o.schema_id) as schema_name,
o.name as table_name,
SUM(p.rows) as rows
FROM sys.objects o
JOIN sys.partitions p ON o.object_id = p.object_id
WHERE o.type = 'U' AND p.index_id IN (0,1)
GROUP BY o.schema_id, o.name
) p ON t.TABLE_SCHEMA = p.schema_name AND t.TABLE_NAME = p.table_name
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME";
await using var command = new SqlCommand(query, connection);
await using var reader = await command.ExecuteReaderAsync(cancellationToken);
return await FormatQueryResults(reader, cancellationToken);
}
catch (Exception ex)
{
return $"Error listing tables: {ex.Message}";
}
}
[McpServerTool, Description("List all schemas (databases) available in the SQL Server instance.")]
public async Task<string> ListSchemas(CancellationToken cancellationToken = default)
{
try
{
await using var connection = await connectionFactory.CreateOpenConnectionAsync(cancellationToken);
var query = @"
SELECT
SCHEMA_NAME,
SCHEMA_OWNER,
DEFAULT_CHARACTER_SET_CATALOG,
DEFAULT_CHARACTER_SET_SCHEMA,
DEFAULT_CHARACTER_SET_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
ORDER BY SCHEMA_NAME";
await using var command = new SqlCommand(query, connection);
await using var reader = await command.ExecuteReaderAsync(cancellationToken);
return await FormatQueryResults(reader, cancellationToken);
}
catch (Exception ex)
{
return $"Error listing schemas: {ex.Message}";
}
}
private static async Task<string> FormatQueryResults(SqlDataReader reader, CancellationToken cancellationToken)
{
var result = new System.Text.StringBuilder();
if (!reader.HasRows)
{
return "Query executed successfully. No rows returned.";
}
// Get column headers
var columnCount = reader.FieldCount;
var columnNames = new string[columnCount];
var columnWidths = new int[columnCount];
for (int i = 0; i < columnCount; i++)
{
columnNames[i] = reader.GetName(i);
columnWidths[i] = Math.Max(columnNames[i].Length, 10); // Minimum width of 10
}
// Read all rows to determine column widths
var rows = new List<object[]>();
while (await reader.ReadAsync(cancellationToken))
{
var row = new object[columnCount];
for (int i = 0; i < columnCount; i++)
{
row[i] = reader.IsDBNull(i) ? "NULL" : reader.GetValue(i);
var valueLength = row[i].ToString()?.Length ?? 4;
columnWidths[i] = Math.Max(columnWidths[i], valueLength);
}
rows.Add(row);
}
// Build header
result.AppendLine(string.Join(" | ", columnNames.Select((name, i) => name.PadRight(columnWidths[i]))));
result.AppendLine(string.Join("-+-", columnWidths.Select(w => new string('-', w))));
// Build data rows
foreach (var row in rows)
{
result.AppendLine(string.Join(" | ", row.Select((value, i) =>
(value.ToString() ?? "NULL").PadRight(columnWidths[i]))));
}
result.AppendLine($"\n({rows.Count} row(s) returned)");
return result.ToString();
}
}
```