Migrating the update database in asp.net MVC Code-i mode

An overview of the Code-I migration in the Entity Framework. The topics are: enable migration generation and run migration custom migration data movement and custom SQL migration to a specific version (including demotion) generate SQL scripts to automatically upgrade at application startup (Migratedatabasetolatestversion initializer)

building an initial model and database

Before you start using migrations, you need to have a project and a Code first model. Use the Blog and Post specification model for example. Create a new Migrationsdemo Console application to add the latest version of the entityframework NuGet package to the project Tools –> Library Package Manager –> Package Manager Console Run the install-package entityframework command to add a Model.cs file that contains the code shown below. This code defines a Blog class that consists of our domain model and a Blogcontext class that acts as the EF Code-one context.

Using System.Data.Entity;
using System.Collections.Generic;
Using System.ComponentModel.DataAnnotations; 
using System.Data.Entity.Infrastructure;
namespace Migrationsdemo
{Public
class Blogcontext:dbcontext
{public
dbset<blog> Blogs {get; set;}
Public
class Blog
{public
int BlogId {get; set;}
public string Name {get; set;}
} 
}

Now you have a model that you can use to perform data access. Update with the code shown below Program.cs File.

using system;  using system.collections.generic;  using system.linq;  using  system.text;    namespace migrationsdemo  {      class program       {          static void  Main (String[] args)           {               using  (Var db = new blogcontext ())               {                   db. Blogs.add (new blog { name =  "another blog " &nbsp);                   db. SaveChanges ();                    foreach  (var blog in db. Blogs)                   {                        console.writeline (blog. Name);                  }               }                 console.writeline ("press any key to  Exit ... ");              console.readkey ();           }      } }

Run the application and you will see that you have created a Migrationscodedemo.blogcontextDatabase.

If you have SQL Express installed (included in Visual Studio 2010), the database will be in the local SQL Express instance ( . \SQLExpress) is created in. If SQL Express is not installed, then Code a attempts to use LocalDb ( (localdb) \v11.0)-LocalDb is included in Visual Studio 2012.

Note:If you have SQL Express installed, always use it first, even if you are using Visual Studio 2012


(LOCADB database)


(SQL Express database)

Enable Migration

Now we're going to make some changes to our model. Let's introduce a URL attribute to the Blog class.

public string Url {get; set;}

If you plan to run the application again, a InvalidOperationException is displayed, indicating that the model that supports the "Blogcontext" context has changed since the database was created. Consider using Code-I to migrate the update database (http://go.microsoft.com/fwlink/?LinkId=238269).

As indicated by the exception message, you should now start using the Code first migration. The first step is to enable migration for the context. To run the enable-migrations command in the Package Manager console

This command has added the Migrations folder to the project, which contains two files: Configuration class. This class allows you to configure the behavior of the migration against the context. For this walkthrough, we will use the default configuration.
Because there is only one Code a context in your project, Enable-migrations is automatically populated in the context type to which you want to apply this configuration. initialcreate Migration . This migration was generated before migration was enabled because we let Code first automatically create a database. The code in this scaffolding migration represents the objects that have been created in the database. In this case, this class of objects is a Blog table that contains the BlogId and Name columns. The file name contains a timestamp, which is very helpful for sorting.
If a database has not been created, this initialcreate migration is not added to the project. Instead, the code that is used to create the tables will build the scaffolding for the new migration when the add-migration is first invoked.

Build and run migration

The Code first migration has two master commands, which you will familiarize yourself with below. add-migration will build the scaffolding for the next migration, based on the changes you made to the model since the last migration was created. Update-database applies all pending migrations to the database.

We need to build scaffolding for migration to handle the new URL properties that were added earlier. Use the add-migration command to specify names for these migrations, which we call the Addblogurl. Run the add-migration addblogurl command in the Package Manager console. In the Migrations folder, you now have a new addblogurl migration. The migration file name is prefixed with a timestamp, which is helpful for sorting.

namespace migrationsdemo.migrations  {      using System;       using System.Data.Entity.Migrations;             public partial class AddBlogUrl : DbMigration     
 {          public override void up ()           {               addcolumn ("Blogs",  "Url",  c => c.string ());           }                     public override void down ()            {              dropcolumn (" Blogs ", " UrL ");          }      } } 

Now, we can edit this migration or add content to it, and everything is fine. Let's use update-database to apply this migration to the database. Run the update-database command in the Package Manager console. The Code-I migration compares the migration in the Migrations folder with the migration that has been applied to the database. It will understand the need to apply the Addblogurl migration, and then run the migration.

At this point, themigrationsdemo.blogcontext database has been updated and its Blogs table contains the Url column.

Custom Migration

So far, we have built and run the migration without making any changes. Now, let's take a look at how to edit the code generated by default. We need to make some changes to the model and let's add a new Rating property to the Blog class.

public int Rating {get; set;}

and add a new PostClass

public class Post
{public
int PostID {get; set;}
[MaxLength]
public string Title {get; set;}
public string Content {get; set;}
public
int BlogId {get; set;}
Public Blog Blog {get; set;}
}

We also want to Blogclass to add a Postscollection, in order to BlogAnd Postform another layer of relationship.

Public virtual list<post> Posts {get; set;}

We will use the add-migration command to let the Code-I migration automatically build the scaffolding for its best guesses when migrating. We refer to this migration as addpostclass. Run the add-migration addpostclass command in the Package Manager console.

The Code first migration does a good job of building scaffolding for these changes, but some of the content may require us to change: first, we add a unique index to the posts.title column
(Add to line 22nd and 29 of the following code). You also add a blogs.rating column that is not nullable. If there is any existing data in the table, the data is assigned a CLR default value of the new column data type (Rating is an integer, so the default value will be 0). But we want to specify the default value of 3to set a good starting level for the existing rows in the Blogs table.
(You can see the specified default value in line 24th of the following code)

namespace migrationscodedemo.migrations  {      using System;      using System.Data.Entity.Migrations;        public  partial class AddPostClass : DbMigration      {           public override void up ()            {               CreateTable (                    "Posts",                   c => new                       {               &Nbsp;           postid = c.int (Nullable:  false, identity: true),                           title = c.string (maxlength: 200),                           content = c.string (),                            blogid = c.int (nullable: false),                      &NBSP})                    . PrimaryKey (T => t.postid)  
                . ForeignKey ("Blogs",  t => t.blogid, cascadedelete: true)                    . Index (t => t.blogid)                    . Index (p => p.title, unique: true);                 addcolumn ("Blogs",  "Rating",  c => c.int (nullable:
 false, defaultvalue: 3));          }            public override void down ()            {               dropindex ("Posts",  new[] {  "Title" &nbsp.});               Dropindex ("Posts", new[] {  "BlogId"  });               dropforeignkey ("Posts",  "BlogId",  "Blogs");               dropcolumn ("Blogs",  "Rating");               droptable ("Posts");           }      } }

Our edited migration is ready, let's update the database using update-database . This time, specify the –verbose tag so that you can see the SQL that the Code one migration runs. Run the update-database–verbose command in the Package Manager console.

data Movement/Custom SQL

So far, we've learned about migrating operations that do not change or migrate any data, and now look at the migration operations that need to move some data back and forth. There is no native support for data movement at this time, but we can run arbitrary SQL commands anywhere in the script. Now add a post.abstract property to the model. Later, we will populate Abstractfor an existing article with some text beginning with the Content column.

public string Abstract {get; set;}

We will use the add-migration command to let the Code-I migration automatically build the scaffolding for its best guesses when migrating. Run the add-migration addpostabstract command in the Package Manager console. The generated migration is responsible for processing schema changes, but we also want to populate the Abstract columns with the first 100 characters of each article content. To do this, drag down to SQL and run the UPDATE statement After you add the column.
(Add to line 12th of the following code).

namespace migrationscodedemo.migrations  {      using System;      using System.Data.Entity.Migrations;             public partial class AddPostAbstract : DbMigration       {          public override void  Up ()           {         
     addcolumn ("Posts",  "Abstract",  c => c.string ());                sql ("UPDATE Posts SET  abstract = left (content, 100)  where abstract is null ");           }                 &nBsp;   public override void down ()            {              dropcolumn ("Posts",
  "Abstract");          }      }  }

We edited the good migration very well, let's use update-database to update the database. We will specify the –verbose tag so that you can view the SQL that is running against the database. Run the update-database–verbose command in the Package Manager console.

migrate to a specific version (including demotion)

So far we have been upgrading to the latest migrations, but sometimes you may need to upgrade/demote to a specific migration.

If we want to migrate the database to the state it was in when the Addblogurl migrated. You can demote to this migration using the –targetmigration switch. Run the update-database–targetmigration:addblogurl command in the Package Manager console.

This command will run down scripts for addblogabstract and addpostclass migrations.

If you want to roll back to the empty database, you can use the update-database–targetmigration: $InitialDatabase command.

Get SQL Script

If these changes are also required on other developers ' computers, they only need to perform a synchronous operation after we check in the changes to source control. After they get our new migration, you can run the Update-database command to apply these changes locally. However, if you want to push these changes to the test server and eventually to the production, we may want to submit a SQL script to the DBA. Run the update-database command, but specify the –script tag at this time so that the change is written to the script instead of applied. We will also specify the source and target migrations for which to generate scripts. We want the script to be used to migrate from the latest version of the Empty database ($InitialDatabase) (Migrate addpostabstract).
If you do not want to specify a target migration, the migration uses the latest migration as the target. If no source migration is specified, the migration uses the current state of the database.
Running Update-database-script-sourcemigration in the Package Manager console : $InitialDatabase-targetmigration:addpostabstract Command.

The Code first migration will run the migration pipeline instead of actually applying the changes, and it will automatically write the changes to a. sql file. When the script is generated, it is automatically opened in Visual Studio for you to view or save.

Auto-Upgrade at application startup (migratedatabasetolatestversion initializer)

If you are deploying an application, you may want to automatically upgrade the database when the application starts (by applying all pending migrations). This can be done by registering the migratedatabasetolatestversion database initializer. The database initializer simply contains some logic to ensure that the database is installed correctly. This logic is run when the context is first used in an application process (AppDomain).

We can update the Program.cs file (as shown below), set the Blogcontext migratedatabasetolatestversion initializer, and then use the context (line 14th). Note that you also need to add a using statement (line 5th) for the System.Data.Entity namespace.

When you create an instance of this initializer, you need to specify the context type (Blogcontext) and Migration configuration (Configuration)-migration configuration is added to migrations when migration is enabled The class of the folder.

using system;  using system.collections.generic;  using system.linq;  using 
system.text;  using system.data.entity;  using migrationsdemo.migrations;    namespace migrationsdemo  {      class Program       {          static void main (String[] args)           {               database.setinitializer (new migratedatabasetolatestversion<blogcontext,  Configuration> ());                using   (Var db = new blogcontext ())                {                   db. Blogs.add (new blog { name =  "another blog " &nbsp);                   db. SaveChanges ();                     foreach  (var blog in db. Blogs)                   {                        console.writeline (blog. Name);                  }               }                 console.writeline ("press any key to  Exit ... ");  &NBSP;&NBSP;&NBSP;&NBSp;        console.readkey ();           }      } }

Now every time the application runs, it checks to see if the database being targeted is up to date, and if not, all pending migrations are applied.

Summary

In this walkthrough, you learned how to build scaffolding, edit, and run these migrations for a code-based migration to achieve database upgrades and demotion. Also, learn how to get SQL scripts to apply the migration to the database, and how to automatically apply all pending migrations when the application starts.