ITSM/Vertragsverwaltung - Kopie/Diverse Formulare/frmQuery.vb

Imports System.IO
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Public Class frmQuery

    Dim q As New TKB.VV.DB.clsQueries
    Dim queries As New DataTable


    Private Sub BeendenToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BeendenToolStripMenuItem.Click
        Me.Close()
    End Sub


    Private Sub TSBtnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSBtnRun.Click
        Dim q As String = Me.txtQuery.Text
        q = UCase(q)
        If q.IndexOf("INSERT ") > -1 Or q.IndexOf("UPDATE ") > -1 Or q.IndexOf("ALTER ") > -1 Or q.IndexOf("DROP ") > -1 Or q.IndexOf("TRUNCATE ") > -1 Or q.IndexOf("EXEC ") > -1 Or _
           q.IndexOf("EXECUTE ") > -1 Or q.IndexOf("CREATE ") > -1 Or q.IndexOf("WRITETEXT ") > -1 Or q.IndexOf("RUN ") > -1 Or q.IndexOf("DEL ") > -1 Or q.IndexOf("DELETE ") > -1 Then
            MsgBox("Das Query enthält ein unzulässiges Statement und kann nicht ausgeführt werden.", MsgBoxStyle.Critical)
            Exit Sub
        End If
        Run_Query()
    End Sub

    Private Sub Run_Query()
        Try

            Dim qr As New SqlDataAdapter(Me.txtQuery.Text, Globals.sConnectionString)
            Dim dsi As New DataSet()
            Me.Cursor = Cursors.WaitCursor
            dsi.Tables.Clear()
            qr.Fill(dsi, "Daten")
            Me.C1Daten.DataSource = Nothing
            Try
                Me.C1Daten.DataSource = dsi.Tables(0)
                Me.C1Daten.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 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.C1Daten.SelectedRows.Count > 0 Then
            For Each row In Me.C1Daten.SelectedRows
                For Each col In Me.C1Daten.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 TSBtnExcelExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSBtnExcelExport.Click
        Me.SaveFileDialog1.AddExtension = True
        Me.SaveFileDialog1.DefaultExt.Insert(0, "*.xls")
        Me.SaveFileDialog1.Filter = "Excel-Dateien|*.xls"

        Me.SaveFileDialog1.ShowDialog()
        If Me.SaveFileDialog1.FileName <> "" Then
            If Me.SaveFileDialog1.FileName.EndsWith(".xls") Or Me.SaveFileDialog1.FileName.EndsWith("XLS") Then
                Me.DatatableToExcel(Me.C1Daten.DataSource, Me.SaveFileDialog1.FileName)
            Else
                Me.C1Daten.ExportToDelimitedFile(Me.SaveFileDialog1.FileName, C1.Win.C1TrueDBGrid.RowSelectorEnum.AllRows, ";", "", "", True, System.Text.Encoding.Default.BodyName)
            End If
        End If
        Exit Sub
    End Sub


    Public Shared Function DatatableToExcel(ByVal aDataTable As DataTable, ByVal aOutputFilename As String) As Boolean
        Application.DoEvents()
        Dim f As New FrmFortschritt
        Dim app As New Object
        Dim wb As New Object
        Dim ws As New Object
        Try
            f.Show()
            Application.DoEvents()
            f.ProgressBar1.Minimum = 0
            f.ProgressBar1.Maximum = aDataTable.Rows.Count
            app = CreateObject("Excel.application")
            'Dim wb As Object
            'Dim ws As Object
            '        Dim app As New Excel.ApplicationClass
            '       Dim wb As Excel.Workbook
            '      Dim ws As Excel.Worksheet
            wb = app.Workbooks.add()
            'wb = app.Workbooks.Add()
            ws = wb.ActiveSheet()
            Dim dc As DataColumn
            Dim dr As DataRow
            Dim colIndex As Integer
            Dim rowIndex As Integer
            ' Columns erstellen
            For Each dc In aDataTable.Columns
                colIndex += 1
                app.Cells(1, colIndex) = dc.ColumnName
            Next
            ' Rows hinzufügen
            For Each dr In aDataTable.Rows
                f.ProgressBar1.Value = rowIndex
                rowIndex += 1
                colIndex = 0
                For Each dc In aDataTable.Columns
                    colIndex += 1
                    app.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                Next
            Next
            ws.Columns.AutoFit()
            wb.SaveAs(aOutputFilename)
            app.Workbooks.Open(aOutputFilename)
            ' Excel anzeigen wenn fertig exportiert
            app.Visible = True
            ws = Nothing
            wb = Nothing
            app = Nothing

            f.Close()
            Return True
        Catch ex As Exception
            f.Close()
            MsgBox(ex.Message)
        End Try


    End Function


    Private Sub frmQuery_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        q.cpMainConnectionProvider = Globals.conn
        queries = q.SelectAll
        For Each dr As DataRow In queries.Rows
            Me.TSCBQuery.Items.Add(dr.Item("Bezeichnung"))
        Next
    End Sub

    Private Sub TSCBQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSCBQuery.Click

    End Sub

    Private Sub TSCBQuery_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TSCBQuery.SelectedIndexChanged
    End Sub

    Private Sub TSCBQuery_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TSCBQuery.TextChanged
        For Each dr As DataRow In queries.Rows
            If dr.Item("bezeichnung") = TSCBQuery.Text Then Me.txtQuery.Text = dr.Item("Beschreibung")
        Next

    End Sub

    Private Sub TSBtnQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSBtnQuit.Click
        Me.Close()
    End Sub
End Class