199 lines
9.7 KiB
VB.net
199 lines
9.7 KiB
VB.net
Imports DigitalData.Modules.Logging
|
|
Imports Oracle.ManagedDataAccess.Client
|
|
Public Class frmSQLSuggestion
|
|
Private _Logger As Logger
|
|
Private _PostProcessing As ClassPostprocessing
|
|
|
|
Private Sub frmSQLSuggestion_Load(sender As Object, e As EventArgs) Handles Me.Load
|
|
Try
|
|
_Logger = LOGCONFIG.GetLogger()
|
|
_PostProcessing = New ClassPostprocessing(LOGCONFIG)
|
|
|
|
'TODO: Diese Codezeile lädt Daten in die Tabelle "MyDataset.TBDD_CONNECTION". Sie können sie bei Bedarf verschieben oder entfernen.
|
|
Me.TBDD_CONNECTIONTableAdapter.Connection.ConnectionString = MyConnectionString
|
|
Me.TBDD_CONNECTIONTableAdapter.Fill(Me.MyDataset.TBDD_CONNECTION)
|
|
Dim DT As DataTable = DATABASE_ECM.GetDatatable("SELECT * FROM TBDD_INDEX_MAN WHERE GUID = " & CURRENT_INDEXMAN)
|
|
If DT.Rows.Count = 1 Then
|
|
Me.cmbConnection.SelectedValue = DT.Rows(0).Item("CONNECTION_ID")
|
|
SQL_ERGEBNISTextBox.Text = DT.Rows(0).Item("SQL_RESULT")
|
|
SQL_UEBERPRUEFUNGTextBox.Text = DT.Rows(0).Item("SQL_CHECK")
|
|
End If
|
|
lblSave.Visible = False
|
|
Catch ex As Exception
|
|
_Logger.Error(ex)
|
|
MsgBox(ex.Message, MsgBoxStyle.Critical, "Unexpected error in Open Form:")
|
|
End Try
|
|
End Sub
|
|
|
|
Private Sub btnVorschlag_Click(sender As Object, e As EventArgs) Handles btnVorschlag.Click
|
|
TestSQL(cmbConnection.SelectedValue, SQL_ERGEBNISTextBox.Text)
|
|
End Sub
|
|
Private Sub TestSQL(conid As Integer, sql_Vorschlag As String)
|
|
Try
|
|
Dim connectionString As String
|
|
|
|
Dim i As Integer
|
|
|
|
connectionString = DATABASE_ECM.Get_ConnectionStringforID(conid)
|
|
If connectionString <> "" Then
|
|
If connectionString.Contains("Initial Catalog=") Then
|
|
'Dim DT As DataTable = DATABASE_ECM.GetDatatable(sql_Vorschlag)
|
|
'If DT Is Nothing = False Then
|
|
' Dim y As Integer = 0
|
|
' For Each row As DataRow In DT.Rows
|
|
' For Each item In row.ItemArray
|
|
' Console.WriteLine(item)
|
|
' Next
|
|
' Next
|
|
'End If
|
|
|
|
Dim sqlCnn As SqlClient.SqlConnection
|
|
Dim sqlCmd As SqlClient.SqlCommand
|
|
Dim adapter As New SqlClient.SqlDataAdapter
|
|
Dim NewDataset As New DataSet
|
|
sqlCnn = New SqlClient.SqlConnection(connectionString)
|
|
sqlCnn.Open()
|
|
sqlCmd = New SqlClient.SqlCommand(sql_Vorschlag, sqlCnn)
|
|
adapter.SelectCommand = sqlCmd
|
|
adapter.Fill(NewDataset)
|
|
Dim msg As String
|
|
For i = 0 To NewDataset.Tables(0).Rows.Count - 1
|
|
msg &= NewDataset.Tables(0).Rows(i).Item(0) & vbNewLine
|
|
If i = 30 Then
|
|
Exit For
|
|
End If
|
|
Next
|
|
adapter.Dispose()
|
|
sqlCmd.Dispose()
|
|
sqlCnn.Close()
|
|
|
|
If USER_LANGUAGE = "de-DE" Then
|
|
MsgBox("Folgende Daten wurden erfolgreich abgerufen: " & vbNewLine & vbNewLine & msg & vbNewLine & vbNewLine & "Das Ergebnis wurde auf 30 Zeilen eingeschränkt!", MsgBoxStyle.Information)
|
|
Else
|
|
MsgBox("The following data was successfully retrieved: " & vbNewLine & vbNewLine & msg & vbNewLine & vbNewLine & "The Result was truncated to 30 Lines!", MsgBoxStyle.Information)
|
|
End If
|
|
|
|
ElseIf connectionString.StartsWith("Data Source=") And connectionString.Contains("SERVICE_NAME") Then
|
|
Dim sqlCnn As OracleConnection
|
|
Dim sqlCmd As OracleCommand
|
|
Dim adapter As New OracleDataAdapter
|
|
Dim NewDataset As New DataSet
|
|
sqlCnn = New OracleConnection(connectionString)
|
|
' Try
|
|
sqlCnn.Open()
|
|
sqlCmd = New OracleCommand(sql_Vorschlag, sqlCnn)
|
|
adapter.SelectCommand = sqlCmd
|
|
adapter.Fill(NewDataset)
|
|
Dim msg As String
|
|
For i = 0 To NewDataset.Tables(0).Rows.Count - 1
|
|
msg &= NewDataset.Tables(0).Rows(i).Item(0) & vbNewLine
|
|
If i = 30 Then
|
|
Exit For
|
|
End If
|
|
Next
|
|
adapter.Dispose()
|
|
sqlCmd.Dispose()
|
|
sqlCnn.Close()
|
|
|
|
If USER_LANGUAGE = "de-DE" Then
|
|
MsgBox("Folgende Daten wurden erfolgreich abgerufen: " & vbNewLine & vbNewLine & msg & vbNewLine & vbNewLine & "Das Ergebnis wurde auf 30 Zeilen eingeschränkt!", MsgBoxStyle.Information)
|
|
Else
|
|
MsgBox("The following data was successfully retrieved: " & vbNewLine & vbNewLine & msg & vbNewLine & vbNewLine & "The Result was truncated to 30 Lines!", MsgBoxStyle.Information)
|
|
End If
|
|
End If
|
|
Else
|
|
MsgBox("Keine gültige ConnectionID", MsgBoxStyle.Exclamation)
|
|
|
|
If USER_LANGUAGE = "de-DE" Then
|
|
MsgBox("Keine gültige ConnectionID", MsgBoxStyle.Exclamation)
|
|
Else
|
|
MsgBox("No valid ConnectionID", MsgBoxStyle.Exclamation)
|
|
End If
|
|
|
|
End If
|
|
|
|
Catch ex As Exception
|
|
_Logger.Info(" - Unexpected error in TestSQL - Fehler: " & vbNewLine & ex.Message)
|
|
_Logger.Error(ex)
|
|
MsgBox(ex.Message, MsgBoxStyle.Critical, "Unexpected error in TestSQL:")
|
|
End Try
|
|
End Sub
|
|
|
|
|
|
|
|
Private Sub TBDD_CONNECTIONBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
|
|
Me.Validate()
|
|
Me.TBDD_CONNECTIONBindingSource.EndEdit()
|
|
Me.TableAdapterManager.UpdateAll(Me.MyDataset)
|
|
|
|
End Sub
|
|
|
|
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
|
|
frmConnections.ShowDialog()
|
|
Me.TBDD_CONNECTIONTableAdapter.Fill(Me.MyDataset.TBDD_CONNECTION)
|
|
End Sub
|
|
|
|
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
|
|
Try
|
|
'ClassDatabase.Init()
|
|
Dim origresultstring = DATABASE_ECM.GetScalarValue("SELECT SQL_RESULT FROM TBDD_INDEX_MAN WHERE GUID = " & CURRENT_INDEXMAN)
|
|
Dim origcheckstring = DATABASE_ECM.GetScalarValue("SELECT SQL_CHECK FROM TBDD_INDEX_MAN WHERE GUID = " & CURRENT_INDEXMAN)
|
|
Dim origConnection = DATABASE_ECM.GetScalarValue("SELECT CONNECTION_ID FROM TBDD_INDEX_MAN WHERE GUID = " & CURRENT_INDEXMAN)
|
|
Dim insresultstring = SQL_ERGEBNISTextBox.Text.Replace("'", "''")
|
|
Dim inscheckstring = SQL_UEBERPRUEFUNGTextBox.Text.Replace("'", "''")
|
|
If insresultstring <> origresultstring Or
|
|
inscheckstring <> origcheckstring Then
|
|
Dim cmd As String
|
|
cmd = "UPDATE TBDD_INDEX_MAN SET CONNECTION_ID = " & cmbConnection.SelectedValue & ",SQL_RESULT = '" & insresultstring & "',SQL_CHECK = '" & inscheckstring & "',CHANGED_WHO = '" &
|
|
Environment.UserName & "' WHERE GUID = " & CURRENT_INDEXMAN
|
|
If DATABASE_ECM.ExecuteNonQuery(cmd) = True Then
|
|
If USER_LANGUAGE = "de-DE" Then
|
|
lblSave.Text = "SQL-Daten erfolgreich gespeichert - " & Now
|
|
Else
|
|
lblSave.Text = "SQL-Data successfully saved - " & Now
|
|
End If
|
|
|
|
Me.lblSave.Visible = True
|
|
Else
|
|
Me.lblSave.Visible = False
|
|
End If
|
|
|
|
Else
|
|
Me.lblSave.Visible = True
|
|
If USER_LANGUAGE = "de-DE" Then
|
|
lblSave.Text = "Keine Änderungen vorhanden"
|
|
Else
|
|
lblSave.Text = "No changes"
|
|
End If
|
|
End If
|
|
Catch ex As Exception
|
|
_Logger.Error(ex)
|
|
MsgBox(ex.Message, MsgBoxStyle.Critical, "Error in Save SQl IndexMan:")
|
|
End Try
|
|
End Sub
|
|
|
|
Private Sub btnTestNachbearbeitung_Click(sender As Object, e As EventArgs) Handles btnTestNachbearbeitung.Click
|
|
'Die Nachbearbeitungsschritte laden
|
|
Dim DTNB As DataTable = DATABASE_ECM.GetDatatable("SELECT * FROM TBDD_INDEX_MAN_POSTPROCESSING WHERE IDXMAN_ID = " & CURRENT_INDEXMAN & " ORDER BY SEQUENCE")
|
|
If DTNB Is Nothing = False Then
|
|
If DTNB.Rows.Count > 0 Then
|
|
Dim value As String = _PostProcessing.Get_Nachbearbeitung_Wert(txtTest_idxMan.Text, DTNB)
|
|
MsgBox("Der Rückgabewert - nach Durchlaufen von (" & DTNB.Rows.Count.ToString & ") Nachbearbeitungsfunktionen lautet: '" & value & "'", MsgBoxStyle.Exclamation)
|
|
|
|
If USER_LANGUAGE = "de-DE" Then
|
|
MsgBox("Der Rückgabewert - nach Durchlaufen von (" & DTNB.Rows.Count.ToString & ") Nachbearbeitungsfunktionen lautet: '" & value & "'", MsgBoxStyle.Exclamation)
|
|
Else
|
|
MsgBox("The Resultvalue - after processing (" & DTNB.Rows.Count.ToString & ") Postprocessingfunctions is: '" & value & "'", MsgBoxStyle.Exclamation)
|
|
End If
|
|
End If
|
|
End If
|
|
End Sub
|
|
|
|
|
|
Private Sub SQL_ERGEBNISTextBox_KeyPress(sender As Object, e As KeyPressEventArgs) Handles SQL_ERGEBNISTextBox.KeyPress
|
|
If e.KeyChar = Convert.ToChar(1) Then
|
|
DirectCast(sender, TextBox).SelectAll()
|
|
e.Handled = True
|
|
End If
|
|
End Sub
|
|
End Class |