Welcome back.
In this video, we are going to use sequel with Link.
So let's go ahead and create a new project.
And in this case, I'm going to use a WPA project because I would like to display the data in a nice
way.
So I'm going to call that link to SQL or SQL and Press OC.
And what I want to have in here is just going to be a data grid.
So I'm going to create a new data grid here with a name called Main Data Grid.
So it just should display all the data that we will have in our database and use with SQL.
So in here I have our horizon alignment, which is centered and also well actually let's put it left.
Left is good then a height of let's say 400 and margins of ten towards each direction.
Then we also have a vertical alignment towards the top.
So let's do that vertical alignment towards the top and let's close that data grid.
So now we have this data grid here and as you can see, it doesn't have a width yet.
So I'm going to use a width of 450 or something like that, or maybe 520.
So almost the whole width of our main window.
So as you see, 500 is the correct one and the height, well if we want to have 400, then we should
also increase the size of our window.
All right.
So now you have this window and we can go ahead with the server connection.
So let's go ahead to view and here server explorer or control alt SW and it will open up this data connections
screen or the server explorer.
And then here I'm going to create a new table.
So in my case I have two connections here.
So I'm just going to use one of those and then I go ahead and create a new table like that.
And the table should be called university and its ID should be the identity specified.
So this identity specification is set to true.
Next, I want to have another row in here which will be the name of the university and which would be
a and var char 50 here and doesn't allow nulls.
All right.
So that's our university table.
Now let's update that in order to create the table.
So I'm going to update my database.
And if I refresh here, it will show me this university table here with the ID name.
Then I create another table, new table, and this table will also have an identity for the ID.
So let's do the same thing here on the right identity special specification set to true and it should
have a row called name which is an and var char and another one which is gender which will also be an
and var char.
And each of them doesn't allow now.
So we're not allowing now.
And then finally we have the university ID that this table is belonging to and that will be in var cha
50 as well.
So now the table name, well you guessed it, maybe it's student so I'm going to call it student and
now before I run that I need to do some changes.
So I want to add constraint to my foreign keys.
So I'm going to create a new foreign key f k and that will be called University F K.
So this constraint is called University F K and now the foreign key column should be University ID.
So here in squared brackets, university ID and the reference is to the table of dbo dot university.
And then here it should be d id.
So the column is going to be the ID of our university.
And now here I add one more statement on delete cascade.
We had that before.
What we'll do is we'll simply clean up all the different entries if one of the connections is deleted.
Pretty much.
So this university ID now is complaining in here to constraint because university is ideas of another
char, but the ID of our university ID is actually well.
The ID of our university table is actually an integer.
So if we check that we see this is an integer.
And now of course this university ID, if it wants to be the foreign key, well to have the foreign
key of the ID of our university table as its foreign key, then we need to use integer here as well
and I'm going to deactivate allows nulls as well.
So that means it's pretty much every single student belongs to a specific university, so you can't
have multiple universities that he belongs to.
Now let's create another table, a new table, and that one will be called lecture.
So every student should have a lecture or multiple lectures.
And in here the ID should also be the primary identifier.
So identity t specification.
And I'm just going to use var chart here for the name var cha like that and it doesn't allow nulls.
And by the way, the student should of course, be updated.
So it's not available in our tables yet.
But if we update that, then we will see the DB lecture student and university foreign key.
Those tables are created.
So our lecture now should be created.
Our student table should also be created.
And we are lecture, student and university.
Now let's create one last table at new table, and that one will be the student lecture table.
So let's rename it student lecture and it should have an ID and that would also be the primary identifier.
So I'm going to use identity specification.
True.
As you can see, primary key identity.
Now we have a student ID here which is of type int and we have a lecture ID which is also of type int.
So as you can imagine, we have this associative table student lecture which will have all the student
IDs and the lecture IDs in order to know which student has which lecture.
So it's pretty much connecting the lecture table with the student table.
So that's the idea behind this table here.
And of course, we need to go ahead and have some foreign keys.
So let's create new foreign keys, add new foreign key, and this one will be student f k standing for
student foreign key, which has the column of student ID, student ID in the table DB oh dot student.
And the column that we're checking is its ID.
And here I also add this on delete cascade and now I need another constraint.
So I'm going to copy and paste that actually.
Or I could copy and paste it.
So like that copy paste.
Or I can of course create a new foreign key here as well.
So as you prefer, you can do it either way.
Lecture f k like that.
And the foreign key here will be the lecture id sure i dx and here it should also have the structure.
So to table dbo and the table should be a lecture.
And now the ID is the table column that we're looking at.
And of course, this line on the lead cascade.
So on delete cascade.
So why do we have this student lecture?
Let's update it real quick so that we have it.
We have it in order to have, on one hand, all the students that can have multiple lectures and all
the lectures that can have multiple students.
So that's why we need to have this associative table, which only has the IDs of each student and each
lecture, because otherwise it would be way too complicated.
And that's a very clean way to handle this.
In order to open them all shift D and data sources will be opened up.
Now you need to select your project on the right hand side and all shift D again.
Now we can add a new data source, so let's do that and choose database here.
Choose data set here.
Then the connection should be the one that you have.
You can either use the one that you have or you could use a new connection.
But it doesn't make sense as we have set up this specific connection.
So I'm going to go next and this will be the connection string and I'm going to copy that because I
will need it.
Yes.
Save the connection as and I'm going to click next.
And now I can choose the tables and I'm going to use lecture students, student lecture and university.
So those four tables will be required.
And I'm just going to press finish.
Now, in order to use SQL, you need to make sure that SQL tools or link to SQL tools are connected,
are installed.
So if you don't have them, check out the Visual Studio installer.
So open up the Visual Studio Installer.
It's a program that you should have installed and we have used that already.
So just open that up.
Then we are.
Then here on the Visual Studio Community Press on more and modify.
So the one that you have installed, modify your installation and then go to individual components here
and make sure that you have the SQL pieces here.
So SQL at runtime and so forth.
And here link to SQL Tools.
So that's the one that's important.
If you don't have that, if you don't install that, then the further approach will not work.
So go ahead and check the check mark and then modify.
So go ahead and install.
But before you can go ahead and install it, we should save our project.
So I'm going to save my project here because what it will do is it will restart Visual Studio, it will
close down Visual Studio.
So I'll go to modify again and I'm going to go in there.
Individual components choose.
Link to SQL here modify and then let it do its magic.
So press continue here and now it should download and install.
And in my case, it even asked me to save my changes.
And then it will install.
Once the installation is done, Visual Studio will start up again.
So you can shut down your Visual Studio installer again.
So close it and now we can go ahead.
So first of all, I hope you still have it copied.
So we're going to go into the code behind and establish the connection here.
And if you don't have a copy, that's no problem.
So in here we want to create a connection and it will be a string.
Connection string, which is configuration manager.
And if you don't show or if it doesn't show up, you need to add the reference to that configuration
manager.
We have seen that before.
Right?
So you add this reference here, right click and you add the configuration or system configuration here.
Okay.
Now you can add using sys dot configuration and now you can work with the configuration manager here.
So what we need to do is to connect to our data set connection string here and in quotation marks you
need to add the link and in my case it's the name or in general, it's the name of the file that you
have here of the project.
And it's linked to SQL dot properties, dot settings, dot, and then the connection string that we
had.
And in my case it was janitorial dot db connection string.
And then here you need to state that it's the connection string here.
And that's our statement.
And by the way, because you have installed link to SQL Tools, you now can go go ahead and add a link
to a SQL class.
So I'm going to add a new class here, add new item link to SQL.
Link to SQL classes and I'm going to call it link to SQL data classes dot double.
TML All right, so now link to SQL data classes DB.
ML And once that is done and we'll leave that for now as it is, and let's go back to our CSS file.
So our code behind and in here now we can go ahead and create an object of this class that we've just
created.
And now I'm going to call it Link or actually it's going to be my link, SQL data class data context
and I'm going to call it data context.
So that will be pretty much the context of our data in general.
And I need to fill that up with my new SQL.
I need to initialize that with new link to SQL data context and it needs a connection here.
And we're going to use the connection string that we have created.
So now we have the data context set up.
We have our tables set up, our data set, set up and so forth, and then we can go ahead with the SQL
and link stuff in the next video where we're going to use all the link knowledge that we have acquired
so far and apply that on a SQL context.