1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
|
#Region "GetJoin"
''' <summary>
''' retourne une table avec la jointure faite entre les 2 tables passés en parametre
''' </summary>
''' <param name="Table1"></param>
''' <param name="Table2"></param>
''' <param name="fields">les champs seront separés par un ; </param>
''' <param name="errorMessage"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function GetJoin(ByRef Table1 As DataTable, ByRef Table2 As DataTable, ByVal fields As String, ByRef errorMessage As String, Optional ByVal where As String = "", Optional ByVal orderby As String = "") As DataTable
Try
Dim Field_1 As String = fields.Split(";")(0)
Dim Field_2 As String = fields.Split(";")(1)
Dim returnDT As DataTable = New DataTable()
'on recopie les colonnes dans la table de sortie
For Each col As DataColumn In Table1.Columns
If Not returnDT.Columns.Contains(col.ColumnName) Then returnDT.Columns.Add(col.ColumnName, col.DataType)
Next
For Each col As DataColumn In Table2.Columns
If Not returnDT.Columns.Contains(col.ColumnName) Then returnDT.Columns.Add(col.ColumnName, col.DataType)
Next
Dim jointure As String = String.Empty
If Table1.Columns(Field_1).DataType Is System.Type.GetType("System.String") Then
jointure = Field_2 & " = '{0}'"
Else
jointure = Field_2 & " = {0}"
End If
Dim newRow As DataRow = Nothing
'pour toutes les lignes de la table 1
For Each row As DataRow In Table1.Rows
'je fais la joiture sur la table 2 et j en recupere les lignes
Dim rows() As DataRow = Table2.Select(String.Format(jointure, row(Field_1)))
For Each row2 As DataRow In rows
newRow = returnDT.NewRow()
'je recopie les donné de la table1
For Each col As DataColumn In Table1.Columns
newRow(col.ColumnName) = row(col.ColumnName)
Next
'je recopie les donné de la table2
For Each col As DataColumn In Table2.Columns
newRow(col.ColumnName) = row2(col.ColumnName)
Next
returnDT.Rows.Add(newRow)
Next
Next
's il y a une clause where
If where.Length > 0 Then
Dim dv As DataView = New DataView(returnDT)
dv.RowFilter = where.Replace("WHERE ", "")
returnDT = Me.GetTableFromDataView(dv)
End If
's il y a un tri
If orderby.Length > 0 Then
Dim dv As DataView = New DataView(returnDT)
dv.Sort = orderby.Replace("ORDER BY ", "")
returnDT = Me.GetTableFromDataView(dv)
End If
Return returnDT
Catch ex As Exception
Console.WriteLine(ex.ToString())
errorMessage = ex.Message
Return Nothing
End Try
End Function
#End Region |
Partager