(DotNet) NET (2013 год)

How to reorder DataRow with Extension function, Anonymous types, Lambda Expression and Linq Special Row Comparer.

I had wrote some articles about expand Linq-to-SQL with own function Мої поширення Linq-to-SQL (in Ukrainian languages) and the same articles in English you may see on this page Five amazing Linq-to-SQL extension, and also I had wrote some articles about creation Extension function to Data.DataTable class - Змінні Nullable та як обробляти DBNull з бази за допомогою Extension-функції (in Ukrainian languages).In this page I continue a description how to expand Data.DataTable class with own extension.

Main problem of using Data.DataRowCollection is reordering your collection. I many times made similar task, but in this project this task is extremely clear. Let's look to screen below.

In this WebForm I need show two record from DB.


desen

This is a same records inside in my program as row in DataTable.



This two records produced by huge existing BLL as simple Row in DataTable and I don't have opportunity to rebuild this huge BLL.



But, if customer click on screen in one of this records, this record should pop up on the top.



So, we should to reorder record of DataTable.


I resolve this problem by expand Microsoft DataTable class with my own extension function Resort.



Below we may show my function Resort, what demonstrate common pattern of reordering records in DataTable, but this is real code, applicable to my program, what contains many specific of my project.


   1:  Imports Microsoft.VisualBasic
   2:  Imports System.Data
   3:  Imports System.Linq
   4:   
   5:  Public Module Extension3
   6:      ''' <summary>
   7:      ''' Resort datatable for special criteria
   8:      ''' </summary>
   9:      ''' <typeparam name="T"></typeparam>
  10:      ''' <param name="dt">Source Datatable (usually random)</param>
  11:      ''' <param name="WithoutRow_SaveItForLater">Boolean</param>
  12:      ''' <param name="ClientNameToTop">Session("UserN")</param>
  13:      ''' <param name="BasketItemID_IsNothing">Boolean</param>
  14:      ''' <returns>Return reordered datatable</returns>
  15:      <Runtime.CompilerServices.Extension()> _
  16:      Public Function Resort(Of T As Data.DataTable)(dt As T, WithoutRow_SaveItForLater As Boolean, Optional ClientNameToTop As String = Nothing, Optional BasketItemID_IsNothing As Boolean = False) As Data.DataTable
  17:   
  18:   
  19:          Dim Y = (From X In dt.AsEnumerable() Select X).ToList
  20:          If WithoutRow_SaveItForLater Then
  21:              Y = (From X In dt.AsEnumerable() Select X Where X("SaveItForLater") Is DBNull.Value).ToList
  22:          End If
  23:   
  24:          If Y.Count = 0 Then
  25:              Return New Data.DataTable
  26:              Exit Function
  27:          End If
  28:   
  29:          Dim Z As List(Of Data.DataRow)
  30:          If ClientNameToTop Is Nothing Then
  31:   
  32:              If BasketItemID_IsNothing Then
  33:                  Z = (From X In Y Select X Order By X("FirstName") & " " & X("LastName")).ToList
  34:              Else
  35:                  'add second order criteria
  36:                  Z = (From X In Y Select X Order By X("FirstName") & " " & X("LastName"), X("BasketItemID") Descending).ToList
  37:              End If
  38:   
  39:          Else
  40:   
  41:              Dim SpecialRowComparer1 As New SpecialRowComparer(ClientNameToTop)
  42:   
  43:              'insert in anonymous types Q runtime interfaces System.Linq.OrderedEnumerable
  44:              Dim Q = Y.OrderBy(Of String)(Function(X) X("FirstName") & " " & X("LastName"), SpecialRowComparer1)
  45:   
  46:              If Not BasketItemID_IsNothing Then
  47:                  'add second order criteria
  48:                  Q = Y.OrderBy(Of String)(Function(X) X("FirstName") & " " & X("LastName"), SpecialRowComparer1).ThenByDescending(Of Integer)(Function(X) X("BasketItemID"))
  49:              End If
  50:   
  51:              'download Data.DataRow from System.Linq.OrderedEnumerable to List(Of Data.DataRow)
  52:              Z = New List(Of Data.DataRow)
  53:              For Each One As Data.DataRow In Q
  54:                  Z.Add(One)
  55:              Next
  56:   
  57:          End If
  58:   
  59:          If Z Is Nothing Then
  60:              Return New Data.DataTable
  61:          Else
  62:              Return Z.CopyToDataTable
  63:          End If
  64:   
  65:      End Function
  66:   
  67:  End Module
  68:   
  69:   
  70:  Public Class SpecialRowComparer
  71:      Implements IComparer(Of String)
  72:   
  73:      Dim _ClientNameToTop As String
  74:      Public Sub New(ClientNameToTop As String)
  75:          _ClientNameToTop = ClientNameToTop
  76:      End Sub
  77:   
  78:      Public Function Compare(x As String, y As String) As Integer Implements IComparer(Of String).Compare
  79:          If x = _ClientNameToTop And y <> _ClientNameToTop Then
  80:              Return -1
  81:          ElseIf x <> _ClientNameToTop And y = _ClientNameToTop Then
  82:              Return 1
  83:          ElseIf x = _ClientNameToTop And y = _ClientNameToTop Then
  84:              Return 0
  85:          ElseIf x <> _ClientNameToTop And y <> _ClientNameToTop Then
  86:              Return -x.CompareTo(y)
  87:          End If
  88:      End Function
  89:  End Class

This is not simple code, need small efforts to understand it. From record 70 you may see a record comparer, that comparing DataRow records in DataTable structure by criteria, needed for me.

But firstly lets look to function definition in string 16. You may see this is Generic function for any class T as DataTable (in my application expanding class named RandomdtBasketList). For any Extension function (with attributes '<Runtime.CompilerServices.Extension()>') first parameters "dt As T" means class to expand.

In the beginning I transform DataTable to DataRow collection:



First branch from line 30 to 40 is very simple, this is only simple reordering this rows by classic Linq syntax. But line 44 is more interesting. This rows reordering by Lambda Expression, System.Linq.OrderedEnumerable and special Row Comparer. Result is stored in anonymous types Q. And before finist I assembly rows from anonymous type Q to DataRow.




Thats it! But I have many-many interesting Extension function (for example ConvertDataTableToString - simple function to debugging) and I will continue to publish their.





Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23
Link to this page: //www.vb-net.com/DataTable-SortRows/index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>