Welcome back.
In this video, we are going to create our first table.
And before we do that, I would like to show you what the end result of this chapter is going to be.
As you can see, I have a window here called Main Window, and it has multiple different controls in
here.
So we have labels, we have a list box well, multiple buttons and multiple list boxes in general.
And what you can see here are multiple zoos and well, the names are a bit mixed up already because
I have changed them.
But what you can do here is you can check a zoo or select a zoo, and you will see the associated animals
that are within that zoo.
So let's say we have a zoo in Tokyo and we have a shark.
We have, well, multiple sharks.
We have crocodiles, a gecko, hawks, all those kind of animals.
And in New York, we have different types of animals.
So in New York we have a small zoo, we only have a crocodile, but then we decide to add a monkey to
the New York Zoo.
So we are going to add that to the New York Zoo.
And as you can see, these are the associated animals.
Then once I click on one of the animals here on the right or on one of the zoos, my text box here at
the bottom changes its value.
So it just takes the value of my city.
So and here I have, for example, Cairo two and I want to update that.
So I'm going to delete the two and I'm going to update zoo.
And now as you can see, Cairo has been updated.
I can do the same with my Milan Zoo.
So I'm going to update Milan to now this blah blah zoo.
It's a fake zoo, it doesn't exist, so I'm going to delete that entirely.
The same goes for our 44 animal.
I'm going to delete that animal entirely as well.
So generally you can see now that we have multiple different things to consider in here, we are using
multiple different tables.
So for one, we have the zoo table, then we have the animals table, and then we have a table which
takes those two and connects them to each other.
And that's going to be the bigger challenge that we're going to cover in one of the next videos.
And of course, we're going to build this UI.
It's not the most beautiful one, but it's very functional and it will help you to understand, well,
there's database constructs in general and how to use databases in C-sharp and some of my SQL as well.
So I'd say let's get started with this.
And therefore we go back to Visual Studio.
And as you can see, I have two connections here.
You only have one or you should only have one connection and I have another one.
That's the one that I was just connected to in order to show you the demo.
And we're going to use the new one that I've created here.
And as you can see, there is no table in there yet.
So we need to create a new table.
Therefore I right click onto table and I create a new table here.
So add new table and that will open up a little window.
And I'm going to, by the way, make the server explorer a little less intrusive here and how to hide
it like that now it looks a lot better.
So we have our little table which has an ID of type integer allows, nulls is or allow, nulls is deactivated
and there is no default value.
So what you can see here is a little key.
What that means, that is that this is going to be the key column and what you pretty much create here
are the columns.
So we have a column called ID and then we can create an additional column just here.
So I'm going to create one and I'm going to call that one location, location just like that.
And I'm not going to allow nulls.
Now, I need to change the data type.
And the data types are not exactly the ones that we have seen in C sharp, but some of them are similar
to the ones in C sharp.
So you have date datetime characters, bits, integers, and the one that I want to use here is going
to be bar chart 50 and bar chart 50.
What that allows me is to create a string of 50 values and store that inside of my location.
Now another important thing that I want to change is also I want to make this ID our identity specification.
So I'm going to click on identity specification here on a property.
So select the ID and then go to properties on the left, right hand side here and then choose identity
specification and change the is identity value to true.
And now it will increment by one each time.
And what that means is that every single entry in our database will have a unique ID, as creating a
new entry will always increment the ID by one.
That's similar to the example in inheritance that we had where we got the current notification ID and
stuff like that.
So that's pretty much our table.
Now I want to change the table name.
I don't want it to be called table.
I want this table name to be different.
So I'm going to call that Table Zu.
So this is going to be my zu table and I'm going to use a singular value here or a singular word, not
Zeus plural.
And there are multiple great reasons for that.
And if you want to read through it, there is a great article in StackOverflow or actually a great answer
here with six great reasons why you should use a singular term.
So somebody had the same question here, singular versus plural.
And you can check out the answer here.
All right.
So now we have our zoom table, but it's not there yet.
So, by the way, you are in the T SQL tab here.
And in here we are simply creating a table.
So once I press this update key here at the top or this button or shift all due, it will update and
run this SQL code which will create a table in DBO called ZU.
It will have an ID which is of type integer which does not allow null, so it will not have empty values
and it's the primary key and at the same time it's the identity.
So ID is the primary key, which means that we can well locate anything by this ID and then we have
something called location, which is another column which is of type and var char 50, which means there
can be 50 different string characters and then we have not null here as well.
So we create another column which does not allow null.
All right.
So now press this update button and it will update our database.
So I'm going to update the database here by pressing this button.
And now, as you can see, nothing has changed here under tables.
But if you refresh, then suddenly you see the zoo table in here and the zoo table has an ID and a location.
Now I would like to access the zoo and change its values so you can go right or press right.
Click here on the zoo and then show table data.
Well, that will open up, as does Debo the zoo data, which shows that there is an ID and there is
a location.
So this one is null.
Well, because it's empty, we haven't created it yet.
So this rule doesn't exist yet.
That's why it says no now.
But if I enter a location and here, so I'm going to enter a location like New York.
As you can see, I have created this new location which has the ID of one, and we can enter the next
location just below the way you can make it a little wider here and you can add some other cities.
So I'm going to add Tokyo, I'm going to add Berlin, then I'm going to add Cairo and Milan.
And of course, I could go ahead and add many more.
All right.
So now we have multiple different locations and they all have a unique ID.
So starting from one going to five in our case now.
Now let's go ahead and create a new project because we have all of that set up in our server explorer
without even having a project.
So solution here, solution one zero project.
So we are not in a project, so let's create a new one and I'm going to call that one WP F Zoo Manager
and I'm going to press OC.
All right, that we are in WPF and we see our XAML file here.
Next, what we want to do is add a data source.
So as you can see, I press on data source now and if that doesn't show up for you.
So if nothing like this is stated here, so your project currently has no data source, you simply click
on your project on the right hand side.
So in this case, WPF Zoo Manager and then you press data sources, then this will come up and you will
be able to add a new data source.
So I'm going to choose the data source type database here.
Then I press next, then I use the data set as a database model.
And then here you need to choose your data connection.
And I'm going to choose the one that I have created the second time.
So panel is SQL and it's called DB and I'm going to show the string.
Well, that's fine.
In order to make it simpler, I just press yes.
Here include sensitive data and connection string.
Then I press next.
By the way, if you don't want to include a sensitive data, then you can set that up in your application
code later on.
But I'm going to keep it simple.
So I press next and then I get this connection string and it's called Punishers.
DB Connection String in my case, and I'm going to copy that and press next.
Then I can choose my database objects and as you can see, my tables has a zoo database object and I'm
going to just choose that.
And as you can see, there is a data set name which is called authorized DB data set, which we also
can change.
But I'm going to leave it as is now.
I press finish and that will create our data source and as you can see now it's there we have the data
source now here.
And what you can do here now is, for example, right click on zoo and edit data set with designer and
there you'll find your database and a design.
As you can see, there is a table called Zoo.
It has a key value which is called ID or a key column.
Then you have the location and there is a zoo table adapter which has a fill and gate data, SQL functionality.
And what we need to do now is go to our main window, zemo and in here we are going to set up our SQL
string because I copied my string name like that and I'll need that in a second.
So I create a new string.
I'm going to call it connection string, which is going to be accessing the configuration manager,
that connection strings.
And in here in the square brackets, I'll need to enter the string in quotation marks.
Now it's complaining because it doesn't know this configuration manager and I could add it here at the
top.
I could said Yeah, using configuration manager and those kind of things, but we need to set it up
before that.
So I press right click onto my references and I add a reference here and the reference that I want to
use is system dot configuration.
So that one here.
So I hit the check mark here next to system configuration and press.
Okay?
And now this configuration manager can be used and we can go ahead and add using system dot configuration.
Now suddenly the configuration manager is not complaining.
Well, at least the color comes up and now we just need to add the correct connection strings.
And that's going to be the name of our project, which is double WPF in my case, empty space or underscore
zu manager dot properties, dot settings that and then the link that I've copied.
So this is DB connection string.
And next, what we need to do here is to say that this is our connection string.
So we're going to use the connection string property here like that.
So it's a pretty long line of code.
But what's important here is you use the name of your project, dot properties, dot settings, dot
the string that you have set up when creating your data source and then dot connection string afterwards.
All right.
So now this is set up and we can go ahead in the next video where we're going to work a little more
with the SQL part of our application.