Full transcript of “MCP (Model Context Protocol) in C# - Talk with your database!”. Click a timestamp to open the video at that moment. Need another video? Use the YouTube transcript generator.
0:00 Hi, my name is Hammed Fati. Welcome to my channel. Today I want to talk about MCP or model context protocol. What is MCP? MCP is like a universal connector that lets AI assistant uh talk to external tools or databases. Think of it as a bridge that connects your AI to the real world. Just like a universal USB port for AI, one standard connector that uh works with any compatible device. You don't need different c uh cables for every tool. It's plug and play and data flows both ways. MCP works the same way. One standard protocol where AI assistants can connect to any MCP
0:46 compatible tool. plugandplay compatibility and bidirectional communication where AI can both read data and perform actions. Who made it? Anthropic, the company behind cloud AI created MCP because AI assistants were stuck in isolation. Uh they couldn't access live data or use external tools. Every connection had to build from a scratch which was slow and messy. MCP has three simple components uh that work together. Host client server. Host uh is the AI brain like cloud or GitHub copilot that
1:34 want to use tools and data. Client is the app uh we interact with like visual studio code or cloud chat interface that connects everything together. Server is the actual tool or database that provides the functionality. I'm using copilot in this course which is available in visual studio code in agent mode. When I ask copilot for example find members who haven't returned their books by the due date uh visual studio code as a client takes my request GitHub copilot as h
2:20 understand what I want my mcp server that we build it together in this course with the SQL light database do the actual work results flow back to me through the chain. I want to build a MCP server that connects to SQLite database and acts as a smart database assistant. It helps us uh to chat with database structure, write queries, explain table relationships, view and triggers, help add new uh tables or columns to the database, and also serve
3:05 as a perfect onboarding tool for new team members. Let's dive to the code. Okay, first thing is uh package references. We have uh SQL light database. We will create it together. Uh so we need Microsoft data SQL light. I need Dapper to write queries easier. Uh I need YAML.NET to present our data to the AI. So I guess the best uh thing uh the best format for AI to say what we
3:50 want as a text is markdown and I believe YAML is somehow similar to it especially when we have hierarchical data. uh we use model context protocol uh nug get package which is a official car SDK for MCP protocol and we need also Microsoft extension host for our MCP server. I also have a app setting to set my uh database path. uh this is the only things you need to start the project. So the main part for this project is
4:35 gathering the information we want to talk with SQLite. So we need to get all information and metadata from SQLite. So I need the name of tables, their relationships, foreign keys, primary keys, views, triggers, everything. So I have created the skill light schema service which accept connection string and they are very easy queries uh it's not really complex and you always have access to the source code uh via YouTube description you can check it it's a very simple query uh I'm just getting uh for example for this method all the tables
5:22 and information for all of them which contains uh triggers, columns, primary keys, foreign keys and indexes. For each one we have some uh SQL query. The next thing is get a specific table based on the table name and load those information. I need also to know about views. So I need to return uh view details for all of these. We have also models for example SQL light column contains name type not null primary key
6:08 or for example the relation u ship between the tables from table from column to table to column. So you need uh to extract information for this use case it's SQL light for your specific use case uh can be anything you just need to um share your data with the AI. So the next thing is is get foreign key relations and then uh create a table. I want to be able to create a table with a prompt. So AI can uh get the table name and the columns which is the name and
6:53 the type and create it for me. Also it's important to give the feedback to AI. For example in here when I created it successfully I share the output and whenever uh it has a exception I share it as a text because AI understand the text. I I know about the image, audio or video but for this use case we need text. So it's important to share the feedback and output with AI. Uh so if uh any exception happened just share the result with AI too. I want to add a table column also table name uh and column name and column type will be part
7:41 of the method. And then with the alter table SQL light can handle it. Again I sharing any exception if happened and uh for all of the information I need uh I have a specific method uh for them. So we have everything we want. We have also app setting here. It's just a pass of book craft db the name of the database. uh I chose. So uh in my opinion uh now we can build our SQLite database. So let me uh create a new connector
8:27 here SQL light and as I said uh the name of database will be bookcraft. Okay. Now we have MTSQLite database. Uh I provided um query for you. This is a bookcraft database. It's about uh uh library management system. So it contains uh payment, fines, loan, books, books copy and uh a lot of things related to this
9:14 database. uh just consider it's uh not important this database is perfect or designed well we just want to chat with it and get the information about it so for now I just copy paste the script here and I back here to select uh skill editor and I want to execute all okay u no errors no problems so I should be able to see tables, yes, views, yes. For example, for loans, I should see triggers, indexes, yes, all are available. Let's back to the code and
10:02 uh run the application in the programs for now. Uh I read uh the pass from app settings. I created SQLite connection strings and I pass it to SQLite schema service and I want to show you the result. As I said uh we should pass a string to the AI and it's important we somehow categorize the information uh to AI with with some title some uh hierarchical order. So I guess uh markdown will be the best option but uh we don't have any built-in to markdown object uh extension method for example
10:48 and uh I want to use YAML for this. So I guess YAML is good enough to do this task. For converting to YAML I just created uh an extension method uh under extensions folder. You can find it in here and uh it's just a toy uh method takes object and call the serializer from yaml.net library and pass it as a text to us. Uh I just take two from the tables to be able to show them in uh the console and the terminal to you. So let me run the
11:33 application and um let me in here launch it. Okay, let's see how it's look like uh and what we are passing to the AI. So as you can see for example we have audit log the column is here under the name of audit log and log id type integer not null or not. Uh action here timestamp and all of the information. Uh it has trigger or index or not. Uh the other one is a specific table for for example loans here. uh again list of columns and
12:20 um primary key foreign key indexes are here triggers are here with the SQL source code uh it's related to the loans u um table also we have uh information uh of views the name of views the SQL uh query for them and also the foreign key relations from table uh from a column to table to column and if uh there is any action on update or delete. So as you can see you have almost all information as a part of es schema information and we can feed AI for them. Okay, let me stop the app.
13:15 And I guess we don't need to execute this part anymore. So I just comment it. Okay, if you remember I told you we need Microsoft host uh to build our server. So in the program and the main method you need to get configuration app setting data first and then in here uh I created empty application builder and I just added uh add MCP server with
14:02 a uh stddio server transport with tools from assembly and I just build and run. So now it's the time to create bookcraft uh DBMCP server and I'm just passing the configuration because we also need the connection string there. So I just pass the connection. Let's see how it works and how I built uh our MCP server. It's very easy. Believe me, you just uh did 80% of the work by uh gathering the uh information and collecting the
14:48 information. So first step is creating a static class and uh you can add an attribute MCP server tool type. this define u as this class as a MCP server tool and you can add description. This description is a critical part of this MCP server. This is the way you tell AI okay what is this class about and when AI should call it. For example, in here I just mentioned bookcraft DBMCP server is a specialized MCP server for uh exploring and
15:36 modifying bookcraft DBSQL light and I mentioned what what is it about and what you can get from it. retrieve full database schema information. For example, uh inspect individual tables and views. All operation uh is based on the SQL light syntax. So if I ask something else, help me. Okay, this uh bookcraft DB uh doesn't support for example SQL server or SQL MySQL or Postgres SQL. And I also I mentioned some um information uh to AI when you should call this when you can uh when you want to write SQL queries understand database structure or
16:23 add or change a schema. Uh that's the information as I said we have a configure and I need a configuration to get DB pass and I created the connection string method to get the connection string to pass it to my um SQL light schema service and I just created uh the SQL light schema service here and it will be accessible in the for for the whole static methods in this class. Now we want to create our first tool. It's it's really good if you categorize and explain based on it and pass it to the AI. Then AI understand
17:09 based on the prompt should call one of these tools or maybe some of them. We will see some examples about it in uh later in the video. Again, uh I just mentioned um MCP server tool. Uh just be careful. This one is MCP server tool type. But in here it's MCP server tool. It's a static method. And again the brilliant part is the description. I explained for complete general overview of bookcraft database. You should call this method and this method returns just
17:56 static information about what is the bookcraft DB or who created when created and all general information. The next thing is get tables info. I want to get all information and pass it as a YAML to AI. Again I just described when you should call this method. If someone askked you about list of tables, show E schema, where are the tables or something like this. So be careful about description. If you explained well AI uh can easily understand and call your uh tool or a static method uh based on the prompt
18:42 correctly. The other one is get table info. AI should detect the table name and pass it to this method. Uh the other one is the table relation. I mentioned if someone asked as a relation of the foreign keys just call this method. I'm just saying the SQLite schema server the related method and pass it as a YAML. Same for get the views and same for uh the other methods you want to add for extra information. Also we have two kind of actions. One of them is add new uh
19:28 column. It's uh needs a table name, column name and column type. In the description I just mentioned uh what is required what should be and very important part is here as a rule I mentioned uh if there is a invalid type in the prompt just choose the closest uh SQL light for example if I said uh a string this uh method should give uh should get uh text because AI should understand a string is not available. as a valid skillite type and should convert it to the text. We will see an example for this one and also AI can create a
20:13 table. We have a complex data type maybe for AI as a dictionary pairs of the name and type but I explain it well as a part of description. I just gave the AI as an example here. So id trigger primary key auto incre uh increment so AI can understand it as a example and pass it correctly to our uh function. So it's time to create our MCP JSON file uh for in uh vis using in visual studio code to run a copilot in agent mode and use
21:01 the tools. So for this purpose you need to create folder here and name it avs code and inside it create another JSON file as mcp.json and you just need to copy the crochet pass. So let me do that and back in here add server here use the command stddio and write net run dash project and the path of the CS project and the name is there and you already have it. So we can just start or stop the u
21:52 server. So if I click on this I I can start a stop restart and you can see seven tools found. These are exact uh seven aesthetic methods uh we created. So I can just chat here with the co-pilot but I don't want to do uh this here because now co-pilot uh knows about the whole project and information here. I want to make sure it just use our uh tools and get the information from there and no more context. So I will switch to empty Visual Studio Code and copy uh VS Code and the MCP.json
22:43 there. And as you can see I have it and I want to uh run the server. So I click in here and seven tools detected. Now it's time to chat and there is no project no context. Let's me open the chatbot and in here you can ask your questions. Let me extend it a bit and you should choose the agent then it will call our function. Now I have a bunch of questions to ask and u let's see how uh the AI with our information uh can
23:30 provide uh the results. So let me first ask about the general topic. So I will ask AI this question. Can you explain what the craft book database look like? List all tables uh connection one one uh one too many many many and uh the linked self reference and the other things. So let's see what will happen. Okay, as you can see based on this description, our description AI understood should call this method. So
24:15 you can click on continue or you can choose uh always allow but I want to show you how AI call different methods. So I just click on continue and then it's about the relations. So AI understood okay I should also call the get table relations information to have a complete answer. Let's continue. I guess it's really nice. Let's see first what we have here. This is the exact YAML information we passed to the AI and also in here uh sorry in
25:04 here this is a list of information about foreign keys. So based on this information we got really great answer in my opinion. Book graph database, the core entities, the information about books, fine payments and in the end the relation types and also mention no referencing uh or many to many u information in our schema. I get uh it works very well and uh AI understood my prompt my question and called correctly related uh uh tools. So next question will be about writing a
25:51 query to me and let me ask it. uh I want to find members who haven't returned their books uh by the due time and also I need uh they uh are overdue as integer I want to just set a reminder in my application so I need the query let's see I can write the query or not okay honestly in my opinion it's works very well. Uh but let's copy this query and execute it. So I can come here again and open a new
26:36 script. Yes. And paste it in here. So let's execute it. Wow. Uh in my opinion it works. But if you remember part of the query and prompt was about say it in integer. Let's back to the AI and see uh what's happened. So AI mentioned if you want integer just use this part. Okay, let's try it. Run it again. And yes, we have it. It's really nice u SQL query and valid information. So let's back and ask another question.
27:23 So I want to have a CTE query based on my uh database schema. Uh let's see if AI has any suggestion to help me to understand how CTE works and why this uh result and this query is important. So let's ask it. Okay. Uh the question is about which authors have the most book in the library and how many books has each author written. So for it uh created the CT. So let me copy it and uh execute it here to see the results.
28:10 So, as you can see, it's ordered by book count and uh Horakim Murakami has more than the others uh with Malcolm Gladwell. Okay, very well. I guess uh we fed the AI uh really well and AI understands the questions and prompt also better. So, I have more questions. I want to ask for more a complex query uh with the different joins. So let's see. Okay, I just asked give me a query with the several table join. Uh I want
28:56 to just know AI understood the the foreign key relationships. So what is this query about? Uh this is about um showing the users currently have books on loan including book details, branch and how many days overdue. So let's copy it and execute it. Wow, it's it's really really good. So Alice Jansson with this book title in north branch in this time and days overdue very well but I have still more
29:42 questions let's try it this time I want to know about triggers views and store procedures let's see I can understand SQL light has no store procedure or Not okay. I mentioned view. So for the view uh it needs to call get view function. Let's continue. And also get table info. Very well. Fantastic. So it mentioned view and triggers are available but there is no store procedure. Actually SQL light
30:28 has no store procedure. It mentioned the views and explained them uh in one line and also the triggers. It's really well and categorized based on the table loans here and reservations. So really well. But now I want to do some actions. If you remember, we were able to create a table and also add a new column there. So I want to ask for a key value store. Let's see AI can do this action for us or not. I just ask key value store with the key value. Uh both are a string and key
31:13 is primary key. So uh yes create new table should call and it's really un uh fascinating because first I mentioned a string and AI understood it should be text and passed primary key here the name of table is also detected very well let's see it's uh it works or not okay let me check the output yes this is the output we passed to AI I so based on it uh AI mentioned okay it's successfully created but before checking I want to ask another question it's about adding a new uh
31:59 column so I want to have a new column with the name of purpose as a string let's see yes detected add new column uh and for table key value. So I didn't reference it but copilot just understood it. Column name is purpose and the column type is text. Let's continue. And result was successful here and AI told us just did it. Now it's time to check the database. I need to refresh table here and see if uh key values store here. Yes. Yes. Yes.
32:47 And columns are key, value and purpose. Now you can see how MCP can uh facilitate the whole process and honestly we did nothing just gathering the information and just call those method providing a good uh well-formed u data for example YAML in this scenario and uh AI nailed it. That's it. If you found this video helpful, please hit the like button, subscribe for more coding tutorials, and let me know in the comments if you would like to see more. Thanks for watching, and I will see you in the next time.
33:35 Goodbye.
