{"id":5753,"date":"2007-07-12T13:58:00","date_gmt":"2007-07-12T13:58:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2007\/07\/12\/linq-cookbook-recipe-7-selecting-pages-of-data-from-northwind-jonathan-aneja\/"},"modified":"2024-07-05T14:43:22","modified_gmt":"2024-07-05T21:43:22","slug":"linq-cookbook-recipe-7-selecting-pages-of-data-from-northwind-jonathan-aneja","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/linq-cookbook-recipe-7-selecting-pages-of-data-from-northwind-jonathan-aneja\/","title":{"rendered":"LINQ Cookbook, Recipe 7: Selecting Pages of Data from Northwind (Jonathan Aneja)"},"content":{"rendered":"

<\/p>\n

<\/span><\/b> <\/p>\n

Ingredients:<\/p>\n

<\/font><\/font><\/span><\/b><\/p>\n

<\/p>\n

–<\/span>          <\/span>Visual Studio 2008 (Beta2 or Higher)<\/span><\/font><\/font><\/p>\n

<\/span><\/font><\/font><\/p>\n

–<\/span>          Connection to Northwind database<\/span><\/font><\/font><\/p>\n

\n

<\/span><\/font><\/font> <\/p>\n

<\/font><\/font><\/span><\/p>\n

Categories: LINQ-To-Objects, LINQ-To-SQL<\/p>\n

<\/font><\/font><\/span><\/p>\n

 <\/p>\n

<\/font><\/font><\/span><\/p>\n

Instructions:<\/p>\n

<\/font><\/font><\/span><\/p>\n

·         <\/span><\/span><\/span>Create a new Windows Forms project with a Northwind DataContext <\/font><\/font><\/span>(for step-by-step instructions on how to do this please see Recipe #6<\/a>)<\/p>\n

<\/font><\/font><\/span><\/p>\n

·         <\/span><\/span><\/span>Drop a DataGridView control from the Toolbox onto the Form<\/p>\n

<\/font><\/font><\/span><\/p>\n

·         <\/span><\/span><\/span>Add the following line of code in your Form1_Load event:<\/p>\n

<\/font><\/font><\/span><\/p>\n

    <\/span>        <\/span>Dim<\/span> db As<\/span> New<\/span> NorthwindDataContext<\/p>\n

<\/span><\/p>\n

\n

 <\/font><\/p>\n<\/p>\n

One of the key considerations when designing an application is how much data to show the user at a time.  <\/span>For instance, if the user enters a query for products and there’s 10 million matches in the database, it’s probably not a good idea to display all those records on the screen at once.  <\/span><\/font><\/font><\/p>\n

The typical solution for this is to show one “page” worth of data at a time, and then give the user the ability to move to the next page.  <\/span>This results in a faster application which is easier for your customers to use.<\/font><\/p>\n

In order to implement paging with LINQ, we’re going to use two new operators called Skip and Take.  <\/span>For instance, consider the following query:<\/font><\/p>\n

        <\/span>Dim<\/span> q = From<\/span> p In<\/span> db.Products _<\/p>\n

<\/span><\/p>\n

                <\/span>Select<\/span> p.ProductID, p.ProductName, p.UnitPrice _<\/p>\n

<\/span><\/p>\n

       <\/span>         <\/span>Skip<\/span> 20 _<\/p>\n

<\/span><\/p>\n

                <\/span>Take<\/span> 20<\/p>\n

<\/span><\/p>\n

\n

 <\/font><\/p>\n<\/p>\n

This will select 3 fields from my Products table and ignore the first 20 rows, and give me the next 20, or to put it another way: page 2 of my results.  <\/span>Now let’s look at a function we can use to generalize this to any number of rows:<\/font><\/p>\n

    <\/span>Public<\/span> Function<\/span> GetProductsPage(ByVal<\/span> db As<\/span> NorthwindDataContext, _<\/p>\n

<\/span><\/p>\n

      &\nnbsp;                             <\/span>ByVal<\/span> pageNum As<\/span> Integer<\/span>, _<\/p>\n

<\/span><\/p>\n

                                    <\/span>ByVal<\/span> pageSize As<\/span> Integer<\/span>) As<\/span> IEnumerable<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

        <\/span>Return<\/span> From<\/span> p In<\/span> db.Products _<\/p>\n

<\/span><\/p>\n

               <\/span>Select<\/span> p.ProductID, p.ProductName, p.UnitPrice _<\/p>\n

<\/span><\/p>\n

               <\/span>Skip<\/span> (pageNum – 1) * pageSize _<\/p>\n

<\/span><\/p>\n

               <\/span>Take<\/span> pageSize<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

    <\/span>End<\/span> Function<\/p>\n

<\/span><\/span><\/p>\n

\n

 <\/font><\/p>\n<\/p>\n

Note: When using Skip\/Take you must ensure that you select any identity columns (ProductID in this case) when working with SQL Server 2000.  <\/span>For SQL Server 2005 you don’t need to worry about this.<\/font><\/p>\n

We can now use this function by passing in the page number and the size (i.e. number of rows in the page):<\/font><\/p>\n

    <\/span>Private<\/span> Sub<\/span> Form1_Load() Handles<\/span> MyBase<\/span>.Load<\/p>\n

<\/span><\/p>\n

        <\/span><\/p>\n

<\/span><\/p>\n

        <\/span>Dim<\/span> db As<\/span> New<\/span> NorthwindDataContext<\/p>\n

<\/span><\/p>\n

        <\/span>DataGridView1.DataSource = GetProductsPage(db, 3, 20)<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

    <\/span>End<\/span> Sub<\/p>\n

<\/span><\/span><\/p><\/p>\n","protected":false},"excerpt":{"rendered":"

  Ingredients: –          Visual Studio 2008 (Beta2 or Higher) –          Connection to Northwind database   Categories: LINQ-To-Objects, LINQ-To-SQL   Instructions: ·         Create a new Windows Forms project with a Northwind DataContext (for step-by-step instructions on how to do this please see Recipe #6) ·         Drop a DataGridView control from the Toolbox onto the Form ·         […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[192,195],"tags":[83,93,94,117,166],"class_list":["post-5753","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-visual-basic","tag-jonathan-aneja","tag-linq-cookbook","tag-linqvb9","tag-orcas","tag-vb2008"],"acf":[],"blog_post_summary":"

  Ingredients: –          Visual Studio 2008 (Beta2 or Higher) –          Connection to Northwind database   Categories: LINQ-To-Objects, LINQ-To-SQL   Instructions: ·         Create a new Windows Forms project with a Northwind DataContext (for step-by-step instructions on how to do this please see Recipe #6) ·         Drop a DataGridView control from the Toolbox onto the Form ·         […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/5753","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=5753"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/5753\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=5753"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=5753"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=5753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}