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

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
Artless to some advance select and debugging here in part three while restart again with an empty database model a good server Explorer which is attached to northwards database in SQL Server and he saw me drag if you do sound a previous video to begin take products in their categories but now bring in orders and order details which is the one to many and many to many relationship between products and orders and just use renaming of my_on and just in case I forgot to show it previously was bring the stored procedure a throat culture products by category I can bring that into my data models by dragging and dropping and there is my methods but so no have a data model is a bit more robust with four for different entities in the stored procedure lets to write a simple query to deal with sol Star with my default page
Minute 1
And won t be the source code for that Kerry here inside the page load code is create a new instance of Monday context and just make sure everything for not correctly like to do a simple select statement and talk about this much before but this data could view sorry within the review in the back and this grid view like all ASP net server controls supports the online innumerable interface for data binding and conveniently enough this products come what Link returns here inside products does indeed support I innumerable us what allows us to take the grid view said it s data source equals products
Minute 2
At an opponent in a done this a few times as as picture of his versions working in which you get a select statement with everything in it okay so as before or I can filter down where the worthy category category name equals beverages like to show you what s actually going on behind the scenes in this query because if you recall this products is related to category through a relationship which is inferred only to drag and dropping because of the database schema is a foreign key on category to the category table so if you know SQL you know what has happened has to be joined behind the scenes and indeed did you know SQL you know there has to be a joint behind the scenes and that is what s happening let me set a breakpoint here
Minute 3
And run it boots from equals to much switching between VB net and see sharp focus and here s a breakpoint if we step over it you ll see that the products comparable equals this big select statement NASCAR parser one line that s why we have this little debugger visualizer and click on that and see here s the select statement in fact original query even passes in the beverages straying as parameterized as a preference courses passing in his piece euro on this is really good for protecting against SQL injection attacks but you can see that we have a select statement and there is your outer join mapping the category table to products in fact we can execute this right here and see the results are handy saw continual good okay let s do something a bit more interesting with
Minute 4
Turn on your got all be all the products coming back from them to change the slips was good all the products we re very least it least impact can t count it least three or more orders and for each product and will select suddenly went so similar from and let s select him or were creating an anonymous type uses object initialization and allows you to do things like this and have a product that he will get the name of the number of orders and call it out from the order details count it at should select correctly and funnel it to revenue also from the order details for this time will sum up using a
Minute 5
Lambda function in a taken unit price multiply mad times the quantity for something a little more interesting and let s run it to looks like the kind of who set a breakpoint was enabled us back run will see that this square is a bit more complex you can see that we have an home in inner join or here I m sorry really a query that allows us to multiply Dion unit price of the quantity and actually being looked at the Scotus is pretty clean and this is probably right and straight for SQL again I so far to the code at Lancaster generating s been in pretty good clean and usage is as fast
Minute 6
If you read yourself the difference is the fact is he accounting in the something is being done on the own SQL Server rather than your coats of the should be is required at faster than if you had to write all by hand into it on the client side Telnet query returned a lot of data lets some use another feature of link that allows us to paging for a common wets and Oreos to page or limit the amount of JVC at once and say since you 10 rows at a time in the way we would do that and he consistently helped offset the time of starting row equal to a variable once it s zero benefit parameterized that in and go down her right after the query until the products to skip to the starting row and take just the next 10 results him by parameterizing the
Minute 7
To this say Dietrich here with refractory suffragist had missed to hear them or factor that into new method called fine products notetaker starting row home in and when the page loads while Jill get the starting row from the query string six and 32 and a numbskull start row to throw a day so they re starting row passing upon products and that is parameterized if you don t should work fine suffered pass in row equal 20 easy to get the
Minute 8
Set of results row 50 now we have efficient server side paging is well teaching them to do debugging we seems advanced query shaping and is able to verify that SQL generates pretty clean and find a skip and take features allow us to enable server side paging Scott Stanfield of vertigo software and I hope you enjoy this video