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.
