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.

As per the error, your application is not able to connect to the SQL database server. — Chetan
– Chetan, Commented Feb 3, 2019 at 9:53
@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. — RanAB
– RanAB, Commented Feb 3, 2019 at 10:04

Philipp Grathwohl · Accepted Answer · 2019-02-03 10:17:45Z

4

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.

answered Feb 3, 2019 at 10:17

Philipp Grathwohl

2,8763 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RanAB Over a year ago

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 Over a year ago

Maybe this helps: dev.mysql.com/downloads/windows/visualstudio

abestrad · Accepted Answer · 2019-02-03 10:29:37Z

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
                }
        ));

answered Feb 3, 2019 at 10:29

abestrad

9162 gold badges12 silver badges25 bronze badges

Comments

HenryMigo · Accepted Answer · 2019-02-03 10:30:27Z

1

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.

answered Feb 3, 2019 at 10:30

HenryMigo

1641 silver badge7 bronze badges

Collectives™ on Stack Overflow

Connecting database to .net core MVC project

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related