128 lines
7.7 KiB
VB.net
128 lines
7.7 KiB
VB.net
Imports System.Data.SqlClient
|
|
Imports System.Data.SQLite
|
|
Imports System.IO
|
|
Public Class cSQLSync
|
|
|
|
|
|
|
|
Dim sqlConnString As String = Class1.DBDoku
|
|
Dim sqliteFile As String = "C:\verag\offline_db.sqlite"
|
|
Dim sqliteConnString As String = $"Data Source={sqliteFile};Version=3;"
|
|
|
|
Property TbL As String
|
|
|
|
Sub Main()
|
|
' Falls die lokale Datenbank nicht existiert, erstelle sie
|
|
If Not File.Exists(sqliteFile) Then
|
|
SQLiteConnection.CreateFile(sqliteFile)
|
|
|
|
End If
|
|
|
|
' Daten synchronisieren
|
|
'CreateLocalTables()
|
|
|
|
SyncDatabase()
|
|
End Sub
|
|
|
|
' Erstellt Tabellen in der lokalen SQLite-Datenbank
|
|
Sub CreateLocalTables()
|
|
Using sqliteConn As New SQLiteConnection(sqliteConnString)
|
|
Dim convert As New cSQL2SQLLite
|
|
sqliteConn.Open()
|
|
Dim sql As String = convert.Main(TbL)
|
|
Using cmd As New SQLiteCommand(sql, sqliteConn)
|
|
cmd.ExecuteNonQuery()
|
|
End Using
|
|
End Using
|
|
End Sub
|
|
|
|
' Synchronisiert Daten von SQL Server zu SQLite
|
|
Sub SyncDatabase()
|
|
Using sqlConn As New SqlConnection(sqlConnString)
|
|
sqlConn.Open()
|
|
|
|
' TbL_Netzwerkclients-Daten abrufen
|
|
Dim sqlQuery As String = "SELECT * FROM TbL_Netzwerkclients"
|
|
Using sqlCmd As New SqlCommand(sqlQuery, sqlConn)
|
|
Using reader As SqlDataReader = sqlCmd.ExecuteReader()
|
|
Using sqliteConn As New SQLiteConnection(sqliteConnString)
|
|
sqliteConn.Open()
|
|
|
|
' Einfügen oder Aktualisieren (Upsert)
|
|
While reader.Read()
|
|
Dim upsertQuery As String = "INSERT INTO TbL_Netzwerkclients (
|
|
IPAdresse, FQDN, QINFO, NETNAME, Seriennummer, MAC, Benutzer, Passwort, LINK, INFO, TYPE, HOST, NWSTANDORT, Modell,
|
|
Linked, LinkedWith, AddIP, Standort, DHCP, Netzwerk, Firewall, TeamviewerID, TeamviewerKennwort, SSHPort, AnydeskID, AnydeskPassword
|
|
) VALUES (
|
|
@IP, @FQDN, @QINFO, @NETNAME, @Seriennummer, @MAC, @Benutzer, @Passwort, @LINK, @INFO, @TYPE, @HOST, @NWSTANDORT, @Modell,
|
|
@Linked, @LinkedWith, @AddIP, @Standort, @DHCP, @Netzwerk, @Firewall, @TeamviewerID, @TeamviewerKennwort, @SSHPort, @AnydeskID, @AnydeskPassword
|
|
)
|
|
ON CONFLICT(IPAdresse) DO UPDATE SET
|
|
FQDN = excluded.FQDN,
|
|
QINFO = excluded.QINFO,
|
|
NETNAME = excluded.NETNAME,
|
|
Seriennummer = excluded.Seriennummer,
|
|
MAC = excluded.MAC,
|
|
Benutzer = excluded.Benutzer,
|
|
Passwort = excluded.Passwort,
|
|
LINK = excluded.LINK,
|
|
INFO = excluded.INFO,
|
|
TYPE = excluded.TYPE,
|
|
HOST = excluded.HOST,
|
|
NWSTANDORT = excluded.NWSTANDORT,
|
|
Modell = excluded.Modell,
|
|
Linked = excluded.Linked,
|
|
LinkedWith = excluded.LinkedWith,
|
|
AddIP = excluded.AddIP,
|
|
Standort = excluded.Standort,
|
|
DHCP = excluded.DHCP,
|
|
Netzwerk = excluded.Netzwerk,
|
|
Firewall = excluded.Firewall,
|
|
TeamviewerID = excluded.TeamviewerID,
|
|
TeamviewerKennwort = excluded.TeamviewerKennwort,
|
|
SSHPort = excluded.SSHPort,
|
|
AnydeskID = excluded.AnydeskID,
|
|
AnydeskPassword = excluded.AnydeskPassword;"
|
|
|
|
Using upsertCmd As New SQLiteCommand(upsertQuery, sqliteConn)
|
|
upsertCmd.Parameters.AddWithValue("@IP", reader("IPAdresse"))
|
|
upsertCmd.Parameters.AddWithValue("@FQDN", reader("FQDN"))
|
|
upsertCmd.Parameters.AddWithValue("@QINFO", reader("QINFO"))
|
|
upsertCmd.Parameters.AddWithValue("@NETNAME", reader("NETNAME"))
|
|
upsertCmd.Parameters.AddWithValue("@Seriennummer", reader("Seriennummer"))
|
|
upsertCmd.Parameters.AddWithValue("@MAC", reader("MAC"))
|
|
upsertCmd.Parameters.AddWithValue("@Benutzer", reader("Benutzer"))
|
|
upsertCmd.Parameters.AddWithValue("@Passwort", reader("Passwort"))
|
|
upsertCmd.Parameters.AddWithValue("@LINK", reader("LINK"))
|
|
upsertCmd.Parameters.AddWithValue("@INFO", reader("INFO"))
|
|
upsertCmd.Parameters.AddWithValue("@TYPE", reader("TYPE"))
|
|
upsertCmd.Parameters.AddWithValue("@HOST", reader("HOST"))
|
|
upsertCmd.Parameters.AddWithValue("@NWSTANDORT", reader("NWSTANDORT"))
|
|
upsertCmd.Parameters.AddWithValue("@Modell", reader("Modell"))
|
|
upsertCmd.Parameters.AddWithValue("@Linked", reader("Linked"))
|
|
upsertCmd.Parameters.AddWithValue("@LinkedWith", reader("LinkedWith"))
|
|
upsertCmd.Parameters.AddWithValue("@AddIP", reader("AddIP"))
|
|
upsertCmd.Parameters.AddWithValue("@Standort", reader("Standort"))
|
|
upsertCmd.Parameters.AddWithValue("@DHCP", reader("DHCP"))
|
|
upsertCmd.Parameters.AddWithValue("@Netzwerk", reader("Netzwerk"))
|
|
upsertCmd.Parameters.AddWithValue("@Firewall", reader("Firewall"))
|
|
upsertCmd.Parameters.AddWithValue("@TeamviewerID", reader("TeamviewerID"))
|
|
upsertCmd.Parameters.AddWithValue("@TeamviewerKennwort", reader("TeamviewerKennwort"))
|
|
upsertCmd.Parameters.AddWithValue("@SSHPort", reader("SSHPort"))
|
|
upsertCmd.Parameters.AddWithValue("@AnydeskID", reader("AnydeskID"))
|
|
upsertCmd.Parameters.AddWithValue("@AnydeskPassword", reader("AnydeskPassword"))
|
|
|
|
upsertCmd.ExecuteNonQuery()
|
|
End Using
|
|
End While
|
|
End Using
|
|
End Using
|
|
End Using
|
|
End Using
|
|
Console.WriteLine("Synchronisation abgeschlossen!")
|
|
End Sub
|
|
|
|
|
|
|
|
End Class
|