How to configure connection string in MVC 6 in ASP.NET vNext

Question

User-628633671 posted

Hi,

I have created one MVC application in MVC 6 framework in ASP.NET vNext. In my application i am reading database table using Entity Framework,but the connection string which

is present in config.json file,that connection string is not readable in my application,and hence i am not able to get the data from my database table.

The Exception which i am getting is :- "No data stores are configured. Configure a data store using OnConfiguring or by creating an ImmutableDbContextOptions with a data store configured and passing it to the context."

My config.json file is:-

{
"Data": {
"DefaultConnection": {
"EmployeeContext": "Server= .;Database=Manish_Database;Trusted_Connection=True;MultipleActiveResultSets=true"
                              }
            }
}

and Startup.cs file is:-

public void Configure(IBuilder app)
{

// Setup configuration sources
var configuration = new Configuration();
configuration.AddJsonFile("config.json");
configuration.AddEnvironmentVariables();

// Set up application services
app.UseServices(services =>
{
// Add EF services to the services container
services.AddEntityFramework()
.AddSqlServer();

// Configure DbContext
services.SetupOptions<DbContextOptions>(options =>
{
options.UseSqlServer(configuration.Get("Data:DefaultConnection:EmployeeContext"));
});

// Add Identity services to the services container
services.AddIdentity<ApplicationUser>()
.AddEntityFramework<ApplicationUser, ApplicationDbContext>()
.AddHttpSignIn();

// Add MVC services to the services container
services.AddMvc();
});

// Enable Browser Link support
app.UseBrowserLink();

// Add static files to the request pipeline
app.UseStaticFiles();

// Add cookie-based authentication to the request pipeline
app.UseCookieAuthentication(new CookieAuthenticationOptions
{
AuthenticationType = DefaultAuthenticationTypes.ApplicationCookie,
LoginPath = new PathString("/Home/Details"),
});

Answer 1

User744480795 posted

The configuration in your Startup method only applies to Identity context. If you created another context (say EmployeeContext), you need to provide the configuration yourself.

Here is one way how you can do this:

    public class EmployeeContext : DbContext
    {
        public EmployeeContext(IServiceProvider serviceProvider, IOptionsAccessor<EmployeeContextOptions> optionsAccessor)
            : base(serviceProvider, optionsAccessor.Options)
        {
        }

        public DbSet<Employee> Employees { get; set; }
    }

    public class EmployeeContextOptions : DbContextOptions
    {
    }

and in startup:

                // Configure DbContext
                services.SetupOptions<DbContextOptions>(options =>
                {
                    options.UseSqlServer(configuration.Get("Data:DefaultConnection:ConnectionString"));
                });

                services.SetupOptions<EmployeeContextOptions>(options =>
                {
                    options.UseSqlServer(configuration.Get("Data:EmployeeContext:ConnectionString"));
                });

Also, I would recommend a different structure to your config.json, like so (notice "ConnectionString element in both connection declarations)

    "Data": {
        "DefaultConnection": {
            "ConnectionString": "Server=.;Database=MyDb;Trusted_Connection=True;MultipleActiveResultSets=true"
        },
        "EmployeeContext": {
            "ConnectionString": "Server=.;Database=Manish_Database;Trusted_Connection=True;MultipleActiveResultSets=true"
        }
    }

For more context, see how it's done in the Music Store sample:

https://github.com/aspnet/MusicStore

Thanks,

Maurycy

Answer 2

User1751268424 posted

Hi,

It seem need to give a definitive SQL Server name and instance name. Try this similar tested code:

        "DefaultConnection": { 
            "ConnectionString": "Server=HUMANIT2;Database=AccessInfo;Trusted_Connection=True;MultipleActiveResultSets=true"
        }

or similar

        "DefaultConnection": { 
            "ConnectionString": "Server=HUMANIT2\SQLEXPESS;Database=AccessInfo;Trusted_Connection=True;MultipleActiveResultSets=true"
        }

Have fun

Answer 3

User687275828 posted

Hi,

I'm new to MVC.  I can't seem to figure out the next step.

How would you use EmployeeContext in the Controller.

Thanks!

Answer 4

User1752001811 posted

@MaurycyM

The above solution is working with options.UseSqlServer but when i am using options.UseSqlLite then I am getting the following error

No data stores are configured. Configure a data store using OnConfiguring or by creating an ImmutableDbContextOptions with a data store configured and passing it to the context.

Please find my code below 

public TestContext(IServiceProvider serviceProvider, IOptionsAccessor<TestDbContextOptions> optionsAccessor)
: base(serviceProvider, optionsAccessor.Options)
{
       if (!_created)
       {
                   Database.EnsureCreated();// The above error is coming at this place
                  _created = true;
        }
}

I dont get this error while using UseSqlServer. I getting this error only in case of UseSqlLite. 

Doesnt this mean SQLite dont support creation of DataBase ? 

Answer 5

User401360897 posted

Try this example,

https://github.com/aspnet/EntityFramework/blob/6d9642948db1132e20f407db73ea414a9a7fe5a2/test/EntityFramework.SQLite.FunctionalTests/BuiltInDataTypesFixture.cs#L34-L51

If it not work the submit an issue at https://github.com/aspnet/EntityFramework/

Answer 6

User1752001811 posted

I tried the same but now i am getting the following error 

Microsoft.Data.Entity.Update.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Microsoft.Data.SQLite.SQLiteException: SQL logic error or missing database

Code first is working for Sqlite for me. 

Tables , DataBase everything is getting created for me but only problem is while calling

context.SaveChanges() i am getting the above error. Could someone please help me out ?