In this video we'll be exploring making some changes to our database and how making those changes in
the database will reflect in our IBM makes diagram.
So in today's activity we're going to be adding some more fields to our vehicles table.
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
to incrementing is just keeping track of all the records.
And we have a name.
What's in reality for storing vehicle information.
We need more than just a name.
All the things that we would probably be storing would be like the year the make the model.
So that would actually split naming two into make and model and make being the brand and then the model
being that you know the type of vehicle off that brand.
So for instance with a Honda Civic rights Honda being the MC and civic being the model maybe love in
the license plates number etc..
So maybe you have more or fewer things that you would want to store in your database.
But to be we'll be modifying our database to accommodate those additional fields.
So going over to our obscure management studio I just went to the database phone the appropriate table
directly to set design and then we are here in the design view where I can add or remove columns as
I see fit.
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
the year what I want to do is replace year some name with make right and that I'm going to add model
and it's also going to be enviro via our 50 some just want to copy this because it will repeat itself.
We'll have the VIN that's the vehicle identification number.
That's one of those government things.
We have the license plate number so license plate number which is also arch our 50 so even though it
says no we're not doing any math with it in my field.
My general philosophy when it comes to database and numbers would be that if I'm not doing math with
it's I don't need to store it as a numeric data type.
Right.
And then that would actually facilitate if maybe I start fostering just numbers and then it becomes
alphanumeric after a while then it would have already not been a numeric data type.
And then the final one would be the year which I can just set that one to be I'll just set that one
to be integer.
All right.
So integer because you're 20 20 or 20 18 et cetera.
That would be kind of fun no.
So I'll use that one to be an integer.
So after making those adjustments to the database and you notice some inconsistencies so why they're
not a big deal.
I like to keep my you know my naming conventions kind of consistency if I'm using kept some using caps
if I'm not using caps then I'm not using caps.
So just to normalize everything just going to make everything start with a capital letter and then I'm
going to save.
And then this is going to trigger an update to the table so in the database my data table now has new
fields.
And if I go and select the top 1000 then you're going to see all of the rows appearing with all of the
vehicles that we had to date all of them appearing with the make the model and the additional fields
that I added.
So here in the make what was name is no the brand.
Right.
So there are a few that maybe we'll need some refinement because there is no brand name bogey really
and truly buggy is just something that we'll call the Volkswagen Beetle.
Back here in Jamaica but then that's his bogey.
So we can update that later.
That's part of the task at hand.
But ultimately you see that's our database structure has been updated and successfully so.
However when I go back over to my application you're going to notice that the table itself even if I
refresh I think there's a refresh here well there is not.
So even if I was to refresh this would not be it's actually I have to go through some process to get
this data model updated.
So we're going to start off by right clicking in the empty space.
We have the idea makes documents or diagram open once again where I click in the empty space and that
I want to see update model from database.
When I click that we get back a similar menu to what we would have won when we're creating the data.
And then I can go with to refresh click on tables and click finish.
Once I do that then you're going to see some updates.
But then sometimes this doesn't quite work out exactly how we envisioned it because if you notice it
routine that the origin I.D. and name and took on the additional fields and then it took on an additional
property which we don't necessarily want sometimes is actually easier having updated the database to
actually just select all the tables all the entities in the model and remove them so I just highlighted
them and I press delete and then it will ask me Are you sure you want to delete I can see yes.
Because at the end of the day that is of very little consequence to the database itself.
Of course if I build right.
No I'm going to get some errors because anything that I was referencing those models is not going to
throw an error because they no longer exist.
All right.
So here I just did a bill that I'm seeing a bunch of errors because I'm making reference to classes
that no longer exist.
Someone to right click again and then I can see updated models from database once more and this time
I would use the IDE so because I just deleted all the tables.
No the engine is realizing that OK there are tables in the database that's I don't type in the model
and that would be my two tables anyway.
So I can just click tables just like we did when we were creating the diagram.
Click finish and it will import the new tables with their new columns and there.
That looks a bit better.
So we're getting back or two tables and we're getting back types of car with all of the fields from
the original set or rather from the modifications that we made.
No having done that we actually have to go back and refract to some of our code because we would be
making reference to columns that don't exist.
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
our Special Select we are referencing Q that I'd use.
Come on I and cued up name these two no longer exist.
Actually no.
We are actually selecting Q Dot idea with a capital I.
So there we go.
Cute idea if I kept it to I so the case those months or however you set up the columns in Eskew well
or in the database itself.
Right.
So remember that when we were designing it I went and changed the.
Come on I d to capital I idea had changed a name from when I changed the column name to make.
So those little things do matter.
So because I wrote code to facilitate the common idea and then Fossett did a column called name have
to go back and meet those changes.
So I don't want name but this time I want make all right in this situation though there are more columns
that I want to display because this code was populating my grid view so I don't want to just see I.T.
and being as a matter of fact I really probably don't want to see idea at all.
So this time I can actually modify this I'm just going to comment oh it's this existing code and I'm
going to rewrite someone to save our cars is equal to my DV object dot the TV that I'm interested in
types of cars and then I just break line right here and say dot select and then I'm selecting Well I've
need my lambda expression first I'm using q again and then my arrow and then the columns that I'm interested
in this time are Q Dot MC comma Q Dot model
well sorry I actually need to specify the new object my but so I started listing all the columns that
was the wrong path what I need to say is I want a new object open curly brace and then I start listing
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
to Hugh dot model.
The vision is equal to two dot Vin.
Right.
So remember that when we're making our new model I can determine what I want to be the name of the property
but then I'm mapping it.
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
in from what is being selected from our timber.
So I'm just filling all this model.
So once again make model Vin karma.
What does.
Do we have we have the year is equal to two ducks and that would be cute.
A year.
And then finally we have the license plate number is equal to Q Dot's license plate number.
All right.
And then went just in the brake line at license plate number because it's going off screen.
That's the only reason I'm doing that.
So if you don't want to do that that's fine.
And then I'm going to end with my semicolon.
So there you see this goes to the actually formatted it for it because they realize that OK you are
going off screen you have a lot some thus putting it back the way visual studio wants it.
And then at the end of that select I'm going to see not least because I like working with the least
data type.
Part so let's review this one more time we're selecting from our table types of cars and I'm selecting
a new object.
So we have our lambda expression and I'm selecting into a new object that has fields make and model
Vin here and license plate number and for each of these fields I'm just making sure that I am Mark the
appropriate value coming in from our database.
Right.
And then after selecting all of them I'm just converting that into at least.
So when I actually run this and I'm just going to comment on these two lines to remember that we did
these two lines because we wanted the column names to kind of like human readable but look at what's
went up and so I'm going to comment in these two oat.
All right so I'm not renaming any columns so often selecting cars into this variable and then setting
that variable as a data source for my grid view and then I click start go to my manage vehicle listing
and then you're going to notice that the grid is actually generating with all of the columns coming
from my model.
So MC has its own column model has its own column.
Vin has its own column.
Of course there were all empty in the database so they're all empty in the grid view.
But the point is I use the custom model to generate the columns for the grid view.
So at this point we can actually go back and augment our design of our vehicle listing so that we see
that we have a grid view that probably needs a bit more space.
You probably don't want your users to be scrolling from left direct to see all of the details on the
car.
So we can rearrange the design so that the grid view has a bit more space.
So then me just exit this.
Go back to our design view and then what I'm going to do is expand this table will expand this this
window a bit more and move the buttons.
So underneath the grid view and then I'm going to expand the grid view and make it a bit wider.
All right.
So now that it's wider let's see how that looks.
All right.
So that's what my own looks like it now fills out that entire grid and of course the more rows that
appear the more cars you have is the more that this will expand horizontally well vertically sorry.
No just to revisit or add her entire record window.
Another modification that you may want to make is to the drop down list for type of car so you can see
that it does kind of reverted to that was weird looking series of off items that we saw when we're just
trying to populate it so we can just go back and revisit that code where we were actually binding.
So just look at her entire record fleet right click then go to view code and then I find the form Lord
phone shown to where we were populating it.
So we're actually populating all of the types of cars or binding the display member to be name and then
binding the value member to be ideas.
So one I want to change is the capital I.D. and then the volume member can be name but then what I do
is modify the query that was being used to fill the car.
The combo box initially.
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
on commenting them all because I want us to see where we're coming from and where we're going or it's
somewhat to say via cars is equal to and my entity object inside of utterance a card which is formally
form one is car into entity.
So that's another reason I said earlier keep it consistent because you don't want to have to guess which
name you used in which form if you use one name across all forms then you just know that this is the
object meaning that you're using right.
So in this one we use car and entities as our object name.
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
and then I'm going to do my lambda expression and then I'm going to create a new object a new abstract
object and this new object I really need I need to be equal to Q Dot I.D..
Right.
So I do need I.D. still form my value member and I do need names somewhere to create another field called
name that is going to bar to name which I'm already expecting.
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
an empty string onto Q onto cued up make and then add on cue dot model.
So in other words when we retrieve the records from the database after this query we're getting back
the idea and then we're getting back some fields called a name which is going to have a value of the
meek and empty string and the model.
So if it's a Honda Civic then we expect to see a Honda Civic up here in our dropdown these strikes.
So after doing that and execute and then I load the ad or into a car window then I went to get an arrow
and this arrow I'm going to spare you the details of the arrow and I'm just going to let you know that
it's because we missed the DOT list.
So I always see that's I like working with lists and this are very flexible and they really cause problems
so I'm actually going to just put in the dot to list that I missed earlier and then I'm going to start
again.
And then you're going to see that it ruins this time.
And there we go.
So it loads properly this time and you can see in our dropdown we do have or what we had as a name.
It's showing again but we don't have anything as model in our database.
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
back and I want to modify the record so I'm just going to go to our table right click see edit top 200
that's going to do it quick and dirty for no someone's is a Honda Civic.
So we have a Honda Civic for rental all right.
And I'm just going to make sure it gets committed and then I'm going to execute again and we see what
happens.
All right so knowing our dropped on this we see the name appearing but it's really the make space the
model so the others don't have all models so that you're seeing is the make.
All right.
So that's so you can go about customizing the user experience because if I use a looks at this they
really don't know what went into getting a Honda Civic to appear in one land they're just seeing Honda
Civic.
As far as they're concerned.
Right.
And then in our vehicle listing when I revisit that page I'm seeing that we have Honda Civic a blank
blank blank because we didn't put in any data for the rest of those.
All right.
So in our next video we're going to look at implementing some additions and editing or adding and editing
functionality for our records in our car into records vehicle listing feature.