In this tutorial we will configure VS Code to use an MCP Server that connects to a SQLite database. In a similar manner, you can also connect to PostgreSQL, MySQL, and SQL Server. This is a very compelling proposition because it allows developers to use AI to assist in generating code that dynamically interacts with data in a relational database.
Prerequisites
You will need to install the following software to proceed:
Visual Studio Code with the GitHub Copilot Chat extension
In the GitHub Copilot Chat panel, choose any Claude model followed by Agent Mode.
Click on the tools icon in the prompt window.
You will see a list of commands that the MCP server can carry out with SQLite.
We can now start querying the database using natural language. Start with this prompt:
You have access to the Northwind database through an MCP server. What are the tables in the database?
It detects that it can use the list_tables command.
Click on Continue. I got the following output:
Similarly, you can ask another question like:
Display the contents of the suppliers table.
Yet, another question:
What are the products supplied by "Exotic Liquids"?
Conclusion
It is very easy to connect VS Code with a relational database MCP server. In addition, you can similarly connect MCP Servers any client C# application. MCP Servers open a ton of possibilities for AI aided software development.
In this tutorial we will configure VS Code to use an MCP Server that connects to a PostgreSQL database. In a similar manner, you can also connect to SQLite, MySQL, and SQL Server. This is a very compelling proposition because it allows developers to use AI to assist in generating code that dynamically interacts with data in a relational database.
Prerequisites
You will need to install the following software to proceed:
Visual Studio Code with the GitHub Copilot Chat extension
Docker Desktop
Latest versions of node.js, npm, and npx
The Database
We will run a PostgreSQL database with a sample Northwind database in a Docker container. Therefore:
Start Docker Desktop on your computer
Run a PostgreSQL container by executing this command in a terminal window:
docker run --name psqlnw -e POSTGRES_PASSWORD=VerySecret -p 5433:5432 -d melmasry/nw-psql:1.0.0
In the GitHub Copilot Chat panel, choose any Claude model followed by Agent Mode.
Click on the tools icon in the prompt window.
You will see a list of commands that the MCP server can carry out with PostgreSQL.
We can now start querying the database using natural language. Start with this prompt:
You have access to the Northwind database through an MCP server. What are the tables in the database?
It detects that it can use the list_tables command.
Click on Continue. I got the following output:
Similarly, you can ask another question like:
Display the contents of the suppliers table.
Yet, another question:
What are the products supplied by "Exotic Liquids"?
Conclusion
It is very easy to connect VS Code with a relational database MCP server. In addition, you can similarly connect MCP Servers any client C# application. MCP Servers open a ton of possibilities for AI aided software development.
In this tutorial we will configure VS Code to use an MCP Server that connects to a MySQL database. In a similar manner, you can also connect to SQLite, PostgresDB, and SQL Server. This is a very compelling proposition because it allows developers to use AI to assist in generating code that dynamically interacts with data in a relational database.
Prerequisites
You will need to install the following software to proceed:
Visual Studio Code with the GitHub Copilot Chat extension
Docker Desktop
Latest versions of node.js, npm, and npx
The Database
We will run a MySQL database with a sample Northwind database in a Docker container. Therefore:
Start Docker Desktop on your computer
Run a MySQL container by executing this command in a terminal window:
docker run -p 3366:3306 --name mariadb-nw -e MYSQL_ROOT_PASSWORD=secret -d melmasry/maria-nw:1.0.0
In the GitHub Copilot Chat panel, choose any Claude model followed by Agent Mode.
Click on the tools icon in the prompt window.
You will see a list of commands that the MCP server can carry out with MySQL.
We can now start querying the database using natural language. Start with this prompt:
You have access to the Northwind database through an MCP server. What are the tables in the database?
Click on Continue. I got the following output:
Similarly, you can ask another question like:
Display the contents of the suppliers table.
Yet, another question:
What are the products supplied by "Exotic Liquids"?
Conclusion
It is very easy to connect VS Code with a relational database MCP server. In addition, you can similarly connect MCP Servers any client C# application. MCP Servers open a ton of possibilities for AI aided software development.
This article provides a step-by-step guide on creating and configuring a C# MCP server in Visual Studio Code, enabling seamless integration with a students API for development purposes. It covers the entire process, from setting up the server, deploying it Docker Hub, to consuming its functionality within a command-line C# application.
Let us add additional functionality for reversing text and displaying the length of text. Inside the EchoTool class in Program.cs, add these two additional methods.
[McpServerTool, Description("Echoes back message in reverse.")]
public static string Reverse(string message) => new string(message.Reverse().ToArray());
[McpServerTool, Description("Returns length of the message.")]
public static int Length(string message) => message.Length;
Configuring VS Code
The next step is to get VS Code to recognize the MCP server.
In the root of your solution (not project), create a folder named .vscode. Then, inside the .vscode folder, create a text file named mcp.json. You will see a button "Add Server...". If you do not see the "Add Server..." button, do the above using the default tab in VS Code, instead of Solution Explorer.
Select “Command (stdio) Run a local command that implements the MCP server”:
Command to run: dotnet run
Server ID: student-mcp-server
The following content is created in mcp.json file:
{
"servers": {
"student-msp-server": {
"type": "stdio",
"command": "dotnet",
"args": [
"run"
]
}
}
}
Right-click on the .csproj file and copy path:
With the path saved in your computer’s clipboard, update the args section in mcp.json so it looks like this:
In a Models folder, add a Student class that represents each item of data in the API endpoint, with this content:
public class Student {
public int StudentId { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
public string? School { get; set; }
public override string ToString() {
return $"Student ID: {StudentId}, First Name: {FirstName}, Last Name: {LastName}, School: {School}";
}
}
In the Models folder, add a class named StudentContext with this code:
The StudentContext partial class is used for source generation of optimized JSON serialization and deserialization code with System.Text.Json in .NET.
Also inside the Models folder, add another class named StudentService with this code:
public class StudentService {
readonly HttpClient _httpClient = new();
private List<Student>? _studentsCache = null;
private DateTime _cacheTime;
private readonly TimeSpan _cacheDuration = TimeSpan.FromMinutes(10); // adjust as needed
private async Task<List<Student>> FetchStudentsFromApi() {
try {
var response = await _httpClient.GetAsync("https://apipool.azurewebsites.net/api/students");
if (response.IsSuccessStatusCode) {
var studentsFromApi = await response.Content.ReadFromJsonAsync<List<Student>>(StudentContext.Default.ListStudent);
return studentsFromApi ?? [];
}
} catch (Exception ex) {
await Console.Error.WriteLineAsync($"Error fetching students from API: {ex.Message}");
}
return [];
}
public async Task<List<Student>> GetStudents() {
if (_studentsCache == null || DateTime.UtcNow - _cacheTime > _cacheDuration) {
_studentsCache = await FetchStudentsFromApi();
_cacheTime = DateTime.UtcNow;
}
return _studentsCache;
}
public async Task<Student?> GetStudentByFullName(string name) {
var students = await GetStudents();
var nameParts = name.Split(' ', 2);
if (nameParts.Length != 2) {
Console.WriteLine("Name does not contain two parts");
return null;
}
var firstName = nameParts[0].Trim();
var lastName = nameParts[1].Trim();
foreach (var s in students.Where(s => s.FirstName?.Contains(firstName, StringComparison.OrdinalIgnoreCase) == true)) {
Console.WriteLine($"Found partial first name match: '{s.FirstName}' '{s.LastName}'");
}
var student = students.FirstOrDefault(m => {
var firstNameMatch = string.Equals(m.FirstName, firstName, StringComparison.OrdinalIgnoreCase);
var lastNameMatch = string.Equals(m.LastName, lastName, StringComparison.OrdinalIgnoreCase);
return firstNameMatch && lastNameMatch;
});
return student;
}
public async Task<Student?> GetStudentById(int id) {
var students = await GetStudents();
var student = students.FirstOrDefault(s => s.StudentId == id);
Console.WriteLine(student == null ? $"No student found with ID {id}" : $"Found student: {student}");
return student;
}
public async Task<List<Student>> GetStudentsBySchoolJson(string school) {
var students = await GetStudents();
var filteredStudents = students.Where(s => s.School?.Equals(school, StringComparison.OrdinalIgnoreCase) == true).ToList();
Console.WriteLine(filteredStudents.Count == 0
? $"No students found for school: {school}"
: $"Found {filteredStudents.Count} students for school: {school}");
return filteredStudents;
}
public async Task<List<Student>> GetStudentsByLastName(string lastName) {
var students = await GetStudents();
var filteredStudents = students.Where(s => s.LastName?.Equals(lastName, StringComparison.OrdinalIgnoreCase) == true).ToList();
Console.WriteLine(filteredStudents.Count == 0
? $"No students found with last name: {lastName}"
: $"Found {filteredStudents.Count} students with last name: {lastName}");
return filteredStudents;
}
public async Task<List<Student>> GetStudentsByFirstName(string firstName) {
var students = await GetStudents();
var filteredStudents = students.Where(s => s.FirstName?.Equals(firstName, StringComparison.OrdinalIgnoreCase) == true).ToList();
Console.WriteLine(filteredStudents.Count == 0
? $"No students found with first name: {firstName}"
: $"Found {filteredStudents.Count} students with first name: {firstName}");
return filteredStudents;
}
public async Task<string> GetStudentsJson() {
var students = await GetStudents();
return System.Text.Json.JsonSerializer.Serialize(students);
}
}
Again, in the Models folder, add a class named StudentTools with this content:
[McpServerToolType]
public class StudentTools(StudentService studentService) {
[McpServerTool, Description("Get a list of students and return as JSON array")]
public Task<string> GetStudentsJson() {
return studentService.GetStudentsJson();
}
[McpServerTool, Description("Get a list of students")]
public Task<List<Student>> GetStudents() {
return studentService.GetStudents();
}
[McpServerTool, Description("Get a student by name")]
public Task<Student?> GetStudent([Description("The name of the student to get details for")] string name) {
return studentService.GetStudentByFullName(name);
}
[McpServerTool, Description("Get a student by ID")]
public Task<Student?> GetStudentById([Description("The ID of the student to get details for")] int id) {
return studentService.GetStudentById(id);
}
[McpServerTool, Description("Get a student by ID and return as JSON")]
public async Task<string?> GetStudentByIdJson([Description("The ID of the student to get details for")] int id){
var student = await studentService.GetStudentById(id);
if (student == null) {
return null;
}
return System.Text.Json.JsonSerializer.Serialize(student, StudentContext.Default.Student);
}
[McpServerTool, Description("Get a student by name and return as JSON")]
public async Task<string?> GetStudentJson([Description("The name of the student to get details for")] string name) {
var student = await studentService.GetStudentByFullName(name);
if (student == null) {
return null;
}
return System.Text.Json.JsonSerializer.Serialize(student, StudentContext.Default.Student);
}
[McpServerTool, Description("Get students by school")]
public async Task<List<Student>> GetStudentsBySchool([Description("The name of the school to filter students by")] string school) {
var students = await studentService.GetStudentsBySchoolJson(school);
return students;
}
[McpServerTool, Description("Get a student by Last Name")]
public async Task<List<Student>> GetStudentsByLastName([Description("The last name of the student to filter by")] string lastName) {
var students = await studentService.GetStudentsByLastName(lastName);
return students;
}
[McpServerTool, Description("Get a student by First Name")]
public async Task<List<Student>> GetStudentsByFirstName([Description("The first name of the student to filter by")] string firstName) {
var students = await studentService.GetStudentsByFirstName(firstName);
return students;
}
}
Register StudentService in Program.cs by adding this code right before “await builder.Build().RunAsync();”:
builder.Services.AddSingleton<StudentService>();
In mcp.json, restart the student-mcp-server. It should now show that there are 12 tools.
Enter this prompt in the chat:
Get me a list of students and display them in a table for easy reading here.
You can double-check that the docker image has indeed been published by searching for it in Docker Desktop:
Restart the MCP server. You will notice a new image and running container:
Try out the students MCP server by asking it some questions about students. For example:
List students in the school of mining.
Simple MCP C# client that uses Ollama
Up to now, we have relied on VS Code as our client. In fact, you can make any of your applications a client to an MCP server. Let us create a simple command-line application that takes on the role of a client to our C# MCP Server.
The client app will use ollama to host the small language model (LLM) llama3.2:3b our local computer. If you do not already have ollama installed on your computer, you can get it from https://ollama.com/download.
Pull the LLM with:
ollama pull llama3.2:3b
Inside the root solution folder, McpStudents, add a client console application with the following terminal window commands:
Replace contents of Program.cs in the OllamaMcpClient project with:
using ModelContextProtocol.Client;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.AI;
using System.Text.Json;
// MCP Client Transport
string currentDirectory = Directory.GetCurrentDirectory();
string serverDirectory = Path.GetFullPath(Path.Combine(currentDirectory, "..", "StudentsMcpServer"));
var clientTransport = new StdioClientTransport(new StdioClientTransportOptions
{
Name = "Students MCP Server",
Command = "dotnet",
Arguments = ["run"],
WorkingDirectory = serverDirectory,
});
// Logger
using var loggerFactory = LoggerFactory.Create(builder =>
builder.AddConsole().SetMinimumLevel(LogLevel.Information));
// Create MCP Client
var mcpClient = await McpClientFactory.CreateAsync(clientTransport);
// Get available tools from MCP Server
var mcpTools = await mcpClient.ListToolsAsync();
var toolsJson = JsonSerializer.Serialize(mcpTools, new JsonSerializerOptions { WriteIndented = true });
Console.WriteLine("\nAvailable Tools:\n" + toolsJson);
await Task.Delay(100);
// Configure Ollama LLM Client
var ollamaChatClient = new OllamaChatClient(
new Uri("http://localhost:11434/"),
"llama3.2:3b"
);
var chatClient = new ChatClientBuilder(ollamaChatClient)
.UseLogging(loggerFactory)
.UseFunctionInvocation()
.Build();
// Prompt loop
Console.WriteLine("Type your message below (type 'exit' to quit):");
while (true)
{
Console.Write("\n You: ");
var userInput = Console.ReadLine();
if (string.IsNullOrWhiteSpace(userInput))
continue;
if (userInput.Trim().ToLower() == "exit")
{
Console.WriteLine("Exiting chat...");
break;
}
var messages = new List<ChatMessage>
{
new(ChatRole.System, "You are a helpful assistant."),
new(ChatRole.User, userInput)
};
try
{
var response = await chatClient.GetResponseAsync(
messages,
new ChatOptions { Tools = mcpTools.ToArray<AITool>() });
var assistantMessage = response.Messages.LastOrDefault(m => m.Role == ChatRole.Assistant);
if (assistantMessage != null)
{
var textOutput = string.Join($" ", assistantMessage.Contents.Select(c => c.ToString()));
Console.WriteLine("\n AI: " + textOutput);
}
else
{
Console.WriteLine("\n AI: (no assistant message received)");
}
}
catch (Exception ex)
{
Console.WriteLine($"\n Error: {ex.Message}");
}
}
Let's find out if our client app works. Inside a terminal window in the OllamaMcpClient folder, run the console app with:
dotnet run
I asked for details about the student with ID = 15 and got this output:
MCP Client using OpenAI or Azure OpenAI
Visit source code on GitHub to view MCP client code that works with OpenAI or Azure OpenAI.
Conclusion
By now, you have experienced the power of MCP and how it can be used to connect your custom resources which can be connected to AI. This not only helps with the process of software development, but it also opens up the opportunity to link your apps to live resources.
