Introduction: Picking a database
For most programming languages and frameworks out there, the ability to communicate with a database is a very important aspect. This is even more true for frameworks targeted towards the web where you will quickly run into the need for a database to store data for your website. You CAN rely on other storage solutions, e.g. data files in the XML or JSON format, but it comes with several disadvantages compared to working with a good database. But you're in luck - ASP.NET MVC makes it easy for you to start working with a database!
Picking a database engine
So what is a good database? There are almost as many answers to that question as to the classic "What is the best programming language?"-question, but much like when you have to pick a programming language, the answer is usually "Pick the best one for the current task". And you're in luck, because the .NET framework, and thereby ASP.NET MVC, supports a huge selection of database engines, either natively or with the help of a database provider found in the NuGet package system.
On the other hand, when using ASP.NET MVC to create a website, the task is often very all-round, where your solution has to solve many different types of problems. Therefore, it often makes sense to pick a database engine that does a lot of things very well, like Microsoft SQL Server. It has a couple of major advantages:
- It's made by Microsoft, just like the .NET framework, so communication between the two will always be optimal
- If you choose to have your web solution hosted by one of the many hosting providers who support ASP.NET MVC Core, they will likely also offer SQL Server
- You can easily, and for free, use the "SQL Server Express LocalDB" (more on that later) during the development process on your PC
- You can work with the database (design tables, edit rows etc.) directly from Visual Studio
- It scales very well - you can use it for a small, personal website, and if that website should grow into a huge community with thousands of visitors, SQL Server will still have more than enough power and scalability to support it
MS SQL Server is a great choice to get you started working with databases in ASP.NET Core MVC, which is why we will be using it in this tutorial. However, as soon as you have seen how ASP.NET MVC and MS SQL Server works together, feel free to experiment with other databases - you will soon realize that a lot of what you have just seen and learned can be re-used even if the underlying database changes.
What if you're not working on Windows?
A lot of .NET developers use Windows machine for the development process, since Visual Studio was historically only available on this OS. However, times are changing and Microsoft has been offering a very nice version of Visual Studio for Mac for several years. You can of course also write .NET code on a Linux computer, either using Visual Studio Code or any other text editor + the .NET compiler.
However, MS SQL Server is still a bit more limited when it comes to your choice of Operating System. As of writing, SQL Server (and the LocalDB implementation) will for instance not run on a Mac. This is not a problem if you have access to a remote SQL Server, running on Windows somewhere on the Internet, but if you want to be able to do local testing of your database, you may consider using a different database engine. A great alternative to MS SQL Server could be SQLite, which uses the SQL language just like MS SQL Server. In other words, you can use this alternative and still follow most of this tutorial.
To show you how to work with databases in ASP.NET MVC, I have decided to create a complete database-driven web application, using Microsoft SQL Server, from scratch. We'll be implementing a TODO-list and in the following articles I will take you through the entire process, where we'll be using a lot of the techniques already demonstrated in this tutorial, while combining them with the techniques needed to work with a database. As a little teaser, here's a screenshot of what we'll be building:
However, before we start setting up the database and writing some code, we need to talk about another important subject: Which database framework should we use for the communication between the your code and your database? We'll discuss that in the next article.