1 00:00:00,240 --> 00:00:01,090 ‫Welcome back. 2 00:00:01,110 --> 00:00:05,210 ‫In this video, we are going to use sequel with Link. 3 00:00:05,250 --> 00:00:07,440 ‫So let's go ahead and create a new project. 4 00:00:07,440 --> 00:00:12,540 ‫And in this case, I'm going to use a WPA project because I would like to display the data in a nice 5 00:00:12,540 --> 00:00:13,020 ‫way. 6 00:00:13,020 --> 00:00:20,400 ‫So I'm going to call that link to SQL or SQL and Press OC. 7 00:00:21,750 --> 00:00:27,990 ‫And what I want to have in here is just going to be a data grid. 8 00:00:28,080 --> 00:00:36,390 ‫So I'm going to create a new data grid here with a name called Main Data Grid. 9 00:00:36,420 --> 00:00:42,810 ‫So it just should display all the data that we will have in our database and use with SQL. 10 00:00:42,810 --> 00:00:52,800 ‫So in here I have our horizon alignment, which is centered and also well actually let's put it left. 11 00:00:53,340 --> 00:01:02,880 ‫Left is good then a height of let's say 400 and margins of ten towards each direction. 12 00:01:03,000 --> 00:01:06,420 ‫Then we also have a vertical alignment towards the top. 13 00:01:06,420 --> 00:01:13,560 ‫So let's do that vertical alignment towards the top and let's close that data grid. 14 00:01:13,560 --> 00:01:18,330 ‫So now we have this data grid here and as you can see, it doesn't have a width yet. 15 00:01:18,330 --> 00:01:30,270 ‫So I'm going to use a width of 450 or something like that, or maybe 520. 16 00:01:30,270 --> 00:01:34,710 ‫So almost the whole width of our main window. 17 00:01:34,710 --> 00:01:41,970 ‫So as you see, 500 is the correct one and the height, well if we want to have 400, then we should 18 00:01:41,970 --> 00:01:44,910 ‫also increase the size of our window. 19 00:01:45,060 --> 00:01:45,390 ‫All right. 20 00:01:45,390 --> 00:01:51,540 ‫So now you have this window and we can go ahead with the server connection. 21 00:01:51,540 --> 00:01:59,670 ‫So let's go ahead to view and here server explorer or control alt SW and it will open up this data connections 22 00:01:59,670 --> 00:02:01,440 ‫screen or the server explorer. 23 00:02:01,560 --> 00:02:03,540 ‫And then here I'm going to create a new table. 24 00:02:03,540 --> 00:02:05,520 ‫So in my case I have two connections here. 25 00:02:05,520 --> 00:02:12,390 ‫So I'm just going to use one of those and then I go ahead and create a new table like that. 26 00:02:13,170 --> 00:02:22,130 ‫And the table should be called university and its ID should be the identity specified. 27 00:02:22,560 --> 00:02:27,000 ‫So this identity specification is set to true. 28 00:02:28,530 --> 00:02:35,430 ‫Next, I want to have another row in here which will be the name of the university and which would be 29 00:02:35,430 --> 00:02:39,270 ‫a and var char 50 here and doesn't allow nulls. 30 00:02:39,900 --> 00:02:40,230 ‫All right. 31 00:02:40,230 --> 00:02:43,710 ‫So that's our university table. 32 00:02:43,710 --> 00:02:46,770 ‫Now let's update that in order to create the table. 33 00:02:46,980 --> 00:02:49,290 ‫So I'm going to update my database. 34 00:02:49,290 --> 00:02:56,190 ‫And if I refresh here, it will show me this university table here with the ID name. 35 00:02:56,550 --> 00:03:05,160 ‫Then I create another table, new table, and this table will also have an identity for the ID. 36 00:03:05,310 --> 00:03:13,590 ‫So let's do the same thing here on the right identity special specification set to true and it should 37 00:03:13,590 --> 00:03:24,670 ‫have a row called name which is an and var char and another one which is gender which will also be an 38 00:03:24,670 --> 00:03:25,590 ‫and var char. 39 00:03:27,240 --> 00:03:29,040 ‫And each of them doesn't allow now. 40 00:03:29,040 --> 00:03:30,360 ‫So we're not allowing now. 41 00:03:30,360 --> 00:03:40,350 ‫And then finally we have the university ID that this table is belonging to and that will be in var cha 42 00:03:40,350 --> 00:03:41,160 ‫50 as well. 43 00:03:41,160 --> 00:03:49,740 ‫So now the table name, well you guessed it, maybe it's student so I'm going to call it student and 44 00:03:49,740 --> 00:03:52,680 ‫now before I run that I need to do some changes. 45 00:03:52,680 --> 00:03:58,080 ‫So I want to add constraint to my foreign keys. 46 00:03:58,080 --> 00:04:08,400 ‫So I'm going to create a new foreign key f k and that will be called University F K. 47 00:04:09,510 --> 00:04:17,250 ‫So this constraint is called University F K and now the foreign key column should be University ID. 48 00:04:17,550 --> 00:04:35,100 ‫So here in squared brackets, university ID and the reference is to the table of dbo dot university. 49 00:04:35,100 --> 00:04:37,740 ‫And then here it should be d id. 50 00:04:37,740 --> 00:04:41,280 ‫So the column is going to be the ID of our university. 51 00:04:41,730 --> 00:04:47,490 ‫And now here I add one more statement on delete cascade. 52 00:04:47,490 --> 00:04:48,690 ‫We had that before. 53 00:04:48,690 --> 00:04:55,470 ‫What we'll do is we'll simply clean up all the different entries if one of the connections is deleted. 54 00:04:55,470 --> 00:04:56,220 ‫Pretty much. 55 00:04:56,220 --> 00:05:02,430 ‫So this university ID now is complaining in here to constraint because university is ideas of another 56 00:05:02,430 --> 00:05:07,950 ‫char, but the ID of our university ID is actually well. 57 00:05:07,950 --> 00:05:12,090 ‫The ID of our university table is actually an integer. 58 00:05:12,090 --> 00:05:14,910 ‫So if we check that we see this is an integer. 59 00:05:14,910 --> 00:05:20,850 ‫And now of course this university ID, if it wants to be the foreign key, well to have the foreign 60 00:05:20,850 --> 00:05:27,030 ‫key of the ID of our university table as its foreign key, then we need to use integer here as well 61 00:05:27,030 --> 00:05:29,850 ‫and I'm going to deactivate allows nulls as well. 62 00:05:30,330 --> 00:05:35,970 ‫So that means it's pretty much every single student belongs to a specific university, so you can't 63 00:05:35,970 --> 00:05:38,490 ‫have multiple universities that he belongs to. 64 00:05:39,150 --> 00:05:47,130 ‫Now let's create another table, a new table, and that one will be called lecture. 65 00:05:47,370 --> 00:05:51,690 ‫So every student should have a lecture or multiple lectures. 66 00:05:51,690 --> 00:05:55,950 ‫And in here the ID should also be the primary identifier. 67 00:05:55,980 --> 00:05:59,370 ‫So identity t specification. 68 00:06:01,540 --> 00:06:11,650 ‫And I'm just going to use var chart here for the name var cha like that and it doesn't allow nulls. 69 00:06:12,160 --> 00:06:14,980 ‫And by the way, the student should of course, be updated. 70 00:06:14,980 --> 00:06:17,890 ‫So it's not available in our tables yet. 71 00:06:17,890 --> 00:06:24,760 ‫But if we update that, then we will see the DB lecture student and university foreign key. 72 00:06:24,760 --> 00:06:26,500 ‫Those tables are created. 73 00:06:26,890 --> 00:06:29,850 ‫So our lecture now should be created. 74 00:06:29,860 --> 00:06:33,730 ‫Our student table should also be created. 75 00:06:35,200 --> 00:06:38,590 ‫And we are lecture, student and university. 76 00:06:38,590 --> 00:06:45,730 ‫Now let's create one last table at new table, and that one will be the student lecture table. 77 00:06:45,730 --> 00:06:57,210 ‫So let's rename it student lecture and it should have an ID and that would also be the primary identifier. 78 00:06:57,220 --> 00:07:02,020 ‫So I'm going to use identity specification. 79 00:07:02,020 --> 00:07:02,650 ‫True. 80 00:07:03,880 --> 00:07:06,030 ‫As you can see, primary key identity. 81 00:07:06,040 --> 00:07:19,300 ‫Now we have a student ID here which is of type int and we have a lecture ID which is also of type int. 82 00:07:19,420 --> 00:07:28,390 ‫So as you can imagine, we have this associative table student lecture which will have all the student 83 00:07:28,390 --> 00:07:32,830 ‫IDs and the lecture IDs in order to know which student has which lecture. 84 00:07:33,520 --> 00:07:37,630 ‫So it's pretty much connecting the lecture table with the student table. 85 00:07:37,630 --> 00:07:40,180 ‫So that's the idea behind this table here. 86 00:07:40,450 --> 00:07:43,900 ‫And of course, we need to go ahead and have some foreign keys. 87 00:07:43,900 --> 00:07:51,850 ‫So let's create new foreign keys, add new foreign key, and this one will be student f k standing for 88 00:07:51,850 --> 00:08:09,280 ‫student foreign key, which has the column of student ID, student ID in the table DB oh dot student. 89 00:08:10,180 --> 00:08:13,270 ‫And the column that we're checking is its ID. 90 00:08:15,700 --> 00:08:23,440 ‫And here I also add this on delete cascade and now I need another constraint. 91 00:08:23,440 --> 00:08:25,960 ‫So I'm going to copy and paste that actually. 92 00:08:25,960 --> 00:08:27,340 ‫Or I could copy and paste it. 93 00:08:27,340 --> 00:08:28,930 ‫So like that copy paste. 94 00:08:28,930 --> 00:08:32,260 ‫Or I can of course create a new foreign key here as well. 95 00:08:32,260 --> 00:08:34,810 ‫So as you prefer, you can do it either way. 96 00:08:34,840 --> 00:08:38,290 ‫Lecture f k like that. 97 00:08:38,290 --> 00:08:50,800 ‫And the foreign key here will be the lecture id sure i dx and here it should also have the structure. 98 00:08:50,800 --> 00:08:56,290 ‫So to table dbo and the table should be a lecture. 99 00:08:58,960 --> 00:09:03,760 ‫And now the ID is the table column that we're looking at. 100 00:09:05,510 --> 00:09:09,220 ‫And of course, this line on the lead cascade. 101 00:09:09,230 --> 00:09:12,560 ‫So on delete cascade. 102 00:09:13,070 --> 00:09:15,580 ‫So why do we have this student lecture? 103 00:09:15,590 --> 00:09:17,780 ‫Let's update it real quick so that we have it. 104 00:09:18,260 --> 00:09:24,080 ‫We have it in order to have, on one hand, all the students that can have multiple lectures and all 105 00:09:24,080 --> 00:09:26,120 ‫the lectures that can have multiple students. 106 00:09:26,120 --> 00:09:31,730 ‫So that's why we need to have this associative table, which only has the IDs of each student and each 107 00:09:31,730 --> 00:09:35,240 ‫lecture, because otherwise it would be way too complicated. 108 00:09:35,240 --> 00:09:37,730 ‫And that's a very clean way to handle this. 109 00:09:38,780 --> 00:09:43,730 ‫In order to open them all shift D and data sources will be opened up. 110 00:09:43,730 --> 00:09:48,620 ‫Now you need to select your project on the right hand side and all shift D again. 111 00:09:48,620 --> 00:09:53,930 ‫Now we can add a new data source, so let's do that and choose database here. 112 00:09:53,930 --> 00:09:55,400 ‫Choose data set here. 113 00:09:56,170 --> 00:09:59,260 ‫Then the connection should be the one that you have. 114 00:09:59,290 --> 00:10:02,470 ‫You can either use the one that you have or you could use a new connection. 115 00:10:02,470 --> 00:10:06,100 ‫But it doesn't make sense as we have set up this specific connection. 116 00:10:06,400 --> 00:10:12,670 ‫So I'm going to go next and this will be the connection string and I'm going to copy that because I 117 00:10:12,670 --> 00:10:13,390 ‫will need it. 118 00:10:13,660 --> 00:10:13,990 ‫Yes. 119 00:10:13,990 --> 00:10:17,620 ‫Save the connection as and I'm going to click next. 120 00:10:18,370 --> 00:10:24,430 ‫And now I can choose the tables and I'm going to use lecture students, student lecture and university. 121 00:10:24,430 --> 00:10:27,490 ‫So those four tables will be required. 122 00:10:27,490 --> 00:10:30,040 ‫And I'm just going to press finish. 123 00:10:30,910 --> 00:10:38,600 ‫Now, in order to use SQL, you need to make sure that SQL tools or link to SQL tools are connected, 124 00:10:38,740 --> 00:10:39,580 ‫are installed. 125 00:10:39,580 --> 00:10:44,230 ‫So if you don't have them, check out the Visual Studio installer. 126 00:10:44,230 --> 00:10:46,630 ‫So open up the Visual Studio Installer. 127 00:10:47,230 --> 00:10:50,410 ‫It's a program that you should have installed and we have used that already. 128 00:10:50,830 --> 00:10:52,660 ‫So just open that up. 129 00:10:53,420 --> 00:10:54,290 ‫Then we are. 130 00:10:55,840 --> 00:11:00,280 ‫Then here on the Visual Studio Community Press on more and modify. 131 00:11:00,490 --> 00:11:06,790 ‫So the one that you have installed, modify your installation and then go to individual components here 132 00:11:06,790 --> 00:11:12,060 ‫and make sure that you have the SQL pieces here. 133 00:11:12,070 --> 00:11:15,730 ‫So SQL at runtime and so forth. 134 00:11:17,230 --> 00:11:20,320 ‫And here link to SQL Tools. 135 00:11:20,320 --> 00:11:21,790 ‫So that's the one that's important. 136 00:11:21,790 --> 00:11:26,320 ‫If you don't have that, if you don't install that, then the further approach will not work. 137 00:11:26,320 --> 00:11:29,760 ‫So go ahead and check the check mark and then modify. 138 00:11:29,770 --> 00:11:31,510 ‫So go ahead and install. 139 00:11:31,870 --> 00:11:36,640 ‫But before you can go ahead and install it, we should save our project. 140 00:11:36,640 --> 00:11:42,370 ‫So I'm going to save my project here because what it will do is it will restart Visual Studio, it will 141 00:11:42,370 --> 00:11:44,020 ‫close down Visual Studio. 142 00:11:44,020 --> 00:11:49,180 ‫So I'll go to modify again and I'm going to go in there. 143 00:11:51,040 --> 00:11:54,310 ‫Individual components choose. 144 00:11:56,040 --> 00:12:01,500 ‫Link to SQL here modify and then let it do its magic. 145 00:12:03,330 --> 00:12:06,930 ‫So press continue here and now it should download and install. 146 00:12:08,220 --> 00:12:12,930 ‫And in my case, it even asked me to save my changes. 147 00:12:14,650 --> 00:12:15,940 ‫And then it will install. 148 00:12:16,360 --> 00:12:19,930 ‫Once the installation is done, Visual Studio will start up again. 149 00:12:21,480 --> 00:12:25,170 ‫So you can shut down your Visual Studio installer again. 150 00:12:25,170 --> 00:12:29,250 ‫So close it and now we can go ahead. 151 00:12:29,670 --> 00:12:32,690 ‫So first of all, I hope you still have it copied. 152 00:12:32,700 --> 00:12:36,900 ‫So we're going to go into the code behind and establish the connection here. 153 00:12:36,900 --> 00:12:39,150 ‫And if you don't have a copy, that's no problem. 154 00:12:39,150 --> 00:12:45,330 ‫So in here we want to create a connection and it will be a string. 155 00:12:49,110 --> 00:12:54,570 ‫Connection string, which is configuration manager. 156 00:12:54,570 --> 00:13:00,630 ‫And if you don't show or if it doesn't show up, you need to add the reference to that configuration 157 00:13:00,630 --> 00:13:01,050 ‫manager. 158 00:13:01,050 --> 00:13:02,040 ‫We have seen that before. 159 00:13:02,040 --> 00:13:02,280 ‫Right? 160 00:13:02,280 --> 00:13:10,050 ‫So you add this reference here, right click and you add the configuration or system configuration here. 161 00:13:10,050 --> 00:13:10,840 ‫Okay. 162 00:13:11,010 --> 00:13:21,210 ‫Now you can add using sys dot configuration and now you can work with the configuration manager here. 163 00:13:21,210 --> 00:13:33,330 ‫So what we need to do is to connect to our data set connection string here and in quotation marks you 164 00:13:33,330 --> 00:13:41,790 ‫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 165 00:13:41,790 --> 00:13:43,020 ‫have here of the project. 166 00:13:43,020 --> 00:13:51,270 ‫And it's linked to SQL dot properties, dot settings, dot, and then the connection string that we 167 00:13:51,270 --> 00:13:51,600 ‫had. 168 00:13:51,600 --> 00:13:55,950 ‫And in my case it was janitorial dot db connection string. 169 00:13:57,250 --> 00:14:03,340 ‫And then here you need to state that it's the connection string here. 170 00:14:03,340 --> 00:14:05,140 ‫And that's our statement. 171 00:14:05,680 --> 00:14:12,760 ‫And by the way, because you have installed link to SQL Tools, you now can go go ahead and add a link 172 00:14:12,760 --> 00:14:14,430 ‫to a SQL class. 173 00:14:14,440 --> 00:14:22,330 ‫So I'm going to add a new class here, add new item link to SQL. 174 00:14:25,490 --> 00:14:37,760 ‫Link to SQL classes and I'm going to call it link to SQL data classes dot double. 175 00:14:38,940 --> 00:14:43,500 ‫TML All right, so now link to SQL data classes DB. 176 00:14:43,530 --> 00:14:50,970 ‫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. 177 00:14:50,970 --> 00:14:57,540 ‫So our code behind and in here now we can go ahead and create an object of this class that we've just 178 00:14:57,540 --> 00:14:58,230 ‫created. 179 00:14:58,590 --> 00:15:05,760 ‫And now I'm going to call it Link or actually it's going to be my link, SQL data class data context 180 00:15:05,760 --> 00:15:08,340 ‫and I'm going to call it data context. 181 00:15:08,610 --> 00:15:12,810 ‫So that will be pretty much the context of our data in general. 182 00:15:12,810 --> 00:15:18,960 ‫And I need to fill that up with my new SQL. 183 00:15:18,990 --> 00:15:26,490 ‫I need to initialize that with new link to SQL data context and it needs a connection here. 184 00:15:26,490 --> 00:15:29,970 ‫And we're going to use the connection string that we have created. 185 00:15:30,570 --> 00:15:33,060 ‫So now we have the data context set up. 186 00:15:33,060 --> 00:15:40,110 ‫We have our tables set up, our data set, set up and so forth, and then we can go ahead with the SQL 187 00:15:40,110 --> 00:15:45,240 ‫and link stuff in the next video where we're going to use all the link knowledge that we have acquired 188 00:15:45,240 --> 00:15:48,780 ‫so far and apply that on a SQL context.