ASP.NETでのデータベース検索ページの作成に役立つ13のヒント

1  ANDERSON
2  ARTHUR
3  BARTON
4  BOUTON
5  DEIDRICK
6  DOBSON
7  HAMILTON
8  JERICHO
9  MONTGOMERY
10 RIDDLEY
11 STEVENS
12 TILLY
13 WILCOX
14 WILLIAMS
15 ZEUSS

DECLARE @LastName varchar(50),
   @FirstName varchar(50),
   @Address varchar(50),
   @City varchar(50),
   @State varchar(2),
   @Zip  varchar(50),
   @StartRowIndex int,
   @MaxRows int,
   @Alphachar varchar(1) ,
   @SortCol varchar(20)
SET @STATE  = ’NY’
SET @MaxRows = 100
SET @startRowIndex = 0
SET @SortCol  = ’ADDRESS’
SET @AlphaChar  = ’’
EXEC [dbo].[LookupEmployees]   @LastName, @FirstName , @Address ,
   @city, @state, @zip, @startRowIndex , @MaxRows ,  @alphachar,
   @SortCol

SELECT CustomerID, LastName, FirstName, Address,
   City, State, Zip, ROW_NUMBER() OVER (ORDER BY
CASE  @SortCol
   WHEN ’LASTNAME’ THEN LastName+Firstname
   WHEN ’ADDRESS’ THEN Address
   WHEN ’CITY’ THEN City+LastName+Firstname
   WHEN ’STATE’ THEN State+LastName+Firstname
   WHEN ’ZIP’  THEN Zip+LastName+Firstname
   ELSE LastName + Firstname
END)
AS RowNum
FROM Customers

WHERE LastName LIKE
   ’%’ + COALESCE(@LastName,LastName)+ ’%’  AND
   Address LIKE
   ’%’ + COALESCE(@Address,Address) + ’%’ AND ...

WHERE
CASE WHEN @lPaging = 1 AND @SortCol= ’LASTNAME’
   AND SUBSTRING(LastName,1,1) >=
   RTRIM(@AlphaChar) THEN 1
WHEN @lPaging = 1 AND  @SortCol= ’ADDRESS’
   AND SUBSTRING(Address,1,1) >=
   RTRIM(@AlphaChar) THEN 1
WHEN @lPaging = 0 AND RowNum BETWEEN
   ( CASE @StartRowIndex WHEN -1 THEN
   (RecCount )  -  @MaxRows ELSE
   @StartRowIndex  END )  AND
   (CASE @StartRowIndex WHEN -1 THEN
   ( RecCount ) - @MaxRows ELSE
   @StartRowIndex  END ) + @maxRows
THEN 1

