1 00:00:01,200 --> 00:00:02,250 ‫Welcome back. 2 00:00:02,280 --> 00:00:05,520 ‫In this video, we are going to create our first table. 3 00:00:05,520 --> 00:00:10,230 ‫And before we do that, I would like to show you what the end result of this chapter is going to be. 4 00:00:10,260 --> 00:00:16,530 ‫As you can see, I have a window here called Main Window, and it has multiple different controls in 5 00:00:16,530 --> 00:00:16,830 ‫here. 6 00:00:16,830 --> 00:00:24,090 ‫So we have labels, we have a list box well, multiple buttons and multiple list boxes in general. 7 00:00:24,090 --> 00:00:31,050 ‫And what you can see here are multiple zoos and well, the names are a bit mixed up already because 8 00:00:31,050 --> 00:00:32,340 ‫I have changed them. 9 00:00:32,340 --> 00:00:39,210 ‫But what you can do here is you can check a zoo or select a zoo, and you will see the associated animals 10 00:00:39,210 --> 00:00:40,880 ‫that are within that zoo. 11 00:00:40,890 --> 00:00:44,640 ‫So let's say we have a zoo in Tokyo and we have a shark. 12 00:00:44,640 --> 00:00:46,440 ‫We have, well, multiple sharks. 13 00:00:46,440 --> 00:00:50,610 ‫We have crocodiles, a gecko, hawks, all those kind of animals. 14 00:00:50,610 --> 00:00:52,890 ‫And in New York, we have different types of animals. 15 00:00:52,890 --> 00:00:59,460 ‫So in New York we have a small zoo, we only have a crocodile, but then we decide to add a monkey to 16 00:00:59,460 --> 00:01:00,480 ‫the New York Zoo. 17 00:01:00,480 --> 00:01:03,750 ‫So we are going to add that to the New York Zoo. 18 00:01:03,870 --> 00:01:07,200 ‫And as you can see, these are the associated animals. 19 00:01:07,200 --> 00:01:13,980 ‫Then once I click on one of the animals here on the right or on one of the zoos, my text box here at 20 00:01:13,980 --> 00:01:15,990 ‫the bottom changes its value. 21 00:01:15,990 --> 00:01:18,750 ‫So it just takes the value of my city. 22 00:01:18,750 --> 00:01:23,820 ‫So and here I have, for example, Cairo two and I want to update that. 23 00:01:23,820 --> 00:01:26,850 ‫So I'm going to delete the two and I'm going to update zoo. 24 00:01:26,970 --> 00:01:29,220 ‫And now as you can see, Cairo has been updated. 25 00:01:29,220 --> 00:01:32,430 ‫I can do the same with my Milan Zoo. 26 00:01:32,550 --> 00:01:36,660 ‫So I'm going to update Milan to now this blah blah zoo. 27 00:01:36,690 --> 00:01:39,900 ‫It's a fake zoo, it doesn't exist, so I'm going to delete that entirely. 28 00:01:40,200 --> 00:01:43,050 ‫The same goes for our 44 animal. 29 00:01:43,050 --> 00:01:46,110 ‫I'm going to delete that animal entirely as well. 30 00:01:46,710 --> 00:01:54,780 ‫So generally you can see now that we have multiple different things to consider in here, we are using 31 00:01:54,780 --> 00:01:55,890 ‫multiple different tables. 32 00:01:55,890 --> 00:02:02,280 ‫So for one, we have the zoo table, then we have the animals table, and then we have a table which 33 00:02:02,280 --> 00:02:04,860 ‫takes those two and connects them to each other. 34 00:02:05,880 --> 00:02:10,470 ‫And that's going to be the bigger challenge that we're going to cover in one of the next videos. 35 00:02:10,470 --> 00:02:12,900 ‫And of course, we're going to build this UI. 36 00:02:12,930 --> 00:02:19,980 ‫It's not the most beautiful one, but it's very functional and it will help you to understand, well, 37 00:02:20,010 --> 00:02:26,340 ‫there's database constructs in general and how to use databases in C-sharp and some of my SQL as well. 38 00:02:26,670 --> 00:02:29,190 ‫So I'd say let's get started with this. 39 00:02:29,190 --> 00:02:32,100 ‫And therefore we go back to Visual Studio. 40 00:02:32,100 --> 00:02:34,350 ‫And as you can see, I have two connections here. 41 00:02:34,350 --> 00:02:38,190 ‫You only have one or you should only have one connection and I have another one. 42 00:02:38,190 --> 00:02:41,640 ‫That's the one that I was just connected to in order to show you the demo. 43 00:02:41,640 --> 00:02:45,270 ‫And we're going to use the new one that I've created here. 44 00:02:45,270 --> 00:02:48,510 ‫And as you can see, there is no table in there yet. 45 00:02:48,510 --> 00:02:50,760 ‫So we need to create a new table. 46 00:02:50,790 --> 00:02:56,790 ‫Therefore I right click onto table and I create a new table here. 47 00:02:56,790 --> 00:03:02,370 ‫So add new table and that will open up a little window. 48 00:03:02,700 --> 00:03:10,380 ‫And I'm going to, by the way, make the server explorer a little less intrusive here and how to hide 49 00:03:10,380 --> 00:03:12,720 ‫it like that now it looks a lot better. 50 00:03:12,780 --> 00:03:21,690 ‫So we have our little table which has an ID of type integer allows, nulls is or allow, nulls is deactivated 51 00:03:21,690 --> 00:03:23,340 ‫and there is no default value. 52 00:03:23,340 --> 00:03:25,980 ‫So what you can see here is a little key. 53 00:03:26,070 --> 00:03:34,380 ‫What that means, that is that this is going to be the key column and what you pretty much create here 54 00:03:34,380 --> 00:03:35,130 ‫are the columns. 55 00:03:35,130 --> 00:03:39,690 ‫So we have a column called ID and then we can create an additional column just here. 56 00:03:39,690 --> 00:03:47,280 ‫So I'm going to create one and I'm going to call that one location, location just like that. 57 00:03:47,280 --> 00:03:49,200 ‫And I'm not going to allow nulls. 58 00:03:49,200 --> 00:03:51,360 ‫Now, I need to change the data type. 59 00:03:51,360 --> 00:03:58,080 ‫And the data types are not exactly the ones that we have seen in C sharp, but some of them are similar 60 00:03:58,080 --> 00:03:59,040 ‫to the ones in C sharp. 61 00:03:59,040 --> 00:04:06,960 ‫So you have date datetime characters, bits, integers, and the one that I want to use here is going 62 00:04:06,960 --> 00:04:11,070 ‫to be bar chart 50 and bar chart 50. 63 00:04:11,070 --> 00:04:17,910 ‫What that allows me is to create a string of 50 values and store that inside of my location. 64 00:04:19,710 --> 00:04:28,200 ‫Now another important thing that I want to change is also I want to make this ID our identity specification. 65 00:04:28,200 --> 00:04:32,100 ‫So I'm going to click on identity specification here on a property. 66 00:04:32,100 --> 00:04:38,520 ‫So select the ID and then go to properties on the left, right hand side here and then choose identity 67 00:04:38,520 --> 00:04:42,750 ‫specification and change the is identity value to true. 68 00:04:43,930 --> 00:04:47,380 ‫And now it will increment by one each time. 69 00:04:47,380 --> 00:04:55,780 ‫And what that means is that every single entry in our database will have a unique ID, as creating a 70 00:04:55,780 --> 00:04:59,260 ‫new entry will always increment the ID by one. 71 00:04:59,410 --> 00:05:06,340 ‫That's similar to the example in inheritance that we had where we got the current notification ID and 72 00:05:06,340 --> 00:05:07,230 ‫stuff like that. 73 00:05:07,240 --> 00:05:09,300 ‫So that's pretty much our table. 74 00:05:09,310 --> 00:05:11,470 ‫Now I want to change the table name. 75 00:05:11,470 --> 00:05:13,600 ‫I don't want it to be called table. 76 00:05:13,750 --> 00:05:16,150 ‫I want this table name to be different. 77 00:05:16,150 --> 00:05:19,390 ‫So I'm going to call that Table Zu. 78 00:05:20,320 --> 00:05:28,150 ‫So this is going to be my zu table and I'm going to use a singular value here or a singular word, not 79 00:05:28,150 --> 00:05:29,170 ‫Zeus plural. 80 00:05:29,350 --> 00:05:31,840 ‫And there are multiple great reasons for that. 81 00:05:31,840 --> 00:05:37,480 ‫And if you want to read through it, there is a great article in StackOverflow or actually a great answer 82 00:05:37,480 --> 00:05:42,250 ‫here with six great reasons why you should use a singular term. 83 00:05:42,250 --> 00:05:46,090 ‫So somebody had the same question here, singular versus plural. 84 00:05:46,090 --> 00:05:48,250 ‫And you can check out the answer here. 85 00:05:48,820 --> 00:05:49,420 ‫All right. 86 00:05:49,420 --> 00:05:53,170 ‫So now we have our zoom table, but it's not there yet. 87 00:05:53,170 --> 00:05:57,040 ‫So, by the way, you are in the T SQL tab here. 88 00:05:57,040 --> 00:06:00,010 ‫And in here we are simply creating a table. 89 00:06:00,010 --> 00:06:07,030 ‫So once I press this update key here at the top or this button or shift all due, it will update and 90 00:06:07,240 --> 00:06:12,820 ‫run this SQL code which will create a table in DBO called ZU. 91 00:06:12,850 --> 00:06:19,660 ‫It will have an ID which is of type integer which does not allow null, so it will not have empty values 92 00:06:19,660 --> 00:06:23,800 ‫and it's the primary key and at the same time it's the identity. 93 00:06:23,950 --> 00:06:31,960 ‫So ID is the primary key, which means that we can well locate anything by this ID and then we have 94 00:06:31,960 --> 00:06:37,690 ‫something called location, which is another column which is of type and var char 50, which means there 95 00:06:37,690 --> 00:06:43,000 ‫can be 50 different string characters and then we have not null here as well. 96 00:06:43,000 --> 00:06:47,200 ‫So we create another column which does not allow null. 97 00:06:47,680 --> 00:06:48,100 ‫All right. 98 00:06:48,100 --> 00:06:54,280 ‫So now press this update button and it will update our database. 99 00:06:54,280 --> 00:06:57,400 ‫So I'm going to update the database here by pressing this button. 100 00:06:57,760 --> 00:07:01,390 ‫And now, as you can see, nothing has changed here under tables. 101 00:07:01,390 --> 00:07:09,760 ‫But if you refresh, then suddenly you see the zoo table in here and the zoo table has an ID and a location. 102 00:07:11,380 --> 00:07:18,010 ‫Now I would like to access the zoo and change its values so you can go right or press right. 103 00:07:18,010 --> 00:07:21,100 ‫Click here on the zoo and then show table data. 104 00:07:22,210 --> 00:07:28,930 ‫Well, that will open up, as does Debo the zoo data, which shows that there is an ID and there is 105 00:07:28,930 --> 00:07:29,860 ‫a location. 106 00:07:29,860 --> 00:07:31,350 ‫So this one is null. 107 00:07:31,360 --> 00:07:34,030 ‫Well, because it's empty, we haven't created it yet. 108 00:07:34,030 --> 00:07:35,350 ‫So this rule doesn't exist yet. 109 00:07:35,350 --> 00:07:36,640 ‫That's why it says no now. 110 00:07:36,670 --> 00:07:43,390 ‫But if I enter a location and here, so I'm going to enter a location like New York. 111 00:07:43,390 --> 00:07:50,050 ‫As you can see, I have created this new location which has the ID of one, and we can enter the next 112 00:07:50,050 --> 00:07:57,250 ‫location just below the way you can make it a little wider here and you can add some other cities. 113 00:07:57,250 --> 00:08:05,290 ‫So I'm going to add Tokyo, I'm going to add Berlin, then I'm going to add Cairo and Milan. 114 00:08:05,290 --> 00:08:07,570 ‫And of course, I could go ahead and add many more. 115 00:08:08,140 --> 00:08:08,710 ‫All right. 116 00:08:08,710 --> 00:08:14,800 ‫So now we have multiple different locations and they all have a unique ID. 117 00:08:14,830 --> 00:08:18,130 ‫So starting from one going to five in our case now. 118 00:08:18,700 --> 00:08:25,360 ‫Now let's go ahead and create a new project because we have all of that set up in our server explorer 119 00:08:25,360 --> 00:08:26,830 ‫without even having a project. 120 00:08:26,830 --> 00:08:30,040 ‫So solution here, solution one zero project. 121 00:08:30,040 --> 00:08:40,210 ‫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 122 00:08:42,670 --> 00:08:44,270 ‫and I'm going to press OC. 123 00:08:46,690 --> 00:08:51,220 ‫All right, that we are in WPF and we see our XAML file here. 124 00:08:51,250 --> 00:08:54,580 ‫Next, what we want to do is add a data source. 125 00:08:54,580 --> 00:08:58,750 ‫So as you can see, I press on data source now and if that doesn't show up for you. 126 00:08:58,750 --> 00:09:04,750 ‫So if nothing like this is stated here, so your project currently has no data source, you simply click 127 00:09:04,750 --> 00:09:06,520 ‫on your project on the right hand side. 128 00:09:06,520 --> 00:09:13,030 ‫So in this case, WPF Zoo Manager and then you press data sources, then this will come up and you will 129 00:09:13,030 --> 00:09:15,610 ‫be able to add a new data source. 130 00:09:15,610 --> 00:09:19,960 ‫So I'm going to choose the data source type database here. 131 00:09:20,380 --> 00:09:25,570 ‫Then I press next, then I use the data set as a database model. 132 00:09:26,950 --> 00:09:29,980 ‫And then here you need to choose your data connection. 133 00:09:29,980 --> 00:09:33,640 ‫And I'm going to choose the one that I have created the second time. 134 00:09:33,640 --> 00:09:41,190 ‫So panel is SQL and it's called DB and I'm going to show the string. 135 00:09:41,200 --> 00:09:42,430 ‫Well, that's fine. 136 00:09:42,640 --> 00:09:45,280 ‫In order to make it simpler, I just press yes. 137 00:09:45,280 --> 00:09:48,190 ‫Here include sensitive data and connection string. 138 00:09:48,340 --> 00:09:50,230 ‫Then I press next. 139 00:09:50,230 --> 00:09:55,330 ‫By the way, if you don't want to include a sensitive data, then you can set that up in your application 140 00:09:55,330 --> 00:09:55,900 ‫code later on. 141 00:09:55,900 --> 00:09:57,400 ‫But I'm going to keep it simple. 142 00:09:57,400 --> 00:10:02,260 ‫So I press next and then I get this connection string and it's called Punishers. 143 00:10:02,440 --> 00:10:07,660 ‫DB Connection String in my case, and I'm going to copy that and press next. 144 00:10:07,660 --> 00:10:15,340 ‫Then I can choose my database objects and as you can see, my tables has a zoo database object and I'm 145 00:10:15,340 --> 00:10:17,230 ‫going to just choose that. 146 00:10:17,230 --> 00:10:22,660 ‫And as you can see, there is a data set name which is called authorized DB data set, which we also 147 00:10:22,660 --> 00:10:23,230 ‫can change. 148 00:10:23,230 --> 00:10:26,050 ‫But I'm going to leave it as is now. 149 00:10:26,050 --> 00:10:34,600 ‫I press finish and that will create our data source and as you can see now it's there we have the data 150 00:10:34,600 --> 00:10:35,680 ‫source now here. 151 00:10:35,680 --> 00:10:43,800 ‫And what you can do here now is, for example, right click on zoo and edit data set with designer and 152 00:10:43,800 --> 00:10:46,870 ‫there you'll find your database and a design. 153 00:10:46,930 --> 00:10:49,180 ‫As you can see, there is a table called Zoo. 154 00:10:49,180 --> 00:10:53,350 ‫It has a key value which is called ID or a key column. 155 00:10:53,350 --> 00:11:02,140 ‫Then you have the location and there is a zoo table adapter which has a fill and gate data, SQL functionality. 156 00:11:02,140 --> 00:11:10,930 ‫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 157 00:11:10,930 --> 00:11:16,270 ‫string because I copied my string name like that and I'll need that in a second. 158 00:11:17,740 --> 00:11:19,660 ‫So I create a new string. 159 00:11:20,470 --> 00:11:29,200 ‫I'm going to call it connection string, which is going to be accessing the configuration manager, 160 00:11:30,040 --> 00:11:33,280 ‫that connection strings. 161 00:11:33,370 --> 00:11:40,530 ‫And in here in the square brackets, I'll need to enter the string in quotation marks. 162 00:11:40,540 --> 00:11:46,090 ‫Now it's complaining because it doesn't know this configuration manager and I could add it here at the 163 00:11:46,090 --> 00:11:46,390 ‫top. 164 00:11:46,390 --> 00:11:53,650 ‫I could said Yeah, using configuration manager and those kind of things, but we need to set it up 165 00:11:53,860 --> 00:11:54,640 ‫before that. 166 00:11:54,640 --> 00:12:02,470 ‫So I press right click onto my references and I add a reference here and the reference that I want to 167 00:12:02,470 --> 00:12:06,010 ‫use is system dot configuration. 168 00:12:06,010 --> 00:12:07,270 ‫So that one here. 169 00:12:07,930 --> 00:12:12,760 ‫So I hit the check mark here next to system configuration and press. 170 00:12:12,760 --> 00:12:13,450 ‫Okay? 171 00:12:14,200 --> 00:12:23,950 ‫And now this configuration manager can be used and we can go ahead and add using system dot configuration. 172 00:12:24,670 --> 00:12:28,060 ‫Now suddenly the configuration manager is not complaining. 173 00:12:28,060 --> 00:12:34,000 ‫Well, at least the color comes up and now we just need to add the correct connection strings. 174 00:12:34,690 --> 00:12:41,950 ‫And that's going to be the name of our project, which is double WPF in my case, empty space or underscore 175 00:12:41,980 --> 00:12:52,030 ‫zu manager dot properties, dot settings that and then the link that I've copied. 176 00:12:52,030 --> 00:12:55,660 ‫So this is DB connection string. 177 00:12:57,580 --> 00:13:01,660 ‫And next, what we need to do here is to say that this is our connection string. 178 00:13:01,660 --> 00:13:05,860 ‫So we're going to use the connection string property here like that. 179 00:13:05,860 --> 00:13:07,720 ‫So it's a pretty long line of code. 180 00:13:07,720 --> 00:13:16,480 ‫But what's important here is you use the name of your project, dot properties, dot settings, dot 181 00:13:16,480 --> 00:13:23,260 ‫the string that you have set up when creating your data source and then dot connection string afterwards. 182 00:13:23,770 --> 00:13:24,160 ‫All right. 183 00:13:24,160 --> 00:13:30,430 ‫So now this is set up and we can go ahead in the next video where we're going to work a little more 184 00:13:30,430 --> 00:13:33,970 ‫with the SQL part of our application.