How To Connect MySQL With ASP.NET Core

Question

User-2004582644 posted

I need connect .NET CORE Application with MySQL and read data from MySQL, using .NET Core MySQL connector

this is the tutorial

I followed step by step the instructions of the tutorial, but on debug in VS 2019 the error is

System.NullReferenceException: 'Object reference not set to an instance of an object.'

ConnectionString is null, why?

On this part

private MySqlConnection GetConnection()
{
    return new MySqlConnection(ConnectionString); 
}

Any idea?

I really tried everything I could find on google without success...

SakilaContext.cs

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MvcSakilaCore.Models
{
    public class SakilaContext
    {
        public string ConnectionString { get; set; }

        public SakilaContext(string connectionString)
        {
            this.ConnectionString = connectionString;
        }

        private MySqlConnection GetConnection()
        {
            return new MySqlConnection(ConnectionString);
        }

        public List<Film> GetAllFilms()
        {
            List<Film> list = new List<Film>();

            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT * FROM film", conn);
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(new Film()
                        {
                            FilmId = reader.GetInt32("film_id"),
                            Title = reader.GetString("title"),
                            Description = reader.GetString("description"),
                            ReleaseYear = reader.GetInt32("release_year"),
                            Length = reader.GetInt32("length"),
                            Rating = reader.GetString("rating")
                        });
                    }
                }
            }

            return list;
        }
    }
}

FilmsController

public class FilmsController : Controller
{
    public IActionResult Index()
    {
        SakilaContext context = HttpContext.RequestServices.GetService(typeof(RazorPagesMovie.Models.SakilaContext)) as SakilaContext;
        return View(context.GetAllFilms());
    }
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();
    services.AddMvc(); 
    services.AddTransient<MySqlConnection>(_ => new MySqlConnection(Configuration["ConnectionStrings:DefaultConnection"]));
    services.Add(new ServiceDescriptor(typeof(SakilaContext), new SakilaContext(Configuration.GetConnectionString("DefaultConnection"))));
}

        app.UseEndpoints(endpoints =>
        {

            endpoints.MapControllerRoute(
                name: "default",
                pattern: "{controller=Films}/{action=Index}/{id?}");

        });

appsettings.json

{

  "ConnectionsStrings": {
    "DefaultConnection": "server=localhost;port=3306;database=sakila;user=XXX;Password=XXX",
    "ProviderName": "MySql.Data.MySqlClient"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },

  "AllowedHosts": "*"
}

Answer 1

User314352500 posted

Hi,

Try:

{

  "ConnectionStrings": {
    "DefaultConnection": "server=localhost;port=3306;database=sakila;user=XXX;Password=XXX",
    "ProviderName": "MySql.Data.MySqlClient"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },

  "AllowedHosts": "*"
}

"ConnectionStrings" instead of "ConnectionsStrings"

Hope this help

Answer 2

User-857013053 posted

You have wrote ConnectionsStrings in appsettings.json file. but actually you have write to ConnectionStrings in appsettings.json file. 

Answer 3

User-217098811 posted

Hi

Chevy Marl S

ConnectionString is null because the “ConnectionStrings” in your appsettings.json  is written as “ConnectionsStrings”

Best Regards

Yinqiu

Answer 4

User-2004582644 posted

Hi,

Try:

{

  "ConnectionStrings": {
    "DefaultConnection": "server=localhost;port=3306;database=sakila;user=XXX;Password=XXX",
    "ProviderName": "MySql.Data.MySqlClient"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },

  "AllowedHosts": "*"
}

"ConnectionStrings" instead of "ConnectionsStrings"

Hope this help

thanks all!