This is part 25 of azure tutorial. In this video, we'll discuss how to push changes that
is both code and database changes and update web application that is already deployed in azure. So,
basically we want to keep the build on my local machine in sync with the build that is already
deployed in azure. To migrate local database changes to azure sql database, we are going to
use entity framework code first migrations. If you are new to entity framework code first migrations,
we discussed them in Part 50 of asp.net core tutorial. I'll have the link available in
the description of this video. Now, let's take a look at the current state of the web application
both on my local machine and the build that is already deployed in azure. If you have been
following along with this course, we have a web application that displays inspirational quotes.
As you can see from the URL, at the moment this web application is running on my local machine and
these quotes that we see on this page are coming from a sql server database. So, if we take a look
at sql server management studio, notice in the object explorer at the top we are connected to sql
server localdb on my local development machine. We can say that by looking at the name of the server.
So, on this server we have our database QuotesDB and within this database we've got these two
tables - __MigrationHistory and Quotes. This Quotes table contains the list of five quotes
that are displayed by our web application and here are those five quotes and then we did not
create this table __MigrationHistory explicitly. Who created it? Well, it's automatically created
by entity framework to keep track of migrations that are applied to our QuotesDB database. So,
here is that table. At the moment, we've got one row here. Notice the name of the migration,
it ends with _InitialCreate. So, this migration is already applied on this QuotesDB database. Again,
if you're new to migrations, we discussed them in detail in part 50 of our asp.net core tutorial.
Now, this application along with the sql database is already deployed in azure. Take a look at the
URL here. This application is running in azure and the UI looks exactly the same as what we
have on our local machine. Now, let's compare the databases. So, if we take another look at
sql server management studio, notice in the object explorer at the bottom here, we are connected to
sql server in azure. Again, we can tell that by looking at the name of the server. So the schema
of this sql server instance in azure is exactly the same as the schema on sql server localdb on my
local development machine. So, notice within the sql server in azure, we have that same QuotesDB
database and those two tables, __MigrationHistory and Quotes and if you take a look at this query
window Sql Query 2, again when I hover the mouse over this, from the tooltip, you can see
these two queries are being executed against sql server in azure and in these two tables,
notice we have the same rows as what we have on my local development machine. So, the point that I'm
trying to make is, both the web application itself and the database schema match with what we have
on my local development machine and what is deployed in azure. Now, let's take a look at
the enhancements that we want to make to our web application. On the UI along with the quote text
we also want to display the author name as you can see right here. Obviously, to be able to
do this change, we need "Author" column in the "Quotes" database table. Now, before we make
any changes to our web application code, first let's enable entity framework migrations because
it is these migrations that we are going to use to push database changes from our local development
machine to azure. So, to enable migrations, let's get to package manager console in visual
studio. For that, click on "View", "Other Windows" and then "Package Manager Console".
Type the command "Enable-Migrations" and to have the commands auto completed, press the "Tab" key.
Here is the important bit, as the command is being executed, pay attention to "Solution Explorer".
Notice a new folder with name "Migrations" appeared and in this folder we have a file
that ends with the name _InitialCreate.cs. Let's take a look at this file.
Notice, all this migration is doing is creating this "Quotes" database table on our local database
but if you recollect within this QuotesDB database, we already have the "Quotes"
table and if you take a look at the name of this migration file, it matches with the migration id
that we have in __MigrationHistory table. So, the point that I'm trying to make is, this
migration is already applied on the database on our local development machine and the same is true
even with azure. Now, let's see what's going to happen if we try to again apply this migration on
our local development machine. For that, let's execute Update-Database command from package
manager console window. There we go. Command completed and notice the message that we have
here - No pending explicit migrations. Now, let's run the project and see if we have any changes
on the UI side. There we go. UI is exactly the same as before and it makes sense because we
haven't really made any change to our application. Now, when we deploy this web application in azure,
we want the entity framework code first migrations to be executed. So, we have to configure that. We
configure that on the "Publish" page of our project. So, right click on the project name
and select this option - "Publish". Click on this "Edit" link and then "Settings"
from this drop down list here. We need to select our sql server instance running in azure. Notice,
this last option here, by looking at the icon we can tell this is our on-premises
sql server database. So, in our case it is sql server localdb. Notice, when I hover the
mouse over this, you can actually see it is sql server localdb. So, this is our local development
database. This is not what we want at runtime. We have to select here, our sql server instance
in azure and look at this second option here, we have the cloud icon and the database name matches
QuotesDB. So, this is what we want to select and then make sure you select these two check boxes as
well. You want to execute code first migrations. So, let's check that box and then click "Save"
and then "Publish". There we go. Again, even in azure from the UI standpoint, there's no change,
it's exactly the same as before. Now, let's make the changes and here's the plan, first
we make both the code and database changes on our local development machine, test the application
and if we are happy with the changes, we will then push them to azure. So, the first thing
that we want to do is, add "Author" column to this "Quotes" database table. Since we're using
entity framework code first approach, all we need to do for that is, introduce "Author" property
in this "Quote" class. Next, we need to add a new migration that actually adds "Author"
column to the database table. So, let's get to package manager console and execute this command
"Add-Migration" and I named the migration - Add_Quote_Author". There we go. In the
"Migrations" folder, we now have a new file for this new migration and this file is already open
within visual studio here, and notice all this migration is doing is, adding "Author" column
to our "Quotes" database table. Now, when we add this new column to this Quotes database table,
we also have to update the column value to reflect the author name. So, we need to issue
update statements and I already have code which does that. Let me paste that right here. As you
can see, all we are doing is using an instance of StringBuilder and building our update queries.
Basically, we want to update all the five existing rows in our quotes database table with author name
and then we're using the Sql() method to execute these sql queries. So, let's save our changes
and we want to apply this migration on our local database. So, for that,
let's get to package manager console and execute Update-Database command.
There we go. Migration successfully applied. Let's get to sql server management studio
and execute both these queries against our local sql server database. Notice, from the migration
history table we can say our second migration - Add_Quote_Author is applied and if you take a look
at the "Quotes" database table, the "Author" column is added and it is also populated with
data. Now, all that is left to do is, display the author name next to the quote text itself
and we display the quotes using our default webpage default.aspx. So, next to the quote
text, we want to display the author name and here is the code for that. All we are doing is, binding
to the "Author" property. Let's also do the same change in the alternating item template here
and then run our application locally. There we go. On our local machine, we have the changes
as expected, along with the quote text itself, we also have the author name displayed. Now,
we want to push these changes to azure. For that, all we need to do is, get to
the "Publish" window in visual studio and then click this "Publish" button.
There we go. We see the changes as expected. Now, let's execute both these queries against
our sql server in azure. Notice, even the database schema is exactly the same
as what we have on our local database. That's it in this video. Thank you for listening.