ASP .NET DetailsView with Paging and ObjectDataSource
Using a DetailsView with an ObjectDataSource
This article describes how to implement a Data Layer and Business Layer in a DetailsView grid using an ObjectDataSource. It will implement paging by creating additional code in the class to provide the ObjectDataSource with the SelectCountMethod and use maximumRows and startRowIndex to return the correct record from a SqlDataReader.
Create the T-SQL View on SQL Server to provide some data for the DetailsView.
ALTER VIEW [dbo].[vw_employees]
AS
SELECT ROW_NUMBER() OVER (ORDER BY HumanResources.Employee.EmployeeID ASC) AS 'RowNum',
HumanResources.Employee.EmployeeID,
Person.Contact.FirstName + CASE WHEN Len(IsNull(Person.Contact.MiddleName,''))>0 THEN ' ' + Person.Contact.MiddleName + ' ' ELSE '' END +
Person.Contact.LastName as EmployeeName,
Person.Address.AddressLine1, Person.Address.AddressLine2, Person.Address.City,
Person.StateProvince.StateProvinceCode, Person.Address.PostalCode,
HumanResources.Department.Name as DeptName
FROM
Person.Address INNER JOIN
HumanResources.EmployeeAddress ON Person.Address.AddressID = HumanResources.EmployeeAddress.AddressID INNER JOIN
HumanResources.Employee ON HumanResources.EmployeeAddress.EmployeeID = HumanResources.Employee.EmployeeID INNER JOIN
HumanResources.EmployeeDepartmentHistory ON
HumanResources.Employee.EmployeeID = HumanResources.EmployeeDepartmentHistory.EmployeeID INNER JOIN
HumanResources.Department ON HumanResources.EmployeeDepartmentHistory.DepartmentID = HumanResources.Department.DepartmentID INNER JOIN
Person.StateProvince ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID INNER JOIN
Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID
Create the ASP .Net Web Project
Create a new Web Project in Visual Studio. Add a class for the Data Layer (the one here is sqlDataLayer.cs). Note the addition of the using System.Data.SqlClient statement as a SqlDataReader will be used to get the data and the addition of a namespace Emp.DL so that the data layer may be accessed by the business layer.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
namespace Emp.DL
{
public class sqlDataLayer
{
public static string getConn()
{
string path =
@"Data Source=MSQLSERVER2008;Initial Catalog=AdventureWorks;Integrated Security=SSPI";
return path;
}
public static SqlDataReader SqlEmployees(int startNum)
{
SqlConnection conn = new SqlConnection(getConn());
conn.Open();
startNum = startNum + 1;
string cmdText = "SELECT RowNum,EmployeeID, " +
" EmployeeName, AddressLine1, City, StateProvinceCode, PostalCode FROM dbo.vw_Employees
WHERE RowNum = " + startNum;
SqlCommand cmd = new SqlCommand(cmdText, conn);
SqlDataReader sr = cmd.ExecuteReader();
return sr;
}
public static int SqlEmpCount()
{
SqlConnection conn = new SqlConnection(getConn());
conn.Open();
string cmdText = "SELECT count(EmployeeID) FROM dbo.vw_Employees";
SqlCommand cmd = new SqlCommand(cmdText, conn);
int i = 0;
SqlDataReader sr = cmd.ExecuteReader();
while (sr.Read())
{
i = Int32.Parse(sr[0].ToString());
}
return i;
}
}
}
Add another class as the Business Layer for the project. The Employee.cs class will handle the business objects that interact with the data. Note the addition of the using System.Data.SqlClient and the using Emp.DL lines as well as the namespace of Emp.BL in the code. The static method returning a SqlDataReader will provide the data to the ObjectDataSource that will be created and the static method sdrEmpCount provides the number of records in the view to the ObjectDataSource for paging purposes.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Emp.DL;
namespace Emp.BL
{
public class Employee
{
public static SqlDataReader sdrEmployees(int maximumRows, int startRowIndex)
{
SqlDataReader dr = sqlDataLayer.SqlEmployees(startRowIndex);
return dr;
}
public static int intEmpCount()
{
int dr = sqlDataLayer.SqlEmpCount();
return dr;
}
}
}
Add the ObjectDataSource and DetailsView
Code the main page with the ObjectDataSource and the DetailsView. Note that the ObjectDataSource TypeName is the namespace and the class name of Emp.BL.Employees and that the SelectMethod is the SqlDataReader returned from sdrEmployees. Also, the SelectCountMethod used is the integer from intEmpCount. The DetailsView calls the ObjectDataSource using the DataSourceID property.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Employees</title>
<link rel="stylesheet" type="text/css" href="StyleSheet1.css" />
</head>
<body>
<form id="form1" runat="server">
<div class="page1" >
<table>
<tr>
<td>Adventure Works Employees</td>
</tr>
<tr>
<td>
<asp:DetailsView ID="dvEmps" runat="server" DataSourceID="odsEmps" AllowPaging="true"
AutoGenerateRows="false" >
<Fields>
<asp:BoundField HeaderText="EmployeeID" DataField="EmployeeID" />
<asp:BoundField HeaderText="Employee Name" DataField="EmployeeName" />
<asp:BoundField HeaderText="Address1" DataField="AddressLine1" />
<asp:BoundField HeaderText="City" DataField="City" />
<asp:BoundField HeaderText="State" DataField="StateProvinceCode" />
<asp:BoundField HeaderText="Zip" DataField="PostalCode" />
</Fields>
<PagerSettings Mode="NextPreviousFirstLast"
FirstPageText="<<"
LastPageText=">>"
PageButtonCount="1"
Position="Bottom"/>
</asp:DetailsView>
</td>
</tr>
</table>
</div>
</form>
<asp:ObjectDataSource ID="odsEmps" runat="server" TypeName="Emp.BL.Employee"
SelectMethod="sdrEmployees" EnablePaging="true"
SelectCountMethod="sdrEmpCount" >
</asp:ObjectDataSource>
</body>
</html>
Note that there was a call to a stylesheet used to add some color to the page.
body
{
background-color: #ABC;
}
.page1
{
background-color: #FFE;
font: Arial;
font-size:medium;
}
Test the web page by running it.
The page moves to the next record when the arrow is clicked to show that the Paging works.
