Setting Up a Persistent Database
We have decided to use
MySQL for our relational database for
CodingEvents MVC project.
First we need to
set up a new database in MySQL Workbench and then we can connect it to
Create the Database
In MySQL Workbench, do the following:
Create a new schema,
Add a new user,
codingevents, with a new password. Give the user “all” privileges to modify your new schema.
CodingEvents to the Database
Now, attach MySQL to your project in
Program.cs by adding the following property:
connectionString with your username, password, and database name.
serverVersion with the actual version of your MySql Workbench.
Visual Studio will automatically add the correct
using statement which will shift the lines once you provide correct version numbers the
serverVersion. This will move
serverVersion to lines 8 and 9.
We now need to add a couple of NuGet packages to support our database connection. This process differs slightly for Windows and MacOS users.
Install MySQL Dependency
Working with the NuGet Package Manager
Open the NuGet Package Manager in Visual Studio:
Windows - Tools > NuGet Package Manager > Manage NuGet Packages for Solution
Mac - Project > Manage NuGet Dependencies
Search for for all of the packages listed below. Select the package and install.
When installing these packages, make sure that the versions are the same as the .NET Core version your project is using. You can confirm this is the case by reviewing the code in your csproj file.
We will need to install the following NuGet packages:
Pomelo.EntityFrameworkCore.MySql. This dependency provides code that is able to connect to a MySQL database from within an ASP.NET Core application using EF.
Microsoft.EntityFrameworkCore.Relational. This is a mapping framework that automates access and storage of data in your project’s database.
Microsoft.EntityFrameworkCore.Design. This helps manage data migrations and the design-time logic.
You can view installed packages and their dependencies by navigating to Dependencies > NuGet in the Solution Explorer (or the Solution pane on Mac) and expanding a given package.
Ensuring Connection Success and Security
Before we can get into the ins and outs of using ORM, we need to make sure that our application has a corresponding database and that our application is ready to connect to MySQL. We can start to do this by creating new schemas and setting user privileges in MySQL Workbench. We also must make sure that the MVC application has the correct dependencies, username, and password to access the schema.
If we do not do these steps, then our application will not be able to use a persistent data source.
Setting the value of the
connectionString property using the values of the username and password is NOT a best practice. We regularly commit our code to Github, meaning anyone who reads the code in our repository can see the username and password. While you can do it for the applications in this class, you do not want to do it in the future.
To avoid this in the future, you can configure your
connectionString string to reference environment variables. You then hide the appropriate info by setting the environment variable’s value equal to the password, for example.
We used the
documentation provided by Pomelo to code the current
If you are interested in more secure methods of connection, see Microsoft’s documentation on Configuration to learn how to keep the username and password to your database safe and secure.
Troubleshooting Your Connections
If you are not able to connect your MVC project to your MySQL database, try the following:
Double check the username, password, and database name.
Check the versions of all the dependencies you added to your project. They should all be the same major version.
- For example if you use Pomelo 6.0.2 with both Microsoft.EntityFrameworkCore.Relational and Microsoft.EntityFrameworkCore.Design 6.0.10. These are all contained within version 6 and should work.
- If one of them was version 7.0.3, this discrepancy may be cause errors.
Use the NuGet manager to verify and set versions. You can install higher versions or lower versions as needed.
Check Your Understanding
True/False: Writing usernames and passwords in plain text in a file is a GREAT idea!
True/False: We need Entity Framework Core AND a MySQL provider to successfully use ORM in this project.