dotnet-core ef sql connection

Question

I want to set sql connection to config.json and use it from AuditContext class. But when I run example drop-database from PMC, I get an error.

System.ArgumentException: Format of the initialization string does not conform to specification starting at index 21. at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, String& keyname, String& keyvalue) at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary2 parsetable, String connectionString, Boolean buildChain, Dictionary2 synonyms) at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary2 synonyms) at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerConnection.CreateDbConnection() at Microsoft.EntityFrameworkCore.Internal.LazyRef1.get_Value() at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.GetContextInfo(String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.GetContextInfoImpl(String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

Here is my Startup.cs class

using System.Threading.Tasks;
using Audit.Models.Entity;
using Microsoft.AspNetCore.Authentication.Cookies;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json.Serialization;

namespace Audit
{
public class Startup
{
    private IHostingEnvironment _env;
    private IConfigurationRoot _config;

    public Startup(IHostingEnvironment env)
    {
        _env = env;

        var builder = new ConfigurationBuilder()
            .SetBasePath(_env.ContentRootPath)
            .AddJsonFile("config.json")
            .AddEnvironmentVariables();

        _config = builder.Build();
    }
    // This method gets called by the runtime. Use this method to add services to the container.
    // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddSingleton(_config);

        services.AddDbContext<AuditContext>();

        services.AddIdentity<ApplicationUser, IdentityRole>(config =>
        {
            config.User.RequireUniqueEmail = false;
            config.Cookies.ApplicationCookie.LoginPath = "/auth/login";
            config.Cookies.ApplicationCookie.Events = new CookieAuthenticationEvents()
            {
                OnRedirectToLogin = async ctx =>
                {
                    if (ctx.Request.Path.StartsWithSegments("/api") && ctx.Response.StatusCode == 200)
                    {
                        ctx.Response.StatusCode = 401;
                    }
                    else
                    {
                        ctx.Response.Redirect(ctx.RedirectUri);
                    }
                    await Task.Yield();

                }
            };
        }).AddEntityFrameworkStores<AuditContext>();

        services.AddLogging();

        services.AddMvc()
            .AddJsonOptions(config =>
                {
                    config.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
                });

    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
    {


        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
            loggerFactory.AddDebug(LogLevel.Information);
        }
        else
        {
            loggerFactory.AddDebug(LogLevel.Error);
        }

        app.UseStaticFiles();

        app.UseIdentity();

        app.UseMvc(config =>
        {
            config.MapRoute(
              name: "Default",
              template: "{controller}/{action}/{id?}",
              defaults: new { controller = "App", action = "Index" }
              );
        });
    }
  }
  }

Here is config.json

{
  "ConnectionStrings": {
    "AuditDbContextConnection": "Server=xxx.xxx.xxx.xxx;Database:databasename;User Id:user;Password:password;"
  }
}

Here is AuditContext.cs

using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace Audit.Models.Entity
{
    public class AuditContext : IdentityDbContext<ApplicationUser>
    {
        private IConfigurationRoot _config;

        public AuditContext(IConfigurationRoot config, DbContextOptions options) : base(options)
        {
            _config = config;
        }

        public DbSet<Aql> Aql { get; set; }
        public DbSet<AqlVer> AqlVer { get; set; }
        public DbSet<Cartiglio> Cartiglio { get; set; }
        public DbSet<CartonBox> CartonBox { get; set; }
        public DbSet<CartonBoxLog> CartonBoxLog { get; set; }
        public DbSet<Entry> Entry { get; set; }
        public DbSet<Fashion> Fashion { get; set; }
        public DbSet<Level> Level { get; set; }
        public DbSet<MasterCode> MasterCode { get; set; }
        public DbSet<Settings> Settings { get; set; }
        public DbSet<SubCode> SubCode { get; set; }
        public DbSet<TotalProduction> TotalProduction { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {

            base.OnConfiguring(optionsBuilder);
            optionsBuilder.UseSqlServer(_config["ConnectionStrings:AuditDbContextConnection"]);
        }


    }

}

What I did wrong and where ? Thanks.

Answer 1

So, as discussed in the comments the problem was that the connection string contained ':' instead of '='.

So instead of:

{
  "ConnectionStrings": {
    "AuditDbContextConnection": "Server=xxx.xxx.xxx.xxx;Database:databasename;User Id:user;Password:password;"
  }
}

This would make it:

{
  "ConnectionStrings": {
    "AuditDbContextConnection": "Server=xxx.xxx.xxx.xxx;Database=databasename;User Id=user;Password=password;"
  }
}