1 00:00:00,450 --> 00:00:05,730 In this video we'll be going through adding our database to our project. 2 00:00:05,730 --> 00:00:11,910 So in the previous dude of downloaded and installed Microsoft SQL Server express if you didn't already 3 00:00:11,940 --> 00:00:18,870 have it and you would have used it to design two tables one for the car and so the record on one for 4 00:00:18,870 --> 00:00:22,110 the types of cars that's in the database. 5 00:00:22,110 --> 00:00:30,420 So we want to know add the references to this database to our project and so we're going to go through 6 00:00:30,600 --> 00:00:38,100 that by a step and a Microsoft makes it relatively easy to do that through the use of Ada net. 7 00:00:38,340 --> 00:00:40,710 So we'll see exactly how that works. 8 00:00:40,710 --> 00:00:47,760 So the first step is to go into your solution explore right click on the project and click add. 9 00:00:48,030 --> 00:00:55,270 So we're adding a new item and then you go to the data categories to see visual see sharp items equal 10 00:00:55,290 --> 00:01:00,800 to data and then we're choosing a deal putting it into two data models. 11 00:01:00,810 --> 00:01:03,270 So that's the one that we're using. 12 00:01:03,270 --> 00:01:10,570 And then I'm going to give it a name so the name I'm going to use is car and the B. 13 00:01:10,680 --> 00:01:11,400 All right. 14 00:01:11,400 --> 00:01:15,870 So the name of this data model is car into D. 15 00:01:16,200 --> 00:01:24,300 And then I click add the next screen asks me the type of model that's I want so if I want the designer 16 00:01:24,300 --> 00:01:31,830 from database into design or model empty code first model are good for us from database clicking on 17 00:01:31,830 --> 00:01:33,260 each will give you a description. 18 00:01:33,270 --> 00:01:37,410 But the default one that is selected is the one that I'm interested in. 19 00:01:37,410 --> 00:01:42,870 And if you want to read up on what it does it says that is just going to create a model based on an 20 00:01:42,870 --> 00:01:44,400 existing database. 21 00:01:44,400 --> 00:01:51,600 It will settle the connection string and everything so that we can interact with the database through 22 00:01:51,600 --> 00:01:52,290 this model. 23 00:01:52,290 --> 00:01:59,850 So that's the one we want e f designer from database we click next and then we move on to a screen where 24 00:01:59,850 --> 00:02:02,640 it wants us to choose our connection. 25 00:02:02,640 --> 00:02:06,470 So I had built an application using North Wind in the past. 26 00:02:06,480 --> 00:02:10,830 So it's hung up on that connection but that's not what I'm interested in right. 27 00:02:10,860 --> 00:02:16,440 So I'm just going to go to new connection and then I'm going to type in the server name so remember 28 00:02:16,440 --> 00:02:23,610 that your server name would be your P.C. name backslash escudos or Xia express or other. 29 00:02:23,760 --> 00:02:30,170 But a shortcut could be just to put that dot the backslash and see askew Express. 30 00:02:30,390 --> 00:02:35,550 So I'm dot is equivalent to your machine name pretty much in this situation. 31 00:02:35,760 --> 00:02:42,930 And then leaving that there you can go down to the part where you select or enter a database name you 32 00:02:42,930 --> 00:02:48,350 drop this down and you'll see all of the databases that you have on the ESC express. 33 00:02:48,360 --> 00:02:54,600 I have to or you may have few because you have these default ones or maybe you've been using it from 34 00:02:54,600 --> 00:03:00,660 before but the one you're interested in is car rental because that's the one that we're using for this 35 00:03:00,660 --> 00:03:01,310 project. 36 00:03:01,680 --> 00:03:08,610 So I select car and so I can test the connection just to make sure everything is okay and then I click 37 00:03:08,610 --> 00:03:10,440 okay to proceed. 38 00:03:10,440 --> 00:03:15,240 Once that is done you see that the con takes care of changes and it showing you the connection string 39 00:03:15,240 --> 00:03:16,700 that is going to be generated. 40 00:03:16,830 --> 00:03:21,240 And I'm going to actually just walk you through what you're looking at in this section because it looks 41 00:03:21,240 --> 00:03:23,270 confusing but it's really simple. 42 00:03:23,400 --> 00:03:25,590 Once you know what you're looking for. 43 00:03:25,590 --> 00:03:28,750 So the meta data section is just seeing that. 44 00:03:28,750 --> 00:03:33,870 Okay we're adding This is like some stuff to declare the model that will be added. 45 00:03:34,350 --> 00:03:40,710 That's auto generated you know that's up the wearable that the major parts of it though would be the 46 00:03:40,920 --> 00:03:47,850 part that says connection string is equal it's a data source so data source is talking about the server 47 00:03:47,850 --> 00:03:49,100 you're looking at. 48 00:03:49,110 --> 00:03:54,850 So when I say a server you're looking at right now we're looking at our local server our own P.C. with 49 00:03:54,880 --> 00:04:03,720 the school express database instance is hosting our database in the event that this database was not 50 00:04:03,720 --> 00:04:07,790 on your P.C. then this would not be the same value. 51 00:04:07,800 --> 00:04:14,450 This would be that maybe the IP or the machine name off that other computer where that database is some 52 00:04:14,490 --> 00:04:20,040 going through this to tell you that if you're developing this in an enterprise setting where you know 53 00:04:20,040 --> 00:04:26,370 you you have your P.C. and the database is on another P.C. somewhere else in the company you need to 54 00:04:26,370 --> 00:04:33,660 connect your application to that central database then the data source would be the name of the machine 55 00:04:33,990 --> 00:04:37,850 or the IP address to that particular machine. 56 00:04:37,860 --> 00:04:38,520 All right. 57 00:04:38,520 --> 00:04:44,970 The Qatar love talks about the database that you are connecting to and then well integrated security 58 00:04:45,020 --> 00:04:48,440 dichotomy into multiple active results. 59 00:04:48,450 --> 00:04:53,580 That is very important for multiple persons to be able to connect to the database simultaneously. 60 00:04:53,580 --> 00:04:57,540 But as you can see those are all already there and they're already set to true. 61 00:04:57,540 --> 00:05:03,690 So the two parts I want to focus on they are the data source and the initial catalogue. 62 00:05:03,750 --> 00:05:10,930 Once again data sources the name or the address of the machine that has the database and then in catalogue 63 00:05:11,010 --> 00:05:14,920 would be the database in an enterprise setting. 64 00:05:14,930 --> 00:05:22,380 You would be probably shut down by security as in access to that central database would be metered by 65 00:05:22,380 --> 00:05:22,900 security. 66 00:05:22,910 --> 00:05:28,980 So in other situations you your went up a user idea and a password inside the connection string to be 67 00:05:28,980 --> 00:05:29,780 able to connect. 68 00:05:30,150 --> 00:05:31,260 But that's for a later date. 69 00:05:31,260 --> 00:05:33,710 So right now we have it local. 70 00:05:33,750 --> 00:05:35,430 We have our connection string. 71 00:05:35,490 --> 00:05:42,840 It will be saved in the fire app config so you can see config is one of the files that we've got with 72 00:05:42,840 --> 00:05:43,530 our project. 73 00:05:43,560 --> 00:05:50,850 So that will be modified to have this connection string but we can click next and then we can choose 74 00:05:50,850 --> 00:05:52,280 the latest into different work. 75 00:05:52,290 --> 00:05:53,340 That's fine. 76 00:05:53,340 --> 00:05:54,860 We can next again. 77 00:05:54,880 --> 00:06:01,410 Now this screen is going to ask us which objects do we want to import into our model from the database. 78 00:06:01,410 --> 00:06:06,660 So if I drop down into tables I'm going to see all of the tables listed in the database which is all 79 00:06:06,660 --> 00:06:10,620 of the two that we just created so I can just tick tables. 80 00:06:10,620 --> 00:06:16,590 So if you add more tables or three or using another database of all the tables you want all the tables 81 00:06:16,590 --> 00:06:21,270 your tables you want all of your views likely views you want all of his third procedures you take them 82 00:06:21,540 --> 00:06:23,100 whatever is in the database. 83 00:06:23,220 --> 00:06:30,360 This entity framework model is able to import it and put it in the context of your project. 84 00:06:30,360 --> 00:06:31,090 All right. 85 00:06:31,230 --> 00:06:36,300 So I want to take tables and I'll leave these default settings. 86 00:06:36,330 --> 00:06:39,960 Notice the name of the namespace car into a model. 87 00:06:40,050 --> 00:06:40,910 That's fine. 88 00:06:40,920 --> 00:06:48,250 The click finish and then it will go ahead and do its magic so once he's finished doing the loading 89 00:06:48,250 --> 00:06:50,610 and creation then we see our models. 90 00:06:50,620 --> 00:06:56,040 So let me just take some time to explain exactly what you're looking at here. 91 00:06:56,200 --> 00:07:02,230 If you're familiar with database design then you're familiar with diagrams and one of the diagrams that's 92 00:07:02,230 --> 00:07:06,320 very important would be the entity relationship diagram. 93 00:07:06,400 --> 00:07:13,140 So the integer relationship diagram shows like a visual representation of your table the fields in there 94 00:07:13,150 --> 00:07:20,210 maybe the data types and to some extent and also any relationships between each table. 95 00:07:20,260 --> 00:07:26,470 Each table is an entity and then you have the relationships between them hence the name entity a relationship 96 00:07:26,470 --> 00:07:27,520 diagram. 97 00:07:27,520 --> 00:07:33,040 Now if you're looking at this and you're familiar with what the yard looks like then you'd notice that 98 00:07:33,040 --> 00:07:34,720 this looks just like any the. 99 00:07:35,050 --> 00:07:41,800 I have the two entities in my database are the two tables and I see the relationship between them. 100 00:07:41,800 --> 00:07:49,600 When I click this line is going to highlight that the idea in types of cars is related to type of car 101 00:07:49,600 --> 00:07:50,310 I.D.. 102 00:07:50,470 --> 00:07:57,850 And it also shows you that you can directly access the data instead of type of car through that relationship. 103 00:07:57,850 --> 00:08:04,900 We'll get into that later but the point is that the model is giving you direct access to the tables 104 00:08:04,900 --> 00:08:07,240 within the context of C sharp. 105 00:08:07,240 --> 00:08:14,320 So once you have added this model and you reach this far I encourage you to do A build and that build 106 00:08:14,560 --> 00:08:22,540 can be done using the keyboard shortcuts control shift and b or you can just go to build and build solutions. 107 00:08:22,540 --> 00:08:26,650 So it's the same thing but you're not actually starting the obligation. 108 00:08:26,650 --> 00:08:28,460 You don't need to start application. 109 00:08:28,540 --> 00:08:34,360 You just wanted to control a shift and B and let everything recompile so that everything knows that. 110 00:08:34,360 --> 00:08:34,740 Okay. 111 00:08:34,740 --> 00:08:37,420 No the database connection is there. 112 00:08:37,420 --> 00:08:37,750 All right. 113 00:08:37,750 --> 00:08:45,310 So once we have seen that we have a successful build then we can proceed with the rest of our development 114 00:08:45,850 --> 00:08:50,740 before it's in a court I want to just walk you through this diagram and what you need to know. 115 00:08:50,770 --> 00:08:58,030 So I'm going to right click in the empty space click properties and then I'm going to say all right 116 00:08:58,450 --> 00:09:03,580 in the properties we have two very important names one more imports on the other. 117 00:09:03,610 --> 00:09:12,100 So we have entity container name which is called car into entities and then we have namespace called 118 00:09:12,280 --> 00:09:14,450 car rental model. 119 00:09:14,470 --> 00:09:22,960 So whenever we need to access our data models here we need to know all these two names are. 120 00:09:23,560 --> 00:09:29,000 When that time comes you will see however another important thing that's I want to note. 121 00:09:29,020 --> 00:09:35,230 Let's go to the solution explorer and then you'll see that some references got added and you would see 122 00:09:35,260 --> 00:09:43,780 the new file got added and you'll see that there also class files with the same names as the tables. 123 00:09:43,780 --> 00:09:47,350 So these class files were actually generated per table. 124 00:09:47,400 --> 00:09:51,570 So you see car into the record but yes when you click on it you look at it. 125 00:09:51,580 --> 00:09:56,230 It was also generated when you look at it it looks just like a C sharp plus. 126 00:09:56,260 --> 00:10:04,600 So the point is that when we want to get data or insert data or manipulate data we're literally just 127 00:10:04,600 --> 00:10:10,840 doing all of it to an interface called the links which will look at later but an interface called the 128 00:10:10,840 --> 00:10:18,310 link that will interact with these more need to see sharp glasses and once they're interacting with 129 00:10:18,310 --> 00:10:20,770 them we're actually interacting with our database. 130 00:10:20,790 --> 00:10:29,470 So a dual use is a nice little interface to connect to our database without actually needing to worry 131 00:10:29,470 --> 00:10:36,790 about a secure and other types of leaks that would be needed for that kind of operation but we can keep 132 00:10:36,820 --> 00:10:41,050 everything homogenous and everything looking like C sharp code no. 133 00:10:41,170 --> 00:10:47,230 The last thing that we're going to do in this video is actually very data related and that is to insert 134 00:10:47,260 --> 00:10:53,520 some default value so that when we start hooking up to the database we don't have any problems. 135 00:10:53,560 --> 00:10:58,750 So notice that we need to specify the type of car from our farm. 136 00:10:58,750 --> 00:11:02,950 So what we want to do eventually is to bind. 137 00:11:03,410 --> 00:11:10,720 Let me go to the design we want to bind the items that's our in this list to what is in the database. 138 00:11:10,750 --> 00:11:16,870 So we went through the manual step of thinking this car it's going to edit items and inserting these 139 00:11:17,200 --> 00:11:23,320 what I want to do is have these in the database or whether more than these are fewer than these but 140 00:11:23,320 --> 00:11:30,640 these values I don't want them hardcoded because if the Honda is 0 tomorrow I want to be able to control 141 00:11:30,640 --> 00:11:36,380 it from the database instead of having to come into the design every time and change that option. 142 00:11:36,390 --> 00:11:36,930 Right. 143 00:11:37,030 --> 00:11:42,050 So how much are you going to clean these out from here from this election. 144 00:11:42,240 --> 00:11:43,080 Okay. 145 00:11:43,270 --> 00:11:51,510 And then I'm going to go over my database and then I went to insert these values into that table sir. 146 00:11:51,550 --> 00:11:53,620 Click on tables they expand. 147 00:11:53,680 --> 00:12:00,820 I click on types of cars right click and go to edit top two hundred rows so later on we'll actually 148 00:12:00,820 --> 00:12:08,110 develop an interface that allows us to manage the types of cars in the database but Forno just humor 149 00:12:08,110 --> 00:12:09,600 me and type them in manually. 150 00:12:09,820 --> 00:12:16,840 So we have Honda we have Lada we have buggy and Toyota. 151 00:12:16,870 --> 00:12:21,880 Those were the values that I had in my farm for the types of cars. 152 00:12:21,940 --> 00:12:25,870 So we have Honda Lada buggy and Toyota. 153 00:12:25,870 --> 00:12:28,000 Those are the types of cars in our database. 154 00:12:28,420 --> 00:12:37,120 So in our next video we will actually come back to our code behind and run a query that retrieves the 155 00:12:37,120 --> 00:12:47,620 types of cars and then binds it to the list of items available inside of or humble box and then we'll 156 00:12:47,620 --> 00:12:52,180 go ahead and see how we can collect data and submit to the database.