update the database from package manager console in code first environment

Question

Code First Environment

I'm trying to update the database from package Manager console. If my domain class changes, I have to drop and create the database. Instead of dropping the database, how can I update the database?

Commands

1. By using this command, I installed the Entity Framework successfully.

   PM> Install-Package EntityFramework
   

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

   PM> Enable-Migrations
   

3. By using this command, I may update the table but I have a problem here.

   PM> Update-Database
   

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?

Soundboard

As it states, you cannot connect. Check if SQL Server is running and you have correct connection string. – Cyndycynera

I have added the connection string in Configuration file – Soundboard

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

Eldrida

If i use this command i may able to update the database if changes in single POCO class . If i have changed more number of Domain calss Field , how can i update ? – Soundboard

If i use this command i got following error . Scaffolding migration 'update1'. The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration update1' again – Soundboard

,I have checked this, first one is used to update the table but if we are using it will update the table if class is not exist only. Please check this. – Soundboard

best answer for my case! – Pyrrhic

Doesn't work for me. OUTPUT: Update-Database : A parameter cannot be found that matches parameter name 'ConnectionString'. – Geez

A parameter cannot be found that matches parameter name 'connectionstring'. – Mezoff

@Mezoff - Try using -ConnectionStringName as the parameter. So, if you have a connection string in your config file named MyConnection your update-database command would look like this: update-database -ConnectionStringName MyConnection – Periosteum

Probably no need to specify the connectiomnstring with every command. Please see my answer ( https://mcmap.net/q/437920/-update-the-database-from-package-manager-console-in-code-first-environment ). – Ally

@Ally that's only for Core applications? – Repeated

@jamheadart: I am not sure about that. – Ally

For Net Core apps (EF Core 5), the command is Update-Database -Connection <string> – Saccharose

@Aquiles, thanks for your comment! If I were you, I'd post a new answer talking about how to do this for EF Core 5. You'd get my upvote! :) (The point of SO is to provide visitors with the best answer right away, and because your comment is a comment instead of an answer, voters can't make that happen.) – Greenhead

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.

Candicecandid

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

Rhys

I didn't downvote you, but I got a compiler error when trying to use your snippet: The type arguments for method 'System.Data.Entity.Database.SetInitializer<TContext>(System.Data.Entity.IDatabaseInitializer<TContext>)' cannot be inferred from the usage. Try specifying the type arguments explicitly – Boniface

That can be corrected with Database.SetInitializer<ApplicationDbContext>(... or whatever your context type is. Mine works without it though. – Rhys

Answer 4

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.

Ally

Setting the startup project worked for me. Otherwise it was reading the appsettings.json file from a different project. Thanks for the tip! – Bashemeth

Answer 5

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

Alaynaalayne

Downvoted, even though it won't show. No such option exists in Visual Studio 2017 ! – Arcboutant

Answer 6

I just use update-database -Force

Once the code first database is configured, there is a possibility of losing data during the database update. With -Force, we take that into account.

Spitball

Answer needs supporting information Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Contempt

Answer 7

My solution may help someone: Under Connected Services delete localdb. Then right-click on Connect Services and click Add. Select database. Next, it will ask for the Connection string name ConnectionStrings:default leave the name Connection string value hit the ... provide the information and test connection Migrations should now work.

Jase

"Connected Services" - where? It's not clear what you refer to. And why would anyone have localdb there and be willing to "delete" it? This looks like a highly personal issue and it's not likely to help others. – Unyoke

could you add a few pictures and make it clear? – Tanyatanzania