SQL Database App with Windows GUI – Project Tutorial

In this course, you will learn how to design and use databases, you will create a graphical Windows application that can display and modify data from a MySQL database server. Shad Sluiter is your instructor for this course, he is a professor of computer science and software development at Grand Canyon University. Hi, in this video, I'm going to show you how to create this application that's on the screen. This will be a Visual Studio project. And we are going to focus in on working with databases. So let's look at the features of what we're going to build. And then I'm going to give you the outline of the different subjects that we're going to cover some of the technical things that you'll learn. And then finally, we'll get into the part where we start building it. So let's look at the features first. And then we'll get into what we actually have to learn some of the features, you can see on the application that we have a database. So that's the main feature. And we are going to have a bunch of songs. So you can see that I have albums on the top table here. And when I click on an album, I have a preview of the album cover. And then in the bottom part, we can see the different songs that are on that album. So for instance, here we got help, and have a video player over here. And so we can listen to The Beatles, as they call for help. So if I choose the other song here, you'll see that it'll automatically switch, it looks to me like that one's unavailable. And then we get Ticket to Ride. And I have to suffer through an ad. So this is just a YouTube player. It is not anything saved to my computer. Alright, so now I'm getting Ticket to Ride like I asked for it to begin with. Now I can also delete some of these things. So for instance, if I wanted for some reason to get rid of Abbey Road, I can try the delete button up here. And then I get a confirm message that says Are you sure you want to delete number three, as you can see, album number three is listed here. And I don't actually want to delete this right now. Okay, so now let's talk about what are some of the features and some of the programming that you're going to learn. First of all, you can see that we're going to build a database, let's see what this database looks like. You can see I have a table here called album, another one called track, and I filled in some data for you, we're going to be working with MySQL and this tool called MySQL Workbench. And so you can see that the table definition here shows that we have a foreign key relationship between the album and all of the tracks that are on that album. So I will take you through the process of setting up a database and defining the different fields and then creating this link between the two tables called a foreign key. So this video is targeted at people who know some programming, but are not really familiar with databases to a great deal. And so what we're going to do is focus in on how to build this database, and to write the queries that can make this application work. My name is shad Sluiter, and I teach software development at Grand Canyon University in Phoenix, Arizona. So in this course, you're going to see the emphasis on working with databases. So I'm teaching a course on SQL and on Mongo right now. And so you get to see the benefit of what we're doing in class. If you would like to look at the full series of this thing, you can either subscribe to the channel that you're looking at now on YouTube, or you can see the more extensive version on steady coding.org, which is another website. So I welcome you to come back, because people that are in my classroom are becoming professional software developers and getting great jobs. And so you could do that too. Okay, so let's get into the first phase of this. So phase number one is build the database. phase number two is to build the application. And then phase three is to connect those two things together. So we're starting off here at the beginning, which could take a little while, we're going to build a database. So what we're going to do first is design the album table. And then after we have the album table working, we will go and try to make it connect to the application. And then we'll come back to the track and we'll add the song tracks later. So we're going to build this application kind of piecewise. So we'll get very basic to start with and then more advanced as we get through this series of videos. So let's start with building a new database. Okay, so let's get started with the tools that we're going to need. So in this first part, we're going to need to have a SQL Server. So there are various types of SQL databases that you can install. We're going to be using MySQL which is free it's open source, it runs on multiple platforms. However, there are other alternatives besides MySQL even though it's the most popular and widely used database probably in the planet. You could use Postgres, which apparently has more features, you could use Microsoft SQL, which is a very scalable and is used by a lot of enterprise customers, you could go to Oracle, they have a very expensive system. And you could probably find others as well. But MySQL is what we're choosing because it has great tools, and it's free. So the tool that we're going to be using is MAMP, Ma, MP that stands for Macintosh, Apache, MySQL, and PHP. Now, don't let the Macintosh part scare you away, because if we were to go look at the downloads, we're going to see that the first version that they recommend is for Windows. So they have a Windows version and a Mac version, which is really good, because in my classroom, I have students that have both types of operating systems on their computer. So we're going to install MAMP. And we're going to wait for the download to finish. And then when we set it up, we will not choose MAMP Pro, but we will just use the free version, that will work just great for our purposes. Another tool that we'll be using in this project is MySQL Workbench. And you can see the download page here. Now MySQL Workbench is used for arranging more complex databases. And so we won't see this for the first part of the tutorial, because we're only going to have a single table to get started with. But just to let you know that this is coming, we will be using this eventually, to create the application that will make this media player work, we're going to be using Visual Studio version 2022. And we'll be creating a Windows desktop application. So we'll get to that part. When we get to the application. We're first of all going to build the database though. Okay, so now I have skipped ahead, I have installed MAMP. I have chosen not to use MAMP Pro, so I unchecked one of those boxes. And now I have this application up and running. Now I want to start this. So I'm going to click Start servers. And you're going to see two different little green dots up here, one for Apache, and the second one for MySQL server. Now we could care less about Apache server for this application. This is for hosting websites. And this is not going to be a web application. However, we're going to be using a SQL server so that way, we have the ability to handle the database. And so that's why I installed MAMP. Now there are other packages that you could use. If this doesn't work out for you, you could use Wempe wa MP, which is for Windows, you could use XAMPP x a MP, you can search for that it has the same functionalities with different menus in different places. Another tool, which is really good that is not required you to install anything is the USB web server. So the second link on the page here actually has a free version of what USB web server is. And you can download this. And you can literally run it from a USB stick, you could run it on any computer lab or you don't have access to install software. And it will do the same features as what we're going to do in the video. So take your pick, I'm going to be using MAMP. And that's what the tutorials will be easiest to follow it. So go ahead and pick MAMP if you can otherwise choose one of the alternatives. Okay, so now we're back. Now that we got the application running, let's go and choose this button called Open start page. And you're going to see that the website load opens to localhost slash MAMP. So let's go to tools. And I want to choose PHP myadmin. And this will bring up all of the databases that I have installed on this server. So you can see over on the left side, I have music, MySQL and a few other things. So the only thing that we're going to focus in on here is this one called music. And since that I've already created this application, I have some data in it, you can skip ahead, if you know how to do all of this, we need to create a table and fill it with some data. But I'm going to do that right now. So you can see the process. The first thing I'm going to do is I'm going to create a new database. So I have music, I'm going to create a new one. So let's click the link at the top. And this one, I'm just going to call it music two, because I'm going to recreate the same application. The language that you choose, of course, is probably English. If you're watching this video, I'm just going to choose the general text that we have here. Now we are supposed to create a table and the first table we're going to create is called albums. And now we have to decide how many columns we're going to put in the albums table. Okay, so now I'm going to cheat and I'm going to look back to what I actually created in this application. So here's the other tab. You can see I'm going to create 1234566 columns, and I'm going to have different data types in each column. So let's change this number to a six and then choose Create table. Let's click the Go button. The first thing we're going to do is name the six columns. So just going straight down the left side of the form I'm going to type in ID and a database ID is always the first item it's a number that has a unique identifier for each record. The next thing we're going to put in is the album title. And I'm using an underscore to separate the words, you don't have to use underscores. But it's a pattern that a lot of people use. I'm going to use artists year image, Name and Description to be the rest of these. So these were going to be the six columns that describe each album in our database. Now let's take a look at the next item here. So the type, so every one of these right now says int, which stands for integer. Now, integers are numbers, of course. Now if I want to have something besides an integer, I can just select it from here, I'm going to choose a text version. So var char and text are two two choices. Let's take a look at the hints to see why we would pick one or the other. So var char is a variable length, character string. And it is used for things like words or titles, so it fits perfectly with the album title. And then the next item over here, we can say how many characters so let's say 100 letters is the length of our title. Now for the rest of these, we're going to add some more details. So for the artist, this would also make sense to use a variable character length var char, we've got a year which will leave right at integer that's easy to work with, we could pick date, but year is pretty simple to work with. We're going to work with an image name, which is a URL. So it's the it's the HTTP slash Wikipedia address that we're going to get images from. And so this could be pretty long, let's leave it at, let's say 1000 characters for the maximum length. Then for the description, let's change this to text. Now the difference between text and var char is kind of subtle. So text is usually used if you have a large section like a text area on a webpage, where you expect to see paragraphs of information. Var char usually as a single line for like a name or an address or something rather short. So both of them are saving letters in in a format that looks the same to us. But they are different in the computer's mind. So text works well for description. Okay, so this thing is ready to go. So down here at the bottom, you can see that there's a save button, let's click it and cross our fingers. So as soon as I click save, you can see now I have switched to the tab called structure. So structure tells me what my database is made of. So you can see that we have all of these data types that we just created. And there's going to be no data. So if I choose Browse, we're going to see that the column headers are here. And there's actually no data in the database yet, I'm gonna change one item that I forgot to do previously, the first item is an ID number. And that is supposed to be a unique number that the computer generates. So I'm going to click the Change Icon. And let's see why I would want to change that. So I'm going to switch over here to this item that says auto increment, a underscore I've had is not artificial intelligence, that's auto increment. And when I select it, it's going to automatically choose the next number available for whatever record I put in. So the first one will be record number one, and then two, and then so on. It never deletes or goes backwards. So it'll go to infinity eventually. But we just want to make sure it's unique. So let's say let's click save. Now as soon as I click save, you notice there's a new icon here, it's got a little key. So that tells me that the ID is now a primary key, which is important, it means that it is the only item really, that has to be filled in, in all six columns. And it will be used to connect to this table to other tables later on. So that's enough to get us started with our database, let's add some data. So as I tried to add data, I click on the Insert tab. And now I'm going to fill in some items, I'm going to leave the ID blank because the computer is going to automatically provide the next number in the database. So the first one will be one. So we'll leave it blank. The title for our album is Abbey Road, and it's done by the Beatles, and the date is 1961. Now let's see if we can get some information from the internet to fill in the other items. So I've got a description and an image URL. Let's just use our friends at Wikipedia to help us out. So I'm going to search in Google for Wikipedia and Abbey Road. And let's bring up the article that tells us about Abbey Road. So first of all the image let's take a copy of that. So I'm going to right click it and choose Copy Image address. And let's switch back into our database. And now let's paste it here. So let's see Ctrl V and you can see that the URL to this image is from uploads at Wikipedia. So I don't have to save this to my disk. I'm just going to rely on whatever's hosted at Wikipedia, so if they delete this picture from the website, my application will no longer be able to show the image. So it saves me space, but then I have to rely on them not to delete my image. Let's scroll down a little bit. And you can see the last item here is a text field. And we're supposed to add a description. Well, here's a nice description, let's just copy the first part of the Wikipedia article. And let's paste it into our database. And now I don't have to type anything. Now I'm going to click go. So it says here at the top that one row has been inserted. And it tells me the SQL statement that was just executed, you notice it says Insert into has the table name, then it mentions all of the column names here. And then the next statement is values. And then below that, you can see all of the text. So if I click on Browse, I should be able to go back and see that now I have one item called Abbey Road. If I want to see one of these in detail, I can just double click it. And you can now see that I have a whole bunch of data in the description, which takes multiple lines. So that is how you can fill in a table. If you would like to look at the full series of this thing, you can either subscribe to the channel that you're looking at now on YouTube, or you can see the more extensive version on study coding.org, which is another website. So here's your homework, I want you to fill in about a half a dozen different albums. So I'm picking the Beatles, you can choose any one you like. But we need to have something that we can search for. So for the next video, I'm going to show you how to query data and you can search for certain keywords or filter it. So come back again, and we'll continue on our database application. Hi, and this second video of a series we're going to continue to explore building a SQL database application. Here's the index of all of the things we're going to learn. In the first video we worked through creating our first database. In this video, we're going to start to write some queries using the SQL language. In the future, we're going to talk about the front end connecting our database to that front end, doing some searches inserting records, creating foreign keys, joining tables together, creating what's called UML diagrams, making compound queries or multiple queries in one step, we're going to delete items from the database. And then finally, at the very end of this course, we're talking about features that we didn't get to, but you could probably explore. So this whole series here is available, not only here where you're watching, but also on study coding.org, which is my channel where I create applications. My name is shad Sluiter, and I welcome you to come to class with me virtually even where you can become a professional software developer. So this course that we're covering right now is focusing on databases. But as you can see from the selections on steady coding.org, that you can learn C sharp programming for websites or Java or Node js, and JavaScript and other languages. So many of my students have become professional software developers and have great jobs. And I'd like the same thing to happen for you. So come along, and join us in class and plan your future. So just as a reminder, where we're going with this application, this is the final product. And as you can see, we have a list of albums at the top of the page. And then we have a list of music or tracks at the bottom, and then a YouTube player so that we can hear the music. So in the past video, we created the database that goes behind this top control, which is a grid. And we're going to work on some queries this video where we can select items based on what's in the database. So searching for specific titles, selecting specific columns of this table. And then eventually, we're going to come and create this application that you see and display the data on the front end you might call it or on the form of the application. So let's go take a look at where we were. And then we're going to get closer to the vision that you see here on the screen. So as a reminder, we built our application using this tool here MAMP, which includes MySQL as its database. So I've got the two little green dots here indicating that the application is running and the database is being served. Now I'm going to click this button here that says open start page. So here is the start page. As you can see, the address is localhost MAMP. I'm going to the tools menu and choosing my admin. And I'm going to take a look at the database here. So music two is what we created in the last video. And so far we have an albums item so you can see on the screen now that I have several items that I imported in the last video so I have a bunch of albums from The Beatles. So I have the title, the artists the year the image name and the description, both of those items at the end. were taken directly from Wikipedia. So in this video, we're going to practice doing some selections. And then in the next we're going to do some programming actually in the form. So let's go to the part that says SQL. And we're going to learn how some SQL queries work. So as you can see, there is automatically some code written for us, which is very helpful. Let me zoom in a little bit, so we can see very well on the video. There we go. So select star, it says from albums were one. So let's make this even simpler, I'm going to take out the where collection, and just say this statement, select star from albums. And let's click go and see what that does. So what this is telling us is select everything that there is in the albums table. And you can see that the list here includes all of the items. Now we're going to do a little bit more sophisticated searches than that. So let's focus in on this last part where it says where so where is going to be a condition. So we can say, some name, like ID, and then I'm going to put in an equal sign, and then specify a specific number. So let's try three. So I just happen to remember that the first album in my table is ID three. So if I click on go, we're going to see exactly one result, because album, three here is Abbey Road. So let's try a different search. So I'm going to say where ID equals something else. So I'm going to put in 17. I believe 17 is one of my albums. So let's click go. And sure enough, it looks like Hard Day's Night in my collection is number 17. So that is one way to do a SQL statement. Now let's do another word, we can have a condition to say something like I want to look for where ID is anything greater than 10. And let's choose a go. And this time, you can see that I have two items, I have 16 and 17, which are bigger than 10. You can experiment with others, let's try something like a less than sign. So let's say if I say ID is less than 10. And what comes out now, so we've got ourselves, four of them. So 34567 Are the results here. So you can see that the SQL statements are able to select items based on a condition. All right, so now we're going to do another query. And this time, I'm going to search in the title of the album. So let's see if we can find anything that has the letter A in the album title. So this will hopefully find things like Abbey Road. So the way we do this is we put in the word album title after the word where so where album title. Now instead of an equal sign, I'm using a like operator. So like says we're going to match a partial match. And so I want to match something that is like the letter A. Let's see if this does anything. Now before I click the Go button, I'm going to make sure that this box down here called retain query box has a checkmark that will allow me to come back and modify this without retyping the entire mess. So let's go ahead and choose go. And I am disappointed. The results down below says it returned an empty set. So nothing like a. So what is missing here is the ability to search for wildcards. So I'm going to put in a percent sign before the a and a percent sign for the after, which means find a somewhere in the middle of the word. Let's see if that does any better. So I click go. So let's look at the results down below. And you can see that there are three results. It says here, Abbey Road, Yellow Submarine and Hard Day's Night are all matched. And so this here is a like statement that gives us a result. So the percent sign and the Like operator are both important. Now a couple other things that we're going to notice here is these backticks. So how do you type those if you're working in here application, because we're going to be copying and pasting these search queries into our Visual Studio application. These are actually optional. So I'm going to delete the backticks they're only useful is if you need them for holding a space in your in your query. So if I click go, the query works just like it did before. If I had something like album collection as my name for the table, I would have to put in those backticks as you see it here. But for right now, we don't need them. So I'm just going to back out to where I was before. Alright, let's do another go just to make sure that it's still working. Okay, so I have three albums. Now, what if I only care about retrieving a certain pieces of these data? So for instance, if I wanted to know the album title, and I cared about the year, just those two, for some reason, that's what my application needed. So instead of the star, I would erase that and change it to something else. So if I type in something like an A, you're going to see that there are suggestions here so album title is what I want to select I'm going to put a comma, and what was the other I wanted to year. So I'm going to put in the word year. And those two column names show up from the results down here. Let's see if that works. And I choose go. And the results now indicate that I'm only getting two columns in my response. So if I'm ignoring everything else in my database, I can do specific searches using the column names. Let's say I want to rename the search results. For some reason I can say album title as the word title. And year as let's make it up about date of publish. You can use any name you want, really, let's choose go. And let's see what that does at the bottom. So now you can see that the titles of the results now are renamed. So title and date of publish are the exact same column names, it's just that I've renamed them. That is something that people do frequently when they have to, for some reason, match up a property in their application to a column name in a database. And so here's a couple of examples that we've done. So let me summarize what I'll do on the screen here of all of the queries that you just wrote. So first of all, we did just a standard query where we selected everything, then we did a query where there is a greater than or less than sign, then we did a query with likes, and we indicated that we had to have wildcard characters before and after a string. If we want to get accurate results for the likes. Then we wanted to be able to select certain column names. And so the column names are specified instead of the star. So star gives you all columns in a table. And you can specify just a few columns if you need to, and ignore the rest. And then finally, we have some searches where we rename the column results where we say, rename the title as title, and the year as date of publish. So those are some of the things that you can do with SQL. Now there's a lot more things to do with the SQL searches, but I will leave those for another exercise. In the meantime, we're anxious to get this thing working in our application. So in the next video, we're going to create an app that will display the search results that we've done here, and put them on a form in a Windows app. So let's get started with building that app. Next. If you would like to look at the full series of this thing, you can either subscribe to the channel that you're looking at now on YouTube, or you can see the more extensive version on steady coding.org, which is another website. Hi, and this second video of a series, we're going to continue to explore building a SQL database application. Here's the index of all of the things we're going to learn. In the first video, we work through creating our first database. In this video, we're going to start to write some queries using the SQL language. In the future, we're going to talk about the front end, connecting our database to that front end, doing some searches inserting records, creating foreign keys, joining tables together, creating what's called UML diagrams, making compound queries or multiple queries in one step, we're going to delete items from the database. And then finally, at the very end of this course, we're talking about features that we didn't get to, but you could probably explore. So this whole series here is available, not only here where you're watching, but also on study coding.org, which is my channel where I create applications. My name is shad Sluiter, and I welcome you to come to class with me virtually even where you can become a professional software developer. So this course that we're covering right now is focusing on databases. But as you can see from the selections on steady coding.org, that you can learn C sharp programming for websites or Java or no JS, JavaScript and other languages. So many of my students have become professional software developers and have great jobs. And I'd like the same thing to happen for you. So come along, and join us in class and plan your future. So just as a reminder, where we're going with this application, this is the final product. And as you can see, we have a list of albums at the top of the page. And then we have a list of music or tracks at the bottom, and then a YouTube player so that we can hear the music. So in the past video, we created the database that goes behind this top control, which is a grid. And we're going to work on some queries this video where we can select items based on what's in the database. So searching for specific titles, selecting specific columns of this table, and then eventually we're going to come and create this application that you see and display the data on the front end you might call it or on In the form of the application, so let's go take a look at where we were. And then we're going to get closer to the vision that you see here on the screen. So as a reminder, we built our application using this tool here MAMP, which includes MySQL as its database. So I've got the two little green dots here indicating that the application is running and the database is being served. Now, I'm going to click this button here that says open start page. So here's the start page. As you can see, the address is localhost MAMP. I'm going to the tools menu and choosing my admin. And I'm going to take a look at the database here. So music two is what we created in the last video. And so far, we have an albums item. So you can see on the screen now that I have several items that I imported in the last video, so I have a bunch of albums from The Beatles. So I have the title, the artists, the year, the image name and the description, both of those items at the end are taken directly from Wikipedia. So in this video, we're going to practice doing some selections. And then in the next we're going to do some programming actually in the form. So let's go to the part that says SQL. And we're going to learn how some SQL queries work. So as you can see, there is automatically some code written for us, which is very helpful. Let me zoom in a little bit. So we can see very well on the video. There we go. So select star, it says from albums were one. So let's make this even simpler, I'm going to take out the where collection, and just say this statement, select star from albums. And let's click go and see what that does. So what this is telling us is select everything that there is in the albums table. And you can see that the list here includes all of the items. Now we're going to do a little bit more sophisticated searches than that. So let's focus in on this last part where it says where so where is going to be a condition. So we can say some name like ID, and then I'm going to put in an equal sign and then specify a specific number. So let's try three. So I just happen to remember that the first album in my table is ID three. So if I click on go, we're going to see exactly one result, because album three here is Abbey Road. So let's try a different search. So I'm going to say where ID equals something else. So I'm going to put in 17. I believe 17 is one of my albums. So let's click go. And sure enough, it looks like Hard Day's Night in my collection is number 17. So that is one way to do a SQL statement. Now let's do another word, we can have a condition to say something like I'm going to look for where ID is anything greater than 10. And let's choose a go. And this time, you can see that I have two items, I have 16 and 17, which are bigger than 10. You can experiment with others, let's try something like a less than sign. So let's say if I say ID is less than 10. And what comes out now, so we've got ourselves four of them. So 34567 Are the results here. So you can see that the SQL statements are able to select items based on a condition. All right, so now we're going to do another query. And this time, I'm going to search in the title of the album. So let's see if we can find anything that has the letter A in the album title. So this will hopefully find things like Abbey Road. So the way we do this is we put in the word album title after the word where so where album title. Now instead of an equal sign, I'm using a like operator. So like says we're going to match a partial match. And so I want to match something that is like the letter A. Let's see if this does anything. Now before I click the Go button, I'm going to make sure that this box down here called retain query box has a checkmark that will allow me to come back and modify this without retyping the entire mess. So let's go ahead and choose go and I am disappointed. The results down below says it returned an empty set. So nothing like a. So what is missing here is the ability to search for wildcards. So I'm going to put in a percent sign before the a and a percent sign for the after, which means find a somewhere in the middle of the word. Let's see if that does any better. So I click go. So let's look at the results down below. And you can see that there are three results. It says here Abbey Road, Yellow Submarine and Hard Day's Night are all matched. And so this here is a like statement that gives us a result. So the percent sign and the Like operator are both important. Now a couple other things that we're going to notice here is these backticks. So how do you type those if you're working in here application because we're going to be copying and pasting these search queries into our Visual Studio application. These are actually optional, so I'm going to delete the backticks they're only used sequel is if you need them for holding a space in your in your query. So if I click go, the query works just like it did before. If I had something like album collection as my name for the table, I would have to put in those backticks as you see it here. But for right now, we don't need them. So I'm just going to back out to where I was before. All right, let's do another go just to make sure that it's still working. Okay, so I have three albums. Now, what if I only care about retrieving a certain pieces of these data. So for instance, if I wanted to know the album title, and I cared about the year, just those two, for some reason, that's what my application needed. So instead of the star, I would erase that and change it to something else. So if I type in something like an A, you're going to see that there are suggestions here. So album title is what I want to select, I'm going to put a comma, and what was the other I wanted a year. So I'm going to put in the word year. And those two column names show up from the results down here. Let's see if that works. And I choose go. And the results now indicate that I'm only getting two columns in my response. So if I'm ignoring everything else in my database, I can do specific searches, using the column names. Let's say I want to rename the search results. For some reason I can say album title as the word title. And year as let's make it up, how about date of publish, you can use any name you want, really, let's choose go. And let's see what that does at the bottom. So now you can see that the titles of the results now are renamed. So title and date of publish are the exact same column names, it's just that I've renamed them. That is something that people do frequently when they have to, for some reason, match up a property in their application to a column name in a database. And so here's a couple of examples that we've done. So let me summarize what I'll do on the screen here of all of the queries that you just wrote. So first of all, we did just a standard query where we selected everything, then we did a query where there is a greater than or less than sign, then we did a query with likes, and we indicated that we had to have wildcard characters before and after a string. If we want to get accurate results for the likes. Then we wanted to be able to select certain column names. And so the column names are specified instead of the star. So star gives you all columns in a table. And you can specify just a few columns if you need to, and ignore the rest. And then finally, we have some searches where we rename the column results where we say, rename the title as title, and the year as date of publish. So those are some of the things that you can do with SQL. Now there's a lot more things to do with the SQL searches, but I will leave those for another exercise. In the meantime, we're anxious to get this thing working in our application. So in the next video, we're going to create an app that will display the search results that we've done here, and put them on a form in a Windows app. So let's get started with building that app. Next, if you would like to look at the full series of this thing, you can either subscribe to the channel that you're looking at now on YouTube, or you can see the more extensive version on steady coding.org, which is another website. Hi, and welcome to another video in this project here with our SQL database application. In this index, we're showing all of the things that we're doing in this course, in the first two videos, we created a database, and then we ran some queries. In this video, we're going to create the front end. So we're going to actually start creating an application in Visual Studio, that will be able to display the results of any queries that we do against our database. In the future, we're going to have these topics after we finish all of that they'll still be more left unsaid. So there'll be future plans that you can implement. So my name is shad slaughter, and I teach software development at Grand Canyon University in Phoenix, Arizona. And so I'm glad that you're here, you're going to become a professional software developer if you're able to do these kinds of tasks. So my website is study coding.org, where you'll find all of the courses that I've taught over the past years. So not only with web development and databases, but with mobile applications and security and other issues. So check it out. And if you'd like anything there, you can join for a small fee, and you get the source code. So let's take a peek at the application as it is in its finished state. So you can see that we have tables that are being searched and then we're playing some music. So these are all albums that are from the Beatles and then tracks on Each of those albums and then a player here, so we can see a YouTube video. Now what we're going to do and this video is we're going to focus in on this section up here where we're able to display the albums and do searches against them. So in the previous video, we set up the database. Now we're going to create the application that does the front end. Alright, so now let's talk about the different ways that people use applications when they are talking to databases. The app that we're going to build here is a very simple app. And that's why I chose this desktop app to do it, it doesn't take much effort to learn how to do this front end. However, if you want to become a professional software developer, you should probably learn how to create web applications. Now I have tutorials for how to do that in different languages. So C sharp is a very popular choice for creating web apps. And so the key word that you're looking for is asp.net. And that is the framework that Microsoft uses to connect websites to databases and create a fully functional web page that is an application. An even more popular way to create websites with a common language is Java. So Spring Boot is the key word that you should be looking for when you're looking for frameworks on how to build websites with Java. And of course, I have a tutorial for how to build a java web application. Another very popular choice for building websites is using the language php. And as a matter of fact, the application that you see in this tutorial is MAMP, which includes PHP as its default language interpreter. And so PHP is also available on steady coding.org. If you want to learn how to build websites with that language. Also, you can work with pure JavaScript, if you work with the Express framework on Node js, you can build a website in the same manner that other websites are built in other frameworks. So there's lots of different choices that you can pick from to build a website. Why are we then not building a website, you might ask, because this class is focused in on SQL and the databases that go with it. So it's very simple to build the application that we're about to right now. But if you want to be more advanced, if you want to be more employable, then you should probably take some of the other courses that I offer, or others do, on how to build websites and connect them to databases like MySQL. Anyway, we're trying to build this app. So what we have on the screen is our final product, and we're going to implement just the first part here. And let's get started. So as you can see, I have the database still running in the background here. So I actually don't need that right now. So I'm going to just push that off on to my other desktop. Okay, so let's get started with Visual Studio, you can see that I have two versions installed on my computer, I have 2022 and 2019. So honestly, 2019 works a little bit faster on my old computer. But since we're in 2022, we might as well get with the times and show you this bigger, heavier version that is going to have all the features we need. So here it is. This is the startup screen to say what kind of projects would you like to open or build. So I'm going to select the item that says create a new project. And I'm going to make sure that I select the right places. So the templates up here you can search for or you can filter them using these choices. So I want to do C sharp Windows app and a desktop, then I'm going to go down and pick one of these two options. Now, Microsoft is really good at confusing people about which one is which, what is the difference between a Windows Forms app and a Windows Forms app with dotnet framework? Well, in the in the description, this one says it uses dotnet framework, these two things are different even though they have very confusing and similar language. So I believe the choice that I made for this one to get the most current is the first item that says Windows Form app. And dotnet framework below is not the choice that is for an older version. So let's play try next and see what happens. So now we need to create this with some kind of a name. So I'm going to say this is a database SQL music app. And I'm going to leave the other choices the same you can see the location where I'm putting this is on my desktop. And let's choose Next. Now I know that I've chosen the correct application type because of the next screen. So the framework that pops up next is VS version 6.0 of dotnet. So it says long term support. So at the time of this video here in 2022. This is the most current version that I can add program. If you pick something that's older than this, frankly, it will work just just as well probably. But six Dotto is what we've got right now. And if you're from the future, this will probably still continue to work very similar. So I'm not doing anything that is cutting edge with the dotnet framework. So I'm picking the current one just because that's a good practice. OK, let's create it and then give it a minute to build everything. All right, everything came out. So we have form one here. And I'm just going to click the green button at the top that says make this application run I just want to test it out to make sure everything's good. And I should see an app on the screen. So sure enough, here comes form one. And I could Minimize Visual Studio in the background. So that way, I have my beautiful blank app to look at on the beautiful background. Now I'm going to put two things on this screen to get started with. And then we're going to start connecting our application to the database. So the two things that I'm going to put on here, our button, and then some kind of a data grid that I can use to display a bunch of pieces of data. So the button we're going to drag in from our common controls area. And I'm going to change the text of the button so that it says load albums. So at least the user knows what that button is supposed to do. Let's make it a little bit wider, so it all fits. And the second item that I'm looking for is in the data controls area. So I searched for something called the data grid view. And the data grid view is really why we're building this app in Windows Forms because it requires no configuration, almost none anyway, so it'll be able to display data from the database. And of course, I need to make this wider, I've got kind of a poor user interface design. When we're finished here, we're going to have a whole bunch of stuff on one screen, which is very easy to program. But really, if I were teaching this as a user interface class and a user experience, I would have multiple forms. But remember, the focus on this course is on SQL and how to make the database work. So that's why our app is going to look kind of busy on the on the main form. So I got this thing here with it with a form and it has a button and a grid control. Next, I'm going to have an album class. So this is a data class in object oriented programming that will connect our properties in the in the in the class with the column names in our database table. Now I'm recalling all of the things that I put into the database. As I create this album class, each property here is going to represent a string or an integer or something like that, some data type that is in an album. So the first thing that each album has is an ID number. So I type in PR O P, and then press TAB TAB to get the property of an integer. The first thing is called ID. The second property is the album's name. So you could call it album title, you can call it album name, I'm going to purposely not name this the same thing as my database. Because I want to make sure that we we, we handle certain exceptions or certain cases, when there's discrepancies between a class name and a table name. Generally, people like to keep them the same, but I'm purposely making them different here, just to highlight some things. Then we go on, we're going to have the artists name. So in my case, it's always the Beatles. And then the next one is a year. So an integer value for the year comes up, we're going to make an image URL. So this is the title image of the album. So this is not the URL for the song The the YouTube song yet, this is just an image of what we got a Wikipedia and also from Wikipedia, we grabbed the first paragraph, which is the description. So that also matches as a string type. So I've got a bunch of properties here that are corresponding to the table that we made in the previous video, all of these things about an album. Now I know that later, I'm going to add some songs to the album. But I want to keep this simple for this video. So I'm just going to leave comments now to say that coming soon, is a list of type track. And this list of tracks will be associated with the album. But right now, we're just going to put it in comments because we're doing one thing at a time. Okay, so we got ourselves a bunch of properties. Now I'm going to make some data and put it into our form. And we'll connect to the database in a while. But for right now, I just want to create some dummy data that will make sure that this grid has something to look at. Now I want to handle all of the database operations in another class. And it will be the kind of the interface between the app and the table. So I'm going to call this thing albums. Dao. So Dao is a shortcut for data access object, its job is to be able to do all the queries. And then it'll provide a layer between these, the front end and the back end. So just to make sure that we keep these things, kind of simple. In each video, I'm going to first of all construct this thing without connecting to the database itself. I'm going to create some dummy data, and then we'll display it in the grid. So we're not going to keep this code here for very long. It's just a test. I'm going to type the word list and an open bracket. And one of the nice features that you get of Visual Studio 2022 is the type of head help and it predicted correctly that I wanted to make a list called albums. It's a list of type album. What it did not predict was that I wanted to initialize the list so it has some list value in it. There's not an end D list, it's not a no list, it's got. It's got values. So it's a, it's a list of zero values, but it still is defined. Alright, so this is just a placeholder right now we're just going to create an empty list. Now let's go into form one. And let's create some events that will fill this list with some fake data. So I'm going to switch over to form one design. And I'm going to double click on the load albums. And so now we have an event for this button, click so button, click one. Now, this is going to be temporary code. We'll delete it in a few minutes, but we're just going to test it out. So I'm going to add the albums Dao class and initialize it with a new instance of this class. So when we click the button, it'll create a brand new list from scratch every time we click it. Now, as soon as I have the list created, I'm going to start filling it with some data. So let's create a new album. I'll name this thing as an album of a one. And after we create the new album, let's fill it with some properties. So the first item that we're up to is ID. So Id one sounds good to me, let's take the album name to be my first album. And I might also be the artist. So put my own name in there, the year is 2022. Let's put in an image URL of nothing yet, I guess I don't know what URL I want for the picture. For the description, let's put in nothing special. So now we have a one. And we might also make this have two albums since it is a list. So I'll copy and paste album one, and I'll name the second one is a two and change the first name. So it is my second album. Now what I'd like to do is add the first song to my albums Dao. So I'm going to try that, but I'm going to come up with a problem. So it says, albums Dao dot albums. Now there's a list of albums in there, but I can't see it for some reason. But it allows me to continue ahead typing and add a one. So why can't I actually add something that album's a one, let's see, it says in my problem help, it says it's inaccessible due to its protection level. So means I probably made it private. And I could turn it public so that I could actually do the thing that I'm trying to do now. So let's go back to the album's Dao. And sure enough, let's go here and change this to public. And we'll save it. And now what happens when I come back to my form one, so the error goes away, looks like that was the problem. And we've solved it. Now very helpful, I go to the next line, and I type in an A, and I get some type of head help. And it guesses that I want to add the second album to the list. Okay, great. So all of that work was to simply add two albums to our list of data. Now, I want to associate this list with the control that's on the screen. Now one of the tricky parts about building an application with C sharp and Windows Forms is called a binding source. So binding source is the ability to connect a list of items such as albums, to the control, so the grid control. And so I'm just going to define this here. And you're going to kind of follow along to see how the format works. So binding source is the class name that is allowed to connect these two. And I'm going to let the type ahead helped me to call it a new binding source. Now I'm going to rename the binding source because I'm going to specifically have one for the album's and another one later for the tracks. So I'm going to name this one as the album binding source. So to make that work, or make that binding source work, we're going down to the click button action. And so after I've added a couple of albums, I'm going to create a connection between the lists and the grid view. So this is where the binding source comes in. So I'm going to define this thing as the album binding source dot and get a property called data source. And I'm going to set that equal to be the list that I made. So it is the album's Dao. And it's not as smart as I thought, I have to add a dot and tell it that the list is called albums. So putting these two together should allow the grid source to show these two new albums. Now there's one more final step that we have to do, we have to tell the grid view that this binding source is associated with it. So I type in group data grid view, one dot, and it has a property called datasource. And so programmatically, we're setting it up to be equal to this list. Okay, so now all of this connection should be done correctly. At least I think it is. Let's try it. I'm going to run the app. So I click the little green triangle at the top. And let's see what happens. So I've got the app. Let's see, I'll minimize the rest of this. So it's kind of out of the way. And what is Cortana get out of the way. Okay, so here we go. Now I'm going to choose Load albums, and look at there. So the binding source of these two albums now displays all of the squares in like a spreadsheet grid. So that's the magic of this control. It automatically creates the number of columns it needs, and if these get To be too long, it allows it puts in scroll bars. And so this is easier to do than if you're trying to create some kind of a web page. Most of this binding source and data source does the work for you. All right. So that is not exactly what we want. We have a couple of albums, they're nice albums, but they're not the real ones. What I want to do is get all those Beatles albums that are in the database. And so we have still not connected our database to the control. Although you can see we are almost there. So in the next video, we're going to do that we're going to set up a connection between the SQL Server and the app that you see here. And then we'll get the data live from the database. So let's take a look at the index of where we've been. So you can see that we're on part three, which is building a front end. So the front end is got some pieces to it, it's not complete, but at least we can see where we're going. What's next. Next is to connect that app that we just started and connect it to the database. So we're not using fake data anymore. We're getting the actual albums from The Beatles. And so that will be the next task that we do. So we're on Part Four coming up. Hi, welcome back to another SQL application tutorial. So we're in an application series, which is about building applications using MySQL. We did Part Three already. And now we're moving on to part four. So in the previous videos, we've created some tables. And now we're about ready to connect those databases to the front end of our application. So the other parts that are coming up are going to help us do searches and to make more complex queries. And eventually, we're going to get to the point where we have a fully functioning app. So if this is interesting to you, then let's keep right on going. My name is shad Sluiter, and I teach software development and computer science at Grand Canyon University. So many of my students are using these tutorials to become professional software developers. So congratulations on choosing a good path for a good job, I hope you have a great future. Now here is the application where we left it from the last video where we had a simple set of fake data, we had two albums in our data access object. And they really don't relate to the database yet, however, they look like the albums that we put into the database. So we're getting closer. So by the time you're done here, you're going to see the application show all of the Beatles albums on the screen instead of these two fakie. Alright, so let's go modify this data access object and make it work like it's supposed to. So we're going to delete all the junk that was already in our Dao, and we're going to add some new things. So in this first step, we have to make a connection to our database. So every database has a user name and password authentication or some kind of a way to make sure that the right people are looking at the database. And so ours is going to be very simple to start with. The first property that we put in is called the data source. So the data source is where does the server live. Now if we were on the internet, we would put in some kind of an IP address, or we would put in Amazon aws.com, or some detailed long name for a server. Since local host is here, that means we're talking to the MAMP server. So remember, we have this map server running here. And this thing is running on localhost. So we're, we're essentially doubling up our computer to do two things. One is the application, and the other is the database server. In the real life. Sometimes these are divided into separate machines. But anyway, we're using localhost for now. Next, we need to specify something called a port. So let's bring in the map server again and check out to see where this might be. So let's go to MAMP. And choose Preferences. And let's see what else we can do. It says a bunch of stuff about PHP, what I'm interested in is ports. So in this tab, here, we can see that there are ports. So 80 is currently the the setting for my web server. And then 3306 is the port for our SQL database. So ports are just numbers that computers agree to communicate on like it's like a channel. It's like on your TV when you have channel 15.1 and 15.2. It's like they're the dot part is the sub channel or it's the port number for a major channel. So our port number here is 3306. Now we need to specify how to log in to this server. So user name and password is what we need to provide. Now I know that the username and password for my particular MAMP installation is root and the password is root. If you're using USB web server or wham you might have something slightly different so check the help files or the installation guide. If this does and work for you. So root and root works for MAMP. The last item on the list is telling it which database it needs to connect to. And as you might recall, the name is music two. So just bringing up music two is right here, just refreshing our memory of what we have for data. Now, the way a data access object works is you create a bunch of methods that are going to perform actions for the main program. So the first action that I'm going to create is one that will get all of the albums from the database. So the return type of this function is called a list of type album. And then appropriate name for this would be Git or fetch or search for all. So I'm going to name it as get all albums, because I think that's the most clear. Now to make this work, I'm going to start by creating an empty list of albums. And then I'm going to return that list at the end of the function. So I'm going to rename the list as return these. So the next step is we're going to create a connection. So I'm going to type in something that will not work at the first it's called My SQL connection. And it's a new class. So I will create a new class and one parameter in it is the connection string. Now, why doesn't it work? Well, by default, Microsoft expects you to be talking to a SQL Server, that's their version of SQL. But we built this thing with MySQL, so we have to go get a extra piece of software called the dependency to make this work. So let's see if the computer is able to help us out in our problem. So I'm going to hover here, and it says, I have no idea what you're talking about my SQL connection doesn't exist in my list of known classes. Let's see if there are potential fixes. So the first few fixes are, you can generate this and create your own. However, you can see that there is a MySQL dot data or data, different dependency. So let's go ahead and choose this. And it says I'm going to install this. So how is it going to do that, let's go ahead and choose Find the latest version and install it. Now I'm going to just sit and watch and magically, something will happen. And the problem disappeared. Where did the problem get resolved? Let's take a look at how that is resolved. So you can see that the error is gone. What I'm going to do is right click on the project, go down to New get packages. So manage nougat packages, is the dependency manager in SQL. And you can see that it says here I'm looking at installed packages. And my sequel, that data is already been installed. And it comes from the Oracle Corporation. Now, Oracle actually owns MySQL, they paid quite a bit of money for it. And I guess owning free software is maybe a status symbol, do they actually make any money on MySQL, they must somehow I'm not quite sure how they do it. But anyway, they are the owners of MySQL, and they manage this extra dependency that allows us to connect using their classes here. So apparently, it's working, I'm going to close the nougat Package Manager. Now the next step that we're going to do is we're going to create an open connection. So I'm going to type in my connection and type dot open. And that will log in to the server. Now there's going to be a whole bunch of more stuff coming up here, we're going to do a SQL statement, and then we're going to execute it. But I want to first check to make sure that everything is connecting properly. So I'm going to skip some lines and then just type return, and then the list called return these. So right now it's still an empty list, but it will not be an empty list for very long. Now I want to make sure that I can test this out. So I'm going back to form one in the code here. And I'm going to start removing all of the junk that we put in earlier. So I'm going to delete the majority of the junk that we put in earlier and just leave this binding source as the item. So I'm going to create a new instance of the album's data access object. So we'll do albums Dao equals a new albums Dao. Now, I want to associate the datasource of this grid with the event called get all albums. So I'm going to just erase the one piece of code here at the end of the write and change it to get all albums. Now remember, get all Elbaz is still returning an empty list, but it will try to connect to the server let's let's just run this and see if there are any errors. Okay, the application is up and running. Let's go ahead and choose get all albums. And it returned an empty list you can see it must have curl must have connected correctly. Let's let's try to break that and see if there's a problem that we can resolve later. So I'm going to go in and change something. So local host I could change any one of these details so localhost exe, or I could put in the wrong port number or I could put in the wrong username. Any one of those will cause this to break. So I'm going to put an X in for the server name and then try it again. So now when I choose Load I albums, what happens? There, I finally get an error, something's coming up here and what is it? It says no such host is known. So you may have gotten a different error based on whether you change the password or the port number. But this is telling me that the open connection did not work. And so the program stopped. So that's one way to test to see if you got the right connection string or not. All right, let's fix that. So I want it to actually work. Now we're going to finish off with this get all albums. So the next item down is we're going to write the SQL statement. So I'm going to create a new, a new instance of an object called a my SQL command. So I'll name it is command. The values that we're going to create when we do the instantiation is two parts. First is the string, which is the actual SQL statement. So we're going to say select star from the albums table. And then we have to provide a comma and then the connection that we defined earlier. Next, we're going to use a USING statement. So there's USING statement, it's kind of like a while loop, it says use this object, and then when it's done with a loop, then destroy the object. So what I want to do is create something called a MySQL data reader. And this reader will be the result of executing the command that we defined up on line 25. So this reader then can be used in a loop to be able to fetch each line. Now we're going to go through each column in the data table. And we're going to associate it with a new property in a class. So we're going to make a new album, I'll name it A. And for each of the properties, we're going to go through the column names. So ID is going to be equal to the reader dot get, and we want to get the right type of value. So this is going to be an integer. So integer, 32 is the size of the data, you could use other integer types like 16. But 32 seems to work. So we're gonna say at position zero, expect to see an integer. The next item is the album name or the title. So that is coming from reader dot get string at the second column, which is item number one. And then so on, we go down to the artists name, and the year and the image URL. And then finally, the description. Notice that the index number goes from 01234, and five, so there are five numbers for six columns of data. And we have to match up the data type accordingly, otherwise, the program won't work. After we have the new album, called a we want to add it to our list. So return these dot add is the command that will attach this as another item in our search results. Now to clean up after ourselves, we want to close the connection. So SQL might be smart enough to close the connection for us. But it's always supposed to be here. So after you're done query, do connection dot close. So this time, we should be able to run the app. And instead of getting just empty data, we might see something. Let's go ahead and try it out. Okay, the app is up and running. Let's see what happens when I choose Get load albums. Okay, and there they are. So I got lucky and everything worked out fine. Now I'm going to create an error just to see if we can do some troubleshooting. So let's close this. And let's say I changed one of these things, instead of a five, I put a six. What would that do? What kind of an error Do you suppose that would make? It probably won't work. But let's see why. Let's go ahead and choose run this. And now it says here you have specified an invalid column ordinal. So in other words, I only had up to five and you chose six. So that one here is I suppose that's an English. Sometimes computer errors are hard to understand. Let's say instead of get int i accidentally put in get string. So let's see what get string will do for us what kind of an error will that too. So get string is not it's not given to let me It says an ID is not a string. Here's a really common thing. So let's say I make a mistake in my SQL statement. So instead of albums, I put in the word album, and let's see if that makes a difference. Let's run that. So now I got the application running and I choose go. And we got an issue. It says here an exception it says table music to dot album doesn't exist. So if you do a typo in this statement right here, things aren't going to go well either. So albums is the name. Another good practice in writing SQL statements is not to rely on the star for selecting all items from the table. This will make your code more rigid, you might say or more, more specific, but it will also avoid ever so if I change the star to ID album, artists and year and all the rest of them, it should still work the same if I've typed those correctly, so I'm going to try it again. Run the app and when I choose Get albums, I've got a problem. It says here the problem is that there is no such thing as image you URL, what is the image URL supposed to be? So let's bring that up. Here it is. Image Name. Okay. So that's what it is. Let's fix that. Okay, so image name should work. Let's try to run it again and see what happens this time. Okay, here we go load albums. And this time, they all seem to work like they're supposed to. So we've got ourselves a working SELECT statement, we've got the first section done of our application. Now, it would be kind of nice to be able to search like, right now I'm getting all of the albums, how do I do a search so that I can only get some albums of the ones that I'm interested in? Well, that's exactly what we'll do. In the next video, we'll do a select statement using the where clause, so that way, we can do searches on our collection. Hi, and welcome back to another edition of our SQL application tutorial. In this application, we're demonstrating how to use MySQL in various different stages of app development. So we're up to part five, which is how to create searches. So if you haven't caught the first four parts, how to set up an application, how to set up a database and how to do some queries, then go back in that playlist and check those out. Right now we're trying to create a searchable text box where we can put in a partial word, and then get some search results from a list of albums. So there's a whole bunch more stuff coming up. So look at all of these subjects that will make a complete app. And then of course, when we get to the end, I'll give you some challenges so that you can continue on with your learning. My name is shad Sluiter, and I teach software development at Grand Canyon University in Phoenix, Arizona, a lot of my students have become professional software developers, which I assume is what you're trying to do here, as well. So stick with me. And you can also look at my website at steady coding.org, where you can become a software developer with C sharp or Java, make yourself mobile applications or other things that are important to the career of a software developer. So I'm glad you're here. Let's get started now with doing searches so that our application is more powerful than it was before. So I'm bringing up on the screen here, everything that we've created, and also a preview of where we're going before the end of this video. So first of all, you notice we have MAMP, which is our database server. And then we have the Visual Studio application that we have in front of us. What's new in this video is this box up here that says Search. So if I were to put a letter in like the letter A and choose Search, you're going to see that only albums in my database that have the letter A are given as a result. So you can see Abbey Road, Yellow Submarine and Hard Day's Night. Let's try something else like M y and see if there's anything that has the word my in it doesn't look like it. Let's try another one. So let's try H E. And you can probably guess that help is one of the songs that The Beatles wrote. If I leave the search box empty and choose Search, I should get all the results. And the button here that says load albums still continues to work and shows all of the albums in my database. So that's what we're going to build right now. Okay, so we've just gone back in time for one video. And I am now left with the state of the application as it was before where we had a load albums button but no search box. So let's start from this point, and then move forward. So let's start with the user interface. And then we'll do the back end programming in a minute. So the user interface changes are pretty simple, we're going to put a button on here and put the word search on it. And so that way, we can at least have an event. So let's put the text of search on the top. And then we'll also put a text box on the screen. So a text box is where we can enter in some values and then use the contents of that box to do the searching. Alright, so that's all the interface changes we're going to make. Now let's start programming. So I'm going to go to the search button and double click it. And we have a new button click handler button to click. Now for the first version of this button, I'm just going to steal all of the code from button one, which was to search for everything in the database. So let's just copy and paste this here. And then we'll make some modifications so that way it will search for only specific songs. So the one important change that we're going to make here is changing the method that we're going to rely on from our data access object, our Dao, so instead of searching for everything, I'm going to rename this method and provide it one parameter, we're going to name it as search titles, and then provide the contents of that textbox. So the contents of the textbox are textbox one dot txt. So those are the only changes we need to make here in form one to make it work. But we need to now add this event here because you can see that search titles has an underlying read on it, which means it doesn't exist yet and we need to go fix that now. So let's go open up albums D AIO and let's go check to see what we have here. So I'm going to start selecting, we've got this get all albums method. And so I'm holding the shift key and the arrow to select all the way down to the very last. So this is going to be duplicated and then modified slightly. So I'm pressing ctrl C. Alright, so now I've got the items copied, I'm going to create some space here, and then Ctrl V for paste. Let's see, if I got one extra bracket it looks like so let's take out him. So that little red thing goes away. Okay, so now I've got a duplicate of the method called get all albums, what I want to do is change that to search titles. Inside of the method, I'm going to put in a parameter called string, and we'll name it search term. So we're expecting to get some information from the form. So that way we can query the database based on a keyword search. Now let's save this and go back to form one CS and see if the error goes away. So sure enough, now search titles with a textbox. One text parameter is now valid. So the two different methods, two different methods sync up well. So let's go back to the DAO and continue working. What I'm going to do first seems like the logical thing to do, but we will, we'll have errors, but let's type it out anyway, just see what the first intentions would be, if I were programming this without some documentation to look at. So what I want to do then is in the sequel search string, I want to add the WHERE statement. And I want to say I want to search for all albums where the album title is like the search term. And so will that work, if you remember from previous videos, that we're missing the percent sign, which is a wildcard character. And so that's the first problem that will cause this to error out. But there's another, but anyway, I'm going to try and test it anyway. So let's put in a search term with a percent, and then a percent to follow it. And let's see if that works. I predicted it won't. So let's just test it and prove my point. So I'm searching for the letter A, and then type search. And sure enough, it doesn't work. Look at the error. It says you have something wrong with your syntax right near the word search term at line one. And so now we're baffled. We tried to do this with other searches in the past. But for some reason, our application doesn't behave like it did in the PHP myadmin screen. So what are we going to do, the first thing I'm going to do is stitch together a couple of things into a single string. So I'm going to name my string as search wild phrase, which is simply the search term with an addition of a percent sign at the beginning and another at the end. And so that's step one, to make this whole string work a little bit better. So now in my brilliants, I think well, I can just take that string now and attach it to the SQL statement that I had before. Will this work? Let's try it and see if it doesn't. So I run the application. And I type in a letter and choose Search. And sure enough, we have the same error. So I'm not getting any closer. Well, I am. But I'm trying to show you the common errors that I went through as a first time programmer. And what you're probably trying to do as well. So we're going to abandon this idea and move toward the solution. A further step toward the solution is to get rid of this trying to concatenate strings together and use a placeholder. So I'm going to put an at symbol and search to make this happen. So let's figure out where I came up with that. So actually, I looked in the documentation, believe it or not, and Microsoft tells us what to do. What we're trying to prevent is called SQL injection attacks. And so this great article is very simple to understand if you can go through the examples. So let's take a look at what they had for an example code that they recommend not to do. It's exactly what I was trying to do, where you take a item and you try to concatenate with plus signs. And when you're done, you have a SQL statement. So it's vulnerable to hackers doing things that you can do is called SQL injection attacks. And I'm not going to go into that right now. But if you look in the description, I can show you a course that deals exclusively with SQL injection and cross site scripting, all kinds of security issues with programming. But for right now we're just trying to get this thing to run. Let's go down and we're gonna go past example two, because it's doing the same thing where we're concatenating strings. And so we're trying to find a solution. And sure enough, it comes up with one. So the solution is this. It's called parameter queries. And the way it looks like is that we have to come up with something that has an at symbol, and then we use let's see, where is it? We Have something where we do parameters right here. So we're going to have a command that looks like that parameters add with value. So we're not going to do the exact code here, but something very close to it. So take time to read this article. And you'll be a lot smarter when it comes to using a sequel app and avoiding SQL injection attacks. So to make this work, we're going to split up our command item here, our command object and put some extra parameters into it. So I'm eight, I'm cutting out the sequel string, and in the line 64, where it says, Make a new command, I'm just going to leave it with no parameters just to parentheses. Then in the following line, I'm going to say I'm going to add a parameter text or a command text to this, and it is the SQL statement. And I'm going to make sure that I have the at search parameter in there. So it's a placeholder for something else. Then following that placeholder, we're going to put a new line in that says command dot parameters dot add with value. And then I can tell the computer that at search is really a substitution for this search wild fruit. Another line that we need to do is tell the command what connection we're using. So I'll say command dot connection equals, and then we have a string up above called connection, which was defined like way up at the beginning of the of the of the class here. So we have a connection, we have parameters and command text. Let's see if this works. So I got the app running. And let's try letter A, and do a search. And we've got some results this time. So it looks to me like we've got exactly what we want. So let's try and search for the letter B again, and see what we got. Let it be let's try the word L E. And do we get anything we got one song. And if I take all of the items out and do a search, we get the whole results. So this to me looks to like a search results that is successful. Now in the next video, we're going to add a picture over here. So we're going to have the album photo on the screen, which is a pretty simple case, we've already got the image URL in our database. Now can we make it show up on the form? Well, the answer is yes. And it's not very hard. Hi, welcome back to another part for our SQL application tutorial we're doing here. In this video, we're going to continue on building the interface for our app. So in the previous videos, we've done all of these items that you see on the screen, we're at the point now where we can search for an album in our database. So what we're going to do in the future is to continue on to build all of these features, until we have a complete idea of how to use MySQL. And so continue on and will become a software developer together. So let's take a look at the application as we're going to finish today. So you can see we have a little icon on the screen that shows a picture of the album cover. And every time I click one of the rows in my table, you can see that the album art is displayed over here in the picture box. And so a couple of things we're going to have to do to make this happen. First of all, we're gonna have to put this picture box on the screen, of course, the second thing is that when we click the grid, we're going to have to find out what row we're in, and what column we're going to get the information from, and then load that into the picture box. So when all that's done, you'll have yourself what you see on the screen here. So let's get started with a code. Okay, so I've reversed the application. So now the picture box is gone. And we're ready to start adding that feature now together. So I'm going to close this and start the process. So here's the process, we're going to take this form and put a picture box on it. So let's go find the picture box, first of all, and let's see it's called picture box right there in wind forms is as simple as dragging something onto the screen and changing the size. Okay, so now let's check to see if we can add something to that box. So you can see that it's it's empty right now. And there's multiple ways to put a picture into a box, what we're going to do is use the URL from one of our videos. So to get one of the video URLs, I'm going to go to the PHP myadmin page. And then I'm going to find the, let's say the first one here. So let's do a CTRL A to Ctrl. Select it all Ctrl C to copy it. And then let's come back into the Visual Studio. Now in here, we're going to be able to add this and I need some kind of an event. To do that for right now. I'm just going to choose Load albums. And at the end of the album, I'm going to load this so let's see if I type in picture one box one and then there is a command for load I believe it is yes. So it's as simple as telling it what URL you want. So I'm going to put in a quotation marks. Paste in the string and assign Aculon. So that should load an image from the internet, it's as simple as that. So let's go ahead and run it and test it out. So when I click the Load albums, you can hopefully see there is the picture. So that is Abbey Road. If I click any of these things, it doesn't change. So we've still got that to go. And it appears that only the top left corner of the album is showing. So those are the things that we still have to do. But we have proven that we can load a picture from the internet. First of all, I want to change the property of this picture. So let's click here. And let's go down to the bottom where it says appearances. Now what I'm looking for is a way to scale this image. And I'm not quite sure which one was. Here it is it's called size mode. So right now it's set at normal. And let's look at the choices. So stretch might be the first thing you think of, but you can get all kinds of weird warped pictures. The one we're looking for is zoom, and the others you can experiment with. But for right now, we're just going to try zoom. And let's run it again to check to check to see what kind of a picture look that looks like. So I click on Load albums, and there I get the whole photo. So it's scaled proportionally. And it automatically adjusts to the frame. Very nice. So I'm not really interested in seeing Abbey Road every time I click the grid, I want to see the actual picture that I'm supposed to get from the grid. So let's click the grid once. Now, you might be tempted to double click the grid if you want an event to occur. So that's like clicking a button. But in this case, what I want to do is to go to the properties and choose the events and look at my choices. So the mouse has a click cell, click contents and cell double click. So let's do click cell. So I'm in this square here, I'm just going to double click here. And now I have a sell click event. This one works better than the other two, I can guarantee you that. But just take my word on it. So now what I want to do is check to see if this actually works. So the simplest way to do a test is to do a message box and show it. Let's go ahead and test it out. Okay, so I'm going to load some albums. And then I'm going to click something. And sure enough, it says clicked. So you can see I can click on any cell in the grid and I get a clicked message. Now how can I tell which cell I clicked right now it's just kind of generally saying you click the cell. So the key here is the word sender. If you look into the parameters that are the click event has sender is the item that is sending this message. So I'm going to save the sender as a data grid view object. So I assume that the sender is always the data grid view. So I will just say that is equal to the sender. Now you can see that there's a problem. So sender is a generic object. And data grid view is a specific type of object. So to make this fix occur, it says, Would you like to do a cast. So let's go ahead and choose this and add the explicit cast and tells it now that I guarantee you that if this receives a click event, it's coming from a grid view. So now I want to capture some values about the grid. So for instance, the row number, so let's create an integer variable, and name it row clicked, we're going to get it from the data grid view dot current row dot index. So current row is the current row that's selected, and index is the row number. And then I'm going to do a message box to show some feedback. So we can see if this is actually doing what we want, we want to get the row number numbers. So let's go ahead and click the Go button and see what this message box shows. So we show the album's first and now when we click something, you can see that it says I clicked row number three, now count these down. So Abbey Road is zero, let it be as one help us to and then Revolver is three. So it is not showing me the album ID number. It's showing me the row number. So this is row number zero. And let's see what the last one is. So it's not 17 it is row six. So now that I have the row number, I can go get the column. So if you if you count the columns, just like the rows we have ID is zero. I will name as one artist two, years three, and image URL would be fourth. So the fourth column of the row that was clicked contains the string that we want to put in the picture box. Okay, let's see if we can get all that in code. Okay, so here's the process. Now we're going to create another string and it's going to be called Image URL. So we're going to get this from the data grid view. And the type of head help is really pretty much all right on here. It's pretty close. I want to get the data grid view dot rows, and then in square brackets tell it which row number which is the row clicked. Then I want to get the cells so a little bit off on this one but cells and I want to get cells, the array of cells at position four And that's actually the fifth column. And then I'm going to get the value and then change it into a string. And then I've got myself the value that I'm trying to put into the picture box. Let's put a message box out to test this one, and see if that's doing what we expect. Let's run it again. And check. Okay, away we go. So we click on albums. And let's click something. So it says here, you click row two, and the image URL is this thing. Is that indeed what we're after? So it looks like the Beatles album cover. Oh, help. That's the word. Help. Okay. Let's try another one. Let's go to the Abbey Road. And do we get anything. So we got zero. And then it looks to me like Abbey Road is in the filename. So we've we've got the right URL. Now, all we have to do now is put it into the picture box. All right, so I'm a little tired of hearing these picture box, things show up. So I'm going to comment them out. So they're out of the way. So the last statement that I'm looking for is Picture Box, one dot load. And then in parentheses, I have to put the string of that image. And so that is a string that we just created a minute ago. It's called Image URL. So that should load the picture every time we click a cell. Let's give it a shot and see what happens. Okay, the album's are loaded now. And let's pick somebody let's try let it be. And it looks like it help. And revolver, Rubber Soul, they're all coming up. Nice. So all of my pictures are being loaded, based on the image URL field that is over here in column number five. So the next video that we're going to do is going to be inserting new records. So we need to create a little data input form and add new albums as the user chooses to. So let's do that in the next step. Hi, in this video, we're going to continue on working with this tutorial on the SQL database application. So we've gone through a few sections already. And so we are about ready to do inserts. So we've done searching, we've done creating a table. What we're going to do now is add records to a SQL database. And so we'll create a form, and then we'll execute an INSERT statement using SQL. So here's the rest of the tutorial. So make sure that you stick around to watch the entire thing. My name is shad Sluiter, and I teach software development at Grand Canyon University. We're in the middle of a process here to make you a software developers so many of my students have already graduated and gone on to get great jobs. And I'm trying to add you to the list. So make sure that you subscribe, or go to my website, which is steady coding.org, where you can see all of the things that we do in class. So welcome aboard. So now I'm going to show you where we're going to end at this video. So you can see the application that we have in front of us here now has a data entry form. So I kind of shoved it into the corner up here on the left. And you can see that I've already filled out a whole bunch of items. Let's see where I got that. So if I switch back into Wikipedia, you're going to see that I've been copying information, such as the description, and the image URL, and the year. So we have a new album to add to our database. So I filled in all of these fields here. So Sergeant Pepper's Lonely Hearts Club Band and the rest of the items. Now I click the Add button, I get a pop up that says there's been one record inserted. Now I want to either click Load albums to see everything. And you can see that number 19 is Sergeant Pepper's or I can choose the search. And you can see that as also shows up here. So let's just type in an S, G, and search and we got one item. So it did insert correctly. And now I can search for it. So that's what we're going to have by the time we're done with this video. So let's get started with modifying our application to look like this. Alright, so you can see that I've got the application on the screen as we left it in the previous tutorial. Now it's time to put in this insert form. So I'm going to drag in a whole bunch of things and arrange them inside of a container. And then we'll add some code. Okay, so now it's time to set up the rest of the controls on our form. So let's put some space up here in the upper left corner, so we can add some input fields. First of all, take the image for the album and drag it over to the right, so we create some space. Now I'm going to use a control container. So a container is one that is kind of a grouping thing. So we look in the grouping section or the container section. And we find group box one, so we'll drag him in. So the name of this form or the sub form really is called add album. So we'll set the text to add album so that we know the user knows what's going on. Now I'm going to put in a bunch of labels. So these labels are going to indicate what we're entering. So we're going to say Skip the ID number. First of all, just leave that out, we're going to start with the album name. And so let's put in an album name, change the text value to album name. And then we'll do artists. And then we'll follow it with a year and the image URL and the description. Now I want to arrange these. So we'll set the left margin to be all equal. And then we'll also set it so that they're vertically spaced. So there's some controls up here in the top in the ruler, or the in the control bar, you might call it, where you can do all of these arrangements. So you don't have to be very good at arranging things, that computer will help you with that. Next, we need to put in some text boxes. So let's drag in a text box for album name. Now instead of leaving the name as textbox, to as you can see here, I'm going to change the name of the control because we're going to reference that in our code. So my convention from working with other types of forms is to use a prefix on every one of my controls. So TX T indicates a textbox, and then an underscore, and then a description of what that's for. So txt underscore album name, is what I'll use for the next one is Tx T underscore and we'll say artist or album artist. And so we'll put in another text box for the year. And we'll set that to be year and then TX T is the prefix. Let's go with image URL and description following the same pattern. So TX T, is the prefix that I always use. Now you might have a different process and many other tutorials will lead you in another way. But this seems to work for me. Let's also arrange these so that they're all nice and straight. So we'll choose a left margin so that they're all lined up to the left. And we'll also spread them vertically, then we can finally maybe change the width, so they all match, and then resize the group box so it fits nicely. And then let's try to squeeze it into the upper left corner of our form, we might have to move some of the other controls out of the way to make this happen. But we will see that there might be enough space there. Now this is kind of a poor example of user interface design. And we're trying to squeeze everything under one form, simply because we're trying to make this as easy to program as possible. This is not the way that you would probably want to treat your users if you were making a real app. Can you imagine if you had a phone app with all of these controls jammed on one screen? Not a good idea. But we're focusing in the database, right? This is a sequel class, and we're making the simplest form possible. Okay, so we got the form setup, now we need a button, and then an event to go with it. So that way we can talk to the database. So the last item is to stick in a button called add. So let's create a little more space in our group box. And then we'll drag in a button. And let's just set the text of the button to the word add. I'm going to double click on the buttons. So that way we have an event, a click event. And now we're going to program something in here that will talk to the database. So the first thing I want to do is capture all of those text inputs and create a new album object. So let's define the new album object. We'll call it album. And I'm going to create a new one using an inline constructor here. So we're going to create all of the properties except for the ID. So the first item is the name. So the name of the album is going to get its value from the text control. So now you can see why I use txt underscore as the prefix for all of my text items. They're really easy to find on this help when you do type cyst. So TX T artists know it's called album name, and then we get the text property of that textbox. The next item is the artist name. And let's choose the txt artists name and ensure that you put dot txt, then we're going to use the year now this one is going to flag an error because a year is an integer and we're going to assign it a text value. Don't worry, we'll come back and fix this. So we're going to get the text from the input box, then we're going to have an image URL. And we'll get the same idea. So text image URL. And then finally we have the description. So now we have all of the properties except for the ID associated with something from the form. So far, so good. Now for that year, what are we going to do for that? So the command we're looking for is in the integer 32 class. So we'll type in int 32 dot, and you can see that there is a method called parse, and it's Its job is to take a text field and convert it into an integer. So we'll just surround the text property with parentheses and use int 32 dot parse. This should all work as long as the user fills out the fields correctly. So we're not doing any error checking here. Remember, this is a simple app, and we're talking about the database issues. And we're not trying to make a user application so much. Now that we have an album class, we can now try to add it to the database. So we're going to create a new instance of the albums Dao, which is the class that we created in previous exercises. So we're going to create the new instance. And then I'm going to call on a method that doesn't exist yet. But we will create that in a minute. So the method is add one album, and in parentheses, we use the parameter album, the one that we just got from the form. Now I am going to come back and fix this underlying error. One other feature though, I want to do is have an integer result come back from this method. So that will tell me if an insert was correct or not. So an integer value of result is what we're going to get. Now we're going to come back and finish this code a minute. But now we have to go and code in the other class, the album's Dao, because right now, as you can see, we have an underlying, so let's go check the underlying. And it says here, the album's Dao does not contain a definition for add one album. So we haven't programmed that yet. Let's see if we can get some help here. So show potential fixes. And the only fix that says is would you like to create this. So sure enough, I'll click it. And somewhere someplace, somehow, the error goes away, we need to go find it. So I'm going to save this and switch into albums Dao and see what's there. So let's refresh our memory from what was here. So I scroll to the top, and we have a class with get all albums. Then we have another method down here called Search titles. And then finally, there's a new one that was just inserted for us called add one elbow. Now, I'm going to copy some of the code that we used in previous examples, and then we're going to modify it. So the simplest code I can pick from is the very first method called get all albums. So I'll just copy the contents of that method, and paste it into my insert command that I'm going to do here at the bottom. Now, I'm going to erase most of the code, but I do need to keep around a thing like, open the connection, and close the connection and create a SQL command. Now the SQL command is going to be drastically different. But it still is a command. So I'll leave that line. Now the command here is going to have the select statement. So let's delete the select statement. And we're going to replace it with an INSERT statement. Now, I'm not very good at SQL, or maybe we're new at it, let's say, let's go and have some help from our MySQL admin page. So I have here the admin page for the MySQL server. And up at the top, there is a SQL statement. So I'm going to get some help from here. So as you recall, we were able to do SELECT statements with help here, I'm going to choose the insert button, and it gives us an exact line, what we need to use as a model. Isn't that handy. So I'm just going to copy the entire thing, and then change it once we get into our program. So let's copy here, and then switch back into our Visual Studio and paste it inside of these quotation marks. Now let's see what we need to change here. So we have a statement that says insert into albums. Well, that's the table that we want to insert into. And then you can see the next section lists all of the column titles that are in the table. So which of these columns titles are we inserting? Is the answer all of them? Not quite, we are treating the first column differently, the ID number, we don't want to provide an ID number when we insert because the table is set up to automatically increment the counter variable for ID. And so in this statement, I'm going to erase the first column. So Id does not include itself here. And so album title is the first column. Now the rest of these are valid. Now when we get to the values here, I'm going to also replace those, so let's select them all. And let's delete. Now instead, I'm going to use what are placeholders so I'm going to use an at symbol, and then some name that refers to this column. So referring to the rest of these, we're going to have the album title, and then an ad for the artist name and ad for the year. Then we're going to have an ad for the image URL and then an ad for the description. So now it's time to adjust the parameters. So the command that we want to use to associate a parameter, those add symbols with the actual value is this command dot parameters dot, add with value. And then in parentheses, we're going to have two things. The first is a string, which says which parameter name are you trying to associate. So the first column is the album title. So we'll put at album title. Now the second item is the actual value that we are using for the insert. So we have a variable called album. Where did that come from? So the album dot album name is coming from the parameter up online 92. So when this method is called, it has to have an album supplied to it. So we've got the actual data coming in there. So that's this first parameter, then we'll associate one property of the object with the first parameter in the SQL statement. So the rest of these parameters are going to be very similar. So I'll copy and paste. So we're going to copy and paste and then we'll switch the next one to artists. And the album dot artists name is the actual value, then we're going to go to add year. And we'll get that from the property of the album year. Then the third one, or the fourth one is image URL, notice I'm using a capital letters so that way they match exactly. And then we're going to have the image URL from the album property. And then finally, the description. So all of these parameters should work. If the user fills out the form correctly, if they fill out the form, in a bad way, if they put in strings for the year, for example, then the app will crash. So remember, we're not checking for errors in the input field, we're making this a simple user interface. So the next item is we have to execute the query. So let's type in command dot execute. And there the the one of the first suggestions is called a non query execute non query, that means you're not selecting anything, we're doing an insert. So I guess that goes as a non query. Now it says here, what we need is a statement, it says against the connection, and then it returns the number of rows affected. Well, that's very handy, because we want to get those numbers of rows affected, and send it back. So let's choose execute non query, I'm gonna press tab, and then we have the execution. Now we need to save this. So let's put in a new value value and call it result. Or let's call it new rows. And then we'll get it from this execute non query. So that works out well. Then finally, when we want to return something, we're going to return the number of new rows. And let's see if all of the errors go away. I believe they do. Good. Now, we're almost done. But we need to go back to form one. And let's see what we can add after the insert. So the results are here. So I'm going to just put in a message box. So I'm going to say message box Show. And then we're going to tell it how many different rows were inserted. Okay, so now we've got everything set up, it looks like we're ready to run. So we just have to click the green button and try it out. All right, there's the beautiful looking app, let's Minimize Visual Studio. And let's go back into our Sergeant Pepper's. Let's see if we can use this. So I'm going to select the first paragraph. And let's put in the description below. So my user interface doesn't allow for much space. But as you can see, I can put in a very long text. So the image URL, I'm going to work up the from the bottom, so let's go and right copy, right click here, choose Copy Image address. And let's paste that into the image URL. The rest of these I think we can fill out so what were the other ones, we've got 1967 here, and then the artists that looks like the Beatles, and then says a lot of typing. So I'm going to copy that title and paste that. Okay, all that work. Now we're ready to click the Add button. And what do we get? It says one new row was inserted. And let's choose a load albums. And let's go to the bottom and you can see that I've put it in twice now. And so if I click one of these, we get ourselves the URL image that seems to be loading. So I inserted Sergeant Pepper's twice, you probably get one if you click the Add button, you'll get two as well. And you get a whole bunch of them if you click it multiple times. The next thing that we're getting to in this tutorial series is creating a foreign key. Because now that we've got albums, we need to add some tracks or individual songs. And each song is of course associated with an album, which is going to require us to learn how to make two tables and a foreign key. So we're gonna get started with foreign keys next. Hi, and welcome back to another section of our SQL database application tutorial. I think this is part eight, and we are learning how to use SQL databases. So here's the index of everything that we've learned so far. You can see that we've created tables and we've created a simple app that can read and insert records. In this video we are going to talk about foreign keys, you're going to learn how to use a new tool that will create links between tables. So we will have an albums table, and then another table in our database for each of the songs, we'll call it our tracks table. And then the relationship between them is very important. And so we'll learn the how to use the tool called MySQL Workbench. So a lot of concepts here about SQL databases that we're going to explore. Right now, here's the rest of the sequence that we're going to learn. So we're about halfway through. And by the time you're done, you will become a sequel expert, we might say my name is shad Sluiter, and I teach software development at Grand Canyon University in Phoenix, Arizona, I'm glad you've joined us. So many of the students that I've taught in the past are now employed very successfully as software developers. So I'm inviting you to come along and learn some of the same things that we do in class. In this particular course, SQL databases, you use this in almost all types of software development. So this is an essential part of your education. So make sure you check out the website at steady coding.org, where you can see all of the courses that I've been teaching in the past few years. So here's a preview of what we're going to do before the end of the video, you can see on the screen that I have a new tool up. This is called my sequel workbench. And a workbench tool is used to design databases. So by the time you're finished, you're going to have not only the album's table that you see here, but this link to another table called the tracks table. And so this contains all of the songs that each album has. And you can see there's a link between the two called a foreign key. And so all these concepts that we're going to learn right now, so if you haven't installed this tool yet, let's go and find it. So I'm googling MySQL Workbench installs. So let's see what we got here for one of the links. And this is the one you want here. So MySQL Workbench Download Now. And then you're going to have the choice of which platform you want to install. So I'm using Windows, you might have something else, but go ahead and choose the download and go ahead and run the setup. And when you're finished, you will have the application that you see on my computer. So I've got the application installed, I'm just going to click the little dolphin icon and run it. So you can see the first screen is welcome to my SQL workbench. Now, I'm going to remove this item here because you don't have that yet. So let's see, delete the connection, and click Delete. So the first thing you do when you launch this is try to connect your MySQL Workbench to a server. Now you can see in the background that I have the MAMP server running. And I have the green dots indicating that the two servers are active. So the Apache server which honestly we are not using, but it's are automatically included in this package. MySQL server is also running. So the tool MySQL Workbench will work with any type of SQL MySQL server. So whether you're installing MAMP, or lamp or USB Web Server, or you just installed MySQL as a standalone product, they will all be able to connect using MySQL Workbench. So you might have a similar setup that I do. But hopefully it's something that is as close as possible so that the tutorial makes sense to you. Alright, so let's see how we can make this work. So as you recall, the server here has a user name and password. So root is the username. And root is the password for this particular instance of MySQL. So that's important to be able to connect. Now I'm going to just minimize this. And now we're going to go into MySQL Workbench. So now down here, in the section where the mic SQL connections are listed, I'm going to click the plus sign. And this will add a new connection. Now if you if you don't find that plus sign, you can probably find that over here under the databases menu. But I'm just going to go with this plus sign. So I'm going to set up a connection. So the name of the server is localhost. So that's a great name for the connection. Down below here is the IP address of my server. So I'm going to type in localhost again, you could leave it as 127 001. But localhost is synonymous, then the port number is 3306. So that's the default port. And if you want to check to see if you're running on that port, you go to your map server, go to the map menu, pick preferences, choose ports, and you'll see the port number listed here. So this is 3306. So I'm just going to cancel this and minimize map. So I'm just verifying that 3306 is the correct number and it likely is on your computer too. Now I'm going to test the connection and it says it was successful. So if this is not Successful check your passwords, check to see if you've got the right ports and everything. Let's click OK, and choose OK again. So now I've added a new connection down here. So I don't have to go through and set up the connection again, the next time I run this application, I can just double click on this gray box, which I'm going to do now. So I've double clicked. And hopefully I've connected. Now you can see on the left side here, I have two tables, I have a music table and a music to table. If you don't see that you can switch because you might have administration on this tab. And then what I want to select is schemas. So the schemas are the database names. So let's take a look inside of here. So music two is the application that we've been working on. So I'm going to expand this, choose tables. And you can see that there's an albums table. So if I were to expand that further, you can see the column names. And then you're going to see more details about our, our design. So we have ID, the title, the artist, the year, the image and the description. So those are all the items that we set up in the PHP admin page. And so you can see the same kinds of materials here. But my SQL Workbench will do more administration tasks and an easier way than the previous admin screen that we've been using up until now. So what I want to do is add a second table and make a foreign key. So just to see where we're going, I'm going to collapse music, too. And I'm going to show you what music looks like because I created this application earlier, just to prepare for this tutorial. So to show you how this works, I'm going to go to database, you won't be able to do this yet. So just watch. I'm going to choose reverse engineer. And I'm going to click Next. And then I'm going to log in, and then click OK. Now it says you've connected, let's go through this steps here. So next, and it says which one of the schemas would you like to connect to. So music two is the one we're currently using. I'm going to take that one off, and I'll show you the one that I created an earlier tutorial. So music is what I'm after. Choose next, it says so far, so good with green checkmarks. Next again, it says you're going to import to tables and execute. And this time when I'm done, something happened in the background, I click Next. And then finally I click Finish. And here we are. So now you can see the results of what we're trying to get to. So I have two tables, I have an albums table and attracts table. So that's where we're going. Now let's back up and see if we can get to this point. So I'm going to close these tabs at the top of the screen. And don't save whatever's there. And we're back. So I want to open up the music to database. So let's try this again. This time you work with me. Alright, so click on database, choose reverse engineer. And then we're going to choose Next, we're going to choose Next, we're going to select music to choose Next, choose next. Execute. And next, and finally finish. Okay, so all of that work gets us to this diagram. So what we're looking right now at is a diagram called an E R diagram. This is the design of your database. You can see it has one table, and it shows you all the column names in it. Now we're going to add a new table and then resynchronize our database to this diagram. So there's a couple of steps involved here. And you have to do it exactly right. Or it will give you an error. So first of all, we're going to create a table. So over here in this column here, you can see that there is an icon that says place a new table. So I'm going to click it and then click out here. And I got table one. So table one doesn't have anything in it. Of course, it looks kind of like a title only. Let's double click it. So when I double click it, I get a another bunch of things that I can configure. The first thing is I'm going to give it a name. So let's call this thing tracks and press enter. Now what are we going to have in the tracks table, let's go down to the columns. So the first thing you usually do is you set up an ID and leave it as an integer. We're going to check auto increment as our option. The next item down. So the next item down I'm going to call it track title. And so I'll use an underscore character here. You don't have to use underscore, you can just shove them all together into a single word if you like. But I would recommend that you do not use spaces in any of your column names. So this is going to be a VAR char type of column. And since the title of the album is probably Longer than 45 letters, let's set it to something bigger. I'll choose 100 characters. So let's add some other items. So after the title, let's put in another column, and we'll call it number. So this is the track number, like if it's track number one on the album, so it's an integer. So usually there's about eight to 10 tracks per album. So we'll see some number between one and 10. The next item down that we're going to put is a string. So this is video underscore URL. So this will be a YouTube link so we can actually play the video. So a number of characters that we're going to need here is what maybe 200 or so how long is the URL for a YouTube video, I'm not quite sure. So 200 is a safe number. Let's add a lyrics column. So the lyrics will be the other words to the song. And for some reason, we might want to print that to the screen. So a lyrics could be really long. So look at all of the options for our different data types. So there's a lot of different options for choosing a text field, I'm going to choose the one that has text and a parenthesis. So I can specify that it has some number of items, let's say 2000. Is that enough letters for a song, I think it might work. So we'll use text. And then that's the last one we're going to put. So it looks to me like I have a couple of extra columns here that I didn't mean to do. So let's go and select the item that was accidentally put in there. I'm going to right click on it and choose Delete. And then let's do the last one as well. And so now we're down to just a few different columns here. Okay, so all of this here is I think, correct. So I can close this tab. And you can see the changes occur here. Now, this is not quite finished, we're missing the link between these two columns, or between these two tables. So let's go look at these items here. So these are different links. If I want to have a relationship, I can have a one to one relationship, or a one to many. Here's another kind of one to one and another kind of one to many, many to many relationships. So complex relationships can be modeled here in MySQL Workbench. The one I'm looking for is this one that says a it's got a dotted line on it, I always do this backwards, so I might have to delete my links, but I'm going to try it anyway. So I'm going to click once here. Now I'm not holding the button down, I just clicked once. And I'm gonna go click a table. So let's try this one, and then click this table, and it creates a link between them. And sure enough, it's backwards. So look at the little marks at the end of the row. And this was this is why I know it's backwards. This says that on the side here is a triple link, it looks like a bird's foot. And the other side has a single link. So that means that for every one track, there are many albums. So that's backwards, because the opposite is true. Each album has many tracks. And each track belongs exactly to one album. So I messed up. So I did that on purpose, actually, because I wanted to show how to delete an item because it's so oftenly done incorrectly. So I'm selecting the line there. And I'm pressing Delete on the keyboard, it didn't work. Let's try right click and choose Delete then. And it says you want to delete this relationship. And I say delete, and it's gone. Let's try it the other way. So I'm going to click here again. This time, I'm clicking on the track and then back to the album. And as you can see, now the relationship is the opposite direction. So we have a single album matches with many tracks. And then here there's a new item. It is the it says at the bottom albums ID. So this is the foreign key. The Foreign Key says we're going to store a number in the table that tells us which album we belong to. So each album has an ID number. And each track has an associated album to go with it. Okay, so that is our model. Now we're going to try to synchronize this to our database. Now when we do this synchronization, we're going to encounter an error. And I'm going to leave the error on the screen because it's so common. And so we'll fix that up. So the first time we do this, it will not work. But here's the attempt. So if I want to resynchronize my database, I click here. And I can choose two things I can choose forward engineer which will erase my current database and replace it with a model on the screen. Or I can try synchronize model now where is that so synchronized down here. So this is the one that I prefer. So let's try synchronize. Now we're going to see the same choices here. We have a server number and then a password so we have not updated the database yet. This is the process of changing The model in our database. Let's see, click next. Now, I'm not going to do any of these checks here. So click Next again, it says make sure that you connect. So I type in the word root for my password. Click OK. Says that was correct. Let's go to next again. Now it says which database are you trying to synchronize. And so I am using music too. Let's click Next again. And it says it's connected. Let's click Next. And now you can see a preview of what's going to change. So the albums and tracks tables are currently in the design that I just drew, we're going to synchronize it with the database, which has a existing albums table. And then the second item is a new table. So when I select one of these, you can see that there is a statement below. So this is the SQL statement that is going to be executed, if you wanted to use the command prompt and type all of this out, you could, you'd have to know every single type of exception and option to be able to type this correctly, which is why I always use MySQL Workbench, because honestly, I can't remember all of these things very well. So this isn't going to work. But we're going to try it anyway. So let's click Next. And it gives us the preview. So preview the changes. So it's going to say I'm going to create a table, the table is going to be called tracks, it's going to be in the music to database, it's going to have all of these columns, and then it's going to have some primary keys going on here. And this problem right here, the word visible is going to cause our problem to halt. It's not going to work. And I'll show you why. So let's execute, login. And you can see we have a problem. So this is so common people Google this all the time to find out. Why can't I synchronize? It doesn't tell me why. It just says there's a problem. And here's what the problem is. So we're going to close this. So the synchronization failed. Here's what the problem is, we have a version number here. So if I go to Edit, and preferences, and then I select the modeling my sequel, we're going to have a version number. So this tool is set up to synchronize with MySQL, eight dot something 8028. Now, if you already have version eight on your computer, then you didn't receive any errors. But more than likely, you have an older version like I do. So let's go and check out MAMP and see what version we're running. So I click here, choose MAMP preferences. And let's go to server does it tell us here it does. So our database server is running version 5724. So you can see the difference now, version eight, version five, it doesn't work. So I have to tell my sequel workbench that this is the version I am targeting 5724. So I'm going to come back to here and type that in. So I could probably just type in 5.0. So I have to actually set this to version five to perhaps I just had an error when I tried five to seven. So let's try 5.2. And now I'm ready to synchronize the models. So the two tables are here. And this ER diagram is on the screen. If for some reason you want to save this diagram, you can you can go to File and Save. And if you lose it, you can bring it back. Anyway, I'm going to go to the synchronized now. So let's choose database. And let's try synchronize the model. And then we're going to log in and try to see what happens. So let's see put in our password. And choose Next, making sure that's music to is selected. Next again, next again, previewing the settings here. So if I click here, you're going to scroll down and see the same thing except for this index no longer has the word visible in it. That's the change between SQL, MySQL five and MySQL eight, at least in this one case. So let's click Next. And let's preview it if you want, you can see all the commands and then choose execute. This time there are no errors. So I'm feeling better about this. Let's go ahead and choose Close. So we have now a foreign key. Now let's go back to our other tool. This is the my admin tool from PHP. So I'm going to collapse music and look at music too. So let's go ahead and create click this green arrow or refresh the page either one and let's see what comes up now. So I open up the schema called Music two and now I have two items here I have albums and that looks the same as it did before no change but I have tracks. So now I want to insert some items into the tracks table. So I'm going to pick one of the album's that I've already looked at. And I'm choosing Abbey Road. I hope that Wikipedia has a list of the songs here on lb Abbey Road. Okay, here's one. So come together is one of the songs. So I'm going to insert some data here into my table. So I'm going to copy come together, and then switch back into my, my PHP admin. So I'm in the tracks table. And I'm going to choose Insert. And the track title, I'm going to paste that in. So come together is the name. So the number on this thing, it's track number one and the album. And now I need a video URL. So the video URL that I'm looking for is on YouTube, of course. So let's, let's play the song here. And you can see that I've got the right one. So this has come together. So I'm going to just swipe the URL from the command bar or the URL address bar here at the top and choose Copy. Let's stop that. And now I'm going to go back and insert it here where it says Video URL. So it's just a text string, but it will link us to a video. So how nice is that? Okay, down here, the lyrics don't have the lyrics. So I'm looking for the lyrics. So I Google it. And sure enough, here comes a whole bunch of stuff. So let's go ahead and copy all of this words that Google gives us. And let's try that. Okay, so I'm gonna copy this, and paste it into the lyrics section. So now I have come together Yeah, as my lyrics. And then the finally we have a foreign key. So when I choose an album number, it's going to save, I'm going to force you into one of these albums. So you can see the bottom group here has the number of the album listed first. So three in Abbey Road, the top group is reversed. So Abbey, road three is the order, you can pick either one of these, and they'll both work. So I'm going to choose three and choose go. Now this insert command was successful, you can see that there was one row inserted. And then it gives me the SQL statement that was done here. So all of this here is what I didn't have to type, it did it for me. But you can see the statement and how the work goes. So it's insert into the tracks table. It lists all of the column names, and then the values. So you notice the no value for ID is listed. So that's auto increment. The rest of the stuff is all things that we just entered in the form. So come together, one, the URL and everything else. So that is how to insert. Let's go ahead and browse the table, and you can see that we have one item come together. So here's your homework, I need you to put in two or three different tracks for each of the albums in your collection. So use the same process that I did go looking on Wikipedia or wherever else you can find the resources. And especially make sure that you get the video URL from something on YouTube. And then we're ready to go on to the next step. In the next part of the video, we're going to use table joins. So we created a foreign key. And now the next step is to select items using that foreign key to be able to use in our application. So we're moving ahead, and you're getting Smarter Every Day with SQL see in the next video. Hi, welcome back to another tutorial in our SQL database application. In this tutorial, we're going to work with joins. So if you look at the table of contents of all the things that we've learned, we're up to the end of row two here. So table joins is the ability to take two tables and put them together. Here's the entire playlist of everything that we're learning, so make sure that you stick around for the rest of it. My name is shad Sluiter, and I teach software development at Grand Canyon University. So I'm welcoming you to come to class with me in this virtual ways so that you can become a professional developer. So let's get started with this next section. So this is how the application is going to look by the time we're done with this video. So you can see that if I click an item, up on the top section, I have an album title. And then when I click it, the bottom table is refreshed so that it includes the tracks. So for instance, the help album has three tracks and it has helped the the night before and then Ticket to Ride. So I can't play the video yet. There's going to be a video player over here soon. So this is where we're going we're gonna have a second table of data that depends on the items that are in the first table. So in the first section here, we're going to add a few items to our form. So let's start with the user interface. is designed. So the first thing I need to do is add a label and then another grid view. So I'm just going to drag in a label. And then after I put it there, I'm going to give it a new name. So it's going to have the text as tracks. And then below that, let's put in a new data grid view class or a new object. And we'll resize it so that we leave some space over on the right side, because we're going to put a video player there eventually, but not in this video, but coming up soon. So that's the only changes we have to make to the user interface. So the most of the work here and this part is going to go in the code where we fetch data. So the part I want to work on first is in the album's Dao. So let's just refresh our memory what albums Dao has, I'm going to select the first item and collapse it, and then the second method, and so we have search, we have ADD, and we have get all so those are the three things that we see here. So we're going to make a another item here that will fetch all of the tracks from the database that match an album. Now, if you recall from the previous tutorial, we created this foreign key relationships so that way, we have tracks that have an album ID number in their table. So that's how we're able to make this work. So I like to reuse code and then manipulate it so I don't have to type so much. So I'm going to copy all of the lines for the method for searching for albums. So let's select everything from the top to the bottom. And I'm going to paste this new method down at the bottom of the class, then I'd have no need to rename it. So I go back up to line 122, it looks like in my file, and I'm going to name this thing as get track for album or get tracks for album. And the parameter that we're going to expect to be provided here is an integer. So we're going to get the album ID and return a list of tracks. So that'll be similar to searching for a name, but a few changes. So let's make those changes now. So the first change we're going to make is we're going to return a list of tracks instead of a list of albums. So I replaced the word album with track. And you can see that the computer doesn't know what a track is yet, we haven't defined that. We've just made a database full of tracks, but we haven't given an object to our application. So usually those two things go together. So let's make one. So I'm going to go to the suggestions. So I hover over track and shows potential items. And it says here generate track. And it says Where would you like to put that I'm going to select the first item, which is in a new file. So I select that. And then over here, you can see that track is now defined. So it has no properties, we can put those in now. But that was just one way to make a new class. So let's go put all the properties in that are in our database. So the first property of the class is the ID number, which is an integer. And then I'm going to get the next suggestion, which is a string with a name. So the name of the track is our second property. The third property is going to be the number which is the track number on the album, which is an integer. And then we go on to the video URL, the video URL is a string. And then finally, we have a lyrics property, which is also a string. So we define all these things. And now we're able to create new instances of tracks. So let's go ahead and save this and go back into the DAO class. So I switch back into Dao. And you can see that now track is defined. So there is no error on line 125, we're not going to do any searching by name. So I'm going to delete this wild phrase. Now here's where the main part is going to take effect. So we are going to erase this select statement and change it with another one. So what I want to select then is every all the columns, so star from the tracks table, and I'm going to have a WHERE statement where we have a match of the album ID to some placeholder here, so we'll call it at album ID. So let's double check to make sure I got the right column name here, because I'm not quite sure that album ID is what I actually chose. So let's go back into the MySQL admin and take a look at tracks. And you can see that albums underscore id over here is the actual name. So that's incorrectly typed in my code. So let's correct it here. And they'll say, albums underscore id make sure that the column name matches this property here. The next line down we have to define what the parameter is. So it's at albums ID is the placeholder that I used in the previous line. And then I have a parameter called album ID. Now just to prove that I got that right, let's scroll up just a few lines to see where that comes from. So that's coming from here on line 122 So we're passing an integer in, which has to be valid. So we're going to get down to the for loop now or the while loop where we do the reading of each line. We're no longer reading albums, this is adapting to tracks now. So let's change the type from album A to B track, and we'll use T for a placeholder name. Now we can erase all of the properties that we used for album and replace them. So we'll go through the list of the anything that's in the table. First of all, it's the ID, then it's the name, then it's the number, then I believe it was the URL for the video. And then finally, the lyrics. And so those are all properly given to us with this nice type ahead help from Visual Studio. The last item where we add something to the list of return these is instead of a, we're going to change it to T because we're changing tracks. Now it looks like I'm done, except I've got a strange error at the bottom. Let's see what that is all about. It says here, we've got a problem. It says you're trying to return tracks, and you were promising albums. So let's scroll up to see at the top of the function to see what's going on here. So in this line here on 122, the datatype for the return says album, and that's supposed to be tracks. So I think that's everything we need. So this function here, or this method, should be able to fetch all the tracks given an albums ID number. Now, we can't test this out yet in the user interface. Otherwise, I would go and run it. Now, we need to add something else. So that way we can produce the proper output. Okay, so let's start at the top now of our form one program and look what we did to set up the data grid view, we had to have something called a binding source. So let's create a binding source for the second grid. So I'm going to name this thing as tracks binding source. And we'll just instantiate a new instance of it. Let's do some other code. So if you looked at this button, click, we had a data source from the get all albums and then assigned it to the binding source. So we're not going to go through all that again, I'm just going to borrow this and modify it. So let's copy here. And let's come down to the bottom. Now, whenever we click the grid, we want to be able to update the binding source for the second grid. So let's go ahead and paste in some things that we had before. So instead of albums binding source, I'm going to have it as tracks binding source. And then you can see that album's Dao is undefined inside of this function. So let's create a new instance of it now. So we got a new instance. And then we're going to redefine the function that we're using just on the next line. So the function that we created earlier, or the method is called Get tracks for album. And then inside the parentheses, we need a, we need an album number, well, we've just clicked something on the grid. That's why we're in this function here. And we need to get something from the click. So we have it right up here. It's on row 45. This is telling me which row was clicked. So we'll just use that. So what I want to get out of the data grid is the number or the ID number of the album. So I can get that by saying go to the data grid view, get the rows, tell me which row was clicked, then I'm going to get columns zero, so that is cells, bracket zero, and then give me the value of that. So that should be an integer because the first column tells me the ID of the album. Now you can see there's a problem here, it says, I'm getting the value, but it's not guaranteed to be an integer. So let's come down to show potential fixes. And choose Add explicit cast. And so what's this little thing in the front here, it says, make sure that this is an integer, and then we'll convert it into something that we can use to look up by an integer. The next line down says instead of data grid view to one, let's change it to two. And instead of the album source, let's change that to tracks. So it's the track tracks binding source. Okay, so that apparently is going to work, I'm going to cross my fingers and see what happens. Okay, got the app up and running, I'm going to try loading the albums. And they show up here. Now I'm going to click on Help. And you can see that it went to the database and selected three items. So each of these has a various numbers of tracks in them. So this shows us the track and all of the properties that are there. Now, we're not quite done yet, because I wanted to show you what foreign keys can do when it comes to joining tables. Okay, so that kind of takes us to the end of this part where we're trying to put two tables on the screen. We didn't do exactly joins in this lesson, but we did use results from two different tables. So that kind of gets you one step further to the end. Now take a look at what's coming up. We're going to do UML diagrams and a few other things. So stick around, and we'll keep learning SQL together. Hi, and welcome back to another part for our SQL database application. In this section, we're going to continue on with table joins. So if you've been following along, we're here at the end of row two of our contents. So table joins is what we're going to be working on, you can see that we still have four more subjects that we're going to cover. And by the time you're done, you'll be an expert with working with SQL databases. My name is shad Sluiter, and I teach software development at Grand Canyon University. And you're in the middle of a process of learning how to write sequel applications. So let's take a quick overview of where we're going to be by the end of this video. So what you see on the screen here is the database of our songs. And as you can see, when I click one of the album titles, the rows below show us the tracks. Now this is different than the previous lesson that we did. You can see that the track titles here include things like the album title, and it's listed three times here for in this case, the word help. And then you can see the other show the same results. So what we're going to do is have custom headers for this bottom table. So you can see that there's more table headers here that are actually saved in the database. So how are we going to do that? Well, it's through the magic of join. So the first part of this tutorial is to work with the MyAdmin tool to create the joins, and then to be able to put them into the application as you see it here. So let's get to the first part, which is, what is a join. So let's jump back into the tools where we were designing two different tables and connecting them with a foreign key. So look at this example here. So we have tracks as one table, and albums as the other table. And this link, the foreign key shows us that these two tables are related based on a number that's saved in each of these columns. So the albums underscore ID value here on the right always matches with one of the numbers in the ID column of the albums table. That's the foreign key. And we're going to use it to create joints. So I'm going to jump now into the PHP myadmin page. And you can see that I have the tables for albums and tracks listed here. So we have contents in both tables. So there's actually values to be able to search for now I'm selecting the tracks table, and then I'm going to the sequel tab. And we're going to write a select statement. So let's click on the word select. And you can see that we have all of the column names listed for our suggestion. And then it says here from the tracks table, let's choose the Go button, and just check to see if it's actually working. And so it says we have 11 different items that have been selected. So now let's experiment with changing this select statement into a join. So I'm going to select again. And then I'm going to put in something at the very end. So we're going to get rid of the where clause for a second. And then I'm going to say join. So there is a join. And I'm going to tell it the name. So the album's table, and then you have to say the word on so after the word en then you put in the name of the foreign key. So if I type in here albums ID, and then you're going to say equals albums, and then you have.id. So that's telling it which items in the table contain a foreign key and two, which one it's associated with. So how did I know to use those two? Well, I have this diagram in my mind where I see that albums underscore ID is supposed to equal albums.id, which is the item on the left. So it's always a good idea just to check to see if you've written everything, this will not change the results here. But I just want to make sure I haven't caused an error. So I'm going to check, retain the box and go. And sure enough, I do have an error. What's let's see if we can understand it. It says down at the bottom, it says column ID in the list is ambiguous. Now that's an ambiguous error. I don't know what you mean. What that means is that there are two tables here. And both of them have an ID column. So if you look at the at the diagram, you can you can see that right away. So tracks ID and albums ID are both called ID. And when we join tables, it really wants us to be very clear to say which table we're talking about. So let's do that. Let's specify which table so let's scroll up and the problem is occurring here where it says select ID so it doesn't know which table to get it from now because we can select from both tables. So let's put in here the word albums that's the name of the table and then.id so that will be one selection. We could also say give me the tracks dot i Add. And that is also valid. Let's do the Go button now and see what comes up. So now the results are valid, you can see that it says id and id. And we can't really tell which one is which. So this is a case of being ambiguous, it would be easier for us to understand the results, if we didn't have just the word ID. So since it is kind of difficult to distinguish the two, I'm going to rename them. So I'll say, select albums.id as and then I'll put a new label and a good name for this could be album ID. And the same goes with the track ID. Let's now rename it as again using the keyword as, and we'll say tracks.id As Track ID. Now let's let's go again and see what that does. So scrolling down, I see the results. And now it is very clear to say I know that the first column is the album ID. The second column is the track ID, that makes more sense. So the neat thing about joins is that you can select columns from either table now, and they will show up in the results. So let's say I wanted to, for some reason, add the album title to each row in the search results. So I can just put in there, put in albums dot album title. So what does that do? Let's choose go. And let's see the results now. And you can see that album title now is listed here. So every row contains the album title along with the rest of the items for each track. So we're selecting tracks, but we're adding the album title as the search results. Now I'm going to reduce the number of columns that we're selecting, because that's a lot. So I'm going to remove a few things. So let's get rid of the first item, which was the album ID, let's get rid of that number. And I'm not sure what else I'm going to get rid of. But I'm going to delete several of them to reduce it. So that way when we do the search results, now I click go, we have a fairly narrow items. So I have track title, track title, video, URL, and lyrics. So those are the results from this particular query. Now I'm going to take this query and attempt to use it in my application. Okay, so now it's time to take that SQL statement that was using a join. And we're going to bring it into the application and see what consequences that has. So what I want to do is in this form, whenever we click an album title, I get the search results of the tracks below. So right now, it just shows a list of tracks, which was working, but we've changed the SQL query. So let's see what that does. So I'm going into the data access object here. And I'm going to copy the part that says Get tracks for albums. So let's select everybody all the way to the last item and Ctrl C for a copy, and then paste it. Now I'm going to make some changes here. And so let's just rename this one as get tracks using join. So right now as an identical piece of code, get tracks using join, we're going to have to change the SQL statement now. So the select statement comes from here. So we have this nice statement that does join. So before I take this one step further, I just want to add a WHERE statement, I'm going to say where album's ID, and I'm going to put in something like a three. So it's a placeholder three is a placeholder. So let's, let's try that out, choose go. So the results are that every item comes from album, number three, which is Abbey Road. So that is the results because I said give me ID equals three. So let's take this as our example. I'm going to copy that. So now let's jump back into the code here. And I'm going to erase this select statement that was there and paste over top of it the newest version. So we've got albums ID equals three. So the three isn't quite what I want, I want to have this placeholder album ID. So that way, whatever we asked for will match the album number. Okay, so we got a bunch of columns that are selected, and then we return a track and then hopefully we return the Okay, so now I'm going to come back into the form one, and I'm going to change this code here. So this method was get tracks for album. Let's see what up there is. Now let's try get tracks for album using join. That's the new method that I created. So should work, right I'm selecting let's see what happens when I run it and what surprises might await me. So I run the application, I choose Load the albums, and then I'm going to select help, and we got a problem. So you can see here that the track has, what five different properties. And when we did a select with a joint statement, all of a sudden we're selecting like six or seven column numbers. So you can't just say put these column numbers into the properties of the track object anymore. They just don't match anymore. We can't We can't use this process as it's written. So I'm going to fundamentally change what this method is supposed to return. So right now it says give me a list of tracks. That's what the return earned value is on line 169. So what I want now instead is a something called the J object. And let's see if we can do that it's probably going to cause a problem. So I type in J object. And sure enough, it says I have no idea what you're talking about. So J object is something working with JSON text, which is a very flexible way to create objects. And C sharp doesn't automatically know how to work with JSON, you have to install a library. So let's go down to the package installer, it says, Newton soft JSON. And we'll install whatever it says is the most current package, and the error goes away. Now let's check to see what was installed. So I'm going to right click and choose Manage NuGet packages. And let's check to see Yep, sure enough, we got Newton soft, or Newtown soft, and it's installed here at version 1301. So now, instead of returning a bunch of tracks, I need a bunch of J objects. So down here in the reader area, we're going to expect to add a new item, which is going to be of type J object. So you're going to see what a J object is in a minute, I'm going to print it so you can actually see them. So for right now just let you know that it's a very flexible object, it's kind of like making a track object or an album object, except it has a flexible number of columns and properties. So we're going to create a new one, it's called new track. And then I'm going to add it to my list of return items. So it says here, you got a problem. So new track is not the right type. So let's scroll up a little bit. And I have a list of things called tracks here. And let's change that to J object. And that way will be compatible. Okay, so return these is of type J object. Okay, so the errors go away, we're not quite done, we need to get all of the properties from each column name yet. And so that's what we're going to do here. Okay, I need to cut that out and put it in the while loop. So we're going to make a new object and then add it to the list. So for each column that I select, or each column that I read, I'm going to put it into this J object. So I'm going to make a for loop. And I'm going to say go from for i equals zero to the length or the field count of the reader. So the reader read a row, and it has a bunch of columns in it. And the property called Field count tells me exactly how many columns there are. So it's a flexible amount. Now I want to add some rows, or I am going to add some properties to my new track object here. So I'm going to say new track dot add. Now we need two things, we need a property name and a property value. And we can get those from the reader. The first is to get the readers name property for this thing. So let's say reader dot get name at column i. So i goes from zero to the number of columns. And so that will be a string. That will be the first half of the property, the second half is the value. So to get the value, we have a similar item, we say reader dot get value at position i. So that means we have two parts to each column, we have its column name, plus its property value. All right, so check all that out. I think we got the columns all correctly now. So this will be flexible for the number of columns that we're reading. And then we're going to return it to our our other part of the application. So let's go to form one. So now we come back into our form one, and let's see what happens when I run this. So now I click on Load albums, and I get everybody and I click on Help. And this time, you can see that the select statement is only selecting a few rows. So we have fewer items across there, but they're flexible in number. So if I change the columns that I'm selecting, the bottom tables should be flexible as well. So let's test out that theory. So I'm going to come back down to here, and I'm going to for whatever reason is I'm going to leave out the lyrics. So let's just delete lyrics. And we have one less column. Let's run it and see if that is as responsive as I promised that it would be. So here we go. Let's load the album's. And we try help again. And sure enough, the lyrics column is gone. So I just didn't need it for this part of the application. So you can add more columns and subtract columns to the magic of this JSON object. So this is not only a lesson here and running sequel, but a little bit in C Sharp and how JSON text is handled. So JSON is pretty common, you can see that the advantage is that you're no longer tied to the static number of columns in a thing like this, like the track or if we created a custom class called a data transfer object or a DTO, which takes the number of columns and into effect. So JSON was invented to work with JavaScript, but all other languages, including C sharp have adopted it, and you can see the flexibility of it. So we've moved on to another section. So let's get to the bottom row now and continue to learn about our SQL application. shins, we're going to talk about UML diagrams, and how they are used in design. That's coming up next. Hi, welcome back to another piece of our SQL application tutorial. In this part of the video, we're going to pause on the coding and talk about design. So the subject is u m l diagrams. Now most students of mine would say that design is not the thing that they would prefer to do, but I forced you to do it because it turns you into far more organized in your mindset, and your projects turnout on time, and with far fewer errors. So it's good to invest the time my name is shed Sluiter and I teach software development at Grand Canyon University. So you can check out my tutorials on steady coding.org. Or you can subscribe to my channel. So the first thing I want to show you is a UML diagram for our current application. So you're familiar with our music app. So this diagram shows two classes that hold our data. So you can see on the left side, we have an albums class, and the right side is a track class. So these are in the C sharp code. This is not necessarily in the database yet, but they're very closely related. Here's another diagram, this is called an ER diagram or an entity resource diagram. And this should look familiar to because we built this directly out of the MySQL workbench tool. And so you can see that there are two tables joined by a foreign key. Now you're supposed to see that these are very closely related in this case. So this isn't always the case. Sometimes Java classes or C sharp classes have a lot more methods and properties in them. So the bottom line here where it says method have pretty much ignored because we're just using these classes to hold data. So UML is on the left, er is on the right. Now, you'll also see that there are some different connectors between the classes. So classes can be associated, they can have aggregation, and they can have composition. So in the first case, if we were to say a bicycle as a class, it could be associated to an owner. So it doesn't necessarily have to have an owner. But it could, however, in the second part, we can see that there is an essential piece. So if we were to design the bicycle class, so that it had more components, such as frame parts, these would be called composition. Because the part itself is useless without the parent, or some kind of a component that really has no purpose apart from a bicycle. Now you can make the argument that some things like a wheel here at the bottom, a wheel can exist without the bicycle, it's not very useful, but you can imagine it. So it's kind of a fine distinction when you talk about composition and aggregation. So in the case of our application, I chose to use aggregation here in the UML diagram, you can see that a track is part of a album. And so the album has a property called a list of tracks. So you could argue that tracks can't exist without an album. But you can release a single I think so I chose aggregation to represent this in the UML diagram. Now, if I started to ask for new features in your app, the first thing I would do is to send you to the drawing board. And I would say, let's add some comments. And before we even start coding, I want you to draw the picture of how we're going to handle these. And so we would add a comments table to the database, wouldn't we? And you can see that we would have a foreign key so that each comment is associated with a track. Now the track can have multiple comments. So that's a one to many relationship. So that's what the tables would look like if we were to make an ER diagram for a comments part. Now in the design for the application itself, we would have an associated UML diagram. And so you can see almost the same thing happening here. In this case, I used a little black diamond to indicate that comments are a composition relationship to their track. The reason I chose composition was because if you were to delete a track from the system, the associated comments would have nowhere to go. And so a comment should not exist apart from its parent should not exist apart from a track. So on the left side, you can see that the relationship between a track and an album is labeled as aggregation. That's because a track could exist apart from an album. However, on the right side, I have labeled the comments and tracks as a composition relationship. Because a comment should not exist unless there is an associated track. Now let's add some more features. Let's say if I were to add users and artists to the tables. So you can see that now a comment is associated not using a string For the user name, but an entire class called user, so the user can have maybe a login screen could have a password could have a bio, I don't know what you would put in a user class. But you can imagine all of the things that go into a user, same thing goes with the artist over here. So instead of just using a string, and saying that this album belongs to the Beatles, we could say that the artist has multiple pieces, we can have a little biography on the artist, we can have a list of all albums that the artists have has. And so your your application starts to grow, adding new classes that are related to each other. So then associated with that would be the database. So the associated ER diagram shows all of the tables, and the relationships and the foreign keys that go with them. Now, by the end of this course, I'm going to ask you to create some challenge activities. And those are going to include things like comments. So keep this in mind, because we're going to see that soon. In the next video, I'm going to show you how to do something called compound queries, which is really two queries kind of combined into one action. So we'll do that next. Hi, and welcome to another tutorial for our SQL database application. So in this part of the tutorial, we're going to go to this section here called compound queries. So we're going to combine one result with another. And we're just going to refactor some of the code that we developed in earlier sections. So here's the design that I'm referring to. So on the left, we have the UML. And we have the right, which is an ER diagram. So we're going to be modifying our model here. So you can see about the last line in the album properties is a list of tracks. And so that's what we're going to implement here in this video. My name is shad Sluiter, and I teach software development at Grand Canyon University. So welcome to class, make sure that you check out study coding.org To get the full playlist and other courses as well, I want you to come along and become a software engineer just like many of my students. So just to be clear, the application that you see is going to look the same. So I got the same actions going on here. So when I select an album, I have the tracks appear in the second table on the application form. So we're not changing the look, we're just changing the design. So let's get started in the code. So the first place I want to go is into the album's class. And some time ago, I left a note here that said, we're going to later make a list of tracks as part of the properties here. And so that's what we're going to do right now. So I want to show that we have a list of type track, and we're going to use the get and set properties and make it public. The next part we're going to adopt here is the album's Dao. So I've collapsed all of the methods that are in this class just so that we can get a summary of what we've made. So you can see we have a method to get all the albums to search for albums by title to add an album. And then we have two different selectors at the bottom one is getting the tracks for the album. And then the second item here was also forgetting tracks using a join statement. So joining meant that we could have a flexible number of columns. So we're going to be modifying the the get all albums method here. And we're going to use the get tracks for album as a kind of a secondary helper. So let's get into the get all albums. So you can see that I'm inserting right below the place where we just fetched a new album, I'm going to now add the tracks property to this variable called a so a dot tracks equals, and then I can go to another method in this in this class here to get those tracks. So get tracks for album is another helper methods we're going to use. Now the part that's missing here is that it's expecting a number. So let's type in A, and let's get the let's see, I think it's the ID number. Yes, it is. So if we provide the ID number for this album, we should be able to get all the tracks and then add them to the list of things that we're returning. So that's a small change here. But it's going to allow us to save all the tracks at the same time we fetch the album. Now that we've returned all the tracks as well as the album, we can modify what we do in our form one. So here we are in form one, I'm going to the very top, and I'm going to add one new item. So I'm going to save a Global list of albums that we can refer to after it's been fetched. So we'll make a list called albums. Now, just to prove to you that I can use this list, I'm going to define it here in line 21. And I'm going to fetch all the albums and assign it to this list. And then the next step down, let's assign the data source for the control to the list. So it's an intermediate step. We've just kind of a temporary placeholder. But we can fetch this again without going Add to the database and you'll see that in a second. Now there is an important difference though. So the album's now contain a property that includes the list of all tracks, so we don't have to go back later and fetch those again. So the next section I want to work on is down here in the data grid view, click an event item. So when we click an event, in the current stage, what we have right here are several lines that go back to the database to get the tracks. And that's where we're going to change the main point of this lesson. So right now it is going to the database to get its values. And we're just now going to use the list that we have stored in memory. So I have to know that on line 46, I know which row number was clicked. So that also tells me which album was clicked. So let's use that to our advantage. So I can wipe out any reference to the database now. And its next is to just get the item that was clicked. And so albums Add Row clicked. And that tells me which row number of which album it is. And I'll just swipe the tracks from that list. It's already been saved there. So I just reference it. So let's see if that works. I'm going to run the app now. Alright, here we go. So I'm going to choose Load albums, and let it fetch from the database. There's my list. So let's go ahead and click something like help and see what we get. So we can see that one of the tracks has help and the other two, so Abbey Road, what does it contain, it shows come together, and here comes the sun. So we've got ourselves a list that appears to work the same as it did before, but we've just refactored it. Now one final word about doing compound queries. So you can see here in the album's Dao, I am referring to the database twice. So in the second time, when I go and get the tracks, I am going through another loop. So the more times you add a request inside of a request, your database or response time will become less and less. So be careful how many times you are calling references to another table because you are growing exponentially in the complexity of your for loops. So if you select something in a for loop, and then another thing inside that for loop, you're going from squared to cubed into quadrupled and the power so you can really slow down your database with these kinds of reactions. Now there are ways to make this work better, including indexing, or if you go to a different structure altogether, instead of SQL, you could work with maybe a no SQL database, such as MongoDB. But that's for another course. And so we're trying to learn SQL right now. So we're going to do some deletions from the database. And also I promised you that we're going to have a video player. So we'll maybe we'll sneak that in next time. See us soon. Hi, and welcome back to another tutorial for our SQL database application. In this tutorial, we're going to learn how to use the delete statement. So this is the contents of all of the items in the course that we've looked at so far. So we're on the second to last item, which is deleted. My name is shed Sluiter, and I teach software development at Grand Canyon University. I'm glad you're here in class, and you too can become a professional web developer, you can find all the courses that I teach at study coding.org. So make sure you sign up there and get a great career. So let's take a look at how we're going to finish off this tutorial. So this is the app as we have it right now I'm going to select something like Yellow Submarine. And you can see the new item that I have below is now a button that says Delete selected. So let's click there. And I get a results says one item was deleted. Click OK. And it blinks off. Now if I choose yellow submarine again, you can see that the item is deleted. So that's what we're trying to do is delete a track will save deleting an album for another part. But we're trying to delete tracks in this part. So there are several steps we're going to accomplish. First of all, we're going to add a button to the form, then we're going to add an item to the Dao. And then when we're all done there, we should have our application working. So let's start here with the form. And I'm going to just drag a button in. So we're going to put a button in right below the table. And let's put the text on the button to say delete selected track. Now this isn't the most fancy user interface you've ever seen. But remember, our focus here is about building a SQL database. Alright, so we got the button here. So let's double click it. So now we need to get in this event, we need to get whatever was selected in the table and then call a delete command. So I'm going to scroll up here and I'm going to grab some code that will come in useful here. What I want to do is make sure that I have the number that is selected in the grid. So this here, this row clicked is the value that I'm going to use Oops, I'm gonna copy that. And let's bring it down to our last item. So I want to make sure that the date of it grid view, I think it's number two gets the item. So let's, let's put in the grid two, and then we're going to uncomment the line, just to see if this is working. Let's test it out. All right, we got the app up and running. Let's go ahead and choose Load the albums. Let's try help. I believe there are three items here. So I'm going to pick the night before and choose Delete selected, it says you clicked row one. So let's try this guy and do it again. And this doesn't seem to indicate that it's got the row number correct. Now, I don't want the row number, I want the ID number. And you can see that in my example, 2728 and 30 are the ID numbers that I'm interested in. So I have to fetch the value from that column, to be able to get that value, I'm going to go back up to row 86, it looks like so there was a statement here that says we're going to get the row clicked, and then get the value out of one of the cells. So I'll copy him. Alright, so let's go ahead and paste that item into there. And it says here, data grid view needs to have a more specific name, let's try number two. And I'm going to save this as an integer. And instead of video URL, let's call this thing Track ID. Now I've got almost everything there don't I know this is a string, this needs to get out of the way. And I want to have the cells at column zero. So all of that now tells me to fetch the ID number out of the grid, we still have a problem, though, we have an underline. And let's check to see what it says. It says here, you're trying to convert something called an object to an integer. So what that means is, there's no guarantee that column zero has the integers in it, there might be an integer there, if I saved it in the database as an integer. So I'm going to assume that it's there. And what I need to do then is tell it, we're going to cast the whatever comes out of that row and put it into an integer type. So there's a potential for issue that the app could crash because, well, the database might not have actually an integer there. But remember, this is trying to make this as fast as possible. No error checking really going on. Now I've got this integer here, let's see if it actually is an integer, we'll put a message box in just to confirm that that's actually what we're getting, I'm going to put in a message box that says ID of track equals and then we're going to put in the track ID value. Let's test that out. Once again, the apps running, let's go ahead and choose an album. And then after I have the album, I'm going to pick something. So let's select somebody like Ticket to Ride and then click the Delete button. It says you are on row two clicked OK. And it says Track ID is 30, which seems to indicate correct. So now it's time to advance where we can take that ID 30 and ask the database to delete that value, delete that track number. So in the next row down, I'm going to call upon our Dao. And we're going to call the delete method. So let's make an instance of the Dao. And then we're going to make a new call to it that doesn't exist yet. I want to save the results of that as an integer. And I'm just going to call it result because I think it's the number of rows deleted, but I'm not sure I'll test it out. So I'm just going to call it result, I'm going to be equal to the DAO dot. And I'll invent a new method called delete track, and provide it with the ID number of the track. So now that we're done with that, you're going to see that there's an underline, not quite finished yet. But we do need to confirm something happened. So let's put another message box in to say the result. So what are we going to do with this delete track, it doesn't exist, as you can see. So if I hover over the error, you're going to say there is no method found there. So let's ask for it to generate a result for us. So when I click that link, it should go into the other class and create that method. For us. It's probably not implemented, but at least it's there. Let's go see what happens. So I'm gonna go over to the album's Dao and open it up. And you can see that I have the previous methods here, I have get search add, and here it is delete track. So this is brand new, it returns an integer, and it accepts something called the track ID. We're on the right track. Now let's see if we can add the code to make this happen. So let's go to the previous method, which was adding a new album. So we're going to copy most of the code in there, and then insert it into our current method, which is for deleting, we'll make some modifications, obviously, and then we'll make the SQL statement work. Alright, I'm gonna go through here and clean things up. So first of all, the statement where it says, we are going to do the insert, that obviously is wrong. So I'm going to just delete that for now. And then I'm going to have one parameter instead of four or five of them. So I'll delete all those We're going to say instead of new rows, I'm going to call that a result. And then when I'm done, I'm going to return the result. So what do we have to do? Now we have to add a SQL statement to make this happen. And after the SQL statement, we're going to have to modify the parameter that's going to be used, which is probably called Track ID, so use an app Track ID for the placeholder. Now the only thing left here is to go figure out the SQL statement. And we're going to get some help from the other part of our program, the my admin tool. So now I'm looking at the table here for the tracks. I'm at PHP myadmin. Again, I'm going to come down to the very last item where it says, Yellow Submarine. So I'm going to select the yellow submarine, and then the Delete option here at the bottom. And it says here, I'm going to execute this statement. So this here is the exact code that I need. So I'm going to right click on that, and copy whatever was in there. Now just to test it out, I'm going to choose Yes. And it looks to me like it worked. It says Yellow Submarine is gone. So I got that code. Now I can switch back into my application and right between the little quotation marks, I'm going to paste it. So here is the code. So how do you do a sequel Delete? Well, here's the answer we do delete from, and then it has the table name. And you have a WHERE statement where the tracks ID equals something. So this 43 worked for one instance. Now I'm going to change that to be a track ID as a placeholder. And then I'm going to associate that placeholder value with what was passed in here. So this should all work hopefully. Now remember, all these backticks are kind of optional. I'm just going to leave them in for now because that's what MySQL seems to like, down at the bottom. What do we got here? We got one more error, return return, How about how about if we change that to result? That might make more sense. Okay, so we're going to execute this delete statement, we're going to capture the results and send it back. And hopefully we get a delete. Now I just deleted the yellow submarine and I want to put it back. So I'm just going to come back and do a SQL statement. And I have this prepared where it's inserting new items. So I'm going to go and it looks to me like it, put it back in. Now we're at row 44. So you might have to recreate a track if you've deleted them all. Alright, it's time to run this. Let's go ahead and click the green button for our app. And let's see if we can do a delete. All right, here we go. I'm going to load the albums. And I'm going to try a Yellow Submarine again. So let's go down to Yellow Submarine. There he is. So I've got it selected. Choose delete this, it says you clicked row zero. It was ID 44. And the result was one, so it must be one row deleted. Now let's see where did it go? Did it go away? Let's try yellow submarine again. Let's try it again. And delete it says zero ID 44. This time the result was zero. So you can see that my user interface is not quite updating. The second time I tried to delete this, it says there was nothing to delete. So I believe it was taken out of the database. But the application on the screen the user interface didn't show the results. So let's make a quick tweak. So back here in form one, I'm going to make two changes. First of all, I'm going to erase the data source for grid two. So it'll erase everything that's there from the screen. Then I want to reload the albums because one of the tracks has been deleted, and we need to refresh that. So we'll do another call to the database to fetch everything. Alright, with those two steps, we should be able to see some results. Now I'm going to put in another copy of Yellow Submarine just so I can delete it. So I'm going to switch back. And let's try the SQL statement. And let's see, do I have that copied somewhere. And so I've got this insert statement to copy and then I'm gonna get choose go. So now we're up to track number 45. Let's see if we can delete that track. So switching back, I'm going to run the application this time and let's go and try to delete Yellow Submarine. So I'm going to choose Load everybody. And let's go down to one of my tracks. Let's try a Yellow Submarine. And then I go down to select the yellow submarine and delete it. It says zero track 45 And the result was one this time as you can see, it deleted the screen and emptied out the tracks. Let's try a Yellow Submarine. And this time it is empty. So it looks like the Delete worked. Now in the next video, we're going to talk about more features that I'm going to leave for you as a challenge so that you can develop your skills and make this app complete. See you soon Hi, and welcome back to another tutorial for the SQL database application. In this video, we are finally at the end, and I'm going to show you some of the new features that you're going to do. So here's the table of contents of all of the things. And we are at the last item, which is called future features, which means you get to do them. My name is shad Sluiter, and I've been your guide here throughout the course I teach software development at Grand Canyon University. If you'd like to get the full course on databases or on web development, or on mobile, go to my website at steady coding.org. And you can get a full degree almost just online. Okay, so let's talk about some of the unfinished business now that is left in this application. You can see on the screen here that I have completed a whole bunch of things without the video being on let's take a look at what they are. And I'm going to challenge you to do them. And if you can't, I'll give you some source code that you can look at and learn from. So what did we do. So what I've done is I've added some features such as edit. So if I choose yellow submarine and choose Edit, you can see that the form over here is filled in so that way we can change things. So let's say if I want to change the year from 68, to 69, you can see that the Update button shows that there was one row updated, and so the updated item or appears in the list. Let's take a look down below here. So if I choose Help, and let's try editing help, let's go to the Edit Selected button. And you can see that we have the same thing down below, we can either add a new track, or in this case, edit one. Let's say I want to change this to track number 13. And I'm also going to change the album. So you can see that they dropped down. What would happen if this actually went with Abbey Road, let's click the Update. And it'll give me a status message. And then if I choose Abbey Road, you're gonna see help now appears in the wrong album. Well better change that back so that it's going back to the help album. The other choice is the delete button. So if I wanted to get rid of somebody, I could just push the delete button and away it goes. Now obviously, there's some more code that's involved here. So let's take a look behind the scenes to see how this works. I've repurposed the input forms so that it not only can add a new album, and put down here a new track, I also have the ability to update and edit those things. So let's look in the DAO, which is where a lot of these SQL statements are. That's where all of the SQL statements are stored. So we've got some new features here. So you've got an update, you got an ADD, what else is there is a delete, there is an update at track and get a new track. So all of the operations, the entire CRUD operations, which is create, update, read and delete, they all require some special coding. So those are going to be found inside of the new methods. Let's take a look at one of them. So for instance, update track. So when I do an update track, I'm expecting to get a object for the new track. And then an updated item number. Let's see what we do with those inside of this method. So here is the statement. That's the key part that says, I'm going to run a SQL statement which is updating. So I copied this directly out of the MySQL admin tool. And we're going to be able to adapt it just like we did with the other items in previous tutorials. So we go through here, and we add all the parameters. And I get an updated rows number which tells me how many rows were changed and then return it. Same thing is going to hold true here with the updated album. So I have two items, I say give me a new album, give me the album ID that is going to be changed. And then when I go through here, I get the same idea. So I'm changing a bunch of albums. So there's a little bit more code that I'm going to let you look at, let's try editing a selected. So you're gonna see here that when we click that button, it's going to fill in a whole bunch of text values with the properties of the item that is being edited. And then I'm using a checkbox called editing, to be able to tell if the form should be used to update an item, or to create a brand new one. And so if it's an update, then we're going to set this property to true, and then set the label on the form to be the item number or the the track number. And so there's some tricks to making form have two purposes. So the best way maybe now to learn is to look at the source code for the project as it is. And of course I'll put that on the course at study coding.org. So you can take a look there. So if you'd like to learn how to build more sophisticated apps that are more web based instead of just forms like this is that's probably where you're going to get a real job. Jobs are going to be probably full stack developer and those kind of target keywords. But I hope you You get a job. And if you do, I'd like you to let me know. So you can contact me on LinkedIn or at my YouTube channel. So it's been a great experience with you. I hope you learned a lot. We'll see you in the next course.

Channel: freeCodeCamp.org

Views: 111,286

Keywords:

Id: VX4wl7qIcbA

Length: 190min 24sec (11424 seconds)

Published: Thu Mar 16 2023