This tutorial was created with Microsoft Visual Studio .NET 2008. However, if you are using 2005, you can implement LINQ by downloading Microsoft's LINQ Community Technology Preview release from here.
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
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 just two methods - Skip and Take.
Skip allows us to skip a certain number of records, and Take allows us to select a certain number of records.
In this tutorial, we will be creating a SQL database and adding a LINQ to SQL Class that Visual Studio creates to represent our database. We will then extend the class to support paging of the data, using the methods mentioned above.
Let's start by creating our database. In this example, we will use one table named tblEmployees with three columns - id, name, position.
Once the database is set up, we will add some sample data - we will need at least 5 records to make use of the paging feature.
Once we have our database set up and have added data to it, we then need to create a representation of our database using a LINQ to SQL Class. 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 the tables we will be working with into the Designer, 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
Now 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 static IEnumerable<tblEmployee> Select()
{
EmployeesDataContext db = new EmployeesDataContext();
return db.tblEmployees;
} |
Notice the EmployeesDataContext refers to our LINQ to SQL class.
Next, we add a method to move between the pages of the data:
public static IEnumerable<tblEmployee> SelectPage(int startRowIndex, int maximumRows)
{
return Select().Skip(startRowIndex).Take(maximumRows);
} |
If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!
This method will be called when a new page is requested through PostBack. 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 static int SelectCount()
{
return Select().Count();
} |
The entire class extension will look something like this:
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Data.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
/// <summary>
/// Extension class for Employees.dbml
/// </summary>
public partial class Employees
{
public static IEnumerable<tblEmployee> Select()
{
EmployeesDataContext db = new EmployeesDataContext();
return db.tblEmployees;
}
public static IEnumerable<tblEmployee> SelectPage(int startRowIndex, int maximumRows)
{
return Select().Skip(startRowIndex).Take(maximumRows);
}
public static int SelectCount()
{
return Select().Count();
} |
We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.
Now we are 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.
NOTE: We can change the PageSize to set the number of items on each page.
<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> |
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.
