Video: How Do I: LINQ to SQL: Querying the Database(VB)

Presenter:Scott Stanfield

This video shows how to query a database with LINQ, the underlying SQL with a query visualizer, databinding to ASP.NET controls and record paging.


Click here to go to original video page

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
Groceries and advanced techniques and link concerning debugging with advanced query shaping and some server side paging I ve got a slightly enhanced Northwind data model where we have the same category products relationship that he can Seattle won a many to many relationship between order and the product table to the word details table sports clothes that encode to her default page excellent substrate to be for go straight to the source code lets drop in me review to see me do a couple times of the nice thing about link is that the queries returned by the on the data to date of his contacts in this case the Northwind in a context or queries returned by this competing in the product support and the only innumerable interface which is handy because that s how we ve been doing this data binding
Minute 1
All the ASP net server side controls like the grid view support become upon binding through objects are returned the eye innumerable interface which is what we have here is why we can t do this looks to his source to find a guy from you soonest before this is just loose mature things weren t correctly updated the simple so we have a lot of rose here let s own you do something here let s bring in a query earlier where we had a category category name equals averages and the only way to resolve this in a rootsy shark attack the only way to resolve this query is to do a joint over to the category table fact that s exactly what s going on here behind the scenes if I set a breakpoint here was actually jump in and see what occurred and what query is happening
Minute 2
We can hover over product in that little hourglass or magnifying glass eye can see we have a query joining the product table to the category table that and the fact you can see beverages as he passed in as a parameter and we can excuse directly see the results which may contain you to what s going on and continue the announcer completion great and let me extend this a bit more changes query up little but let s pulled back from the more the product ID also the product name is now owned crater called all number of urged to come from the order details using account for this count the number of rows their effect on the one that everything nudges beverages are really want everything here
Minute 3
Though wasn t at many rows back just from the beverages so in the number of orders and finally the revenue coming from the order details as well when you re sick here s a case where I want to use it as this feature aggregate which is part of the BCP nine solos bring revenue from aggregate detail from the order details until that from the some of the detail in the price times quantity could case where the way of a bit more Republican query exams looks there ago when you can see that try their sturdy orders for child for total revenue of 14 277 now there s lots a roasting return here the next future will show you his own inability to page that data on the server some
Minute 4
That we do along the web and have long list return results we want to skip through to particular row and she only shows in the first 10 results in the way we would do that as we take this resulting query writer product and call skip will skip to Road 50 and take the first 10 results of the heavily safety Ira go to skip through and take on the first head of the way you might use this in your system is once let s create a method called protected some of products and this will take time to value to starting around and caught the illness for it is moving down here parameterized at 50 as a starting row
Minute 5
Kind super row is an integer coming from and can put it in kind from the request query string query string is starting to the list concept of blind products there s her first 10 and in some stir well now equal to 40 or 40 and jumped through works great now when I first saw this and thought because this code you can see right here we do our query and then we skipped the starting rotation next in the lower 40 to the query houses working will link uses a model of deferred execution allows you to set the query of what he wanted to meet over multiple lines or even allows you to do or looking at right now but I
Minute 6
Going to execute query until it s needed and that s the deferred execution model citizens a very efficient way to have gone to set more complex query shape your as you saw the debugging is built initial handy and of course server side of rows skipping is going to be very useful for web applications