Hi,
I am trying to find a logic for reading sql query data and displaying it properly.
i have 2 sql tables Modul & Relation (see attachments) which i am using to create a GDL file format (see Old and New GDL file attachments).
I am trying to extract data from these tables based on the Logic that,
1] for each "von" column element in Relation table, search for row containing "von" element in Modul table and see to which "group" it belongs and place the corresponding nodes and edges under that group.
The nodes and their corresponding edges will be understood from the GDL files which i have attached.
If any group element is empty, then place the nodes and edges in the default group above as shown in the New GDL file example.
Right now the code which i have written in VB.Net does not produce the desired format.
Its coming as :
(for empty group){
nodes
edges
nodes
edges
(for G1 group)
group{
nodes
edges
}
group{
nodes
edges
}
group{
nodes
edges
}
(for G2 group)
group{
nodes
edges
}
group{
nodes
edges
}
group{
nodes
edges
}
}
.........
Whereas it should be like this: (can also see attached New GDL file)
(for empty group)
Default Group {
nodes
edges
nodes
edges
(for G1 group)
group{
nodes
nodes
nodes
edges
edges
edges
}
(for G2 group)
group{
nodes
nodes
nodes
edges
edges
edges
}
}
.........
Imports System.Data.SqlClient
Public Class Form1
Dim myConnection As New SqlConnection
Dim myCommand As SqlCommand
Dim dr As SqlDataReader
Dim connStr As String
'declaring the objects
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim GDL As String
Dim Risiko As Integer
Dim Arr As New ArrayList()
Dim Counter As Integer
GDL = "graph: {title: ""Student""" & vbCrLf & " node.color : lightcyan " & vbCrLf & _
" node.textcolor : darkblue " & vbCrLf & " edge.color : darkblue " & vbCrLf & " outport_sharing: yes " & vbCrLf & _
" splines: yes " & vbCrLf & " manhattan_edges: yes " & vbCrLf & " display_edge_labels: yes " & _
vbCrLf & " node.fontname : ""helvB10"" " & vbCrLf & " edge.fontname : ""helvB10"" " & _
vbCrLf & " layoutalgorithm: dfs " & vbCrLf & " crossing_optimization: yes " & _
vbCrLf & " orientation:lefttoright "
connStr = "Data Source=SIFAR;Initial Catalog=test;Integrated Security=True"
myConnection = New SqlConnection(connStr)
Try
myConnection.Open()
'get groups
myCommand = New SqlCommand("SELECT DISTINCT [group] FROM modul ORDER BY [group]", myConnection)
dr = myCommand.ExecuteReader()
While dr.Read()
Arr.Add(Trim(dr.Item("group").ToString))
End While
dr.Close()
Dim i As Integer
'loop thru each group, blank or non-blank
For i = 0 To Arr.Count - 1
Counter = 0
myCommand = New SqlCommand("SELECT COUNT(*) AS Cntr FROM modul AS m INNER JOIN relation AS r ON m.mod_name = r.von WHERE (m.[group] = '" & Arr.Item(i).ToString & "')", myConnection)
dr = myCommand.ExecuteReader()
Do
While dr.Read()
Counter = Counter + 1
End While
Loop While dr.NextResult
MsgBox(Counter)
dr.Close()
myCommand = New SqlCommand("SELECT DISTINCT m.mod_name, m.eigenrisiko, m.TMP_Eingangsrisiko, r.relation_name, r.von, r.nach, r.tmp_risiko AS risiko FROM modul AS m INNER JOIN relation AS r ON m.mod_name = r.von WHERE (m.[group] = '" & Arr.Item(i).ToString & "') ORDER BY m.mod_name", myConnection)
dr = myCommand.ExecuteReader()
Do
While dr.Read()
If Arr.Item(i).ToString = "" Then
Risiko = CInt(Trim(dr.Item("eigenrisiko").ToString))
'for Nodes
If Risiko <= 100 Then
GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: green} "
ElseIf (Risiko > 101) And (Risiko <= 300) Then
GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: yellow} "
ElseIf (Risiko > 300) Then
GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: red} "
End If
'for Edges
If Risiko <= 100 Then
GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "
ElseIf (Risiko > 101) And (Risiko <= 300) Then
GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "
ElseIf (Risiko > 300) Then
GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "
End If
ElseIf Arr.Item(i).ToString <> "" Then
GDL &= vbCrLf & "graph: {status: clustered" & vbCrLf & "title: """ & Arr.Item(i).ToString & """" & vbCrLf & "textcolor: black" & vbCrLf & "status: folded" & vbCrLf & "width:70" & vbCrLf & "bordercolor: darkgreen" & vbCrLf & "fontname: ""timB"""
Risiko = CInt(Trim(dr.Item("eigenrisiko").ToString))
'for Nodes
If Risiko <= 100 Then
GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: green} "
ElseIf (Risiko > 101) And (Risiko <= 300) Then
GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: yellow} "
ElseIf (Risiko > 300) Then
GDL &= vbCrLf & "node: {title: """ & Trim(dr.Item("mod_name").ToString) & """ label: """ & Trim(dr.Item("mod_name").ToString) & " \n " & Trim(dr.Item("TMP_Eingangsrisiko").ToString) & " \n " & Trim(dr.Item("eigenrisiko").ToString) & """ color: red} "
End If
'for Edges
If Risiko <= 100 Then
GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "
ElseIf (Risiko > 101) And (Risiko <= 300) Then
GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "
ElseIf (Risiko > 300) Then
GDL &= vbCrLf & "edge: {source: """ & Trim(dr.Item("von").ToString) & """ target: """ & Trim(dr.Item("nach").ToString) & """ label: """ & Trim(dr.Item("Relation_Name").ToString) & ":" & Trim(dr.Item("risiko").ToString) & """} "
End If
End If
End While
Loop While dr.NextResult
GDL &= vbCrLf & "}"
dr.Close()
Next i
myConnection.Close()
MsgBox(GDL.ToString())
Catch ex As Exception
End Try
End Sub
End ClassAny help would be most appreciated.
Regards