Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

[How Do I:] Create Data-Driven Web Sites?

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
Hello, and welcome to this ASP.NET video on data and databases, using Visual Web Developer 2005 Express Edition. We're also going to use the SQL Server Express Edition that comes with this to create a simple table and create a couple web pages where you can see the data in the table, and we're going to drill in and show you the editing for that able, as well. It's a pretty common thing that you need to do with a website and data, and you'll see how with just a few quick mouse clicks and a little bit of code, how easy it is to build this. So I'm going to start like I do with almost every project and create a new website. And I'll put it in my websites folder. I'll call it datademo. Now instead of pulling in an existing database for this website, I'm going to use the Database Explorer and the built-in features to add a database dynamically. So in this specially created appdata folder, I simply right-click to add a new item. I'll choose SQL database. And we'll have a simple table of authors. And if I expand Tables, I expect it, of course, to be empty. I'm going to right-click to add a new table. And we'll need—here's where I can define the columns. Notice this is all just built right in, fully integrated to Visual Web Developer. The first column will be AuthorID. And this will be an integer. And I'll use the feature here with SQL Express called the Identity column. So this will automatically manage a primary key for me. First I have to set it as a primary key. But it will create a unique identifier for me and take care of the automatic increments in that primary key. So now the rest of our columns will have a Firstname. I'll make it a variable length. And Lastname, same idea. We'll have a City, which will be fixed at—no, I'll also make it variable. State, which will be fixed at just 2 characters. And a Zip code, since this will be U.S. cities. We'll fix them at 5 characters. And finally, a phone number. Okay. I'll save this table. And I'll call it Authors. It'll be the same as the name of the database, but that's fine. And we're good here. Now I can go in and show the table data directly from here. And it gives me this grid view that I can edit directly in Visual Web Developer. And I could go through here and start typing in data by hand. But instead, what I thought I'd show you is if I close this down, close the connection, I'm going to bring up this tool that you can download directly from Microsoft. It's the SQL Server Management Studio Express. This is a version that's meant for— if you want to go a little bit further with your managing of your SQL Server Express databases, you can try this. Now I have to attach this to that file. This is the only tricky part. If you remember, our website was created under C:\websites, under datademo. We have App_Data. And there's the Authors database. So I have to attach it and press OK. Now I can start playing with it. Go right back in. This is the same view that we had. What I want to do is create a new query, where we want to take data from another database to populate this one. I have the classic pubs database right here, ready to go. And it already has some author data in it. But the columns don't exactly match. So I wrote a query that I've already saved, called PopulateAuthors. And in order for this to work, I need to give this guy a name. Okay. So I can do use authors. And then this statement right here will insert into our brand new empty database data from another database called pubs. So let's see if this works. It did. Now, just to double-check. In our authors database, I'll just show all the data. And there it is. Perfect. So we're done with SQL Server Express. I just thought this would be a good thing to show you, but to exit before we go, we have to detach this database. So I'm going to close my little windows first. I don't need to save the query. So I will detach it and go back to Visual Studio. So now if I go back in, I can see that I have all my table data. This just made it so we have some sample data to work with that looks somewhat realistic. Okay, so right now we've got our website with our database with a single table populated. Now what I want to do is show that table of data in a web page. So I could just double-click it, and I could just go in as a shortcut, just drag some of the tables in directly to the page. But I want to show you how you can bind to different objects besides binding directly to a database. So for that, I'm going to add something called the dataset. Now if you've used ASP.NET 1.1 or 1.0, the datasets here are different, and they're more advanced because of the addition of something called the Table Adapter. Let me show you what I mean. Oh, it's telling me—this is basically code, so it's going to create a special App_Code folder for me, which is all right. So I have a wizard pop up, and it knows that I have this one database in here. It's going to take this connection string and automatically put it in my web config file—good— using AuthorsConnectionString as the name. Perfect. And I'm going to create a SELECT statement to populate my dataset. Right? And I could type it by hand, but I'll use the Query Builder. And we only have one table, so I'll choose that one. And I want all the columns. But we're going to do something a little bit different. This column right here—I'm going to filter this entire table by the state. So I'll give it a variable, state. Now what this means, it's only going to pull back the authors that match this criterion where this variable is specified as the state where the author lives. So I'll save this. So let me test it. And I have to supply it the variable here for the states. I'll show all the authors in California. Perfect. Now what we'd like to do for our UI is to give the user, you and us, a drop-down list of all the distinct or unique states in the authors table. So for that, I'll switch to the Database Explorer. I'll just drag the State column right onto our dataset and modify this query here. Let's go into Configure. I want the distinct set of states from the authors table. And I just need to get data. Let's call it GetStates. And we'll test this one. Perfect. So those are the unique states in our database. There's 8 states. And I can give this a more meaningful name. Instead of Authors, I'll just call it StateList. Great, so we have our dataset containing two table adapters, with one query apiece. Now to use the table adapters, we go back to our default page. Now we can start using our design surface. We'll start by bringing over a drop-down list. This will hold the list of our states. By clicking that, it will allow our page to receive the—whenever I change the option or choose a different state, it'll refresh the page and will post back and allow everything to rebind correctly. So we have to bind it, first of all, to a data source. So I'll choose New Datasource. And here's where you can go directly to Access, or directly to a SQL database or an XML file. But I'll choose the one in the middle. Now I'm going to bind to an object, in this case our data table adapters. Now here's where—I could have written all this code manually. I could write a traditional middle-tier piece of C# or VB.NET code that returns an array or a list of states, without doing any database work at all. I could just hard-code it using arrays and things. But we went the dataset route because it supports a few other things for us automatically, namely the editing and updating features. So that's why we went to the dataset. So I'll choose the StateList table. And all this looks good. Even the defaults are fine. Good. Now let's drop on a grid view. So under Data, we have a lot of new controls here to help us manage data. The biggest one, the one I like the most, is the grid view. It's very flexible. You have lots of formatting options for it. I'll choose that one. And again, like we did with the drop-down list, I need to bind this to a data source. Same idea, but this time, we're going to bind it to the author table adapter. Now if you remember, this is the one that requires a parameter for the state. So on the next step, we will bind that state to a parameter. You can see I have a lot of options. I'm going to bind it to the control that's already on this page, the drop-down list. Now you can see in this page, now that we've configured it, we have a lot of options. We can turn on paging and all this stuff. Let me just save it. I'd better make sure this works. So let's view it in a browser. And there's our California authors, Kansas, Oregon, perfect. So this is working just how we expected. Let's go back to our page. And let's play around with some of these columns here. Let's say I want to turn off a few, like I don't really need to see the phone number and the Zip code right now, so I can turn those off. I can turn on paging and sorting. And I can even add a new column. Let's say I want to add a hyperlink. We'll use this to get the details for the particular author, where we can go and edit. So let's a new column called Details. And it's going to get the hyperlink URL from the AuthorID. So we're going to have to create a page. I probably should have created it first, but we're going to create a page called Details.aspx. And we're going to pass it the field AuthorID, 0. So if the AuthorID is 5, the query string will look like Details.aspx?AuthorID. Let me just show you. And before we test this hyperlink out, let me just go ahead and create the page it's linking to. So we'll add another page called Details. And I want to show—let's see, I want to show the particular author, the author that's selected. Now I don't have a query that will just return one author, so I need to go back to my dataset and modify the Authors table here. I'm going to add a new query to the table adapter to return a specific author. So I'll just modify this existing one or the copy of that one and call it—let's say I want all the authors where the AuthorID equals the parameter that's passed in, the AuthorID. And I just need the GetDataByAuthorID. That gives me a new method. Let's test it real quick. So I want AuthorID 4. Perfect. Let's save that. Let's go back to our Details page. And the Design mode, I'm going to use another data control. This time, instead of grid view, I'll use the details view. And I want the same idea. You know, we can do the formatting. And bind it to a data source. Choose the object. Choose the Authors table adapter, but now I should have two choices, GetData and GetDataByAuthorID. Notice behind the scenes, we also have an Update, Insert, and Delete statement. We're going to be using those this time. Now the AuthorID parameter is coming from the query string. AuthorID. And we'll save it, and for this page, we will enable the editing. Finally, to make it easy to get back to the main page, I'll just drag the default.aspx page on here, to create a hyperlink, and let's go test this out. Bring it up in a browser. And there's our list, and it's sortable. We turned on sorting and paging. So let's go into Sheryl Hunter's details here. That's AuthorID 20. And this has the Zip code and the phone number. And I can edit these as well. So we can change the number to—perfect. All right. And hit the Update button, and there are the changes. If we go back to the Default page, we should see— where'd she go? On the second page. Sheryl Hunter in Richmond. Great. So actually without any code, we're able to get a master details page laid out, where there's some kind of filtering going on that's coming from the database. We have editing and paging and sorting, all done in about 15 minutes. It's a pretty quick demonstration of the new power of the dataset. And also the overall data features in creating databases, all within Visual Web Developer 2005.

Video Details

Duration: 13 minutes and 41 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 5
Posted by: neudesicasp on Aug 22, 2013

See how simple it is to create data-driven web sites using ASP.NET 2.0, Visual Web Developer 2005 Express Edition, and SQL Server 2005 Express Edition. Learn how to create a database, add its data, and display that data on a web page.

Caption and Translate

    Sign In/Register for Dotsub to translate this video.