In a two-tier web application, the data layer is in a separate project. This allows better separation between the presentation layer and the data layer. This way you can reuse the data project in another application that targets the same database.
To access a MySql database you need to add the MySql.Data and MySql.Data.Entity NuGet packages to the data project.
When you develop a two-tier application that targets a MySql database you may get the runtime error
NotSupportedException: Unable to determine the provider name for provider factory of type ‘MySql.Data.MySqlClient.MySqlClientFactory’. Make sure that the ADO.NET provider is installed or registered in the application config.
Even if all database interaction is handled in the data project, and you added the MySql NuGet packages to it, you need to add the MySql.Data and MySql.Data.Entity NuGet packages to the web project too, because the Web.config file contains the connection string that includes the reference to MySql.Data.MySqlClient.
<add name="MY_CONNECTION_NAME" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;port=3306;database=MY_DATABASE;uid=MY_USERNAME;password=MY_PASSWORD" />
At the time of writing do not use version 220.127.116.11 of the packages, it causes the error
Exception thrown: ‘System.TypeLoadException’ in mscorlib.dll
An exception of type ‘System.TypeLoadException’ occurred in mscorlib.dll but was not handled in user code
Inheritance security rules violated by type: ‘MySql.Data.MySqlClient.MySqlProviderServices’. Derived types must either match the security accessibility of the base type or be less accessible.
Until a new, corrected version is published, install version 18.104.22.168 of both packages in both projects.
For more information on how to connect to a MySql database from Visual Studio see Connect to a MySQL database from Visual Studio 2017
As of today weI can connect to a MySQL database, but cannot generate the Entity Data Model from a MySQL database in Visual Studio 2017
To access a MySQL database from Visual Studio 2017
IMPORTANT: First install MySQL for Visual Studio, and after that install MySQL Connector/Net.
If MySQL Connector/Net is already installed on your workstation, uninstall it before installing MySQL for Visual Studio and reinstall MySQL Connector/Net.
Install MySQL for Visual Studio
Available version information is at https://dev.mysql.com/doc/visual-studio/en/visual-studio-intro.html
- Close all instances of Visual Studio,
- Download the installer from https://dev.mysql.com/downloads/windows/visualstudio/
- Start the installer
- Click the Complete button,
- If you select Typical, make sure the Visual Studio 2017 components are selected
Install the MySql Connector/Net the fully-managed ADO.NET driver for MySQL
- Download the installer from https://dev.mysql.com/downloads/connector/net/
- Download version 6.9.10, because version 6.10.5 throws the
Inheritance security rules violated by type: ‘MySql.Data.Entity.MySqlEFConfiguration’. Derived types must either match the security accessibility of the base type or be less accessible
error. More info at https://stackoverflow.com/questions/47227495/inheritance-security-rules-violated-by-type-mysql-data-entity-mysqlefconfigura
- Select version 6.9.10, your operating system and click the Download button
- If you don’t want to create an Oracle account, click the No thanks… link
- Start the downloaded installer
- Click the Complete button
Install the NuGet packages
Make sure to install the same version of the NuGet packages as the MySql Connector/Net
When your application tries to store 4-byte Unicode characters in a MySQL database you may get the error rmessage
java.sql.SQLException: Incorrect string value: ‘\x..\x..\x..\x..\x..\x…’ for column
To store 4-byte Unicode characters in MySQL you need
- a modern version of the database engine, version 5.5 or later,
- set the column collation to COLLATE utf8mb4_unicode_ci in the database,
- configure MySQL to use utf8mb4 in the character columns.
To set the default character set and server settings to utf8mb4
- Open the C:\ProgramData\MySQL\MySQL Server 5.x\my.ini file
- Under [client] add
default-character-set = utf8mb4
- Under [mysql] set the value to utf8mb4 in line
default-character-set = utf8mb4
- Under [mysqld] set the value to utf8mb4 in line
character-set-server = utf8mb4
- Under [mysqld] add the lines
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Restart the MySQL server
- Restart the MySQL database server for the changes to take effect
- in MySQL Workbench reconnect the database.
Change the schema of an existing database
- In MySQL Workbench connect to the database server,
- Right-click the database and select Alter Schema,
- Set the collation to utf8mb4_unicode_ci collation
- Click the Appy button to save the changes,
- Click the Apply button again to confirm the instructions,
- Click the Finish button to execute the script.
To check the database settings execute the SQL query in MySQL Workbench
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
It should show
| Variable_name | Value |
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
Repair and optimize all tables
Make sure you execute the mysqlcheck command to repair and optimize all tables to avoid silent update errors
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqlcheck" -u root -p --auto-repair --optimize --all-databases
The utility makes it easy to execute the repair and optimize commands for every table
# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;
To install the MySQL database server, download the installer from https://dev.mysql.com/downloads/
- Download the MySQL Community Server from https://dev.mysql.com/downloads/
- On the Generally Available (GA) Releases tab select the operating system and click the Go to Download Page button,
- Click the Download button next to the web or offline installer,
- If you don’t want to register at Oracle, click the No thanks, just start my download link,
Install the Visual C++ Redistributable Packages for Visual Studio 2013
- In your web browser navigate to https://www.microsoft.com/en-us/download/details.aspx?id=40784
- Download both 32 and 64-bit packages
- Install both packages. The 64-bit package requires a reboot after installation.
- Execute the downloaded MySQL installer.
How to restore a MySQL database in Windows
To restore a MySQL database on a Windows machine:
- Place the database backup file in the root directory of your C: drive (trust me, this will make your life easier)
- Open the Command Prompt
- Start the MySQL command prompt
mysql -u root -p
This command will prompt you for the MySQL root password
- Switch to the new database
- Import the database
To avoid the Failed to open file ‘…’ , error 2 and error 22 error message
You can have spaces in the name of the backup file, but not in the directory names in the path
Do not use quotes around the file path
Use forward slashes
Do not end the command with semicolon (;)