update the database from package manager console i

Question

Code First Environment

I'm trying to update the database from package Manager console.If my Domain class change, I have to drop and create the database,Instead of dropping the Database how can i update the database.

I have already try by reading some blogs in google.

commands

PM> Install-Package EntityFramework

1. By using this command i install the Entity Framework successfully.

    PM> Enable-Migrations
   

2. By using this command it created the Migration file in my project.

    PM> Update-Database
   

3. By using this command , i may update the table but i have a Problem here.

Error

Specify the '-Verbose' flag to view the SQL statements being applied to the target database. System.Data.SqlClient.SqlException (0x80131904): 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.

Doubt is here

Sometimes It may update if only one field changes in POCO Class. For example I have Updated the more number of Domain class ,How can i Update the Database from Package manager Console. Any Idea ?

Answer 1

You can specify connection string via ConnectionString parameter:

Update-Database -ConnectionString "data source=server_name;initial catalog=db_name;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" -ConnectionProviderName "System.Data.SqlClient" -Verbose

Also you need to use this parameter with the same value for Add-Migration command:

Add-Migration Version_Name -ConnectionString "data source=server_name;initial catalog=db_name;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" -ConnectionProviderName "System.Data.SqlClient" -Verbose

Answer 2

I used

Enable-Migrations -EnableAutomaticMigrations -Force

then

Update-Database

And this migrated my changes (additional columns) while preserving my test data. I think this is the laziest option while prototyping.

Answer 3

Looks like you have multiple issues. Regarding not wanting to drop and recreate the database, that is determined by your database initializer. If you want to use migrations you change it to MigrateDatabaseToLatestVersion. http://www.codeguru.com/csharp/article.php/c19999/Understanding-Database-Initializers-in-Entity-Framework-Code-First.htm

Second, it doesn't matter how many fields you change, they will be rolled into a single migration based on changes from the last migration.

Third, as the others have pointed out, it seems you have a connection string issue. While you can add that to Add-Migration and Update-Migration I would probably fix it in the application. I set mine in the constructor of my context which points to my config file (web.config for ASP.NET).

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext()
        : base("MyConnection", throwIfV1Schema: false)
    {
        Database.SetInitializer<ApplicationDbContext>(new MigrateDatabaseToLatestVersion<ApplicationDbContext, MyObjextContextMigration>());
    }
    ...

Answer 4

You need to update SSDT go to tools> extension and updates > updates > SQL server data tools

Answer 5

Where to put the connection string?

You do not need to specify it with every command in the package manager console. You can put it in appsettings.json in the project where your DbContext class (read "from DbContext derived class") resides.

{
  "ConnectionStrings": {
    "MyConnectionString": "Server=yourServer;Port=5432;Database=yourDatabase;User Id=yourDatabaseUsername;Password=yourDatabasePassword;"
  }
}

It will be used for migrations.

Important: If you have multiple projects in your solution, you must select the project in the 'Default project'-dropdown (in the Package manager Console) and you must set the project as your startup project (in the Solution Explorer).

Failing to do so, might cause the wrong appsettings.json to be used with an incorrect/different connectionstring.

This was my experience with EF Core 2.1 and probably applies to the other versions of EF.