1 00:00:00,650 --> 00:00:01,700 ‫Welcome back. 2 00:00:01,730 --> 00:00:07,910 ‫Now that you know how to create your first little queries, how to set up everything with a database 3 00:00:07,910 --> 00:00:16,220 ‫and so forth, it's time for us to go ahead and get started with the UI and do some crazy stuff with 4 00:00:16,220 --> 00:00:16,520 ‫the UI. 5 00:00:16,550 --> 00:00:19,850 ‫So let's go to the main window XAML file. 6 00:00:21,400 --> 00:00:24,730 ‫And we can we can leave it like that. 7 00:00:25,210 --> 00:00:28,060 ‫Let's close this so we have a little more space here. 8 00:00:30,130 --> 00:00:32,440 ‫And actually like that, it's fine. 9 00:00:32,680 --> 00:00:33,190 ‫All right. 10 00:00:33,190 --> 00:00:35,120 ‫So, first of all, what do we need? 11 00:00:35,140 --> 00:00:37,090 ‫We need a list in here. 12 00:00:37,090 --> 00:00:39,910 ‫And I used a list box. 13 00:00:40,510 --> 00:00:42,340 ‫And actually, let's check it out here. 14 00:00:42,340 --> 00:00:46,690 ‫We can see we have the label, then we have the list box buttons and so forth. 15 00:00:46,690 --> 00:00:52,120 ‫And we're going to start with the label and the list box first before we do any more than that. 16 00:00:53,020 --> 00:00:59,530 ‫And while we could go ahead and do all of that stuff very clean and a clean manner, creating grids 17 00:00:59,530 --> 00:01:02,830 ‫and all the different columns and rows and all those kind of things. 18 00:01:02,980 --> 00:01:09,040 ‫What I will do is I'm simply going to drag and drop everything in there and let them all do the job 19 00:01:09,040 --> 00:01:09,670 ‫for us. 20 00:01:09,940 --> 00:01:15,340 ‫Because this is well, this tutorial in this chapter is not that much about WPF. 21 00:01:15,340 --> 00:01:18,040 ‫It's a lot more about the database component. 22 00:01:18,040 --> 00:01:21,340 ‫So I'm not going to spend too much time on XAML here. 23 00:01:22,390 --> 00:01:28,810 ‫So in order to get something onto our main window, I'm going to zoom in a little bit. 24 00:01:29,110 --> 00:01:34,930 ‫Actually, we don't need that big here, the symbol part, because we're just going to see how the code 25 00:01:34,960 --> 00:01:37,240 ‫will be added up and in here. 26 00:01:37,240 --> 00:01:39,010 ‫I'm just going to drag stuff in. 27 00:01:39,010 --> 00:01:40,510 ‫So what do I need? 28 00:01:40,510 --> 00:01:44,410 ‫Well, I need a label, so I'm going to start with the label. 29 00:01:44,410 --> 00:01:49,240 ‫I'm going to drag it in and I'm going to put it just here at the left top corner. 30 00:01:49,240 --> 00:01:53,380 ‫And instead of label, it's going to say Zoo List. 31 00:01:55,360 --> 00:01:58,870 ‫Then I'm going to add a list. 32 00:01:58,870 --> 00:01:59,770 ‫Box. 33 00:02:01,140 --> 00:02:03,720 ‫Which will contain all the different list elements. 34 00:02:03,720 --> 00:02:10,860 ‫And I'm gonna drag that just below list or below the zoo list, and I'm gonna make it a little higher. 35 00:02:11,340 --> 00:02:13,500 ‫I'm going to change the size by a bit. 36 00:02:13,500 --> 00:02:16,920 ‫So this one will be 500 times. 37 00:02:17,940 --> 00:02:21,840 ‫Let's say 725. 38 00:02:22,550 --> 00:02:23,480 ‫That should be fine. 39 00:02:23,570 --> 00:02:25,490 ‫Now, I can make this a little wider. 40 00:02:25,490 --> 00:02:28,760 ‫And as you can see, everything is being set up here at the bottom. 41 00:02:28,760 --> 00:02:30,460 ‫So there is a margin. 42 00:02:30,470 --> 00:02:36,670 ‫There's vertical alignment towards the top, horizontal alignment towards the left for our label. 43 00:02:36,680 --> 00:02:41,720 ‫Then our list box is horizontally aligned towards the left, has a specific height. 44 00:02:41,750 --> 00:02:45,610 ‫There is some margin to the top and so forth. 45 00:02:45,620 --> 00:02:49,220 ‫So you can see it's all set up here directly for us. 46 00:02:49,490 --> 00:02:58,280 ‫And what I want to do is I want the items to be seen here or the list elements to appear on my zoo list 47 00:02:58,700 --> 00:02:59,510 ‫and. 48 00:03:01,670 --> 00:03:04,850 ‫Therefore I will need to create a name for it. 49 00:03:04,850 --> 00:03:14,270 ‫So I'm going to add a name to my list box and I'm going to call that one name is equal to list Zeus 50 00:03:15,410 --> 00:03:17,510 ‫and well, it's the list of Zeus. 51 00:03:17,510 --> 00:03:19,370 ‫That's why I'm just going to call it like that. 52 00:03:19,370 --> 00:03:26,540 ‫Now let's go back to our code behind, because now I want to show our Zeus on that list and well, we 53 00:03:26,540 --> 00:03:29,090 ‫could run a query and all of that, but that's not going to be enough. 54 00:03:29,090 --> 00:03:37,010 ‫We need to use a specific approach here in C sharp and we can make it very cleanly and quickly with 55 00:03:37,010 --> 00:03:40,310 ‫Link, but we're not going to cover Link until the next chapter. 56 00:03:40,310 --> 00:03:43,370 ‫So for now, we're going to do it the old fashioned way, let's say. 57 00:03:43,430 --> 00:03:50,330 ‫And later on we're going to add the namespace link which will well increase the speed and reduce the 58 00:03:50,330 --> 00:03:51,770 ‫amount of code we need to write. 59 00:03:52,830 --> 00:03:57,570 ‫But for now, what we need to do is we need to have an SQL connection. 60 00:03:57,750 --> 00:04:01,470 ‫So I'm going to create an SQL connection. 61 00:04:02,790 --> 00:04:14,340 ‫And in order to use the SQL connection, I need a namespace here using system DOT data, that SQL client. 62 00:04:15,390 --> 00:04:22,200 ‫And now as you can see, a SQL connection seems to be fine and we can create an SQL connection object 63 00:04:22,200 --> 00:04:24,750 ‫and I'm going to call that a SQL connection. 64 00:04:25,890 --> 00:04:32,760 ‫Now, we need at one point to initialize that, and the best one is to do that in our constructor of 65 00:04:32,760 --> 00:04:33,690 ‫the main window. 66 00:04:33,690 --> 00:04:45,780 ‫So I'm just going to say SQL Connection is going to be a new SQL connection and now it needs well, 67 00:04:45,780 --> 00:04:48,090 ‫there are multiple different overloads. 68 00:04:48,090 --> 00:04:50,670 ‫And the one that I'm going to use is this one here. 69 00:04:50,670 --> 00:04:52,820 ‫String connection string. 70 00:04:52,830 --> 00:04:53,730 ‫Well, guess what? 71 00:04:53,730 --> 00:04:54,930 ‫We have a connection string. 72 00:04:54,930 --> 00:04:56,610 ‫We set that up in the last video. 73 00:04:56,610 --> 00:05:03,420 ‫It's this string called Connection String, which is from configuration manager of type connection string. 74 00:05:03,420 --> 00:05:08,010 ‫So it's a connection string and that's beautiful because that's exactly what we need. 75 00:05:08,010 --> 00:05:12,450 ‫So we're going to hand it over and I'm going to just say like that. 76 00:05:12,450 --> 00:05:14,370 ‫So now we have an SQL connection. 77 00:05:14,370 --> 00:05:24,630 ‫So our main window, so this program here are Double Zoom manager is connected to the database here. 78 00:05:25,360 --> 00:05:29,650 ‫And it's this DB connection string that allows us. 79 00:05:31,230 --> 00:05:31,530 ‫All right. 80 00:05:31,530 --> 00:05:38,880 ‫Now, what I want to do is I want to show all my zoos and therefore I'm going to create a new method. 81 00:05:38,880 --> 00:05:42,610 ‫And it should be private because nobody needs to see. 82 00:05:42,720 --> 00:05:46,890 ‫We couldn't make it public so we can access it from other classes, but I'm just going to make it private 83 00:05:46,890 --> 00:05:48,750 ‫so only this class can access it. 84 00:05:48,750 --> 00:05:54,750 ‫And I'm going to call that one show zoos and in here. 85 00:05:55,770 --> 00:05:57,180 ‫I need a query first. 86 00:05:57,180 --> 00:05:58,740 ‫So I'm going to create a new query. 87 00:05:58,740 --> 00:06:07,080 ‫String query is going to be a select star from zoo. 88 00:06:08,340 --> 00:06:13,830 ‫What that will do is it will select all from our zoo table. 89 00:06:13,830 --> 00:06:17,670 ‫And as you can see here, that's a table and it has all the locations in it. 90 00:06:17,940 --> 00:06:27,000 ‫So now we have this query and we can use that query with an SQL adapter, SQL data adapter, to be precise, 91 00:06:27,000 --> 00:06:29,160 ‫SQL data adapter. 92 00:06:29,490 --> 00:06:41,310 ‫And that SQL data adapter can now go ahead and create a connection to that database that we have and 93 00:06:41,310 --> 00:06:42,260 ‫run the query. 94 00:06:42,270 --> 00:06:43,860 ‫So that's pretty much what it does. 95 00:06:44,220 --> 00:06:51,270 ‫So here we hand over the query and then we hand over the SQL connection, as you can see here. 96 00:06:51,330 --> 00:06:59,220 ‫So we say a SQL connection like that, and now that's pretty much what this is going to be. 97 00:06:59,220 --> 00:07:02,550 ‫And it's a small letter SQL connection. 98 00:07:02,730 --> 00:07:06,660 ‫Like we want to have the instance of our SQL connection, not the class, of course. 99 00:07:06,660 --> 00:07:14,220 ‫So what it's going to do is this SQL adapter will run this query onto that connection. 100 00:07:14,980 --> 00:07:18,620 ‫So onto our SQL connection, which is connecting to our database. 101 00:07:18,640 --> 00:07:18,940 ‫All right. 102 00:07:18,940 --> 00:07:20,290 ‫So that's pretty much what it does. 103 00:07:20,960 --> 00:07:21,640 ‫Okay. 104 00:07:22,800 --> 00:07:31,680 ‫So you can pretty much see this SQL adaptor as a, well, an interface to make tables usable and see 105 00:07:31,680 --> 00:07:32,880 ‫sharp objects. 106 00:07:33,660 --> 00:07:41,220 ‫Then the beauty about SQL adapters is as well that we don't need to open a connection to SQL and close 107 00:07:41,220 --> 00:07:43,830 ‫it because the adapter is going to take care of that. 108 00:07:43,920 --> 00:07:48,060 ‫So it's taking a lot of weight from our shoulders, pretty much. 109 00:07:48,270 --> 00:07:55,800 ‫So what we need to do here is use the using keyword and use our SQL data adapter that we have just set 110 00:07:55,800 --> 00:07:56,250 ‫up. 111 00:07:56,250 --> 00:07:58,610 ‫And in here we can use it. 112 00:07:58,620 --> 00:08:04,140 ‫So I'm going to create a data table which is going to be my zoom table. 113 00:08:05,220 --> 00:08:07,740 ‫And it's a new data table. 114 00:08:08,070 --> 00:08:09,180 ‫Well, not complaining. 115 00:08:09,180 --> 00:08:10,770 ‫It doesn't no data table yet. 116 00:08:10,770 --> 00:08:16,650 ‫We can fix that by using the class system dot data. 117 00:08:16,650 --> 00:08:18,210 ‫So using system data. 118 00:08:18,420 --> 00:08:25,200 ‫And now here I actually made something weird here and just want to create a new data table here. 119 00:08:25,350 --> 00:08:31,050 ‫So what this data table allows us is to store data from tables within a object. 120 00:08:31,170 --> 00:08:36,960 ‫So pretty much a zoo table is an object of our table or an object of C-sharp? 121 00:08:36,960 --> 00:08:37,380 ‫Pretty much. 122 00:08:37,380 --> 00:08:43,260 ‫So this allows us well, it's pretty much an interface between the table and what we have here. 123 00:08:43,350 --> 00:08:50,190 ‫So data table and data set, they are two different classes which we can use to get some data from our 124 00:08:50,190 --> 00:08:51,000 ‫database. 125 00:08:51,360 --> 00:09:00,210 ‫And now I can run my SQL adapter with the fill method, and it's going to fill the zoo table because 126 00:09:00,210 --> 00:09:01,890 ‫now we just created a data table. 127 00:09:01,890 --> 00:09:03,630 ‫Now we fill it with the SQL adapter. 128 00:09:03,630 --> 00:09:07,440 ‫So pretty much with all the content that is given to us from the query. 129 00:09:07,890 --> 00:09:18,090 ‫And what we can do now is get our list views and display a member path, and that's going to be the 130 00:09:18,090 --> 00:09:19,170 ‫location. 131 00:09:20,220 --> 00:09:23,250 ‫And then we need to have the idea as well. 132 00:09:23,250 --> 00:09:24,660 ‫List zoo. 133 00:09:25,740 --> 00:09:29,130 ‫It's going to be selected value path. 134 00:09:29,850 --> 00:09:31,800 ‫We're just going to be our ID. 135 00:09:32,430 --> 00:09:37,800 ‫And now finally we have list zoo's item source. 136 00:09:38,930 --> 00:09:42,740 ‫And that will be our zoo table. 137 00:09:44,150 --> 00:09:45,850 ‫Default view. 138 00:09:45,860 --> 00:09:52,240 ‫So default you get a customized view of the table that may include a filtered view or cursor position. 139 00:09:52,250 --> 00:10:02,510 ‫So what we're doing here with those three lines is we say that the column pretty much of our list zoos 140 00:10:02,510 --> 00:10:08,570 ‫with the content that we want to see is going to be the location from our table, then the selected 141 00:10:08,570 --> 00:10:09,500 ‫value path. 142 00:10:09,500 --> 00:10:17,990 ‫So the value behind the specific item within our list is going to be the ID and as the item source, 143 00:10:17,990 --> 00:10:21,620 ‫we set the zoo table here default view. 144 00:10:21,620 --> 00:10:28,100 ‫So we simply say that the content from our zoo table is going to be the item source for our list. 145 00:10:28,100 --> 00:10:34,610 ‫So it's just going to fill our list now or a list box with the content from the zoo table. 146 00:10:36,110 --> 00:10:41,510 ‫So which information in of the table and the table data table should be shown in our list box. 147 00:10:41,510 --> 00:10:43,100 ‫That's the display member path. 148 00:10:43,100 --> 00:10:47,090 ‫Then which value should be delivered when an item from a list box is selected? 149 00:10:47,090 --> 00:10:48,800 ‫That's the selected value path. 150 00:10:48,800 --> 00:10:52,520 ‫And then finally reference to the data the list books should populate. 151 00:10:52,520 --> 00:10:53,960 ‫And that's the item source. 152 00:10:53,960 --> 00:10:58,430 ‫Well, our zoo table default view, which is the data table which contains all the data. 153 00:10:58,640 --> 00:11:02,120 ‫Now that's great and we can run that. 154 00:11:02,120 --> 00:11:08,660 ‫But there is one thing that we need to consider and well, let's just run it before we consider that 155 00:11:08,750 --> 00:11:12,020 ‫and before we take care of that in a second. 156 00:11:13,220 --> 00:11:15,980 ‫And as you can see, there's our zoo list and it's empty. 157 00:11:15,980 --> 00:11:17,540 ‫And that is because I'm not calling it. 158 00:11:17,540 --> 00:11:20,300 ‫So let's call it in our main window here. 159 00:11:20,330 --> 00:11:23,180 ‫Let's call this show Zoo's method. 160 00:11:23,840 --> 00:11:29,900 ‫And of course, before we run that, it's important that location starts with a capital L, because 161 00:11:29,900 --> 00:11:34,610 ‫if I look at my zoo, then location the chapter data. 162 00:11:38,340 --> 00:11:42,360 ‫The location column name started with a capital L. 163 00:11:42,600 --> 00:11:48,030 ‫Now, if we start that, we should see our zoos on the screen. 164 00:11:49,680 --> 00:11:53,730 ‫And there we are, New York, Tokyo, Berlin, Cairo and Milan. 165 00:11:53,970 --> 00:11:59,550 ‫And if you compare that with your list, so I'm going to close that and we compare it with the data 166 00:11:59,550 --> 00:12:01,350 ‫in the zoo table. 167 00:12:01,350 --> 00:12:06,690 ‫So show table data we can see in New York, Tokyo, Berlin, Cairo and Milan. 168 00:12:07,230 --> 00:12:07,680 ‫All right. 169 00:12:07,680 --> 00:12:11,790 ‫So that was the first step towards our little application. 170 00:12:11,790 --> 00:12:19,380 ‫And as you can see, it's not that difficult when you use a adapter because it's opening the SQL connection, 171 00:12:19,380 --> 00:12:24,410 ‫it's closing the connection as required and it's pretty well short. 172 00:12:24,420 --> 00:12:27,610 ‫The code is not that long, but we can even shorten that later on. 173 00:12:27,630 --> 00:12:36,990 ‫There is only one thing that I would like to still add, and that is I try and catch because when you 174 00:12:36,990 --> 00:12:45,060 ‫use something with databases or connections and stuff like that, it's always good to use, try and 175 00:12:45,060 --> 00:12:50,790 ‫catch because there could be an error, something could happen, an exception could come up. 176 00:12:50,790 --> 00:12:55,890 ‫And I'm going to just catch that with a generic exception. 177 00:12:56,100 --> 00:13:03,570 ‫And this is going to be message box, dot show and it's just going to show the E to string. 178 00:13:03,570 --> 00:13:08,400 ‫So it's just going to show the error because we could, of course, catch all the different exceptions 179 00:13:08,400 --> 00:13:10,950 ‫that could come up whenever we use that. 180 00:13:10,950 --> 00:13:16,260 ‫But I'm just going to keep it simple and just add this one generic exception and it's going to show 181 00:13:16,260 --> 00:13:21,180 ‫us what the error was if showing the zoo should not work for some reason. 182 00:13:23,060 --> 00:13:23,630 ‫All right. 183 00:13:23,630 --> 00:13:25,100 ‫So that's it for this video. 184 00:13:25,100 --> 00:13:31,490 ‫And the next video, we are going to add the functionality so that we can show the associated animals 185 00:13:31,490 --> 00:13:34,190 ‫of that specific zoo that we are clicking on. 186 00:13:34,190 --> 00:13:35,450 ‫So see you there.