1 00:00:00,740 --> 00:00:07,280 In this video we'll be exploring making some changes to our database and how making those changes in 2 00:00:07,280 --> 00:00:11,360 the database will reflect in our IBM makes diagram. 3 00:00:11,360 --> 00:00:18,100 So in today's activity we're going to be adding some more fields to our vehicles table. 4 00:00:18,110 --> 00:00:23,500 Right now we only have an I.D. and we have a name so we know that I.D. is a primary key and that's all 5 00:00:23,500 --> 00:00:25,910 to incrementing is just keeping track of all the records. 6 00:00:26,180 --> 00:00:27,080 And we have a name. 7 00:00:27,100 --> 00:00:29,930 What's in reality for storing vehicle information. 8 00:00:29,930 --> 00:00:31,660 We need more than just a name. 9 00:00:31,670 --> 00:00:36,140 All the things that we would probably be storing would be like the year the make the model. 10 00:00:36,140 --> 00:00:41,420 So that would actually split naming two into make and model and make being the brand and then the model 11 00:00:41,420 --> 00:00:44,630 being that you know the type of vehicle off that brand. 12 00:00:44,630 --> 00:00:52,270 So for instance with a Honda Civic rights Honda being the MC and civic being the model maybe love in 13 00:00:52,300 --> 00:00:54,240 the license plates number etc.. 14 00:00:54,250 --> 00:00:59,660 So maybe you have more or fewer things that you would want to store in your database. 15 00:00:59,660 --> 00:01:05,430 But to be we'll be modifying our database to accommodate those additional fields. 16 00:01:05,510 --> 00:01:11,210 So going over to our obscure management studio I just went to the database phone the appropriate table 17 00:01:11,210 --> 00:01:19,100 directly to set design and then we are here in the design view where I can add or remove columns as 18 00:01:19,100 --> 00:01:20,410 I see fit. 19 00:01:20,420 --> 00:01:27,410 So I did see that I'm going to be adding some I want to see well I'm not going to replace name with 20 00:01:27,410 --> 00:01:35,450 the year what I want to do is replace year some name with make right and that I'm going to add model 21 00:01:35,540 --> 00:01:41,450 and it's also going to be enviro via our 50 some just want to copy this because it will repeat itself. 22 00:01:41,450 --> 00:01:45,490 We'll have the VIN that's the vehicle identification number. 23 00:01:45,530 --> 00:01:47,490 That's one of those government things. 24 00:01:47,490 --> 00:01:56,510 We have the license plate number so license plate number which is also arch our 50 so even though it 25 00:01:56,510 --> 00:02:00,290 says no we're not doing any math with it in my field. 26 00:02:00,290 --> 00:02:06,260 My general philosophy when it comes to database and numbers would be that if I'm not doing math with 27 00:02:06,270 --> 00:02:09,530 it's I don't need to store it as a numeric data type. 28 00:02:09,530 --> 00:02:09,890 Right. 29 00:02:10,250 --> 00:02:15,590 And then that would actually facilitate if maybe I start fostering just numbers and then it becomes 30 00:02:15,590 --> 00:02:21,240 alphanumeric after a while then it would have already not been a numeric data type. 31 00:02:21,480 --> 00:02:28,850 And then the final one would be the year which I can just set that one to be I'll just set that one 32 00:02:28,850 --> 00:02:29,970 to be integer. 33 00:02:29,970 --> 00:02:30,260 All right. 34 00:02:30,320 --> 00:02:34,660 So integer because you're 20 20 or 20 18 et cetera. 35 00:02:34,760 --> 00:02:36,160 That would be kind of fun no. 36 00:02:36,160 --> 00:02:39,050 So I'll use that one to be an integer. 37 00:02:39,110 --> 00:02:45,140 So after making those adjustments to the database and you notice some inconsistencies so why they're 38 00:02:45,170 --> 00:02:46,370 not a big deal. 39 00:02:46,400 --> 00:02:54,470 I like to keep my you know my naming conventions kind of consistency if I'm using kept some using caps 40 00:02:54,770 --> 00:02:57,200 if I'm not using caps then I'm not using caps. 41 00:02:57,230 --> 00:03:03,020 So just to normalize everything just going to make everything start with a capital letter and then I'm 42 00:03:03,020 --> 00:03:04,160 going to save. 43 00:03:04,340 --> 00:03:11,720 And then this is going to trigger an update to the table so in the database my data table now has new 44 00:03:11,720 --> 00:03:12,560 fields. 45 00:03:12,560 --> 00:03:20,330 And if I go and select the top 1000 then you're going to see all of the rows appearing with all of the 46 00:03:20,330 --> 00:03:27,230 vehicles that we had to date all of them appearing with the make the model and the additional fields 47 00:03:27,230 --> 00:03:27,980 that I added. 48 00:03:27,980 --> 00:03:31,970 So here in the make what was name is no the brand. 49 00:03:32,000 --> 00:03:32,730 Right. 50 00:03:32,750 --> 00:03:37,760 So there are a few that maybe we'll need some refinement because there is no brand name bogey really 51 00:03:37,760 --> 00:03:40,990 and truly buggy is just something that we'll call the Volkswagen Beetle. 52 00:03:41,150 --> 00:03:44,780 Back here in Jamaica but then that's his bogey. 53 00:03:44,800 --> 00:03:46,310 So we can update that later. 54 00:03:46,310 --> 00:03:49,010 That's part of the task at hand. 55 00:03:49,070 --> 00:03:55,220 But ultimately you see that's our database structure has been updated and successfully so. 56 00:03:55,220 --> 00:04:02,210 However when I go back over to my application you're going to notice that the table itself even if I 57 00:04:02,210 --> 00:04:06,810 refresh I think there's a refresh here well there is not. 58 00:04:06,830 --> 00:04:13,370 So even if I was to refresh this would not be it's actually I have to go through some process to get 59 00:04:13,370 --> 00:04:16,210 this data model updated. 60 00:04:16,310 --> 00:04:20,080 So we're going to start off by right clicking in the empty space. 61 00:04:20,090 --> 00:04:25,880 We have the idea makes documents or diagram open once again where I click in the empty space and that 62 00:04:25,880 --> 00:04:29,300 I want to see update model from database. 63 00:04:29,300 --> 00:04:34,780 When I click that we get back a similar menu to what we would have won when we're creating the data. 64 00:04:35,150 --> 00:04:39,830 And then I can go with to refresh click on tables and click finish. 65 00:04:39,830 --> 00:04:43,480 Once I do that then you're going to see some updates. 66 00:04:43,490 --> 00:04:49,400 But then sometimes this doesn't quite work out exactly how we envisioned it because if you notice it 67 00:04:49,400 --> 00:04:57,110 routine that the origin I.D. and name and took on the additional fields and then it took on an additional 68 00:04:57,320 --> 00:05:04,210 property which we don't necessarily want sometimes is actually easier having updated the database to 69 00:05:04,210 --> 00:05:11,650 actually just select all the tables all the entities in the model and remove them so I just highlighted 70 00:05:11,650 --> 00:05:17,060 them and I press delete and then it will ask me Are you sure you want to delete I can see yes. 71 00:05:17,170 --> 00:05:22,210 Because at the end of the day that is of very little consequence to the database itself. 72 00:05:22,210 --> 00:05:23,590 Of course if I build right. 73 00:05:23,620 --> 00:05:29,410 No I'm going to get some errors because anything that I was referencing those models is not going to 74 00:05:29,410 --> 00:05:32,620 throw an error because they no longer exist. 75 00:05:32,620 --> 00:05:33,000 All right. 76 00:05:33,010 --> 00:05:39,730 So here I just did a bill that I'm seeing a bunch of errors because I'm making reference to classes 77 00:05:39,730 --> 00:05:41,540 that no longer exist. 78 00:05:41,620 --> 00:05:48,250 Someone to right click again and then I can see updated models from database once more and this time 79 00:05:48,280 --> 00:05:52,250 I would use the IDE so because I just deleted all the tables. 80 00:05:52,260 --> 00:05:58,470 No the engine is realizing that OK there are tables in the database that's I don't type in the model 81 00:05:58,480 --> 00:06:00,720 and that would be my two tables anyway. 82 00:06:00,910 --> 00:06:05,190 So I can just click tables just like we did when we were creating the diagram. 83 00:06:05,200 --> 00:06:10,660 Click finish and it will import the new tables with their new columns and there. 84 00:06:10,750 --> 00:06:12,200 That looks a bit better. 85 00:06:12,220 --> 00:06:19,600 So we're getting back or two tables and we're getting back types of car with all of the fields from 86 00:06:19,660 --> 00:06:24,510 the original set or rather from the modifications that we made. 87 00:06:24,550 --> 00:06:29,920 No having done that we actually have to go back and refract to some of our code because we would be 88 00:06:29,920 --> 00:06:33,560 making reference to columns that don't exist. 89 00:06:33,580 --> 00:06:41,020 So if I go over to my mons vehicle listing the C S form and I look I'm seeing that here when we did 90 00:06:41,020 --> 00:06:45,770 our Special Select we are referencing Q that I'd use. 91 00:06:45,790 --> 00:06:50,200 Come on I and cued up name these two no longer exist. 92 00:06:50,200 --> 00:06:51,440 Actually no. 93 00:06:51,550 --> 00:06:57,010 We are actually selecting Q Dot idea with a capital I. 94 00:06:57,520 --> 00:06:58,600 So there we go. 95 00:06:58,660 --> 00:07:05,050 Cute idea if I kept it to I so the case those months or however you set up the columns in Eskew well 96 00:07:05,260 --> 00:07:08,040 or in the database itself. 97 00:07:08,100 --> 00:07:08,440 Right. 98 00:07:08,440 --> 00:07:11,800 So remember that when we were designing it I went and changed the. 99 00:07:11,800 --> 00:07:19,530 Come on I d to capital I idea had changed a name from when I changed the column name to make. 100 00:07:19,690 --> 00:07:21,650 So those little things do matter. 101 00:07:21,670 --> 00:07:30,320 So because I wrote code to facilitate the common idea and then Fossett did a column called name have 102 00:07:30,330 --> 00:07:31,950 to go back and meet those changes. 103 00:07:31,960 --> 00:07:38,680 So I don't want name but this time I want make all right in this situation though there are more columns 104 00:07:38,680 --> 00:07:45,930 that I want to display because this code was populating my grid view so I don't want to just see I.T. 105 00:07:46,210 --> 00:07:50,550 and being as a matter of fact I really probably don't want to see idea at all. 106 00:07:50,560 --> 00:07:58,540 So this time I can actually modify this I'm just going to comment oh it's this existing code and I'm 107 00:07:58,540 --> 00:08:07,000 going to rewrite someone to save our cars is equal to my DV object dot the TV that I'm interested in 108 00:08:07,000 --> 00:08:17,380 types of cars and then I just break line right here and say dot select and then I'm selecting Well I've 109 00:08:17,470 --> 00:08:24,760 need my lambda expression first I'm using q again and then my arrow and then the columns that I'm interested 110 00:08:24,760 --> 00:08:31,480 in this time are Q Dot MC comma Q Dot model 111 00:08:35,060 --> 00:08:43,000 well sorry I actually need to specify the new object my but so I started listing all the columns that 112 00:08:43,000 --> 00:08:49,540 was the wrong path what I need to say is I want a new object open curly brace and then I start listing 113 00:08:49,540 --> 00:09:02,360 out each one with its name so I would say make is equal to and then I say Q Dot mic karma model is equal 114 00:09:02,360 --> 00:09:05,810 to Hugh dot model. 115 00:09:06,140 --> 00:09:10,680 The vision is equal to two dot Vin. 116 00:09:10,820 --> 00:09:11,270 Right. 117 00:09:11,300 --> 00:09:19,250 So remember that when we're making our new model I can determine what I want to be the name of the property 118 00:09:19,460 --> 00:09:21,230 but then I'm mapping it. 119 00:09:21,230 --> 00:09:28,190 This is my name that I'm giving it and I'm just making it be equal to or mapping it to a value coming 120 00:09:28,190 --> 00:09:31,570 in from what is being selected from our timber. 121 00:09:31,820 --> 00:09:33,340 So I'm just filling all this model. 122 00:09:33,350 --> 00:09:37,680 So once again make model Vin karma. 123 00:09:38,030 --> 00:09:38,510 What does. 124 00:09:38,510 --> 00:09:48,670 Do we have we have the year is equal to two ducks and that would be cute. 125 00:09:48,690 --> 00:09:49,860 A year. 126 00:09:49,950 --> 00:10:04,560 And then finally we have the license plate number is equal to Q Dot's license plate number. 127 00:10:05,060 --> 00:10:06,030 All right. 128 00:10:06,030 --> 00:10:12,020 And then went just in the brake line at license plate number because it's going off screen. 129 00:10:12,030 --> 00:10:13,410 That's the only reason I'm doing that. 130 00:10:13,410 --> 00:10:15,720 So if you don't want to do that that's fine. 131 00:10:15,720 --> 00:10:19,110 And then I'm going to end with my semicolon. 132 00:10:19,110 --> 00:10:24,990 So there you see this goes to the actually formatted it for it because they realize that OK you are 133 00:10:24,990 --> 00:10:29,990 going off screen you have a lot some thus putting it back the way visual studio wants it. 134 00:10:30,300 --> 00:10:38,130 And then at the end of that select I'm going to see not least because I like working with the least 135 00:10:38,460 --> 00:10:39,330 data type. 136 00:10:39,340 --> 00:10:48,970 Part so let's review this one more time we're selecting from our table types of cars and I'm selecting 137 00:10:49,000 --> 00:10:50,070 a new object. 138 00:10:50,080 --> 00:10:57,110 So we have our lambda expression and I'm selecting into a new object that has fields make and model 139 00:10:57,130 --> 00:11:04,390 Vin here and license plate number and for each of these fields I'm just making sure that I am Mark the 140 00:11:04,390 --> 00:11:08,770 appropriate value coming in from our database. 141 00:11:08,770 --> 00:11:09,400 Right. 142 00:11:09,400 --> 00:11:14,890 And then after selecting all of them I'm just converting that into at least. 143 00:11:14,890 --> 00:11:20,320 So when I actually run this and I'm just going to comment on these two lines to remember that we did 144 00:11:20,320 --> 00:11:25,990 these two lines because we wanted the column names to kind of like human readable but look at what's 145 00:11:25,990 --> 00:11:28,960 went up and so I'm going to comment in these two oat. 146 00:11:29,000 --> 00:11:36,280 All right so I'm not renaming any columns so often selecting cars into this variable and then setting 147 00:11:36,280 --> 00:11:43,120 that variable as a data source for my grid view and then I click start go to my manage vehicle listing 148 00:11:44,020 --> 00:11:48,820 and then you're going to notice that the grid is actually generating with all of the columns coming 149 00:11:48,820 --> 00:11:50,270 from my model. 150 00:11:50,290 --> 00:11:53,760 So MC has its own column model has its own column. 151 00:11:53,810 --> 00:11:55,300 Vin has its own column. 152 00:11:55,360 --> 00:11:59,380 Of course there were all empty in the database so they're all empty in the grid view. 153 00:11:59,470 --> 00:12:06,080 But the point is I use the custom model to generate the columns for the grid view. 154 00:12:06,100 --> 00:12:11,680 So at this point we can actually go back and augment our design of our vehicle listing so that we see 155 00:12:11,680 --> 00:12:15,010 that we have a grid view that probably needs a bit more space. 156 00:12:15,040 --> 00:12:20,320 You probably don't want your users to be scrolling from left direct to see all of the details on the 157 00:12:20,320 --> 00:12:20,900 car. 158 00:12:21,070 --> 00:12:26,400 So we can rearrange the design so that the grid view has a bit more space. 159 00:12:26,410 --> 00:12:28,170 So then me just exit this. 160 00:12:28,330 --> 00:12:38,020 Go back to our design view and then what I'm going to do is expand this table will expand this this 161 00:12:38,020 --> 00:12:40,450 window a bit more and move the buttons. 162 00:12:40,510 --> 00:12:48,880 So underneath the grid view and then I'm going to expand the grid view and make it a bit wider. 163 00:12:48,880 --> 00:12:49,220 All right. 164 00:12:49,750 --> 00:12:54,460 So now that it's wider let's see how that looks. 165 00:12:54,520 --> 00:12:54,760 All right. 166 00:12:54,760 --> 00:12:59,860 So that's what my own looks like it now fills out that entire grid and of course the more rows that 167 00:12:59,920 --> 00:13:08,990 appear the more cars you have is the more that this will expand horizontally well vertically sorry. 168 00:13:09,070 --> 00:13:13,320 No just to revisit or add her entire record window. 169 00:13:13,380 --> 00:13:19,240 Another modification that you may want to make is to the drop down list for type of car so you can see 170 00:13:19,240 --> 00:13:30,010 that it does kind of reverted to that was weird looking series of off items that we saw when we're just 171 00:13:30,010 --> 00:13:36,730 trying to populate it so we can just go back and revisit that code where we were actually binding. 172 00:13:36,730 --> 00:13:43,870 So just look at her entire record fleet right click then go to view code and then I find the form Lord 173 00:13:43,870 --> 00:13:46,650 phone shown to where we were populating it. 174 00:13:46,750 --> 00:13:54,550 So we're actually populating all of the types of cars or binding the display member to be name and then 175 00:13:54,550 --> 00:13:56,770 binding the value member to be ideas. 176 00:13:56,800 --> 00:14:04,390 So one I want to change is the capital I.D. and then the volume member can be name but then what I do 177 00:14:04,390 --> 00:14:10,070 is modify the query that was being used to fill the car. 178 00:14:10,150 --> 00:14:12,310 The combo box initially. 179 00:14:12,310 --> 00:14:18,490 So what I'm going to do is comment oh this score that is being used and I'll just retype it so I keep 180 00:14:18,490 --> 00:14:23,290 on commenting them all because I want us to see where we're coming from and where we're going or it's 181 00:14:23,280 --> 00:14:33,070 somewhat to say via cars is equal to and my entity object inside of utterance a card which is formally 182 00:14:33,070 --> 00:14:35,750 form one is car into entity. 183 00:14:35,770 --> 00:14:42,040 So that's another reason I said earlier keep it consistent because you don't want to have to guess which 184 00:14:42,250 --> 00:14:47,560 name you used in which form if you use one name across all forms then you just know that this is the 185 00:14:47,830 --> 00:14:49,910 object meaning that you're using right. 186 00:14:49,930 --> 00:14:53,810 So in this one we use car and entities as our object name. 187 00:14:53,950 --> 00:15:01,720 So our object what types of cars and then I'm one to do a similar thing where I want to see a dot select 188 00:15:02,110 --> 00:15:08,500 and then I'm going to do my lambda expression and then I'm going to create a new object a new abstract 189 00:15:08,560 --> 00:15:16,300 object and this new object I really need I need to be equal to Q Dot I.D.. 190 00:15:16,510 --> 00:15:16,870 Right. 191 00:15:16,870 --> 00:15:25,360 So I do need I.D. still form my value member and I do need names somewhere to create another field called 192 00:15:25,360 --> 00:15:29,590 name that is going to bar to name which I'm already expecting. 193 00:15:29,590 --> 00:15:37,510 But then for a name I'm going to expand its I don't want to say you dot me and then I'm going to concatenate 194 00:15:37,510 --> 00:15:45,060 an empty string onto Q onto cued up make and then add on cue dot model. 195 00:15:45,060 --> 00:15:53,100 So in other words when we retrieve the records from the database after this query we're getting back 196 00:15:53,160 --> 00:15:59,550 the idea and then we're getting back some fields called a name which is going to have a value of the 197 00:15:59,550 --> 00:16:03,020 meek and empty string and the model. 198 00:16:03,030 --> 00:16:08,940 So if it's a Honda Civic then we expect to see a Honda Civic up here in our dropdown these strikes. 199 00:16:08,940 --> 00:16:18,600 So after doing that and execute and then I load the ad or into a car window then I went to get an arrow 200 00:16:18,660 --> 00:16:24,180 and this arrow I'm going to spare you the details of the arrow and I'm just going to let you know that 201 00:16:24,180 --> 00:16:27,740 it's because we missed the DOT list. 202 00:16:27,750 --> 00:16:35,070 So I always see that's I like working with lists and this are very flexible and they really cause problems 203 00:16:35,070 --> 00:16:42,000 so I'm actually going to just put in the dot to list that I missed earlier and then I'm going to start 204 00:16:42,000 --> 00:16:42,550 again. 205 00:16:42,660 --> 00:16:44,600 And then you're going to see that it ruins this time. 206 00:16:48,320 --> 00:16:49,070 And there we go. 207 00:16:49,070 --> 00:16:56,290 So it loads properly this time and you can see in our dropdown we do have or what we had as a name. 208 00:16:56,300 --> 00:17:00,590 It's showing again but we don't have anything as model in our database. 209 00:17:00,620 --> 00:17:07,430 So just to prove that it's going to show Honda's space whatever it is I put us model I'm going to go 210 00:17:07,430 --> 00:17:15,020 back and I want to modify the record so I'm just going to go to our table right click see edit top 200 211 00:17:15,290 --> 00:17:19,590 that's going to do it quick and dirty for no someone's is a Honda Civic. 212 00:17:19,610 --> 00:17:24,080 So we have a Honda Civic for rental all right. 213 00:17:24,140 --> 00:17:30,410 And I'm just going to make sure it gets committed and then I'm going to execute again and we see what 214 00:17:30,410 --> 00:17:31,660 happens. 215 00:17:31,670 --> 00:17:38,390 All right so knowing our dropped on this we see the name appearing but it's really the make space the 216 00:17:38,390 --> 00:17:42,080 model so the others don't have all models so that you're seeing is the make. 217 00:17:42,380 --> 00:17:42,620 All right. 218 00:17:42,620 --> 00:17:47,240 So that's so you can go about customizing the user experience because if I use a looks at this they 219 00:17:47,240 --> 00:17:53,510 really don't know what went into getting a Honda Civic to appear in one land they're just seeing Honda 220 00:17:53,510 --> 00:17:53,930 Civic. 221 00:17:53,930 --> 00:17:55,390 As far as they're concerned. 222 00:17:55,520 --> 00:17:55,820 Right. 223 00:17:56,060 --> 00:18:02,690 And then in our vehicle listing when I revisit that page I'm seeing that we have Honda Civic a blank 224 00:18:02,780 --> 00:18:07,220 blank blank because we didn't put in any data for the rest of those. 225 00:18:07,220 --> 00:18:07,490 All right. 226 00:18:07,880 --> 00:18:15,530 So in our next video we're going to look at implementing some additions and editing or adding and editing 227 00:18:15,530 --> 00:18:21,800 functionality for our records in our car into records vehicle listing feature.