‫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.