Okay, so we've got our SQL server up and running.
And let's go back to VS code to configure this in our application.
So first of all I'm going to go to the app settings.
In fact before we do anything else we should actually stop our API server from running as we've got
a few things to do here to get this switched from SQLite to SQL server.
And we do need to stop our application at this point.
So first of all, we'll go to our app Settings Development dot JSON.
And we're going to change our connection string from what is currently a SQLite connection string to
a SQL server connection string.
So we're going to remove the data source.
And in its place we're going to use a SQL server connection string which looks like this.
So we specify server equals localhost and then a comma and 1433.
And that's going to be the same regardless of whether or not you're using Docker to run SQL server or
you're running SQL server natively on your operating system, as long as you haven't changed any of
the defaults, because 1433 is the standard SQL server port.
Following this, we add a semicolon and then we specify the database name.
So we use database equals.
And I'm just going to use shop as the database name.
Then we'll add a semicolon.
And we say user space ID equals SA.
And I believe casing is important in this file.
So please ensure that user ID is specified exactly as this.
And then we're going to specify the password that we're using here.
And also what I'd suggest is you just go to your Docker compose file.
You copy the password into your clipboard and you paste it into this just to ensure it's going to be
exactly the same.
And then we need one more parameter here, which is going to be trust server certificate equals true
as we're using self-signed certificates everywhere here, and we need to effectively tell it to trust
whatever certificate is being used there as well.
So this is our connection string.
And please make sure you're accurate inside here.
If you do get any database connectivity problems then please do come back and double check.
Every single character inside here is accurate.
There's no room for error with SQL server connection strings, and I would suggest casing as well is
something that you pay attention to.
So then we can open up our terminal as we're going to need an Entity Framework package here as well.
And we'll head over to the NuGet tab.
And we're going to look for Microsoft Dot Entity Framework.
Core.
And we're looking for the SQL server version of this.
So just go ahead and add this to your API.
Csproj.
And that is now installed and available.
And if we take a look and look for API Csproj, we now have SQL server added to this package.
We don't need SQLite anymore.
If you would prefer to remove it, please feel free to do so.
And in fact, that's exactly what I'm going to do.
Just to avoid any confusion, let's just remove that particular package from the API.
Csproj.
And when we do do something like that, we do need to execute dotnet restore, but we'll probably get
an error if we try and do this.
And just to clarify what I'm talking about there, if we use dotnet restore that's going to restore
any NuGet packages inside our csproj.
But I think I'll get an error.
I thought I was going to get an error.
I didn't get an error.
So now everything is or this is the only one available that we can use.
If we go to our program class, I expect we're going to see an error inside here.
And sure enough, we do because you SQLite is no longer available and we're going to swap this for use
SQL server.
Now another thing that we need to think about is I'll just close the tabs.
At the top here is we've got a bunch of migrations inside our solution explorer, inside the data folder
inside migrations.
We've got all of these different migrations created.
Can we use these?
Well, if we take a look at one of our migrations, let's take a look at what's going on inside here.
And is there anything specific to SQLite that this migration uses?
Well, yes, this SQLite autoincrement.
This is an annotation that's applied because we're using or we're using the SQLite version of Entity
Framework.
Can we use this with a SQL server database?
No we cannot.
So we need to remove our migrations.
Now we don't need to keep these migrations.
These can be recreated at any point based on the code that we currently have, and we've also got them
if we want to check the history of what we did with migrations, then we've saved it into source control
anyway, and we can always take a look at what we had at any specific commits that we've made into our
git repository.
What I'm basically saying is that we're okay just to right click and delete the migrations folder,
which is exactly what I'm going to do.
Following this, we can then create a new migration based on our existing code that's relevant for SQL
server.
And that's exactly what we'll do.
So I'll clear this and say dotnet EF migrations add.
And I'll just say SQL server initial and we'll give it an output directory.
Because we've removed the migrations folder.
We do need to specify it this time.
And I'll specify data migrations as the outputs and press return.
So this will go ahead and create that migration based on our existing code.
And if we go and take a look at our migrations now, then inside here we have a single migration for
everything, basically.
And if we take a look in here, we can see that we now have SQL server annotations and various other
differences that you might see based on the different database server type that we're now using.
But otherwise it's pretty much the same thing.
And we can see all of these different options inside here for different tables that are going to be
created, etc..
So that's good.
And then now that we have this in place, we can go ahead and restart our application.
So I'll execute dotnet watch and press return and cross fingers and hope that the database gets created
and our data gets seeded inside to our database.
And I forgot to cross my fingers, which meant it failed.
And why did it fail.
And let's take a look at the exception and see what information we can get from this.
Well, it looks like most of it worked.
It looks like we've got our indexes being created.
We've got our tables being created.
So the majority of this is working.
We can see lots of tables being created.
All of that looks good.
But where is the problem.
And if we take a look at where the exception is occurring and it's happening after the migration has
been applied, but this looks or this appears to be because what the query is we see here is we're checking
to see if we have any users in the database that's going on inside our seed class.
And then we've got an issue with, let's see if we just come down to the exception.
It tells us the connection is closed.
So the issue is not one of authentication.
It's not one of database connectivity because we can see our tables are being created, but it is an
issue with our seed class or our DB initializer class.
So let's take a look at this.
It wasn't a problem in SQLite obviously, but SQL server is fussy.
And if we take a look at what we're doing here, I can see the problem now.
Now it's staring me in the face.
And what we have here is the private static async void void.
This is the issue even though we're not returning anything from this method.
It's an asynchronous method.
So instead of returning void, what we have to return from this is a task.
And then we need to await the seed data.
So a couple of mistakes inside this class didn't affect us with SQLite of course, but it does affect
us with this one.
And this also needs to return a task as well.
Fully init db.
And let's go back to our program class and take a look at that one as well And make sure we're using
await inside here.
And we're not.
So several mistakes there because of SQL lights, not fussiness, but because I've used async inside
the DB initializer class.
Then we do need to await what we're doing here.
So my mistake didn't even notice.
Because if something doesn't go wrong, even if there's a small mistake that you've made, it's hard
to pick up.
And it's only when you see the mistake and you can fix the mistake because something isn't working as
it's supposed to.
Anyway.
That's what I think the problem is going to be.
There's definitely a problem with our seeding of data, because that's where the request failed.
And let's stop the server from running.
And I'm just going to drop the database first of all.
So I'm going to use dotnet f database drop as I want to make sure the database is clean before I try
and do anything again.
And we should get the prompt come up asking us if we are sure.
And I'll say yes to this.
And once our database has been dropped, I'll just clear this window and we'll execute dotnet watch
once again.
And hopefully this time we'll get a successful result.
And that looks a bit more like it.
We've got a clean output now.
And it looks like we're inserting our seed data into the database as well.
So there's a quick way to find out if things are working.
We'll just go back to our application and let's use the one running on the API server.
And I'm just going to log out of Bob at Test.com on this one.
And it looks like I've got a basket here that will not persist.
I do not think if I refresh the page because that cookie will not be inside our new database, and I'll
log in as bob@test.com with the password, and we're logged in as Bob.
And if I can add an item to my cart and click on the baskets, then that confirms our database is functioning
with a SQL server database.
But if I click on the checkout then this is something that we didn't actually test earlier, is what's
going to happen with a user that doesn't already have an address saved in our database.
We've just been working, or at least I've been demonstrating with a user that has an address in a database.
But this is a new database now it's on SQL server.
And if I click checkout what happens is we get an error.
And this is a mistake of mine.
I didn't test the functionality with a user that has an address populated already versus a user that
doesn't have an address populated, and we're attempting to destructure something that is null now because
it's a new database.
Bob doesn't have an address inside there, and we can't destructure something that's null and name doesn't
exist on null.
And that's why we see this specific error here.
So back to the code and let's open up Check out stepper, and I've actually come back from a future
point to correct this mistake, because I didn't actually see it until publishing was occurring.
So the problem here exists on this line.
This is the issue that's given us the problem or the line of code that's giving us the problem.
And specifically, we can't destructure the name from something that's returning null, because our
fetch address query for a user that doesn't have an address doesn't return an address to us.
So we've tricked TypeScript or our TypeScript compiler into thinking that this is okay, because of
the way that we've put this into an empty object and then said as address.
So this isn't going to work for us for an empty address, and we're going to need to do something different.
So instead of data etc. is loading.
We're just going to have data.
Comma is loading for I use fetch address query.
And then we're going to come down.
Please ignore anything that you see inside here that you haven't done yet.
If you do see anything of that nature, because I have come back from a future point to update this
specific lesson.
And just below our other const inside here, we're going to need to do this a slightly different way.
So we're going to say let name comma rest address as just a declaration of two variables.
Then we're going to check to make sure that we have the data.
And if we have then we're going to use in parentheses.
We're going to destructure it here.
We're going to say name comma dot dot dot rest address.
And to the right of the curly brackets equals data.
And this should resolve that problem.
And I've got an issue here.
And we don't actually need to import the address at the top there either.
So this should resolve that problem now because we're working on our production build of react in the
demonstration I've just made, in order for that change to take effect on our production version, we
need to effectively rebuild our react application.
So I'll just stop the running react application I have here.
I'll just execute npm run build, which will create an updated version of our react application and
output that into the root folder.
And once that has completed, we'll just go back to the API.
And I'm just going to stop and restart the API service and execute dotnet watch press return.
And once that has restarted, we should be able to go back to our checkout now and we will be able to
go to the checkout because now we've handled that issue.
So now we've got our production database server.
We need to start thinking about a production platform to host our application on.
The one we're going to use is Azure, and we'll take a look at that next.