You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
407 lines
17 KiB
407 lines
17 KiB
Imports System.IO
|
|
Imports System.Data
|
|
Imports System.Data.SqlTypes
|
|
Imports System.Data.SqlClient
|
|
|
|
Public Class frmQuery
|
|
Inherits System.Windows.Forms.Form
|
|
|
|
#Region " Vom Windows Form Designer generierter Code "
|
|
|
|
Public Sub New()
|
|
MyBase.New()
|
|
|
|
' Dieser Aufruf ist für den Windows Form-Designer erforderlich.
|
|
InitializeComponent()
|
|
|
|
' Initialisierungen nach dem Aufruf InitializeComponent() hinzufügen
|
|
|
|
End Sub
|
|
|
|
' Die Form überschreibt den Löschvorgang der Basisklasse, um Komponenten zu bereinigen.
|
|
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
|
|
If disposing Then
|
|
If Not (components Is Nothing) Then
|
|
components.Dispose()
|
|
End If
|
|
End If
|
|
MyBase.Dispose(disposing)
|
|
End Sub
|
|
|
|
' Für Windows Form-Designer erforderlich
|
|
Private components As System.ComponentModel.IContainer
|
|
|
|
'HINWEIS: Die folgende Prozedur ist für den Windows Form-Designer erforderlich
|
|
'Sie kann mit dem Windows Form-Designer modifiziert werden.
|
|
'Verwenden Sie nicht den Code-Editor zur Bearbeitung.
|
|
Friend WithEvents ToolBar1 As System.Windows.Forms.ToolBar
|
|
Friend WithEvents ImageList1 As System.Windows.Forms.ImageList
|
|
Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
|
|
Friend WithEvents ToolBarButton1 As System.Windows.Forms.ToolBarButton
|
|
Friend WithEvents ToolBarButton2 As System.Windows.Forms.ToolBarButton
|
|
Friend WithEvents ToolBarButton3 As System.Windows.Forms.ToolBarButton
|
|
Friend WithEvents OpenFileDialog1 As System.Windows.Forms.OpenFileDialog
|
|
Friend WithEvents Splitter1 As System.Windows.Forms.Splitter
|
|
Friend WithEvents DokList As C1.Win.C1TrueDBGrid.C1TrueDBGrid
|
|
Friend WithEvents ToolBarButton4 As System.Windows.Forms.ToolBarButton
|
|
Friend WithEvents ToolBarButton5 As System.Windows.Forms.ToolBarButton
|
|
Friend WithEvents ToolBarButton6 As System.Windows.Forms.ToolBarButton
|
|
Friend WithEvents ToolBarButton7 As System.Windows.Forms.ToolBarButton
|
|
Friend WithEvents ToolBarButton8 As System.Windows.Forms.ToolBarButton
|
|
Friend WithEvents ToolBarButton9 As System.Windows.Forms.ToolBarButton
|
|
Friend WithEvents SaveFileDialog1 As System.Windows.Forms.SaveFileDialog
|
|
Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
|
|
Friend WithEvents Label1 As System.Windows.Forms.Label
|
|
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
|
|
Me.components = New System.ComponentModel.Container
|
|
Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(frmQuery))
|
|
Me.ToolBar1 = New System.Windows.Forms.ToolBar
|
|
Me.ToolBarButton1 = New System.Windows.Forms.ToolBarButton
|
|
Me.ToolBarButton2 = New System.Windows.Forms.ToolBarButton
|
|
Me.ToolBarButton3 = New System.Windows.Forms.ToolBarButton
|
|
Me.ToolBarButton4 = New System.Windows.Forms.ToolBarButton
|
|
Me.ToolBarButton5 = New System.Windows.Forms.ToolBarButton
|
|
Me.ToolBarButton6 = New System.Windows.Forms.ToolBarButton
|
|
Me.ToolBarButton7 = New System.Windows.Forms.ToolBarButton
|
|
Me.ToolBarButton9 = New System.Windows.Forms.ToolBarButton
|
|
Me.ToolBarButton8 = New System.Windows.Forms.ToolBarButton
|
|
Me.ImageList1 = New System.Windows.Forms.ImageList(Me.components)
|
|
Me.TextBox1 = New System.Windows.Forms.TextBox
|
|
Me.OpenFileDialog1 = New System.Windows.Forms.OpenFileDialog
|
|
Me.Splitter1 = New System.Windows.Forms.Splitter
|
|
Me.DokList = New C1.Win.C1TrueDBGrid.C1TrueDBGrid
|
|
Me.SaveFileDialog1 = New System.Windows.Forms.SaveFileDialog
|
|
Me.ComboBox1 = New System.Windows.Forms.ComboBox
|
|
Me.Label1 = New System.Windows.Forms.Label
|
|
CType(Me.DokList, System.ComponentModel.ISupportInitialize).BeginInit()
|
|
Me.SuspendLayout()
|
|
'
|
|
'ToolBar1
|
|
'
|
|
Me.ToolBar1.Buttons.AddRange(New System.Windows.Forms.ToolBarButton() {Me.ToolBarButton1, Me.ToolBarButton2, Me.ToolBarButton3, Me.ToolBarButton4, Me.ToolBarButton5, Me.ToolBarButton6, Me.ToolBarButton7, Me.ToolBarButton9, Me.ToolBarButton8})
|
|
Me.ToolBar1.DropDownArrows = True
|
|
Me.ToolBar1.ImageList = Me.ImageList1
|
|
Me.ToolBar1.Location = New System.Drawing.Point(0, 0)
|
|
Me.ToolBar1.Name = "ToolBar1"
|
|
Me.ToolBar1.ShowToolTips = True
|
|
Me.ToolBar1.Size = New System.Drawing.Size(600, 28)
|
|
Me.ToolBar1.TabIndex = 27
|
|
'
|
|
'ToolBarButton1
|
|
'
|
|
Me.ToolBarButton1.ImageIndex = 0
|
|
Me.ToolBarButton1.Name = "ToolBarButton1"
|
|
Me.ToolBarButton1.ToolTipText = "Verlassen"
|
|
'
|
|
'ToolBarButton2
|
|
'
|
|
Me.ToolBarButton2.ImageIndex = 1
|
|
Me.ToolBarButton2.Name = "ToolBarButton2"
|
|
Me.ToolBarButton2.ToolTipText = "SQL-Datei vom Filesystem laden"
|
|
'
|
|
'ToolBarButton3
|
|
'
|
|
Me.ToolBarButton3.ImageIndex = 2
|
|
Me.ToolBarButton3.Name = "ToolBarButton3"
|
|
Me.ToolBarButton3.ToolTipText = "Abfrage ausführen"
|
|
'
|
|
'ToolBarButton4
|
|
'
|
|
Me.ToolBarButton4.ImageIndex = 3
|
|
Me.ToolBarButton4.Name = "ToolBarButton4"
|
|
Me.ToolBarButton4.ToolTipText = "Markierte Zeilen/Spalten in die Zwischenablage kopieren"
|
|
'
|
|
'ToolBarButton5
|
|
'
|
|
Me.ToolBarButton5.Name = "ToolBarButton5"
|
|
Me.ToolBarButton5.Style = System.Windows.Forms.ToolBarButtonStyle.Separator
|
|
'
|
|
'ToolBarButton6
|
|
'
|
|
Me.ToolBarButton6.ImageIndex = 5
|
|
Me.ToolBarButton6.Name = "ToolBarButton6"
|
|
Me.ToolBarButton6.ToolTipText = "SQL-Abfrage aus der Datenbank laden"
|
|
'
|
|
'ToolBarButton7
|
|
'
|
|
Me.ToolBarButton7.ImageIndex = 7
|
|
Me.ToolBarButton7.Name = "ToolBarButton7"
|
|
Me.ToolBarButton7.ToolTipText = "SQL-Abfrage speichen (Ctrl+V im Grid)"
|
|
'
|
|
'ToolBarButton9
|
|
'
|
|
Me.ToolBarButton9.Name = "ToolBarButton9"
|
|
Me.ToolBarButton9.Style = System.Windows.Forms.ToolBarButtonStyle.Separator
|
|
'
|
|
'ToolBarButton8
|
|
'
|
|
Me.ToolBarButton8.ImageIndex = 4
|
|
Me.ToolBarButton8.Name = "ToolBarButton8"
|
|
Me.ToolBarButton8.ToolTipText = "Resultattabelle in einer CSV-Datei Speichen (für Excel)"
|
|
'
|
|
'ImageList1
|
|
'
|
|
Me.ImageList1.ImageStream = CType(resources.GetObject("ImageList1.ImageStream"), System.Windows.Forms.ImageListStreamer)
|
|
Me.ImageList1.TransparentColor = System.Drawing.Color.Transparent
|
|
Me.ImageList1.Images.SetKeyName(0, "")
|
|
Me.ImageList1.Images.SetKeyName(1, "")
|
|
Me.ImageList1.Images.SetKeyName(2, "")
|
|
Me.ImageList1.Images.SetKeyName(3, "")
|
|
Me.ImageList1.Images.SetKeyName(4, "")
|
|
Me.ImageList1.Images.SetKeyName(5, "")
|
|
Me.ImageList1.Images.SetKeyName(6, "")
|
|
Me.ImageList1.Images.SetKeyName(7, "")
|
|
'
|
|
'TextBox1
|
|
'
|
|
Me.TextBox1.Dock = System.Windows.Forms.DockStyle.Top
|
|
Me.TextBox1.Location = New System.Drawing.Point(0, 28)
|
|
Me.TextBox1.Multiline = True
|
|
Me.TextBox1.Name = "TextBox1"
|
|
Me.TextBox1.Size = New System.Drawing.Size(600, 159)
|
|
Me.TextBox1.TabIndex = 28
|
|
'
|
|
'OpenFileDialog1
|
|
'
|
|
Me.OpenFileDialog1.DefaultExt = "*.sql"
|
|
'
|
|
'Splitter1
|
|
'
|
|
Me.Splitter1.Dock = System.Windows.Forms.DockStyle.Top
|
|
Me.Splitter1.Location = New System.Drawing.Point(0, 187)
|
|
Me.Splitter1.Name = "Splitter1"
|
|
Me.Splitter1.Size = New System.Drawing.Size(600, 3)
|
|
Me.Splitter1.TabIndex = 29
|
|
Me.Splitter1.TabStop = False
|
|
'
|
|
'DokList
|
|
'
|
|
Me.DokList.AllowUpdate = False
|
|
Me.DokList.AllowUpdateOnBlur = False
|
|
Me.DokList.AlternatingRows = True
|
|
Me.DokList.Cursor = System.Windows.Forms.Cursors.Default
|
|
Me.DokList.Dock = System.Windows.Forms.DockStyle.Fill
|
|
Me.DokList.FetchRowStyles = True
|
|
Me.DokList.FilterBar = True
|
|
Me.DokList.GroupByCaption = "Drag a column header here to group by that column"
|
|
Me.DokList.Images.Add(CType(resources.GetObject("DokList.Images"), System.Drawing.Image))
|
|
Me.DokList.Location = New System.Drawing.Point(0, 190)
|
|
Me.DokList.Name = "DokList"
|
|
Me.DokList.PreviewInfo.Location = New System.Drawing.Point(0, 0)
|
|
Me.DokList.PreviewInfo.Size = New System.Drawing.Size(0, 0)
|
|
Me.DokList.PreviewInfo.ZoomFactor = 75
|
|
Me.DokList.PrintInfo.PageSettings = CType(resources.GetObject("DokList.PrintInfo.PageSettings"), System.Drawing.Printing.PageSettings)
|
|
Me.DokList.ScrollTips = True
|
|
Me.DokList.Size = New System.Drawing.Size(600, 263)
|
|
Me.DokList.TabIndex = 30
|
|
Me.DokList.Text = "C1TrueDBGrid1"
|
|
Me.DokList.PropBag = resources.GetString("DokList.PropBag")
|
|
'
|
|
'SaveFileDialog1
|
|
'
|
|
Me.SaveFileDialog1.FileName = "doc1"
|
|
'
|
|
'ComboBox1
|
|
'
|
|
Me.ComboBox1.Location = New System.Drawing.Point(272, 4)
|
|
Me.ComboBox1.Name = "ComboBox1"
|
|
Me.ComboBox1.Size = New System.Drawing.Size(312, 21)
|
|
Me.ComboBox1.TabIndex = 31
|
|
Me.ComboBox1.Text = "ComboBox1"
|
|
'
|
|
'Label1
|
|
'
|
|
Me.Label1.Location = New System.Drawing.Point(184, 6)
|
|
Me.Label1.Name = "Label1"
|
|
Me.Label1.Size = New System.Drawing.Size(88, 16)
|
|
Me.Label1.TabIndex = 32
|
|
Me.Label1.Text = "Abfrageauswahl"
|
|
'
|
|
'frmQuery
|
|
'
|
|
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
|
|
Me.ClientSize = New System.Drawing.Size(600, 453)
|
|
Me.Controls.Add(Me.Label1)
|
|
Me.Controls.Add(Me.ComboBox1)
|
|
Me.Controls.Add(Me.DokList)
|
|
Me.Controls.Add(Me.Splitter1)
|
|
Me.Controls.Add(Me.TextBox1)
|
|
Me.Controls.Add(Me.ToolBar1)
|
|
Me.Name = "frmQuery"
|
|
Me.Text = "SQL-Query"
|
|
CType(Me.DokList, System.ComponentModel.ISupportInitialize).EndInit()
|
|
Me.ResumeLayout(False)
|
|
Me.PerformLayout()
|
|
|
|
End Sub
|
|
|
|
#End Region
|
|
|
|
Private Sub ToolBar1_ButtonClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) Handles ToolBar1.ButtonClick
|
|
Select Case ToolBar1.Buttons.IndexOf(e.Button)
|
|
Case 0
|
|
Me.Close()
|
|
Case 1
|
|
Me.OpenFileDialog1.ShowDialog()
|
|
If Me.OpenFileDialog1.FileName <> "" Then
|
|
Load_File(Me.OpenFileDialog1.FileName)
|
|
End If
|
|
Case 2
|
|
Run_Query()
|
|
'If UCase(Microsoft.VisualBasic.Left(Me.TextBox1.Text, 6)) <> "SELECT" Then
|
|
' MsgBox("Es können nur Select-Statements ausgeführt werden")
|
|
' Exit Sub
|
|
'Else
|
|
' Run_Query()
|
|
'End If
|
|
Case 3
|
|
copydata()
|
|
Case 5 'Load Query from Database
|
|
Dim f As New frmDomainSQLQueries()
|
|
f.DomainList.AllowAddNew = False
|
|
f.ShowDialog()
|
|
Try
|
|
Me.TextBox1.Text = f.DomainList.Columns("query").Value
|
|
Catch
|
|
End Try
|
|
f.Dispose()
|
|
Case 6
|
|
Dim f As New frmDomainSQLQueries()
|
|
Clipboard.SetDataObject(Me.TextBox1.Text)
|
|
f.ShowDialog()
|
|
f.Dispose()
|
|
Case 8
|
|
If Me.DokList.Splits(0).Rows.Count > 0 Then
|
|
Me.SaveFileDialog1.AddExtension = True
|
|
Me.SaveFileDialog1.DefaultExt.Insert(0, "*.csv")
|
|
Me.SaveFileDialog1.ShowDialog()
|
|
If Me.SaveFileDialog1.FileName <> "" Then
|
|
Me.DokList.ExportToDelimitedFile(Me.SaveFileDialog1.FileName, C1.Win.C1TrueDBGrid.RowSelectorEnum.AllRows, ";")
|
|
' Me.DokList.ExportToDelimitedFile(Me.SaveFileDialog1.FileName, C1.Win.C1TrueDBGrid.RowSelectorEnum.AllRows)
|
|
End If
|
|
End If
|
|
End Select
|
|
End Sub
|
|
|
|
Private Sub copydata()
|
|
Dim strTemp As String 'string to be copied to the clipboard
|
|
Dim row As Long
|
|
Dim col As C1.Win.C1TrueDBGrid.C1DataColumn
|
|
Dim cols As Integer, rows As Long
|
|
If Me.DokList.SelectedRows.Count > 0 Then
|
|
For Each row In Me.DokList.SelectedRows
|
|
For Each col In Me.DokList.Columns
|
|
strTemp = strTemp & col.CellText(row) & vbTab
|
|
Next
|
|
strTemp = strTemp & vbCrLf
|
|
Next
|
|
System.Windows.Forms.Clipboard.SetDataObject(strTemp, False)
|
|
Else
|
|
End If
|
|
End Sub
|
|
|
|
Private Sub Load_File(ByVal Filename As String)
|
|
Try
|
|
Dim ofile As System.IO.File
|
|
Dim oread As System.IO.StreamReader
|
|
oread = ofile.OpenText(Filename)
|
|
Me.TextBox1.Text = oread.ReadToEnd
|
|
oread.Close()
|
|
ofile = Nothing
|
|
oread = Nothing
|
|
Catch
|
|
End Try
|
|
End Sub
|
|
|
|
|
|
Private Sub Run_Query()
|
|
Try
|
|
|
|
Dim qr As New SqlDataAdapter(Me.TextBox1.Text, Globals.sConnectionString)
|
|
Dim dsi As New DataSet()
|
|
Me.Cursor = Cursors.WaitCursor
|
|
dsi.Tables.Clear()
|
|
qr.Fill(dsi, "Daten")
|
|
Me.DokList.DataSource = Nothing
|
|
Try
|
|
Me.DokList.DataSource = dsi.Tables(0)
|
|
Me.DokList.DataMember = dsi.Tables(0).TableName
|
|
Catch
|
|
End Try
|
|
Me.Cursor = Cursors.Default
|
|
If dsi.Tables.Count < 1 Then
|
|
MsgBox("Abfrage wurde erfolgreich beendet.")
|
|
End If
|
|
Catch ex As Exception
|
|
Me.Cursor = Cursors.Default
|
|
MsgBox(ex.Message)
|
|
End Try
|
|
|
|
|
|
End Sub
|
|
|
|
Private Sub frmQuery_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
|
|
Me.WindowState = FormWindowState.Maximized
|
|
If DivFnkt.MA_Is_Sysadmin(Globals.MitarbeiterNr) = False Then
|
|
Me.ComboBox1.Visible = True
|
|
Me.Label1.Visible = True
|
|
Me.TextBox1.ReadOnly = True
|
|
Dim dt As New DataTable()
|
|
dt = Me.get_queries(0)
|
|
Me.ComboBox1.DataSource = dt
|
|
Me.ComboBox1.DisplayMember = "beschreibung"
|
|
Me.ComboBox1.ValueMember = "query"
|
|
If dt.Rows.Count < 1 Then
|
|
Me.ComboBox1.Text = "Keine Abfrage verfügbar"
|
|
Else
|
|
Me.ComboBox1.SelectedIndex = 0
|
|
End If
|
|
Me.ToolBar1.Buttons(1).Enabled = False
|
|
Me.ToolBar1.Buttons(5).Enabled = False
|
|
Me.ToolBar1.Buttons(6).Enabled = False
|
|
Else
|
|
Me.ComboBox1.Visible = True
|
|
Me.Label1.Visible = True
|
|
Dim dt As New DataTable()
|
|
dt = Me.get_queries(1)
|
|
Me.ComboBox1.DataSource = dt
|
|
Me.ComboBox1.DisplayMember = "beschreibung"
|
|
Me.ComboBox1.ValueMember = "query"
|
|
If dt.Rows.Count < 1 Then
|
|
Me.ComboBox1.Text = "Keine Abfrage verfügbar"
|
|
Else
|
|
Me.ComboBox1.SelectedIndex = 0
|
|
End If
|
|
End If
|
|
|
|
End Sub
|
|
|
|
Public Function get_queries(ByVal type As Integer) As DataTable
|
|
Dim scmCmdToExecute As SqlCommand = New SqlCommand()
|
|
Dim i As Integer
|
|
Dim dtToReturn As DataTable = New DataTable()
|
|
Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
|
|
scmCmdToExecute.CommandText = "dbo.sp_sqlqueries_select"
|
|
scmCmdToExecute.CommandType = CommandType.StoredProcedure
|
|
scmCmdToExecute.Connection = conn.scoDBConnection
|
|
Try
|
|
scmCmdToExecute.Parameters.Add(New SqlParameter("@mitarbeiternr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Globals.MitarbeiterNr))
|
|
scmCmdToExecute.Parameters.Add(New SqlParameter("@type", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Globals.MitarbeiterNr))
|
|
sdaAdapter.Fill(dtToReturn)
|
|
Return dtToReturn
|
|
Catch ex As Exception
|
|
Throw New Exception("Dokument_Information_Wert::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
|
|
Finally
|
|
scmCmdToExecute.Dispose()
|
|
sdaAdapter.Dispose()
|
|
End Try
|
|
End Function
|
|
|
|
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
|
|
Try
|
|
Me.TextBox1.Text = Me.ComboBox1.SelectedValue
|
|
Catch
|
|
End Try
|
|
End Sub
|
|
End Class
|