1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
'def title Connect to MySql in .NET and Entity Framework
'def desc Connecting a .NET application to a MySql database (with Entity Framework) can be tricky. Here is what works for me.
# {title}
{pagenav 3 min;21 May 2020;19 June 2021}
:contents
.numbered
1. {uhref NuGet packages}
2. {uhref Connection string}
3. {uhref Connecting}
.numbered
1. {uhref ASP.NET Startup}
2. {uhref DBContext child class}
I have found that connecting a {` .NET} application to a {` MySql} database (with Entity Framework) can be tricky. Here is what works for me.
Also, {url MySql.Data.EntityFrameworkCore;https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/} has been {b depricated} since {i October 2020}. If you need setup instructions with it, read {url this;https://gitlab.com/Syndamia/syndamiadotcom/-/blob/0510e29526c6d7681587fff4c98a339e8c69e9b5/src/blog/connect-to-mysql-in-csharp-ef/article.md}.
## NuGet packages
You'll need to install {url Pomelo.EntityFrameworkCore.MySql;https://www.nuget.org/packages/Pomelo.EntityFrameworkCore.MySql/}. It provides the stuff necessary for connecting and using a {` MySql} database.
## Connection string
There are {url a lot;https://www.connectionstrings.com/mysql/} of ways to setup the connection string but I've found that this one works the best:
```
"server=localhost; port=YOUR-PORT; user id=YOUR-USER-ID; password=YOUR-PASSWORD; database=NAME-OF-DATABASE; SslMode=none"
If you are using {` MySQL Workbench} and you don't know your port and id, you can look at the server information from the {` MySQL Connections} page.
{img MyServer root 127.0.0.1:3306;./img/connections.png}
In this example, the user id is {i root} and the port is {i 3306}.
## Connecting
To connect, you'll need to call the {` UseMySql} method, either in {url ASP.NET Startup;#asp.net-startup} or in a {url DBContext child class;#dbcontext-child-class}.
The method requires at least two arguments: connection string and {` MySql} server version. The latter you can find, by going to the {` Help/About Workbench} window inside {` MySQL Workbench}.
{img ./img/about-workbench.png}
{img ./img/workbench-version.png}
For the server version argument, create a new object of type {` MySqlServerVersion} and give it's constructor a new object of type {` Version}, to which you give the version numbers. The result should look something like this:
```csharp
'emplace
.UseMySql(CONNECTION_STRING, new MySqlServerVersion(new System.Version(8, 0, 21)))
'endemplace
Where {` CONNECTION_STRING} is a (constant) variable that contains your connection string, and 8, 0, 21 are the version numbers.
### ASP.NET Startup
For {` ASP.NET} applications, the connection must be put inside the {` ConfigureServices} method of the {` Startup.cs} file. If you already have a {` .UseSQL} method call in there, just replace it with your {` .UseMySql} method and arguments.
The result should look something along the lines of:
```csharp
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
\...
services.AddDbContext<AppDbContext>(opt =>
{
opt.UseMySql(...);
});
}
### DBContext child class
Another way to connect is via a {` DBContext} child class (so, inside your own DB context). Just, override the {` OnConfiguring} method and call the {` .UseMySql} method from the {` optionsBuilder} parameter.
```csharp
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseMySql(...);
}
|
