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:] LINQ to SQL: Overview

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
Let's begin our tour of LINQ to SQL by walking through the 4 basic things you do with any database— selecting, insert, updating, and deleting rows, but instead of writing SQL statements directly, we can do it all now from Visual Basic 9. I'm Scott Stanfield with Vertigo Software, and this is the first video of a multi-part series on LINQ to SQL. I already have a simple Visual Studio 2008 project with 2 files in it, the first— this northwind.dbml file—is essentially a model that maps the product in the category tables which come from this right here— this is my Northwinds database installed on SQL Sever 2005—and I just drag the product and category tables on here. Now behind the scenes, there's a VB9 wrapper that maps these 2 database tables into classes I can call directly from VB9. My other file is this default at aspx page, and it just has a couple of buttons and a grid that we can map the results to. So we'll start first by creating code for the query. In fact, I'll just add all 4 of these since I'm here. Okay, now inside the query—when we click that—we're going to use that file I just showed you— the NorthwindDataContext—create an incidence of that, and we're going to query this, of course. We're going to get all of the products coming from the products table, and actually let's just show that—do something really simple. Now, that is a link right there—just 2 lines—really, that's our "hello world" for link. We'll take that and map it to the data source for the grid view, and tell the grid view to data bind. And that's it. And this is pretty much our select star—we've got everything from the product table, so it's kind of a lot—let's modify the query just to return the ones from category ID1, which is the beverages. So if we go back and use a where statement—where the category— now look at this—I can say the category ID equals 1, or I can go into the category table and find all of the ones where the category name is beverages. The reason I can do that is because that mapping file—when I dragged the 2 tables on there— preserved the relationship between products and categories. It's essentially a one-to-many relationship. Let's do something else—let's order by the product ID in a descending order, and let's also modify their return, so we'll use a new feature—this new with—and we're going to select only the product ID— oops, I forgot my underscore. Okay, so we want the product ID followed by the product name, the category name, and then finally the product unit price. so we have 4 columns now. I'll run that, hit the query— there we go, so we have just the ones that are in the beverages category— you can see now the name is coming from the categories table—and we have them in reverse order. So let's take that as a starting point to add the rest of our functionality. Now, I want to get this list of products a lot, so I'm going to create a little helper function called show products; and I'll just paste that in there because after we do all of the other commands, we want to show the products just to show how the tables have been updated. I'll replace that with show products. When the page loads, I'll do the same thing—that way I don't have to hit the query button. Okay, so we have our query taken care of, now let's insert a new record. I'll insert a new product, so to do that I need a new—again—the NorthwindDataContext— and let's create a product P as new product—not process, product— and I'm going to do something different—using this with syntax—this is the new object initializer for functionality— so I can do with .productname = Scott's Apple Juice, and how about set the unit price equal to $2.40, set the category ID equal to 1 so it shows up in our query, and that'll do it. So I have a new product object called P, and I want to add that to the products table inside the NorthwindDataContext, So if I go to the products group and choose insert on submit— so it's like I'm adding it to an array, but the naming here is important— we're only going to insert it into the database once we call the submit method—submit changes. Finally, let's show the product table and run it. So here's our table—our default table. We'll insert our new product—there it is at the top. Good, so let's write the code for update to change the unit price—right here. Let's get the data context, and let's return. We'll create a variable called P1, and we'll take everything from—do another query against the products table— so where—from the products—where the product name starts with —let's just find all of the ones that start with Scott's, and we're going to update that one. Now, I'm pretty sure that we're only going to return one record, but ideally what you'd want is to have it return just the first one so if there are more than one—or just return single. First returns— the first of many—single returns just one, but if there is more than one, it throws an exception, and I'll choose single because I would like for this database to actually have the product name be an alternative primary key. I don't think it is, but let's pretend it is. So now I should have exactly 1 instance, and we'll set the unit price equal to— I'll just multiply it by 1.5, and then submit the changes. And show the products when we're done. So there's our select—we've already inserted it—and when we hit update, you see the unit price change to 5.4. All right, we're almost there—the last one for delete. Starting to get the idea—DB as new NorthwindDataContext. Now, I'm going to do a query slightly different: Instead of using this query syntax, I'll go directly to the objects and do just an alternative way. They ultimately end up being the same code, but look how this one differs. Because we want to find the same product, so I'll go into the products table and find the first one that matches a function, and this is the new lambda functionality that you've heard about for VB9. So I'm essentially executing this little chunk of code that maps or matches the product name to Scott's Apple Juice. For the one record that matches that, we'll return that into P1, go into the products table and ask it to this time delete on submit instead of insert, we'll submit our changes to the database, then finally show our modifications. So let's make sure it's still there—I'll hit update and then delete removes it. So there—we've covered all 4 basic features: query, insert, update, and delete. Now the following videos will go into more detail about how we created this little mapping file, and of course we've got plenty of videos to cover some more advanced uses of LINQ, so stay tuned.

Video Details

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

New language features for C# and VB support data access directly from code, without writing SQL. This video shows the minimum steps to use LINQ (Language Integrated Query) to query, update, add and delete rows from the Northwind database.

Caption and Translate

    Sign In/Register for Dotsub to translate this video.