This is the second in a series of articles on constructing this website. Now that I have a continuous deployment set up to release new vesions of my website automatically, it's time to add the ability to store articles through a database. Here I want to use Entity Framework 5 Code First Migrations, MVC 4 and MVC Scaffolding to create a database driven article system with comments.
I have configured a shortcut within Visual Studio by going to Tools > Options > Environment > Keyboard then typing in "package" in the Show commands containing box to get to: View.PackageManagerConsole. I press Ctrl + P, Ctrl + M and now when I press that the Package Manager Console shows up.
I first run:
PM> Install-Package EntityFramework
In this project it was already installed with the template I selected so nothing happens. I next want to install MvcScaffolding to make creating my views more efficient.
So I run:
PM> Install-Package MvcScaffolding
After this command runs I see that it has to install T4 Scaffolding first because it's a dependency. At this point I can start making my model classes that I want to have scaffolded. To do that I will add a new class file called "Models" inside the Models folder in the web project. I am going to add two classes, one for articles and one for comments. It's better to place each class into a separate file however, I am doing this just to save time right now. I can invoke the scaffolding by class name. Entity Framework conventions allow me to have a column called: "ArticleID" in another class and have it figure out that I want it to be a foreign key. There are other ways to do this however, I am going to try to follow their conventions for now.
This is my what my Models.cs file looks like:
Now it's time to scaffold controllers for these classes through PowerShell (which is what Package Manager Console actually is).
PM> Scaffold Controller Article -Repository PM> Scaffold Controller Comment -Repository
The result of each of these commands created a database context class, a repository class, a controller class, a create, edit, delete, details and index view and a partial view for create or edit which refactors common tasks in create and edit. I am now going to create a link to the aricles index so that when I run this site it I can click to see the article index. To do this I go to the _Layout.cshtml file and add a new menu item like this:
@Html.ActionLink("Articles", "Index", "Articles")
This says, give me a link where the link text says: "Articles" (first parameter) and when I click it, go to the Index action on the Articles controller. Now, when I run the site (F5), it will create my database since I haven't yet created one. I see the Articles link in the menu and click on that. Here I see the column headers for an empty table, based on my real table, and a way to create a new article. When I click create new I see the ability to create a new article, ignoring my ID column as a user interface text box because I followed their convention it knows this is a primary key. I could have used data annotations like "[Key]" on the property to break convention or be more explicit.
Just to test this out I create a new entry, I realize that there is client side validation for the non-nullable CreatedOn column. After clicking create I am brought back to the index with my new article item. From here I can edit, view or delete this item. It's interesting to note that in this case I can see the Comments column shows "0" as if to say that this article has no comments. I'm curious about what is going on behind the scenes in SQL Server so I will stop the application and hunt down the database so I can see my article item.
I open SQL Server Management Studio 2012 and connect to a database engine, I will be connecting to my local SQL Express so my server name is: MYCOMPUTER\SQLEXPRESS because I have SQL Server installed. When I expand the databases I see a database named: "MySite.Models.MySiteContext" and inside of it are my two tables. I do not like that all my string properties are nvarchar(MAX) data types because it's wasteful to allow for so much more storage than I plan to use with these columns. I am going to go back to my classes to add data annotations for validation and add more columns as well.
Now if I run the site again and go back to the Articles section it throws this exception: "The model backing the 'MySiteContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269)." That's informing me that I need to do the work of updating my database. To deal with this I will first need to update my scaffolded code and enable code first migrations.
PM> Scaffold Controller Article -Repository -Force PM> Scaffold Controller Comment -Repository -Force
Because I used a template to create the website I will have to enable migrations for one of the contexts rather than simply the whole project.
PM> Enable-Migrations -ContextTypeName MySite.Models.MySiteContext -Force
This make a Migrations folder and I have an initial create class that has my original database schema. I am now going to add my first migration for the changes I made since the database was created.
PM> Add-Migration AddedArticleCommentValidation
This creates a new class title: "AddedArticleCommentValidation" which has the changes I made to my models. When naming migrations, use class naming conventions. Now with my migrations added to the project I can update the database with my revisions.
PM> Update-Database
After this runs I check in the database to see what's happened and my database has been updated with my new migration. I neglected to add maximum string lengths to a few properties in my Comment class so I can either write a new migration or roll back the my most recently excecuted one. I am going to roll it back to make sure that it's working as I expect. Since the name of the first migration class that was added was "InitialCreate" I will run a way to update to that migration.
PM> Update-Database -TargetMigration:"InitialCreate"
Now when I look in the database I see the previous schema without my new columns or data annotation modifications. When I add a new migration with my updates to the Comment class it tells me that: "Unable to generate an explicit migration because the following explicit migrations are pending:". From here I decide to delete the previously generated migration class.
Here are my new model classes:
Now I can add a new migration with the same name I had since the class has been deleted and it will generate a new file with the changes that I want.
PM> Add-Migration AddedArticleCommentValidation
From here I can run:
PM> Update-Database
Now my database has the changes that I want because my migration has been applied.
From here I am anxious to put this demo online. I want to have my code push fire off a database update on the database server which will apply only the changes that are required on the target database, from there deploy the website package to the server as was being done previously.
To migrate my database I will add a new console application project to the solution that will call the migrate.exe from Entity Framework. The migrate.exe file is located at: D:\MySite\src\packages\EntityFramework.5.0.0\tools in my case but I want to copy the exe to a different folder in case I update my Entity Framework at some point and it changes the directory name. I want to make sure my connection string is valid before trying to use it for a migration so that I can rule that out as an issue.
I put in code that tries to open and close a connection to my database (because I'm hosting with DiscountASP.net):
If it says "OK" then I know my connection string works, otherwise I'll need to resolve the issue. From here I can call the migrate.exe file with my own connection string and arguments that I want to use to migrate the database to it's newest state.
This is my migrate console application:
Now, after compiling the project in Visual Studio, I can run this against the remote database by copying the exe file for my console application (D:\MySite\src\ConsoleMigrator\bin\Debug) into the bin directory for my website (D:\MySite\src\MySite\bin), yes this is a hack of sorts. When I run ConsoleMigrator.exe it connects to the database and updates that database to the latest migration. The remote database now has a table called: "__MigrationHistory" that contains information for my two migrations. In SQL Express the table is in the System Tables folder. If under some circumstance I need to roll back to a specific migration, that can be done with the: /targetMigration="MIGRATIONNAME" parameter to migrate.exe.
I wanted to test that the database migration works before trying to integrate it through TeamCity so that I can know that it can perform the migrations. I want to have this console project's executable in the buildartifacts folder and to run the database migrations after the code tests pass but before deployment of the website. If the database migrations work then the website will be deployed and the site will be updated to the latest version.
It's time to push my changes to the repository. Unfortunately when I do that the build server fails to build my project because of FxCop finding issues with my new code. I am going to correct or ignore all the issues that it identifies in the XML file it generated and try again. After pushing my code and passing FxCop, my site is deployed to the live site. My database was updated from the command line that I did manually but when I try to access the Articles section of the site it crashes because I have not properly specified my connection string to the remote database in the application. Connection string names are created by a convention based on the context name.
I
For subsequent updates I need to run my console migrator to update the remote database. I will remove the migrate.exe file from the website directory and add the migrate file to the buildartifacts then have it called in the build sequence. Then, updating my local database with package manager plus pushing my code to the remote server.
Right now I have run into a few issues with syncing my local and remote database. What I've decided to do is reset my remote and local database and delete my Migrations folder in the website. This will give each a clean state to my current Models. I will re-enabled Entity Framework, add a new migration that contains my current Model and then update my local database. After I run the PowerShell commands and push my code, my local and production database will be synchronized from here forward.
PowerShell:
PM> Enable-Migrations -ContextTypeName MySite.Models.MySiteContext -Force -verbose PM> Add-Migration Initial
Build Script:
This is a huge productivity boost in a green development situation however, if things grew it would be best to have a test and stage area prior to deployment to production. It would also be more ideal to refactor the connection strings, the migrate.exe file and re-scaffold as I develop with custom scaffolds. I'm next going to work on the UI and develop the pages for the site.
