208 lines
8.1 KiB
VB.net
208 lines
8.1 KiB
VB.net
Imports System.Data.SqlClient
|
|
|
|
Public Class cSQL
|
|
|
|
Public Shared Sub SQL2DS(ByRef selector As String, ByRef ds As DataSet, Optional ByRef DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = Class1.DBConString
|
|
Dim con As New SqlConnection
|
|
Dim cmd As New SqlCommand
|
|
con.ConnectionString = DBConnect 'Class1.DBConString
|
|
cmd.Connection = con
|
|
|
|
Dim dataadapter As New SqlDataAdapter(selector, con)
|
|
con.Open()
|
|
dataadapter.Fill(ds)
|
|
con.Close()
|
|
End Sub
|
|
|
|
Sub UpdateDatenbankStruktur()
|
|
Dim conn As New SqlConnection(Class1.DBDoku)
|
|
Dim cmd As New SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", conn)
|
|
conn.Open()
|
|
Dim reader As SqlDataReader = cmd.ExecuteReader()
|
|
|
|
While reader.Read()
|
|
Dim tableName As String = reader("TABLE_NAME").ToString()
|
|
Console.WriteLine("CREATE TABLE " & tableName)
|
|
' Hier kannst du dann den CREATE TABLE Befehl generieren
|
|
UpdateOrCreateTable(Class1.DBDoku, Class1.lokalDBDoku, tableName)
|
|
SyncDatenbank(tableName)
|
|
End While
|
|
conn.Close()
|
|
|
|
End Sub
|
|
Sub SyncDatenbank(tabelle As String)
|
|
Dim conn As New SqlConnection(Class1.lokalDBDoku)
|
|
Dim cmd As New SqlCommand($"
|
|
DELETE FROM {tabelle};
|
|
INSERT INTO {tabelle}
|
|
SELECT * FROM [sqlcluster.verag.ost.dmn].doku.dbo.{tabelle};", conn)
|
|
|
|
Try
|
|
conn.Open()
|
|
cmd.ExecuteNonQuery()
|
|
conn.Close()
|
|
Console.WriteLine("Datenbank erfolgreich synchronisiert!")
|
|
Catch ex As Exception
|
|
Console.WriteLine("Fehler bei der Synchronisation: " & ex.Message)
|
|
End Try
|
|
End Sub
|
|
|
|
Sub UpdateOrCreateTable(sqlServerConnectionString As String, localSqlServerConnectionString As String, tableName As String)
|
|
' SQL Server-Abfrage für die Spalteninformationen
|
|
Dim query As String = $"SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}'"
|
|
Dim columnDefinitions As New List(Of String)
|
|
|
|
' Abfrage für die Spaltenstruktur der Remote-Tabelle
|
|
Using con As New SqlConnection(sqlServerConnectionString)
|
|
con.Open()
|
|
Using cmd As New SqlCommand(query, con)
|
|
Using reader As SqlDataReader = cmd.ExecuteReader()
|
|
While reader.Read()
|
|
Dim columnName As String = reader("COLUMN_NAME").ToString()
|
|
Dim dataType As String = reader("DATA_TYPE").ToString()
|
|
If reader("CHARACTER_MAXIMUM_LENGTH") IsNot DBNull.Value Then
|
|
dataType &= $"({reader("CHARACTER_MAXIMUM_LENGTH").ToString()})"
|
|
If dataType = "varchar(-1)" Then dataType = "varchar(max)"
|
|
End If
|
|
Dim isNullable As String = If(reader("IS_NULLABLE").ToString() = "YES", "NULL", "NOT NULL")
|
|
columnDefinitions.Add($"{columnName} {dataType} {isNullable}")
|
|
End While
|
|
End Using
|
|
End Using
|
|
End Using
|
|
|
|
' Die lokale SQL Server-Datenbank, in der die Tabelle erstellt wird
|
|
Using localSqlCon As New SqlConnection(localSqlServerConnectionString)
|
|
localSqlCon.Open()
|
|
|
|
' Prüfen, ob die Tabelle bereits existiert
|
|
Dim tableExistsQuery As String = $"SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}'"
|
|
Using cmd As New SqlCommand(tableExistsQuery, localSqlCon)
|
|
Dim exists As Object = cmd.ExecuteScalar()
|
|
If exists Is Nothing Then
|
|
' Erstelle die Tabelle, wenn sie nicht existiert
|
|
Dim createTableSQL As String = $"CREATE TABLE {tableName} ({String.Join(", ", columnDefinitions)});"
|
|
Using createCmd As New SqlCommand(createTableSQL, localSqlCon)
|
|
createCmd.ExecuteNonQuery()
|
|
End Using
|
|
Else
|
|
' Falls die Tabelle bereits existiert, überprüfe und füge neue Spalten hinzu
|
|
For Each columnDef In columnDefinitions
|
|
Dim columnName As String = columnDef.Split(" ")(0)
|
|
Dim addColumnSQL As String = $"ALTER TABLE {tableName} ADD COLUMN {columnDef};"
|
|
Try
|
|
Using alterCmd As New SqlCommand(addColumnSQL, localSqlCon)
|
|
alterCmd.ExecuteNonQuery()
|
|
End Using
|
|
Catch ex As Exception
|
|
' Falls die Spalte bereits existiert, wird die Exception ignoriert
|
|
End Try
|
|
Next
|
|
End If
|
|
End Using
|
|
End Using
|
|
End Sub
|
|
|
|
Public Shared Sub UpdateSQL(ByRef table As String, ByRef values As String, ByRef where As String, Optional ByRef DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = Class1.DBConString
|
|
Dim con As New SqlConnection
|
|
Dim cmd As New SqlCommand
|
|
con.ConnectionString = DBConnect ' Class1.DBConString
|
|
cmd.Connection = con
|
|
|
|
con.Open()
|
|
cmd.CommandText = "UPDATE " & table & " SET " & values & " WHERE " & where & ""
|
|
cmd.ExecuteNonQuery()
|
|
con.Close()
|
|
End Sub
|
|
Public Shared Sub UpdateSQL_(ByVal table As String, ByVal values As Dictionary(Of String, Object), ByVal where As String, Optional ByVal DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = Class1.DBConString
|
|
|
|
Using con As New SqlConnection(DBConnect),
|
|
cmd As New SqlCommand()
|
|
|
|
con.Open()
|
|
cmd.Connection = con
|
|
|
|
' Dynamisch SQL-Zeile aufbauen
|
|
Dim setParts As New List(Of String)
|
|
For Each kvp In values
|
|
setParts.Add(kvp.Key & " = @" & kvp.Key)
|
|
cmd.Parameters.AddWithValue("@" & kvp.Key, kvp.Value)
|
|
Next
|
|
|
|
cmd.CommandText = $"UPDATE {table} SET {String.Join(", ", setParts)} WHERE {where}"
|
|
|
|
cmd.ExecuteNonQuery()
|
|
End Using
|
|
End Sub
|
|
|
|
|
|
Public Shared Sub InsertSQL(ByRef table As String, ByRef insert As String, Optional ByRef DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = Class1.DBConString
|
|
Dim con As New SqlConnection
|
|
Dim cmd As New SqlCommand
|
|
con.ConnectionString = DBConnect ' Class1.DBConString
|
|
cmd.Connection = con
|
|
|
|
con.Open()
|
|
cmd.CommandText = "INSERT INTO " & table & " " & insert & ""
|
|
cmd.ExecuteNonQuery()
|
|
con.Close()
|
|
End Sub
|
|
|
|
Public Shared Sub DeleteSQL(ByRef table As String, ByRef where As String, Optional ByRef DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = Class1.DBConString
|
|
Dim con As New SqlConnection
|
|
Dim cmd As New SqlCommand
|
|
con.ConnectionString = DBConnect ' Class1.DBConString
|
|
cmd.Connection = con
|
|
|
|
con.Open()
|
|
cmd.CommandText = "DELETE FROM " & table & " WHERE " & where & ""
|
|
cmd.ExecuteNonQuery()
|
|
con.Close()
|
|
End Sub
|
|
|
|
Public Shared Sub SQLCommand(command As String, Optional ByRef DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = Class1.DBConString
|
|
Dim con As New SqlConnection
|
|
Dim cmd As New SqlCommand
|
|
con.ConnectionString = DBConnect ' Class1.DBConString
|
|
cmd.Connection = con
|
|
|
|
con.Open()
|
|
cmd.CommandText = command
|
|
cmd.ExecuteNonQuery()
|
|
con.Close()
|
|
End Sub
|
|
|
|
|
|
End Class
|
|
|
|
Public Class SQLVariable
|
|
Private Text, Value As String
|
|
Private prim As Boolean
|
|
|
|
Public Sub New(ByVal btext As String, ByVal bvalue As String, Optional bprim As Boolean = False)
|
|
Me.Value = bvalue
|
|
Me.Text = btext
|
|
|
|
End Sub
|
|
|
|
Public ReadOnly Property SQLText() As String
|
|
Get
|
|
Return Text
|
|
End Get
|
|
End Property
|
|
|
|
Public ReadOnly Property SQLValue() As Object
|
|
Get
|
|
Return value
|
|
End Get
|
|
End Property
|
|
|
|
End Class
|
|
|