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
|
# Connect to MySql in .NET and Entity Framework
## Contents
1. [NuGet packages](#nuget-packages)
2. [Connection string](#connection-string)
3. [Connecting](#connecting)
1. [ASP.NET Startup](#asp.net-startup)
2. [DBContext child class](#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, [MySql.Data.EntityFrameworkCore](https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/) has been **depricated** since *October 2020*. If you need setup instructions with it, read [this](https://gitlab.com/Syndamia/syndamiadotcom/-/blob/0510e29526c6d7681587fff4c98a339e8c69e9b5/src/tutorials/connect-to-mysql-in-csharp-ef/article.md).
## NuGet packages
You'll need to install [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 [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.
 In this example, the user id is _root_ and the port is _3306_.
## Connecting
To connect, you'll need to call the `UseMySql` method, either in [ASP.NET Startup](#asp.net-startup) or in a [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`.
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:
<pre><code>.<span class="fnc">UseMySql</span>(CONNECTION_STRING, <span class="kwd">new</span> <span class="cls">MySqlServerVersion</span>(<span class="kwd">new</span> <span class="kwD">System</span>.<span class="cls">Version</span>(<span class="cst">8</span>, <span class="cst">0</span>, <span class="cst">21</span>)))
</code></pre>
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:
<pre><code><span class="com">// This method gets called by the runtime. Use this method to add services to the container.</span>
<span class="kwd">public</span> <span class="typ">void</span> <span class="fnc">ConfigureServices</span>(<span class="cls">IServiceCollection</span> services)
{
<span class="com">...</span>
services.<span class="fnc">AddDbContext</span><<span class="cls">AppDbContext</span>>(opt =>
{
opt.<span class="fnc">UseMySql</span>(<span class="com">...</span>);
});
}</code></pre>
<p></p>
### 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.
<pre><code><span class="kwd">protected</span> <span class="kwd">override</span> <span class="typ">void</span> <span class="fnc">OnConfiguring</span>(<span class="cls">DbContextOptionsBuilder</span> optionsBuilder)
{
optionsBuilder.<span class="fnc">UseMySql</span>(<span class="com">...</span>);
}</code></pre>
<p></p>
|
