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

Data Access Layers in ASP.NET Applications

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
[computerized music] [Joe Stagner] This is the first video in a series of videos on data access in So for this first video, we'll create a Data Access Layer using a typed data set for a simple application. We'll fire up Visual Web Developer Express, and the first thing that we are going to need is a connection to a database that we can program against. So in our case I'm going to go into the Database Explorer, choose Add a Connection, select a server from the servers that are available to this machine. And this machine happens to be running stand-alone as a dev machine, and I have two versions of SQL installed. One is the full-blown version of SQL and the other is SQL Express, so I'm going to choose the full-blown version of SQL. I'm going to use Window's Authentication for development purposes, but certainly you can create a service account for your SQL server database and use that instead. Next we are going to select a database name, and in our case, I'm going to go down and choose this VideoDirectory database. This is a database that I've created that I've prepopulated with 15 of the "How Do I?" videos that are published on Learns, so, which is probably where you went to look at this video. And I'll make a SQL backup of this database available so that you can install it and code against it if you want to follow along with the videos. Otherwise, you can go ahead and use whatever SQL database that you're currently working on. Let's go ahead and test the connection. The test is successful. If you click on the Advanced tab, you can see that I can go in and set at a detail level. I can change some of the details on this connection if I want to. In our case, we will go ahead and be happy with the defaults. Choose OK. And now with the Database Explorer, you can see that I have access to all of the information about the database. In our case, we only have one table, the video's table, so that's the one we are going to be programming against. I'm going to switch back over to the Solution's Explorer and say Create a Website, and we'll name it D-A-L, for Data Access Layer... So we have a default at aspx. Empty page. I've just generated a page load event. Now I want to go over to our project, right-click, say Add a New Item,and I am going to choose a DataSet...and we will just call it Video DS for Video DataSet. This dialog says "You are attempting to add a special file type (DataSet) to an ASP.NET website." In general, to use this type of item in your site, you should place it in the 'App_Code' Folder. "Do you want to place the file in the 'App_Code' folder?" And we are going to say "Yes," and Visual Studio will create that folder for us, and then place the XSD file which is our DataSet in the 'App_Code' folder. Now we need to go ahead and add some data to our DataSet. As you notice here, you can either drag items from the DataSet or you can use the DataSet toolbox on the design surface to create or click. So we could go over here and manually create those objects. But in our case, since we have a database to go against, I'm just going to grab the video's table, drop it over into my DataSet, and voila, we have a representation of our video's table. So here are all the columns, names, descriptions, category. We scroll down a little bit further, and we'll see fields that will contain URL links to the various files that are available for this particular video, so the various video or audio formats. We scroll down a little bit further, and we can see I have some links for files; so these are the support files for this particular video, in our case the VB and C sharp source code. And notice down here we have this videos table adapter, and if we right-click on this and say Configure, we'll see here this is the actual select statement— the SQL select statement that will be used to retrieve data. If we choose Advanced Options, we can see here by default we are able to generate, insert, delete, and update statements. We can also choose to use optimistic concurrency or choose to refresh the data table. And we'll explore those particular options in subsequent "How Do I?" videos on Data Access. We can also use the query builder if we wanted to tailor the query. So if we wanted to exclude certain fields for example, or if we wanted to make this a parameterized query. In our case, we are just going to go with the defaults, but we certainly could like for example specify a sort order or a type of filter. In our case just for the purpose of this first demo, we will go ahead and use the default, but we'll see variations in other videos in this series. Go ahead and click Next. Here we have a dialog where we're able to choose the methods that we want to generate. First we're including a Fill a Data table method, which we are just going to call Fill, and this will basically take a DataTable or DataSet object as a parameter and execute the SQL statement and populate the DataTable or DataSet that's passed in as a parameter with the data that is returned based on the results of the select statement. The second option is to return a DataTable, and in our case we'll just leave the default name for the method Get Data. But this is basically a call that will return a new DataTable, so you don't have to pass one in as a parameter, but it will return in a new DataTable filled with the results of the SQL select statement. And then last, we're going to leave checked the option to create methods to send updates to the database, which creates insert, update, and delete methods that are called to send in individual row changes directly back to the database, We'll click Next and the code will be generated, and you can see here— It says we've generated the select, insert, update, and delete statements. We have generated the table mappings and we've generated the Fill, Get, and Update methods. Now if we go ahead and click on Finish, we can actually switch back over to the Solutions Explorer and take a look at the code for our DataSet. And what you'll see here is that there is really a lot of code that has been generated. Now this is all XML, so it's not VB or C sharp code, but it's all here as declarative indication of how we are going to access the database. So for example, here is our connection string. Here's the delete method and the insert method. And notice here we have all of our column mappings from the properties in the objects that are going to get mapped back to the columns in the database. So here is our select statement and here is our update statement. And the great thing about it being here in code is that it's not a black box. So if after we've done all of our generation and we've invested a lot of coding in our application, and we need to make some changes, we can go ahead and change it in code. We don't have to sort of try and regenerate and then refit the new generated DataSet. Notice here—here are all the mappings where we mapped the what are called source columns here. Basically the properties in our strong type to the actual columns in the database. If we scroll down a little bit farther, we can see the details about each of the items. So for example, the description for the video is of type string, and it has a max length value of 256. So there is really a lot of data that is generated for us in the DataSet here that we have full access to in the source code. Now one very important thing here. Don't skip over this part. You want to make sure that before you start coding against this DataSet, you save them. You'll notice that they are not saved. And I just clicked on Save All, and now they are saved. And you want to do this because if you don't, Intellisense won't be able to pick up the strong typing, it won't be able to give you intellisense support while you're coding against, in our case, the videos DataSet. So now, even though it would seem you would have to do more work to be able to program against a strongly typed Data Access Layer, we don't. We're ready to start to do some codings. So here in the Page Load Event, I'm going to say Dim videosAdapter as New VideosTableAdapters.VideosTableAdapter. And then I'm going to say Dim videos As VideoDS.VideosTableAdapter. Now I can say Videos = VideosAdapter.GetData, and what has just happened is I now have a collection of User Defined types that are videos. Right? So we didn't have to write the class that represented that type. It was generated for us by the DataSet that we're using. And here, now I'm just ready to program against this video's collection. So I can say For Each videoItem As VideoDS.VideosRow In Videos, which is our collection, Response.Write... is the title of our videos. Now here's the really cool part, I can say VideoItem. And look, here are all of the properties and methods that are available based on a video item. Now we didn't have to code this class. It was generated for us. In our case, what we're interested in is the name of the video. So let's go ahead and select the Name property, and then we are just going to wrap up with a page break... So that's all the code that is necessary to iterate through all of the videos in the videos table and display its name in our UI. We haven't beautified the UI yet. But let's go ahead and see what our error is. Ah, yes. We need to close that and notice there that we got a lot of schema errors, but scroll down to the bottom, and we found the actual culprit. So let's go ahead and try that again... and here is a list of the 15 videos that existed in that database. So that said, it's that easy to create a typed DataSet and use it as a Data Access Layer so you can program against the items in the table but to do it a very object-oriented way. [computerized music]

Video Details

Duration: 11 minutes and 50 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 6
Posted by: neudesicasp on Aug 30, 2013

In this video, part 1 of 3, Joe Stagner creates a data access layer using a typed data set for a simple ASP.NET application. For additional information about this video, read the Creating a Data Access Layer data access tutorial.

Caption and Translate

    Sign In/Register for Dotsub to translate this video.