This tutorial will show you how we can extend the LINQ to SQL Class and create methods that will allow us to page data from a SQL database. VB version.
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
Looking for the C# Version? Click Here!
Looking for more .NET Database Tutorials? Click Here!
Using LINQ to SQL, we can make use of the built-in methods to page the database data a lot easier than with using SQL alone. LINQ to SQL can make it extremely easy for us to create pages from our data source using these two methods:
- Skip() -- Allows us to skip a certain number of records;
- Take() -- Allows us to select a certain number of records.
In this tutorial, we will use a SQL database and extend the LINQ to SQL Class that Visual Studio creates to represent our database by allowing the user to page the data displayed in a GridView control.
We will start by creating our database. For this example, we will use one table of employees with three columns - id, name, position.
We should also add some sample data once the database is set up - we will need at least 5 records to make use of the paging feature.
Once we have our database set up and data added to it, we then need to create a representation of our database using LINQ to SQL Classes. Right-click your project in the Solution Explorer, and goto Add ASP.NET Folder > App_Code. Now right-click the App_Code folder and choose Add New Item.. LINQ to SQL Classes. This will bring up the Object Relationship Designer. All we need to do here is drag into the designer the tables we will be working with from the Server Explorer, and then Save. This will allow Visual Studio to create a representation of our database. For this example, we will name it Employees.dbml
Next, we will create an extension of this class by again right-clicking the App_Code folder and choose Add New Item.. Class. We will also name this Employees and change the public class to public partial class. We may need to also add extra assembly references; we will be using the System.Collections.Generic, System.Data.Linq and System.Linq in particular.
We are going to extend this class by providing methods to select the data in pages. Our first method will select all the data:
| Public Shared Function [Select]() As IEnumerable(Of tblEmployee)
Dim db As New EmployeesDataContext()
Return db.tblEmployees End Function |
Notice the EmployeesDataContext refers to our LINQ to SQL class.
Next, we add a method to move between the pages of the data:
| Public Shared Function SelectPage(ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As IEnumerable(Of tblEmployee)
Return [Select]().Skip(startRowIndex).Take(maximumRows) End Function |
This method will be called when a new page is requested. The GridView's paging links will provide the variables required for this method.
Finally, we create a method that will get the number of records in the database:
| Public Shared Function SelectCount() As Integer
Return [Select]().Count() End Function |
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
The entire class extension will look something like this:
Imports Microsoft.VisualBasic
Imports System.Linq
Imports System.Data.Linq
Imports System.Collections.Generic
Partial Public Class Employees
Public Shared Function [Select]() As IEnumerable(Of tblEmployee)
Dim db As New EmployeesDataContext()
Return db.tblEmployees End Function Public Shared Function SelectPage(ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As IEnumerable(Of tblEmployee)
Return [Select]().Skip(startRowIndex).Take(maximumRows) End Function Public Shared Function SelectCount() As Integer
Return [Select]().Count() End Function |
We are now done with the class, and can implement the functionality into our ASPX page. To make this work, we will need to use a GridView control and an ObjectDataSource:
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" /> <asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="DataGrid1" runat="server" Width="400px" /> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" /> </ContentTemplate> </asp:UpdatePanel> |
Because we are using VS.NET 2008, we can simply add AJAX Functionality to our web application using a ScriptManager and UpdatePanel.
In order to implement paging, we need to set the EnablePaging attributes on both of our controls. We also set the Method attributes of the ObjectDataSource to reflect those we created in our partial class - note that the TypeName refers to our class name.
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" /> <asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="DataGrid1" runat="server" AllowPaging="true" PageSize="5" DataSourceID="ObjectDataSource1" Width="400px" /> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
TypeName="Employees" SelectMethod="SelectPage"
SelectCountMethod="SelectCount" EnablePaging="true" /> </ContentTemplate> </asp:UpdatePanel> |
Looking for the C# Version? Click Here!
Looking for more .NET Database Tutorials? Click Here!
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!
|