Connecting database to .net core MVC project

Question

It is my first time using .NET core 2.2 and MySQL workbench ,

I am trying to build a very basic website.

I followed the following Microsoft tutorial

https://learn.microsoft.com/en-us/aspnet/core/tutorials/first-mvc-app/working-with-sql?view=aspnetcore-2.2&tabs=visual-studio

After I added a Scaffolded item,I followed the instructions and opened the NuGet package manager and executed these commands in the cli:

Add-Migration Initial Update-Database

The Update-Database command raised the following error :

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I am working with a Bluehost (Shared Host) server, I modified the permissions so I could connect remotely to the database (and indeed I am connected through MySQL workbench)

I tried changing the ConnectionString to the following:

"ConnectionStrings": 
{
 "Piano3Context": "Server=162.241.*.*;Database=PianoDB;User Id=omyUsrName;password=myPass;Trusted_Connection=True;MultipleActiveResultSets=true;"
}

and yet I receive the same error.

If any other code will help please note and I will post.

• Chetan: As per the error, your application is not able to connect to the SQL database server.
• RanAB: @ChetanRanpariya That was clear, that is why I added the connectionstring - I thought maybe I wrote it in a wrong form. Thanks anyways for the reply.

Answer 1

The tutorial you mentioned is using and SQL-Server. For connecting to a MySql server you need a different database provider. You can install the Pomelo.EntityFrameworkCore.MySql nuget package for Mysql. See the providers page in the microsoft docs.

After that you need to change the options.UseSqlServer from the tutorial to options.UseMySql as described on the mysql providers project page.

• RanAB: Thank alot, solved my problem! Is there by any chance a way that I could connect to my DB using vs17 "Connect to database" ? There is no MySQL option there
• Philipp Grathwohl: Maybe this helps: dev.mysql.com/downloads/windows/visualstudio

Answer 2

In addition, this is how to set the options for MySQL, you can move the config string to the Configuration and use the GetConnectionString method.

services.AddDbContextPool<MvcMovieContext>(
 options => options.UseMySql("Server=localhost;Database=ef;User=root;Password=123456;", 
 mySqlOptions =>
 {
 mySqlOptions.ServerVersion(new Version(5, 7, 17), ServerType.MySql); // replace with your Server Version and Type
 }
 ));

Answer 3

I was beaten to it by @philipp-grathwohl you need to use MySql and configure that in your startup like his answer says.

You could use this command instead which Scaffolds the DBContext and Generates the EF models and Context in one command after you have changed the startup and added the nuget package Pomelo.EntityFrameworkCore.MySql:

Scaffold-DbContext "Server=<ip>;Initial Catalog=PianoDB;Persist Security Info=False;User ID=<username>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" Pomelo.EntityFrameworkCore.MySql -OutputDir Models -context Piano3Context -force

Do let me know if this last command spits out any errors.