WEBVTT 00:00.200 --> 00:03.200 Okay, so we've got our SQL server up and running. 00:03.200 --> 00:07.580 And let's go back to VS code to configure this in our application. 00:07.580 --> 00:10.970 So first of all I'm going to go to the app settings. 00:11.210 --> 00:17.720 In fact before we do anything else we should actually stop our API server from running as we've got 00:17.720 --> 00:22.850 a few things to do here to get this switched from SQLite to SQL server. 00:22.850 --> 00:26.150 And we do need to stop our application at this point. 00:26.150 --> 00:31.790 So first of all, we'll go to our app Settings Development dot JSON. 00:31.790 --> 00:38.360 And we're going to change our connection string from what is currently a SQLite connection string to 00:38.390 --> 00:40.730 a SQL server connection string. 00:40.730 --> 00:42.740 So we're going to remove the data source. 00:42.740 --> 00:47.300 And in its place we're going to use a SQL server connection string which looks like this. 00:47.300 --> 00:54.110 So we specify server equals localhost and then a comma and 1433. 00:54.110 --> 01:00.560 And that's going to be the same regardless of whether or not you're using Docker to run SQL server or 01:00.560 --> 01:06.320 you're running SQL server natively on your operating system, as long as you haven't changed any of 01:06.320 --> 01:11.300 the defaults, because 1433 is the standard SQL server port. 01:11.420 --> 01:16.370 Following this, we add a semicolon and then we specify the database name. 01:16.370 --> 01:17.960 So we use database equals. 01:17.960 --> 01:21.080 And I'm just going to use shop as the database name. 01:21.110 --> 01:22.700 Then we'll add a semicolon. 01:22.700 --> 01:27.770 And we say user space ID equals SA. 01:28.220 --> 01:31.820 And I believe casing is important in this file. 01:31.820 --> 01:36.500 So please ensure that user ID is specified exactly as this. 01:36.800 --> 01:40.190 And then we're going to specify the password that we're using here. 01:40.190 --> 01:45.830 And also what I'd suggest is you just go to your Docker compose file. 01:45.830 --> 01:52.850 You copy the password into your clipboard and you paste it into this just to ensure it's going to be 01:52.850 --> 01:54.050 exactly the same. 01:54.050 --> 02:03.770 And then we need one more parameter here, which is going to be trust server certificate equals true 02:03.770 --> 02:10.430 as we're using self-signed certificates everywhere here, and we need to effectively tell it to trust 02:10.460 --> 02:13.160 whatever certificate is being used there as well. 02:13.160 --> 02:15.380 So this is our connection string. 02:15.890 --> 02:18.320 And please make sure you're accurate inside here. 02:18.320 --> 02:23.300 If you do get any database connectivity problems then please do come back and double check. 02:23.300 --> 02:26.690 Every single character inside here is accurate. 02:26.720 --> 02:34.460 There's no room for error with SQL server connection strings, and I would suggest casing as well is 02:34.460 --> 02:36.080 something that you pay attention to. 02:36.110 --> 02:42.590 So then we can open up our terminal as we're going to need an Entity Framework package here as well. 02:42.590 --> 02:44.930 And we'll head over to the NuGet tab. 02:44.930 --> 02:52.100 And we're going to look for Microsoft Dot Entity Framework. 02:54.110 --> 02:54.920 Core. 02:56.030 --> 02:59.870 And we're looking for the SQL server version of this. 02:59.870 --> 03:03.050 So just go ahead and add this to your API. 03:03.080 --> 03:04.190 Csproj. 03:04.700 --> 03:07.460 And that is now installed and available. 03:07.460 --> 03:14.540 And if we take a look and look for API Csproj, we now have SQL server added to this package. 03:14.540 --> 03:16.130 We don't need SQLite anymore. 03:16.130 --> 03:19.580 If you would prefer to remove it, please feel free to do so. 03:19.580 --> 03:22.430 And in fact, that's exactly what I'm going to do. 03:22.460 --> 03:28.220 Just to avoid any confusion, let's just remove that particular package from the API. 03:28.250 --> 03:29.180 Csproj. 03:29.180 --> 03:34.190 And when we do do something like that, we do need to execute dotnet restore, but we'll probably get 03:34.190 --> 03:36.020 an error if we try and do this. 03:36.020 --> 03:41.990 And just to clarify what I'm talking about there, if we use dotnet restore that's going to restore 03:41.990 --> 03:45.650 any NuGet packages inside our csproj. 03:45.650 --> 03:47.570 But I think I'll get an error. 03:47.570 --> 03:49.220 I thought I was going to get an error. 03:49.220 --> 03:50.210 I didn't get an error. 03:50.210 --> 03:55.490 So now everything is or this is the only one available that we can use. 03:55.490 --> 03:59.570 If we go to our program class, I expect we're going to see an error inside here. 03:59.870 --> 04:06.320 And sure enough, we do because you SQLite is no longer available and we're going to swap this for use 04:06.320 --> 04:07.760 SQL server. 04:08.510 --> 04:12.680 Now another thing that we need to think about is I'll just close the tabs. 04:12.680 --> 04:19.910 At the top here is we've got a bunch of migrations inside our solution explorer, inside the data folder 04:19.910 --> 04:21.410 inside migrations. 04:21.410 --> 04:25.070 We've got all of these different migrations created. 04:25.070 --> 04:26.360 Can we use these? 04:26.360 --> 04:31.400 Well, if we take a look at one of our migrations, let's take a look at what's going on inside here. 04:31.400 --> 04:35.960 And is there anything specific to SQLite that this migration uses? 04:35.960 --> 04:40.130 Well, yes, this SQLite autoincrement. 04:40.130 --> 04:46.220 This is an annotation that's applied because we're using or we're using the SQLite version of Entity 04:46.250 --> 04:47.150 Framework. 04:47.180 --> 04:51.170 Can we use this with a SQL server database? 04:51.200 --> 04:52.490 No we cannot. 04:52.520 --> 04:55.370 So we need to remove our migrations. 04:55.370 --> 04:58.460 Now we don't need to keep these migrations. 04:58.460 --> 05:03.890 These can be recreated at any point based on the code that we currently have, and we've also got them 05:03.890 --> 05:09.230 if we want to check the history of what we did with migrations, then we've saved it into source control 05:09.230 --> 05:15.770 anyway, and we can always take a look at what we had at any specific commits that we've made into our 05:15.770 --> 05:17.090 git repository. 05:17.360 --> 05:23.600 What I'm basically saying is that we're okay just to right click and delete the migrations folder, 05:23.600 --> 05:25.730 which is exactly what I'm going to do. 05:26.210 --> 05:33.170 Following this, we can then create a new migration based on our existing code that's relevant for SQL 05:33.170 --> 05:33.770 server. 05:33.770 --> 05:35.270 And that's exactly what we'll do. 05:35.270 --> 05:39.950 So I'll clear this and say dotnet EF migrations add. 05:39.950 --> 05:45.650 And I'll just say SQL server initial and we'll give it an output directory. 05:45.650 --> 05:47.660 Because we've removed the migrations folder. 05:47.660 --> 05:49.850 We do need to specify it this time. 05:49.850 --> 05:55.160 And I'll specify data migrations as the outputs and press return. 05:55.850 --> 06:00.470 So this will go ahead and create that migration based on our existing code. 06:00.470 --> 06:06.740 And if we go and take a look at our migrations now, then inside here we have a single migration for 06:06.740 --> 06:08.240 everything, basically. 06:08.240 --> 06:15.500 And if we take a look in here, we can see that we now have SQL server annotations and various other 06:15.500 --> 06:20.330 differences that you might see based on the different database server type that we're now using. 06:20.330 --> 06:23.240 But otherwise it's pretty much the same thing. 06:23.600 --> 06:28.190 And we can see all of these different options inside here for different tables that are going to be 06:28.190 --> 06:29.990 created, etc.. 06:29.990 --> 06:31.340 So that's good. 06:31.340 --> 06:37.250 And then now that we have this in place, we can go ahead and restart our application. 06:37.430 --> 06:46.430 So I'll execute dotnet watch and press return and cross fingers and hope that the database gets created 06:46.430 --> 06:50.150 and our data gets seeded inside to our database. 06:50.150 --> 06:54.620 And I forgot to cross my fingers, which meant it failed. 06:54.620 --> 06:56.630 And why did it fail. 06:56.630 --> 07:04.460 And let's take a look at the exception and see what information we can get from this. 07:04.490 --> 07:06.470 Well, it looks like most of it worked. 07:06.470 --> 07:09.050 It looks like we've got our indexes being created. 07:09.050 --> 07:11.180 We've got our tables being created. 07:11.180 --> 07:13.880 So the majority of this is working. 07:13.880 --> 07:15.740 We can see lots of tables being created. 07:15.740 --> 07:17.090 All of that looks good. 07:17.090 --> 07:19.370 But where is the problem. 07:19.640 --> 07:27.170 And if we take a look at where the exception is occurring and it's happening after the migration has 07:27.170 --> 07:35.390 been applied, but this looks or this appears to be because what the query is we see here is we're checking 07:35.420 --> 07:41.570 to see if we have any users in the database that's going on inside our seed class. 07:41.570 --> 07:48.290 And then we've got an issue with, let's see if we just come down to the exception. 07:48.470 --> 07:51.110 It tells us the connection is closed. 07:51.110 --> 07:54.080 So the issue is not one of authentication. 07:54.080 --> 07:59.730 It's not one of database connectivity because we can see our tables are being created, but it is an 07:59.730 --> 08:03.840 issue with our seed class or our DB initializer class. 08:03.840 --> 08:05.040 So let's take a look at this. 08:05.070 --> 08:10.620 It wasn't a problem in SQLite obviously, but SQL server is fussy. 08:11.040 --> 08:15.270 And if we take a look at what we're doing here, I can see the problem now. 08:15.270 --> 08:17.790 Now it's staring me in the face. 08:17.790 --> 08:23.550 And what we have here is the private static async void void. 08:23.550 --> 08:27.150 This is the issue even though we're not returning anything from this method. 08:27.150 --> 08:29.100 It's an asynchronous method. 08:29.100 --> 08:34.350 So instead of returning void, what we have to return from this is a task. 08:35.400 --> 08:38.910 And then we need to await the seed data. 08:38.910 --> 08:45.630 So a couple of mistakes inside this class didn't affect us with SQLite of course, but it does affect 08:45.630 --> 08:46.830 us with this one. 08:46.830 --> 08:50.040 And this also needs to return a task as well. 08:50.070 --> 08:51.090 Fully init db. 08:51.420 --> 08:58.710 And let's go back to our program class and take a look at that one as well And make sure we're using 08:58.710 --> 09:00.360 await inside here. 09:00.360 --> 09:01.080 And we're not. 09:01.080 --> 09:09.090 So several mistakes there because of SQL lights, not fussiness, but because I've used async inside 09:09.090 --> 09:10.470 the DB initializer class. 09:10.470 --> 09:12.720 Then we do need to await what we're doing here. 09:12.720 --> 09:14.640 So my mistake didn't even notice. 09:14.640 --> 09:20.550 Because if something doesn't go wrong, even if there's a small mistake that you've made, it's hard 09:20.550 --> 09:21.270 to pick up. 09:21.270 --> 09:27.120 And it's only when you see the mistake and you can fix the mistake because something isn't working as 09:27.120 --> 09:28.080 it's supposed to. 09:28.110 --> 09:28.770 Anyway. 09:28.770 --> 09:30.480 That's what I think the problem is going to be. 09:30.510 --> 09:34.710 There's definitely a problem with our seeding of data, because that's where the request failed. 09:34.740 --> 09:38.490 And let's stop the server from running. 09:38.490 --> 09:41.250 And I'm just going to drop the database first of all. 09:41.250 --> 09:48.450 So I'm going to use dotnet f database drop as I want to make sure the database is clean before I try 09:48.450 --> 09:50.040 and do anything again. 09:50.040 --> 09:54.540 And we should get the prompt come up asking us if we are sure. 09:54.540 --> 09:56.340 And I'll say yes to this. 09:56.340 --> 10:01.560 And once our database has been dropped, I'll just clear this window and we'll execute dotnet watch 10:01.590 --> 10:02.580 once again. 10:02.580 --> 10:06.210 And hopefully this time we'll get a successful result. 10:07.920 --> 10:09.630 And that looks a bit more like it. 10:09.660 --> 10:11.100 We've got a clean output now. 10:11.100 --> 10:15.930 And it looks like we're inserting our seed data into the database as well. 10:15.930 --> 10:17.910 So there's a quick way to find out if things are working. 10:17.910 --> 10:24.300 We'll just go back to our application and let's use the one running on the API server. 10:24.300 --> 10:28.980 And I'm just going to log out of Bob at Test.com on this one. 10:29.130 --> 10:34.380 And it looks like I've got a basket here that will not persist. 10:34.380 --> 10:41.280 I do not think if I refresh the page because that cookie will not be inside our new database, and I'll 10:41.280 --> 10:49.890 log in as bob@test.com with the password, and we're logged in as Bob. 10:49.890 --> 10:57.450 And if I can add an item to my cart and click on the baskets, then that confirms our database is functioning 10:57.450 --> 11:00.300 with a SQL server database. 11:00.300 --> 11:05.310 But if I click on the checkout then this is something that we didn't actually test earlier, is what's 11:05.310 --> 11:09.330 going to happen with a user that doesn't already have an address saved in our database. 11:09.330 --> 11:14.790 We've just been working, or at least I've been demonstrating with a user that has an address in a database. 11:14.790 --> 11:17.760 But this is a new database now it's on SQL server. 11:17.760 --> 11:22.140 And if I click checkout what happens is we get an error. 11:22.140 --> 11:24.540 And this is a mistake of mine. 11:24.540 --> 11:32.100 I didn't test the functionality with a user that has an address populated already versus a user that 11:32.100 --> 11:38.850 doesn't have an address populated, and we're attempting to destructure something that is null now because 11:38.850 --> 11:39.840 it's a new database. 11:39.840 --> 11:46.110 Bob doesn't have an address inside there, and we can't destructure something that's null and name doesn't 11:46.110 --> 11:46.950 exist on null. 11:46.950 --> 11:50.190 And that's why we see this specific error here. 11:50.190 --> 11:58.110 So back to the code and let's open up Check out stepper, and I've actually come back from a future 11:58.110 --> 12:03.600 point to correct this mistake, because I didn't actually see it until publishing was occurring. 12:03.600 --> 12:06.480 So the problem here exists on this line. 12:06.480 --> 12:11.670 This is the issue that's given us the problem or the line of code that's giving us the problem. 12:11.670 --> 12:17.970 And specifically, we can't destructure the name from something that's returning null, because our 12:17.970 --> 12:22.380 fetch address query for a user that doesn't have an address doesn't return an address to us. 12:22.380 --> 12:29.400 So we've tricked TypeScript or our TypeScript compiler into thinking that this is okay, because of 12:29.400 --> 12:35.340 the way that we've put this into an empty object and then said as address. 12:35.340 --> 12:40.290 So this isn't going to work for us for an empty address, and we're going to need to do something different. 12:40.290 --> 12:43.860 So instead of data etc. is loading. 12:43.860 --> 12:45.420 We're just going to have data. 12:45.420 --> 12:48.990 Comma is loading for I use fetch address query. 12:48.990 --> 12:50.220 And then we're going to come down. 12:50.220 --> 12:54.930 Please ignore anything that you see inside here that you haven't done yet. 12:54.930 --> 13:00.060 If you do see anything of that nature, because I have come back from a future point to update this 13:00.060 --> 13:01.500 specific lesson. 13:01.590 --> 13:07.170 And just below our other const inside here, we're going to need to do this a slightly different way. 13:07.170 --> 13:14.820 So we're going to say let name comma rest address as just a declaration of two variables. 13:14.910 --> 13:17.790 Then we're going to check to make sure that we have the data. 13:17.790 --> 13:22.110 And if we have then we're going to use in parentheses. 13:22.350 --> 13:24.090 We're going to destructure it here. 13:24.090 --> 13:29.280 We're going to say name comma dot dot dot rest address. 13:29.490 --> 13:32.700 And to the right of the curly brackets equals data. 13:32.700 --> 13:35.190 And this should resolve that problem. 13:35.190 --> 13:36.810 And I've got an issue here. 13:36.810 --> 13:40.920 And we don't actually need to import the address at the top there either. 13:40.920 --> 13:49.050 So this should resolve that problem now because we're working on our production build of react in the 13:49.050 --> 13:54.900 demonstration I've just made, in order for that change to take effect on our production version, we 13:54.930 --> 13:59.220 need to effectively rebuild our react application. 13:59.220 --> 14:02.550 So I'll just stop the running react application I have here. 14:02.550 --> 14:11.100 I'll just execute npm run build, which will create an updated version of our react application and 14:11.100 --> 14:14.580 output that into the root folder. 14:14.880 --> 14:18.180 And once that has completed, we'll just go back to the API. 14:18.180 --> 14:30.180 And I'm just going to stop and restart the API service and execute dotnet watch press return. 14:31.350 --> 14:37.500 And once that has restarted, we should be able to go back to our checkout now and we will be able to 14:37.530 --> 14:41.610 go to the checkout because now we've handled that issue. 14:41.610 --> 14:44.190 So now we've got our production database server. 14:44.190 --> 14:48.510 We need to start thinking about a production platform to host our application on. 14:48.540 --> 14:53.340 The one we're going to use is Azure, and we'll take a look at that next.