Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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

SQL Result Set

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.

ASP page DetailsView

The page moves to the next record when the arrow is clicked to show that the Paging works.

ASP .NET Paging