WITH CustListTemp AS
   (SELECT  CustomerID, LastName, FirstName,
    Address, City, State, Zip,
    ROW_NUMBER() OVER (ORDER BY….. )

SELECT TOP (@MaxRows)  CustomerID, LastName,
   FirstName, Address, City, State, Zip,
   RowNum  FROM
   (SELECT CustListTemp.*,  (SELECT COUNT(*) from
    CustListTemp) AS RecCount FROM CustListTemp) CustList WHERE...

List<SqlParameter> oSQLParms = new
   List<SqlParameter>();

// set any parameters to NULL, if they are blank
oSQLParms.Add(new SqlParameter("@LastName",
   LastName.Length > 0 ? LastName : null));
dsCustomer odsCustomer = new dsCustomer();
this.ReadIntoTypedDs(odsCustomer,
   "[dbo].[LookupEmployees]", oSQLParms);
return odsCustomer;

string[] alphabet = new string[] { " ", "A", "B",
   "C",..., "0", "1", "2", "3"...;
for (int i = 0; i < alphabet.Length; i++)
   this.cboAlphaIndex.Items.Add(alphabet[i].Trim());

private void NavBegin()
{
   // set the startrowindex to zero, and make sure
   // we’re not specifying a letter
   Session["startRowIndex"] = 0;
   // set alpha index pulldown back to nothing
   this.cboAlphaIndex.SelectedIndex = 0;
   this.GetData();
}

private void NavPrevious()
{
   // set the startrowindex to the row number for the
   // first record in the current page, minus 1, and
   // minus maxrows
   // so if we’re looking at rows 200-249, and we go
   // back one page, the new start row index would be
   // 200-1-50, or 149....and we’d get back 149-199
   Session["startRowIndex"] = (int)Session["CurrentFirstRow"] -
      (int)Session["MaxRows"];
   this.cboAlphaIndex.SelectedIndex = 0;
   this.GetData();
}

private void NavNext()
{
   // startrow index becomes the value of the last
   // row  [the stored proc does a ’greater than’]
   Session["startRowIndex"] = (int)Session["CurrentLastRow"] + 1;
   this.cboAlphaIndex.SelectedIndex = 0;
   this.GetData();
}

private void NavEnd()
{
   // -1 is the ’magic number’, it tells the stored
   // proc to just grab everything from
   // rowcount-maxrows, to rowcount
   Session["startRowIndex"] = -1;
   this.cboAlphaIndex.SelectedIndex = 0;
   this.GetData();
}

protected void grdResults_Sorting
    (object sender, GridViewSortEventArgs e)
{
   Session["SortCol"] = e.SortExpression.ToString().Trim();
   this.lblAlphaNav.Text = e.SortExpression.ToString().Trim() +
      " starting with...";
   this.GetData();
}

using daCustomer;
private void SetInfo(dsCustomer odsCustomer)
{
   DataRow[] aRows = odsCustomer.dtCustomer.Select
      ("customerid = -1");
   int nTotalCount = 0;
   if( aRows.Length> 0) {
      dsCustomer.dtCustomerRow oRow =
         (dsCustomer.dtCustomerRow)aRows[0];
      nTotalCount = oRow.RowNum;
      oRow.Delete();
      odsCustomer.dtCustomer.AcceptChanges();
   }
...

int nResultCount = odsCustomer.dtCustomer.Rows.Count;
if (nResultCount > 0) {
   Session["CurrentFirstRow"] =
      odsCustomer.dtCustomer[0].RowNum;
   Session["CurrentLastRow"] = odsCustomer.dtCustomer
      [nResultCount - 1].RowNum;
}
this.grdResults.Caption =
   "Number of matching records: " +
   nTotalCount.ToString().Trim() +
   "...click on any column heading to sort";

// set the DataKeyNames property
// to uniquely determine a selected row
this.grdResults.DataKeyNames =
   new string[] { "CustomerID" };

// Insert a button field to the GridView
// so that the user can select a row by clicking
// on the button icon
ButtonField obt = new ButtonField();
obt.CommandName = "Select";
obt.ButtonType = ButtonType.Image;
obt.ImageUrl = "openfolder.ico";
this.grdResults.Columns.Clear();
this.grdResults.Columns.Add(obt);

protected void grdResults_SelectedIndexChanged
   (object sender, EventArgs e)
{
   //  this reads from the DataKeyNames property
   int nCustomerID = (int)this.grdResults.
      SelectedDataKey.Values[0];
   Response.Redirect("CustomerPage.aspx?CUSTID=" +
      nCustomerID.ToString().Trim());
}

INSERT INTO @tPKList
SELECT Tbl.col.value(’.’,’int’) as IntPK
FROM   @XMLString.nodes(’//IDpk’ ) Tbl(col)

-- uses the new parameter XMLString
-- that contains the list of statuses
SELECT <column list>
FROM Customers
   JOIN [dbo].[XMLtoTable](@XMLString) StatusList
ON StatusList.IntPK = customers.statusfk

// code also adds XMLStatuses as a parameter
// to GetCustomers in Listing 4
oSQLParms.Add(
new SqlParameter("@XMLString", XMLStatuses));

DataTable dtStatus = new DataTable();
dtStatus.Columns.Add("StatusPK", typeof(Int32));
dtStatus.Columns.Add("Descript", typeof(String));
dtStatus.Rows.Add(1, "Up to Date");
dtStatus.Rows.Add(2, "Overdue 30 days");
dtStatus.Rows.Add(3, "Overdue 60 days");
dtStatus.Rows.Add(4, "Overdue 90 days");
dtStatus.Rows.Add(5, "Overdue 120 days");
dtStatus.Rows.Add(6, "Account suspended");
// Set the data binding, and the text/value fields
this.chkStatusList.DataSource = dtStatus;
this.chkStatusList.DataTextField = "descript";
this.chkStatusList.DataValueField = "statuspk";
this.chkStatusList.DataBind();

private string GetStatuses()
{
   DataTable dtSelected = new DataTable();
   dtSelected.Columns.Add("IDpk", typeof(Int32));
   foreach (ListItem oItem in this.chkStatusList.Items)
      if (oItem.Selected == true)
         dtSelected.Rows.Add(Convert.ToInt32(oItem.Value));
   DataSet ds = new DataSet();
   ds.Tables.Add(dtSelected);
   return ds.GetXml();
}

List<SqlParameter> oSQLParms = new List<SqlParameter>();
oSQLParms.Add(new SqlParameter("@LastName", LastName));
List<CustomerClass> oCustomers = new List<CustomerClass>();
// pass an instance of the list, SP name/parms,
// and a type reference to the class
this.ReadIntoCollection(oCustomers, "[dbo].[LookupEmployees]",
   oSQLParms, typeof(CustomerClass));

public void ReadIntoCollection<T>List<T> oCollection,
   string cStoredProc, List<SqlParameter> oParmList,
   Type oCollectionType)

SqlConnection oSqlConn = this.GetConnection();
SqlCommand oCmd = new SqlCommand(cStoredProc, oSqlConn);
oCmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter oParm in oParmList)
   oCmd.Parameters.Add(oParm);

oSqlConn.Open();
SqlDataReader oDR = oCmd.ExecuteReader();

while(oDR.Read())  {
   T oItem = (T)Activator.CreateInstance(
      oCollectionType);

// get all the properties of the class
PropertyInfo[] oCollectionProps = (
   (Type) oItem.GetType()).GetProperties();

for (int n=0; n<oCollectionProps.Length; n++)
{
   string cPropName = CollectionProps[n].Name;
   oCollectionProps[n].SetValue
      (oItem, oDR[cPropName], null);
}
// Add the item to the collection
oCollection.Add(oItem);
// Now get the next row in the DataReader

   while(oDR.Read())  {
      CustomerClass oCustomer = new CustomerClass();
      // no need to loop through properties, we
      // know what they are
      oCustomerClass.FirstName = oDR["FirstName"];
      oCustomerClass.LastName = oDR["LastNName"];
   }
   oCollection.Add(oItem);
}

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[LookupEmployees]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[LookupEmployees2]
go
CREATE PROCEDURE [dbo].[LookupEmployees2]
    @LastName varchar(50)=null, @FirstName varchar(50)=null,
    @Address varchar(50)=null,  @City varchar(50)=null,
    @State varchar(2)=null,     @Zip varchar(50)=null,
    @StartRowIndex int, @MaxRows int,
    @AlphaChar varchar(1)=null,   @SortCol varchar(20)=null
AS
BEGIN
SET NOCOUNT ON
DECLARE @lPaging bit
IF  @AlphaChar is null
SET @lPaging = 0
else
SET @lPaging = 1
WITH CustListTemp AS
   (SELECT CustomerID, LastName, FirstName, Address, City, State,
           Zip, ROW_NUMBER() OVER (ORDER BY
CASE @SortCol
WHEN ’LASTNAME’ THEN LastName + Firstname
WHEN ’ADDRESS’ THEN Address
WHEN ’CITY’  THEN City + LastName + Firstname
WHEN ’STATE’ THEN STATE + LastName + Firstname
WHEN ’ZIP’  THEN ZIP + LastName + Firstname
ELSE LastName + Firstname END) AS RowNum
FROM Customers
WHERE LastName LIKE ’%’+COALESCE(@LastName,LastName)+’%’ AND
   FirstName LIKE ’%’+COALESCE(@FirstName,FirstName)+’%’ AND
   Address LIKE ’%’+COALESCE(@Address,Address)+’%’ AND
   City LIKE ’%’+COALESCE(@City,City)+’%’ AND
   State LIKE ’%’+COALESCE(@State,State)+’%’ AND
   Zip LIKE ’%’+COALESCE(@Zip,Zip)+’%’   )
SELECT TOP (@MaxRows) CustomerID, LastName, FirstName, Address,
   City, State, Zip, RowNum
FROM
   ( SELECT CustListTemp.*,
      (SELECT COUNT(*) from CustListTemp) AS RecCount
       FROM CustListTemp )CustList
       WHERE
       CASE
         WHEN @lPaging = 1 AND @SortCol= ’LASTNAME’  AND
            SUBSTRING(LastName,1,1) >= RTRIM(@AlphaChar) THEN 1
         WHEN @lPaging = 1 AND @SortCol= ’ADDRESS’ AND
            SUBSTRING(Address,1,1) >= RTRIM(@AlphaChar) THEN 1
         WHEN @lPaging = 1 AND  @SortCol= ’CITY’  AND
            SUBSTRING(City,1,1) >= RTRIM(@AlphaChar) THEN 1
         WHEN @lPaging = 1 AND  @SortCol= ’STATE’ AND
            SUBSTRING(State,1,1) >= RTRIM(@AlphaChar) THEN 1
         WHEN @lPaging = 1 AND  @SortCol= ’ZIP’  AND
            SUBSTRING(Zip,1,1) >= RTRIM(@AlphaChar) THEN 1
         WHEN @lPaging = 0 AND
            RowNum BETWEEN
               ( CASE @StartRowIndex WHEN -1 THEN
               ( RecCount ) - @MaxRows ELSE  @StartRowIndex END )
            AND
               ( CASE @StartRowIndex WHEN -1 then
               ( RecCount )- @MaxRows ELSE @StartRowIndex END) +
                   @MaxRows
            THEN 1 ELSE 0 END = 1
         END
GO

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace daCustomer
{
   public class daCustomer : SimpleDataAccess.SimpleDataAccess
   {
      public dsCustomer GetCustomers(string FirstName,
         string LastName, string Address, string City,
         string State, string Zip, int StartRowIndex,
         int MaxRows, string AlphaChar, string SortCol)
      {
         if (AlphaChar == "")
            AlphaChar = null;
         List<SqlParameter> oSQLParms = new
            List<SqlParameter>();
         oSQLParms.Add(new SqlParameter("@LastName",
            LastName.Length > 0 ? LastName : null));
         oSQLParms.Add(new SqlParameter("@FirstName",
            FirstName.Length > 0 ? FirstName : null));
         oSQLParms.Add(new SqlParameter("@Address",
            Address.Length > 0 ? Address : null));
         oSQLParms.Add(new SqlParameter("@City",
            City.Length > 0 ? City : null));
         oSQLParms.Add(new SqlParameter("@State",
            State.Length > 0 ? State : null));
         oSQLParms.Add(new SqlParameter("@Zip",
            Zip.Length > 0 ? Zip : null));
         oSQLParms.Add(new SqlParameter("@startRowIndex",
            StartRowIndex));
         oSQLParms.Add(new SqlParameter("@MaxRows",  MaxRows));
         oSQLParms.Add(new SqlParameter("@alphachar", AlphaChar));
         oSQLParms.Add(new SqlParameter("@SortCol",SortCol ));
         dsCustomer odsCustomer = new dsCustomer();
         this.RetrieveDataIntoTypedDs(odsCustomer,
            "[dbo].[LookupEmployees]", oSQLParms);
         return odsCustomer;
      }
   }
}

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {
      if (!this.IsPostBack)
      {
         Session["CriteriaSet"] = false;
         string[] alphabet = new string[] { " ", "A", "B", "C",
            "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P",
            "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "0", "1", "2",
            "3", "4", "5", "6", "7", "8", "9" };
         for (int i = 0; i < alphabet.Length; i++)
            this.cboAlphaIndex.Items.Add(alphabet[i].Trim());
         this.InitializeVars();
      }
   }
   protected void btnRetrieve_Click(object sender, EventArgs e)
   {
      this.GetData();
   }
   private void SetInfo(daCustomer.dsCustomer odsCustomer)
   {
      int nResultCount = odsCustomer.dtCustomer.Rows.Count;
      if (nResultCount > 0)
      {
         Session["CurrentFirstRow"] = odsCustomer.dtCustomer[0].RowNum;
         Session["CurrentLastRow"] = odsCustomer.dtCustomer[
            nResultCount - 1].RowNum;
      }
      this.grdResults.Caption = "Number of matching records: "
         + nResultCount.ToString().Trim();
   }
   private void GetData()
   {
      string FirstName = this.txtFirstName.Text.ToString().Trim();
      string LastName = this.txtLastName.Text.ToString().Trim();
      string Address = this.txtAddress.Text.ToString().Trim();
      string City = this.txtCity.Text.ToString().Trim();
      string State = this.txtState.Text.ToString().Trim();
      string Zip = this.txtZip.Text.ToString().Trim();
      string AlphaChar = this.cboAlphaIndex.Text.ToString().Trim();
      int StartRowIndex = Convert.ToInt32(Session["StartRowIndex"]);
      int MaxRows = Convert.ToInt32(Session["MaxRows"]);
      string SortCol = Convert.ToString(Session["SortCol"]);
      daCustomer.daCustomer odaCustomer = new
         daCustomer.daCustomer();
      daCustomer.dsCustomer odsCustomer =
         odaCustomer.GetCustomers(FirstName, LastName,
         Address, City, State, Zip, StartRowIndex, MaxRows,
         AlphaChar, SortCol);
      this.SetInfo(odsCustomer);
      this.grdResults.DataSource = odsCustomer;
      this.grdResults.DataBind();
   }
   private void InitializeVars()
   {
      Session["startRowIndex"] = 0;
      Session["AlphaChar"] = null;
      Session["CurrentFirstRow"] = 0;
      Session["CurrentLastRow"] = 0;
      Session["MaxRows"] = 15;
      Session["SortCol"] = "LASTNAME";
   }
   protected void btnFirst_Click(object sender,
      ImageClickEventArgs e)
   {
      this.NavBegin();
      this.GetData();
   }
   protected void btnPrev_Click(object sender, ImageClickEventArgs e)
   {
      this.NavPrevious();
      this.GetData();
   }
   protected void btnNext_Click(object sender, ImageClickEventArgs e)
   {
      this.NavNext();
      this.GetData();
   }
   protected void btnLast_Click(object sender, ImageClickEventArgs e)
   {
      this.NavEnd();
      this.GetData();
   }
   private void NavBegin()
   {
      // set the startrowindex to zero, and make sure we’re
      // not specifying a letter
      Session["startRowIndex"] = 0;
      Session["AlphaChar"] = null;
      this.cboAlphaIndex.SelectedIndex = 0;
   }
   private void NavPrevious()
   {
      // set the startrowindex to the row number for the first
      // record in the current page, minus 1, and minus maxrows
      // so if we’re looking at rows 200-249, and we go back one
      // page, the new start row index would be 200-1-50, or
      // 149....and we’d get back 149-199
      Session["startRowIndex"] = (int)Session["CurrentFirstRow"] -
         (int)Session["MaxRows"];
      Session["AlphaChar"] = null;
      this.cboAlphaIndex.SelectedIndex = 0;
   }
   private void NavNext()
   {
      // startrow index becomes the value of the last row  [the
      // stored proc does a ’greater than’]
      Session["startRowIndex"] = (int)Session["CurrentLastRow"] + 1;
      Session["AlphaChar"] = null;
      this.cboAlphaIndex.SelectedIndex = 0;
   }
   private void NavEnd()
   {
      // -1 is the ’magic number’, it tells the stored proc to
      // just grab everything from rowcount-maxrows, to rowcount
      Session["startRowIndex"] = -1;
      Session["AlphaChar"] = null;
      this.cboAlphaIndex.SelectedIndex = 0;
   }
   protected void cboAlphaIndex_SelectedIndexChanged(
      object sender, EventArgs e)
   {
      this.GetData();
   }
   protected void grdResults_Sorting(object sender,
      GridViewSortEventArgs e)
   {
      Session["SortCol"] = e.SortExpression.ToString().Trim();
      this.GetData();
   }
   protected void grdResults_SelectedIndexChanged
                                (object sender, EventArgs e)
   {
      int nCustomerID = (int)this.grdResults.SelectedDataKey.Values[0];
      Response.Redirect("CustomerPage.aspx?CUSTID=" +
         nCustomerID.ToString().Trim());
   }
}

-- Table-valued UDF to convert an XML string
-- to a table-valued UDF
-- Useful if you have an XML string of user-selections,
-- and want to convert them to a Table variable that
-- you can use in subsequent JOIN statements
-- Uses the new XML data type
CREATE FUNCTION [dbo].[XMLtoTable]
   (@XMLString XML )
RETURNS
   @tPKList TABLE  ( IntPK int )   -- returns table variable
AS
BEGIN
INSERT INTO @tPKList
SELECT Tbl.col.value(’.’,’int’) as IntPK
FROM   @XMLString.nodes(’//IDpk’ ) Tbl(col)
-- use nodes() method to shred XML data into relational
-- data.  Incoming XML must have a numeric column
-- with the name IDpk
RETURN
END

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
namespace SimpleDataAccess
{
   public class SimpleDataAccess
   {
      public void RetrieveIntoCollection<T>(
         List<T> oCollection , string cStoredProc,
         List<SqlParameter> oParmList, Type oCollectionType)
      {
         SqlConnection oSqlConn = this.GetConnection();
         SqlCommand oCmd = new SqlCommand(cStoredProc, oSqlConn);
         oCmd.CommandType = CommandType.StoredProcedure;
         foreach (SqlParameter oParm in oParmList)
            oCmd.Parameters.Add(oParm);
         oSqlConn.Open();
         SqlDataReader oDR = oCmd.ExecuteReader();
         while(oDR.Read())  {
            T oItem = (T)Activator.CreateInstance(oCollectionType);
            // get all the properties of the class
            PropertyInfo[] oCollectionProps =
               ((Type) oItem.GetType()).GetProperties();
            for (int n=0; n<oCollectionProps.Length; n++)  {
               string cPropName = oCollectionProps[n].Name;
               oCollectionProps[n].SetValue(oItem, oDR[cPropName], null);
            }
            oCollection.Add(oItem);
         }
         oSqlConn.Close();
      }
      public SqlConnection GetConnection()
      {
         SqlConnectionStringBuilder oStringBuilder = new
            SqlConnectionStringBuilder();
         oStringBuilder.UserID = "sa";
         oStringBuilder.Password = "";
         oStringBuilder.InitialCatalog = "NewCustomer";
         oStringBuilder.DataSource = "KCI890";
         return new SqlConnection(oStringBuilder.ConnectionString);
      }
   }
}

DataView dv = new DataView(dt);
dv.RowFilter =
   "LocationID in (1,2) AND AmountDue > 10000";
dv.Sort = "LocationID, AmountDue DESC";

// anonymous method to filter on Location = 1
List<CustomerRec> oFilteredCustomers =
   oCustomerRecs.FindAll(
   (delegate(CustomerRec oRec)  {
   return (oRec.LocationID == 1 );})
   );

// anonymous method to sort on amount due DESC
// by reversing the incoming parameters
oFilteredCustomers.Sort(
   delegate(CustomerRec oRec1, CustomerRec oRec2)
   { return oRec2.AmountDue.CompareTo
      (oRec1.AmountDue); });

// anonymous method to filter on
// either Location 1 or 2, AND amount due GT 10000
List<CustomerRec> oFilteredCustomers =
   oCustomerRecs.FindAll((delegate(CustomerRec oRec)  {
   return (
      (oRec.LocationID == 1 || oRec.LocationID == 2)
      && oRec.AmountDue > 10000);
   }));

// Now sort on amount due DESC, within Location
// To do so, check the two incoming locations 1st
// If they are equal, reverse the order of two
// incoming parameters, and compare the amount due
// [just like above]
// If they AREN’T equal, compare the two locations
oFilteredCustomers.Sort(
   delegate(CustomerRec oRec1, CustomerRec oRec2)
   { return oRec1.LocationID == oRec2.LocationID ?
       oRec2.AmountDue.CompareTo(oRec1.AmountDue):
       oRec1.LocationID.CompareTo(oRec2.LocationID);
   });