265 lines
11 KiB
VB.net
265 lines
11 KiB
VB.net
Imports System.Data.Odbc
|
|
Public Class ClassDatabase
|
|
Public Shared ConnectionStringRM As String
|
|
|
|
Public Shared Function Init()
|
|
Try
|
|
Dim SQLconnect As New SqlClient.SqlConnection
|
|
SQLconnect.ConnectionString = ConnectionStringRM
|
|
SQLconnect.Open()
|
|
SQLconnect.Close()
|
|
Return True
|
|
Catch ex As Exception
|
|
MsgBox("Error in DatabaseInit: " & ex.Message, MsgBoxStyle.Critical)
|
|
Return False
|
|
End Try
|
|
End Function
|
|
Public Shared Function GetConnectionString(id As Integer)
|
|
Dim connectionString As String = ""
|
|
Try
|
|
'Me.TBCONNECTIONTableAdapter.FillByID(Me.DD_DMSLiteDataSet.TBCONNECTION, id)
|
|
Dim DTConnection As DataTable = ClassDatabase.Return_Datatable("SELECT * FROM TBDD_CONNECTION WHERE GUID = " & id)
|
|
If DTConnection.Rows.Count = 1 Then
|
|
Select Case DTConnection.Rows(0).Item("SQL_PROVIDER")
|
|
Case "MS-SQLServer"
|
|
If DTConnection.Rows(0).Item("USERNAME") = "WINAUTH" Then
|
|
connectionString = "Server=" & DTConnection.Rows(0).Item("SERVER") & ";Database=" & DTConnection.Rows(0).Item("DATENBANK") & ";Trusted_Connection=True;"
|
|
Else
|
|
connectionString = "Server=" & DTConnection.Rows(0).Item("SERVER") & ";Database=" & DTConnection.Rows(0).Item("DATENBANK") & ";User Id=" & DTConnection.Rows(0).Item("USERNAME") & ";Password=" & DTConnection.Rows(0).Item("USERNAME") & ";Password=" & DTConnection.Rows(0).Item("PASSWORD") & ";"
|
|
End If
|
|
' connectionString = "Server=" & DTConnection.Rows(0).Item("SERVER") & ";Database=" & DTConnection.Rows(0).Item("DATENBANK") & ";User Id=" & DTConnection.Rows(0).Item("USERNAME") & ";Password=" & DTConnection.Rows(0).Item("PASSWORD") & ";"
|
|
Case Else
|
|
MsgBox("ConnectionType nicht integriert", MsgBoxStyle.Critical, "Bitte Konfiguration Connection überprüfen!")
|
|
End Select
|
|
End If
|
|
|
|
Catch ex As Exception
|
|
MsgBox("Error in GetConnectionString: " & ex.Message, MsgBoxStyle.Critical)
|
|
MsgBox(ex.Message, MsgBoxStyle.Critical, "Error in GetConnectionString:")
|
|
End Try
|
|
Return connectionString
|
|
End Function
|
|
Public Shared Function Return_Datatable(Select_anweisung As String, Optional CallMethod As String = "")
|
|
Try
|
|
|
|
Dim SQLconnect As New SqlClient.SqlConnection
|
|
Dim SQLcommand As SqlClient.SqlCommand
|
|
SQLconnect.ConnectionString = ConnectionStringRM
|
|
SQLconnect.Open()
|
|
SQLcommand = SQLconnect.CreateCommand
|
|
SQLcommand.CommandText = Select_anweisung
|
|
SQLcommand.CommandTimeout = 600
|
|
|
|
Dim adapter1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(SQLcommand)
|
|
Dim dt As DataTable = New DataTable()
|
|
adapter1.Fill(dt)
|
|
SQLconnect.Close()
|
|
|
|
|
|
Return dt
|
|
Catch ex As Exception
|
|
MsgBox("Error in Return_Datatable: " & ex.Message & vbNewLine & vbNewLine & Select_anweisung & vbNewLine & vbNewLine & "Call-Method: " & CallMethod, MsgBoxStyle.Critical)
|
|
|
|
Return Nothing
|
|
End Try
|
|
End Function
|
|
Public Shared Function MSSQL_ReturnDTWithConnection(connectionId As Integer, sql As String)
|
|
Try
|
|
Dim regex As New System.Text.RegularExpressions.Regex("(@(\d+)@)")
|
|
Dim match As System.Text.RegularExpressions.Match = regex.Match(sql)
|
|
|
|
If match.Success Then
|
|
'Return Nothing
|
|
End If
|
|
|
|
Dim connectionString As String
|
|
connectionString = ClassDatabase.GetConnectionString(connectionId)
|
|
If connectionString <> "" Then
|
|
|
|
Dim SQLconnect As New SqlClient.SqlConnection
|
|
Dim SQLcommand As SqlClient.SqlCommand
|
|
SQLconnect.ConnectionString = connectionString
|
|
SQLconnect.Open()
|
|
SQLcommand = SQLconnect.CreateCommand
|
|
SQLcommand.CommandText = sql
|
|
SQLcommand.CommandTimeout = 600
|
|
|
|
Dim adapter1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(SQLcommand)
|
|
Dim dt As DataTable = New DataTable()
|
|
adapter1.Fill(dt)
|
|
SQLconnect.Close()
|
|
Return dt
|
|
Else
|
|
MsgBox("No Connection received for ID: " & connectionId.ToString, MsgBoxStyle.Exclamation)
|
|
Return Nothing
|
|
End If
|
|
|
|
Catch ex As Exception
|
|
MsgBox("Unexpected Error in MSSQL_ReturnDTWithConnection:" & vbNewLine & ex.Message & vbNewLine & vbNewLine & sql, MsgBoxStyle.Critical)
|
|
Return Nothing
|
|
End Try
|
|
End Function
|
|
Public Shared Function Return_Datatable_CS(Select_anweisung As String, ConString As String, Optional userInput As Boolean = False)
|
|
Try
|
|
Dim SQLconnect As New SqlClient.SqlConnection
|
|
Dim SQLcommand As SqlClient.SqlCommand
|
|
SQLconnect.ConnectionString = ConString
|
|
SQLconnect.Open()
|
|
SQLcommand = SQLconnect.CreateCommand
|
|
SQLcommand.CommandText = Select_anweisung
|
|
SQLcommand.CommandTimeout = 600
|
|
|
|
Dim adapter1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(SQLcommand)
|
|
Dim dt As DataTable = New DataTable()
|
|
adapter1.Fill(dt)
|
|
SQLconnect.Close()
|
|
|
|
|
|
Return dt
|
|
Catch ex As Exception
|
|
If userInput = True Then
|
|
MsgBox("Error in Return_Datatable_CS - Error-Message:" & vbNewLine & ex.Message & vbNewLine & "SQL-Command:" & vbNewLine & Select_anweisung, MsgBoxStyle.Critical)
|
|
End If
|
|
Return Nothing
|
|
End Try
|
|
End Function
|
|
Public Shared Function Return_Datatable_Connection(Select_anweisung As String, connectionId As Integer, Optional userInput As Boolean = False)
|
|
Try
|
|
Dim connectionString As String
|
|
connectionString = ClassDatabase.GetConnectionString(connectionId)
|
|
If connectionString <> "" Then
|
|
|
|
|
|
Dim SQLconnect As New SqlClient.SqlConnection
|
|
Dim SQLcommand As SqlClient.SqlCommand
|
|
SQLconnect.ConnectionString = connectionString
|
|
SQLconnect.Open()
|
|
SQLcommand = SQLconnect.CreateCommand
|
|
SQLcommand.CommandText = Select_anweisung
|
|
SQLcommand.CommandTimeout = 600
|
|
|
|
Dim adapter1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(SQLcommand)
|
|
Dim dt As DataTable = New DataTable()
|
|
adapter1.Fill(dt)
|
|
SQLconnect.Close()
|
|
|
|
|
|
Return dt
|
|
Else
|
|
Return Nothing
|
|
End If
|
|
|
|
Catch ex As Exception
|
|
If userInput = True Then
|
|
MsgBox("Error in Return_Datatable_Connection - Error-Message:" & vbNewLine & ex.Message & vbNewLine & "SQL-Command:" & vbNewLine & Select_anweisung, MsgBoxStyle.Critical)
|
|
End If
|
|
Return Nothing
|
|
End Try
|
|
End Function
|
|
Public Shared Function Execute_non_Query(ExecuteCMD As String, Optional Userinput As Boolean = False, Optional timeout As Integer = 120)
|
|
Try
|
|
|
|
Dim SQLconnect As New SqlClient.SqlConnection
|
|
Dim SQLcommand As SqlClient.SqlCommand
|
|
|
|
SQLconnect.ConnectionString = ConnectionStringRM
|
|
SQLconnect.Open()
|
|
SQLcommand = SQLconnect.CreateCommand
|
|
SQLcommand.CommandTimeout = timeout
|
|
'Update Last Created Record in Foo
|
|
SQLcommand.CommandText = ExecuteCMD
|
|
SQLcommand.CommandTimeout = 600
|
|
|
|
SQLcommand.ExecuteNonQuery()
|
|
SQLcommand.Dispose()
|
|
SQLconnect.Close()
|
|
Return True
|
|
Catch ex As Exception
|
|
MsgBox("Error in Execute_non_Query: " & ex.Message & vbNewLine & vbNewLine & ExecuteCMD, MsgBoxStyle.Critical)
|
|
Return False
|
|
End Try
|
|
End Function
|
|
Public Shared Function Execute_non_Query_withConn(ExecuteCMD As String, ConnID As Integer)
|
|
Try
|
|
Dim connectionString As String
|
|
connectionString = ClassDatabase.GetConnectionString(ConnID)
|
|
If connectionString <> "" Then
|
|
Dim SQLconnect As New SqlClient.SqlConnection
|
|
Dim SQLcommand As SqlClient.SqlCommand
|
|
SQLconnect.ConnectionString = connectionString
|
|
SQLconnect.Open()
|
|
SQLcommand = SQLconnect.CreateCommand
|
|
'Update Last Created Record in Foo
|
|
SQLcommand.CommandText = ExecuteCMD
|
|
SQLcommand.CommandTimeout = 600
|
|
SQLcommand.ExecuteNonQuery()
|
|
SQLcommand.Dispose()
|
|
SQLconnect.Close()
|
|
|
|
Return True
|
|
Else
|
|
MsgBox("No ConnectionID for Conn-ID: " & ConnID.ToString, MsgBoxStyle.Exclamation)
|
|
Return Nothing
|
|
End If
|
|
|
|
Catch ex As Exception
|
|
MsgBox("Error in Execute_non_Query_withConn: " & ex.Message & vbNewLine & vbNewLine & ExecuteCMD, MsgBoxStyle.Critical)
|
|
Return False
|
|
End Try
|
|
End Function
|
|
Public Shared Function Execute_Scalar(cmdscalar As String, Optional Userinput As Boolean = False)
|
|
Dim result
|
|
Try
|
|
|
|
Dim SQLconnect As New SqlClient.SqlConnection
|
|
Dim SQLcommand As SqlClient.SqlCommand
|
|
SQLconnect.ConnectionString = ConnectionStringRM
|
|
SQLconnect.Open()
|
|
SQLcommand = SQLconnect.CreateCommand
|
|
SQLcommand.CommandTimeout = 600
|
|
'Update Last Created Record in Foo
|
|
SQLcommand.CommandText = cmdscalar
|
|
result = SQLcommand.ExecuteScalar()
|
|
SQLcommand.Dispose()
|
|
SQLconnect.Close()
|
|
|
|
|
|
Return result
|
|
Catch ex As Exception
|
|
|
|
MsgBox("Error in Execute_Scalar: " & ex.Message & vbNewLine & vbNewLine & cmdscalar, MsgBoxStyle.Critical)
|
|
|
|
|
|
Return Nothing
|
|
End Try
|
|
End Function
|
|
|
|
Public Shared Function Execute_ScalarWithConnection(connectionId As Integer, cmdscalar As String)
|
|
Dim result
|
|
Try
|
|
Dim connectionString As String
|
|
connectionString = ClassDatabase.GetConnectionString(connectionId)
|
|
If connectionString <> "" Then
|
|
Dim SQLconnect As New SqlClient.SqlConnection
|
|
Dim SQLcommand As SqlClient.SqlCommand
|
|
SQLconnect.ConnectionString = connectionString
|
|
SQLconnect.Open()
|
|
SQLcommand = SQLconnect.CreateCommand
|
|
'Update Last Created Record in Foo
|
|
SQLcommand.CommandText = cmdscalar
|
|
SQLcommand.CommandTimeout = 600
|
|
result = SQLcommand.ExecuteScalar()
|
|
SQLcommand.Dispose()
|
|
SQLconnect.Close()
|
|
Return result
|
|
Else
|
|
MsgBox("No Connection for ID: " & connectionId & " - ExecuteScalar: " & cmdscalar, MsgBoxStyle.Exclamation)
|
|
Return Nothing
|
|
End If
|
|
|
|
Catch ex As Exception
|
|
Return Nothing
|
|
End Try
|
|
End Function
|
|
End Class
|