1217 lines
63 KiB
VB.net
1217 lines
63 KiB
VB.net
Imports System
|
|
Imports System.ComponentModel
|
|
Imports System.Collections.Generic
|
|
Imports System.Diagnostics
|
|
Imports System.Text
|
|
|
|
Imports DevExpress.ExpressApp
|
|
Imports DevExpress.ExpressApp.Actions
|
|
Imports DevExpress.Persistent.Base
|
|
Imports DevExpress.Xpo
|
|
Imports DevExpress.ExpressApp.Editors
|
|
Imports DevExpress.ExpressApp.SystemModule
|
|
Imports DevExpress.Data.Filtering
|
|
Imports MySql.Data.MySqlClient
|
|
Imports Npgsql
|
|
Imports System.Data.SqlClient
|
|
Imports System.IO
|
|
|
|
Public Class DBCExportVC
|
|
Inherits DevExpress.ExpressApp.ViewController
|
|
Dim _WaitFormClass As New WaitFormClass
|
|
Dim _connstr_mysql As String = ""
|
|
Dim _connstr_mssql As String = ""
|
|
Dim _connstr_postgre As String = ""
|
|
Dim _FileName As String = IO.Path.GetTempPath + "DBCopyErrorLog_" + Date.Now.ToString("yy-MM-dd") + ".txt"
|
|
Dim _FileNameInsert As String = IO.Path.GetTempPath + "DBCopyINSERTLog_" + Date.Now.ToString("yy-MM-dd") + ".txt"
|
|
|
|
Public Sub New()
|
|
MyBase.New()
|
|
|
|
'This call is required by the Component Designer.
|
|
InitializeComponent()
|
|
RegisterActions(components)
|
|
|
|
End Sub
|
|
Private Sub aDBCGetTables_Execute(sender As System.Object, e As DevExpress.ExpressApp.Actions.SimpleActionExecuteEventArgs) Handles aDBCGetTables.Execute
|
|
Dim _ocur As Xpo.XPObjectSpace = TryCast(View.ObjectSpace, Xpo.XPObjectSpace)
|
|
Dim _uow As New UnitOfWork(_ocur.Session.DataLayer)
|
|
Dim _DBCLogin As DBCLogin = _uow.GetObjectByKey(Of DBCLogin)(TryCast(View.SelectedObjects(0), DBCLogin).Oid)
|
|
|
|
If _DBCLogin Is Nothing Then Exit Sub
|
|
|
|
Dim _conn_mysql As MySqlConnection = Nothing
|
|
Dim _conn_postgresql As NpgsqlConnection = Nothing
|
|
|
|
Dim _DBCColumns_Collection As New XPCollection(Of DBCColumns)(_uow, CriteriaOperator.Parse("DBCTables.DBCLogin=?", _DBCLogin))
|
|
_uow.Delete(_DBCColumns_Collection)
|
|
|
|
Dim _DBCTables_Collection As New XPCollection(Of DBCTables)(_uow, CriteriaOperator.Parse("DBCLogin=?", _DBCLogin))
|
|
_uow.Delete(_DBCTables_Collection)
|
|
|
|
_uow.CommitChanges()
|
|
|
|
Select Case _DBCLogin.SQLType_From
|
|
Case eSQLType.MSSQL
|
|
LoadTables_MSSQL(_uow, _DBCLogin, 0)
|
|
Case eSQLType.MySQL
|
|
LoadTables_MySQL(_uow, _DBCLogin, 0)
|
|
Case eSQLType.PostgreSQL
|
|
LoadTables_PostgreSQL(_uow, _DBCLogin, 0)
|
|
End Select
|
|
|
|
Select Case _DBCLogin.SQLType_To
|
|
Case eSQLType.MSSQL
|
|
LoadTables_MSSQL(_uow, _DBCLogin, 1)
|
|
Case eSQLType.MySQL
|
|
LoadTables_MySQL(_uow, _DBCLogin, 1)
|
|
Case eSQLType.PostgreSQL
|
|
LoadTables_PostgreSQL(_uow, _DBCLogin, 1)
|
|
End Select
|
|
|
|
_uow.CommitChanges()
|
|
|
|
Frame.GetController(Of RefreshController).RefreshAction.DoExecute()
|
|
End Sub
|
|
Private Sub LoadTables_MSSQL(_uow As UnitOfWork, _DBCLogin As DBCLogin, _DBCType As Long)
|
|
If _DBCType = 0 Then
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCLogin.SQLServer_From, _DBCLogin.SQLUser_From, _DBCLogin.SQLPassword_From, _DBCLogin.SQLDatabase_From)
|
|
ElseIf _DBCType = 1 Then
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
End If
|
|
|
|
If String.IsNullOrEmpty(_connstr_mssql) Then Exit Sub
|
|
|
|
Dim cnn As New SqlConnection(_connstr_mssql)
|
|
Dim cnn2 As New SqlConnection(_connstr_mssql)
|
|
Dim _recordcount As Long = 0
|
|
|
|
Try
|
|
Dim sql As String = "select db=count(*) from sysobjects where xtype='U'"
|
|
Dim cmd As New SqlCommand
|
|
Dim cmd2 As New SqlCommand
|
|
Dim dr As SqlDataReader
|
|
Dim dr2 As SqlDataReader
|
|
|
|
cmd.CommandText = sql
|
|
cmd.Connection = cnn
|
|
cnn.Open()
|
|
|
|
dr = cmd.ExecuteReader()
|
|
|
|
If dr.HasRows Then
|
|
While dr.Read
|
|
_recordcount = dr("db")
|
|
End While
|
|
End If
|
|
|
|
dr.Close()
|
|
cnn.Close()
|
|
|
|
sql = "select name,id from sysobjects where xtype='U' order by name"
|
|
_WaitFormClass.Initwork(1, 1, _recordcount)
|
|
cnn.Open()
|
|
If cnn.State = ConnectionState.Open Then
|
|
cmd = New SqlCommand(sql, cnn)
|
|
dr = cmd.ExecuteReader(CommandBehavior.SingleResult)
|
|
If dr.HasRows Then
|
|
_WaitFormClass.Initwork(1, 1, _recordcount)
|
|
While dr.Read
|
|
_WaitFormClass.DoWork()
|
|
|
|
Dim _DBCTables As New DBCTables(_uow)
|
|
With _DBCTables
|
|
.DBCLogin = _DBCLogin
|
|
.DBCType = _DBCType
|
|
.TableName = dr("name")
|
|
.CreateScript = ""
|
|
.DropScript = ""
|
|
End With
|
|
|
|
sql = "select cname=t1.name,ctype=t2.name,t2.length from syscolumns t1 join systypes t2 on t1.xtype = t2.xtype where t2.name<>'sysname' and t1.id = " & dr("id").ToString
|
|
cnn2.Open()
|
|
|
|
If cnn2.State = ConnectionState.Open Then
|
|
cmd2 = New SqlCommand(sql, cnn2)
|
|
dr2 = cmd2.ExecuteReader(CommandBehavior.SingleResult)
|
|
|
|
If dr2.HasRows Then
|
|
_WaitFormClass.Initwork(1, 1, _recordcount)
|
|
While dr2.Read
|
|
|
|
Dim _DBCColumns As New DBCColumns(_uow)
|
|
With _DBCColumns
|
|
.DBCTables = _DBCTables
|
|
.ColumnName = dr2("cname").ToString
|
|
.ColumnType = dr2("ctype").ToString
|
|
.ColumnLength = dr2("length").ToString
|
|
End With
|
|
|
|
End While
|
|
|
|
End If
|
|
dr2.Close()
|
|
cnn2.Close()
|
|
End If
|
|
'_DBCTables.DBCTableCount = SelectCountMsSql(_DBCTables)
|
|
End While
|
|
End If
|
|
dr.Close()
|
|
cnn.Close()
|
|
_uow.CommitChanges()
|
|
End If
|
|
Catch ex As Exception
|
|
MsgBox(ex.Message)
|
|
Finally
|
|
_WaitFormClass.Finalwork
|
|
End Try
|
|
End Sub
|
|
Private Sub LoadTables_MySQL(_uow As UnitOfWork, _DBCLogin As DBCLogin, _DBCType As Long)
|
|
If _DBCType = 0 Then
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCLogin.SQLServer_From, _DBCLogin.SQLUser_From, _DBCLogin.SQLPassword_From, _DBCLogin.SQLDatabase_From)
|
|
ElseIf _DBCType = 1 Then
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
End If
|
|
|
|
If String.IsNullOrEmpty(_connstr_mysql) Then Exit Sub
|
|
|
|
Dim cnn As New MySqlConnection(_connstr_mysql)
|
|
Dim cnn2 As New MySqlConnection(_connstr_mysql)
|
|
Dim _recordcount As Long = 0
|
|
|
|
Try
|
|
cnn.Open()
|
|
Dim sql As String = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + _DBCLogin.SQLDatabase_From + "';"
|
|
Dim cmd As New MySqlCommand
|
|
Dim cmd2 As New MySqlCommand
|
|
Dim dr As MySqlDataReader
|
|
Dim dr2 As MySqlDataReader
|
|
cmd.CommandText = sql
|
|
cmd.Connection = TryCast(cnn, MySqlConnection)
|
|
|
|
dr = cmd.ExecuteReader
|
|
If dr.HasRows Then
|
|
While dr.Read()
|
|
_recordcount = dr(0)
|
|
End While
|
|
End If
|
|
dr.Close()
|
|
|
|
sql = "SELECT TABLE_NAME as Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + _DBCLogin.SQLDatabase_From + "';"
|
|
cmd.CommandText = sql
|
|
cmd.Connection = TryCast(cnn, MySqlConnection)
|
|
|
|
dr = cmd.ExecuteReader
|
|
If dr.HasRows Then
|
|
_WaitFormClass.Initwork(1, 1, _recordcount)
|
|
While dr.Read()
|
|
_WaitFormClass.DoWork()
|
|
Dim _DBCTables As New DBCTables(_uow)
|
|
With _DBCTables
|
|
.DBCLogin = _DBCLogin
|
|
.DBCType = _DBCType
|
|
.TableName = dr("Name")
|
|
.CreateScript = ""
|
|
.DropScript = ""
|
|
End With
|
|
sql = "SHOW COLUMNS FROM `" + _DBCTables.TableName + "`;"
|
|
|
|
cnn2.Open()
|
|
|
|
cmd2.CommandText = sql
|
|
cmd2.Connection = TryCast(cnn2, MySqlConnection)
|
|
dr2 = cmd2.ExecuteReader
|
|
|
|
If dr2.HasRows Then
|
|
_WaitFormClass.Initwork(1, 1, _recordcount)
|
|
While dr2.Read()
|
|
Dim _DBCColumns As New DBCColumns(_uow)
|
|
With _DBCColumns
|
|
.DBCTables = _DBCTables
|
|
.ColumnName = dr2("Field").ToString
|
|
.ColumnType = dr2("Type").ToString
|
|
.ColumnLength = "0"
|
|
End With
|
|
|
|
End While
|
|
End If
|
|
cnn2.Close()
|
|
dr2.Close()
|
|
End While
|
|
End If
|
|
dr.Close()
|
|
cnn.Close()
|
|
_uow.CommitChanges()
|
|
Catch ex As Exception
|
|
MsgBox(ex.Message, MsgBoxStyle.OkOnly + MsgBoxStyle.Critical, "Hiba!")
|
|
Finally
|
|
_WaitFormClass.Finalwork
|
|
End Try
|
|
|
|
End Sub
|
|
Private Sub LoadTables_PostgreSQL(_uow As UnitOfWork, _DBCLogin As DBCLogin, _DBCType As Long)
|
|
If _DBCType = 0 Then
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCLogin.SQLServer_From, _DBCLogin.SQLUser_From, _DBCLogin.SQLPassword_From, _DBCLogin.SQLDatabase_From)
|
|
ElseIf _DBCType = 1 Then
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
End If
|
|
|
|
If String.IsNullOrEmpty(_connstr_postgre) Then Exit Sub
|
|
|
|
Dim _conn_pgsql As New NpgsqlConnection(_connstr_postgre)
|
|
Dim _conn_pgsql2 As New NpgsqlConnection(_connstr_postgre)
|
|
Dim _cmd_pgsql As NpgsqlCommand
|
|
Dim _cmd_pgsql2 As NpgsqlCommand
|
|
Dim _dr As NpgsqlDataReader
|
|
Dim _dr2 As NpgsqlDataReader
|
|
Dim sql As String = "SELECT table_name as Name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public' and TABLE_CATALOG='" + _DBCLogin.SQLDatabase_To + "'"
|
|
|
|
_conn_pgsql.Open()
|
|
|
|
If _conn_pgsql.State = ConnectionState.Open Then
|
|
|
|
_cmd_pgsql = New NpgsqlCommand(sql, _conn_pgsql)
|
|
_cmd_pgsql.CommandTimeout = 100
|
|
_cmd_pgsql.Connection = _conn_pgsql
|
|
_cmd_pgsql.CommandType = CommandType.Text
|
|
_cmd_pgsql.CommandText = sql
|
|
_dr = _cmd_pgsql.ExecuteReader()
|
|
|
|
Try
|
|
If _dr.HasRows Then
|
|
_WaitFormClass.Initwork(1, 1, 1)
|
|
While _dr.Read()
|
|
|
|
Dim _DBCTables As New DBCTables(_uow)
|
|
With _DBCTables
|
|
.DBCLogin = _DBCLogin
|
|
.DBCType = _DBCType
|
|
.TableName = _dr("Name").ToString
|
|
.CreateScript = ""
|
|
.DropScript = ""
|
|
End With
|
|
sql = "SELECT column_name as Field, data_type as Type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '" + _DBCTables.TableName + "';"
|
|
|
|
_conn_pgsql2.Open()
|
|
|
|
If _conn_pgsql2.State = ConnectionState.Open Then
|
|
_cmd_pgsql2 = New NpgsqlCommand(sql, _conn_pgsql)
|
|
_cmd_pgsql2.CommandText = sql
|
|
_cmd_pgsql2.Connection = _conn_pgsql2
|
|
_dr2 = _cmd_pgsql2.ExecuteReader()
|
|
|
|
If _dr2.HasRows Then
|
|
|
|
While _dr2.Read()
|
|
|
|
Dim _DBCColumns As New DBCColumns(_uow)
|
|
With _DBCColumns
|
|
.DBCTables = _DBCTables
|
|
.ColumnName = _dr2("Field").ToString
|
|
.ColumnType = _dr2("Type").ToString
|
|
.ColumnLength = "0"
|
|
End With
|
|
|
|
End While
|
|
End If
|
|
_dr2.Close()
|
|
End If
|
|
_conn_pgsql2.Close()
|
|
End While
|
|
End If
|
|
_dr.Close()
|
|
_conn_pgsql.Close()
|
|
_uow.CommitChanges()
|
|
Catch ex As Exception
|
|
MsgBox(ex.Message, MsgBoxStyle.OkOnly + MsgBoxStyle.Critical, "Hiba!")
|
|
Finally
|
|
_WaitFormClass.Finalwork
|
|
End Try
|
|
End If
|
|
End Sub
|
|
Private Sub WriteErrorLog(_ex As String, Optional _InsertFile As Boolean = False)
|
|
If _InsertFile Then
|
|
Dim _StreamWriter As New StreamWriter(_FileNameInsert, True)
|
|
_StreamWriter.WriteLine(_ex)
|
|
_StreamWriter.Close()
|
|
Else
|
|
Dim _StreamWriter As New StreamWriter(_FileName, True)
|
|
_StreamWriter.WriteLine(_ex)
|
|
_StreamWriter.Close()
|
|
End If
|
|
|
|
End Sub
|
|
|
|
Private Sub aDBCCopyData_Execute(sender As System.Object, e As DevExpress.ExpressApp.Actions.SimpleActionExecuteEventArgs) Handles aDBCCopyData.Execute
|
|
Dim _ocur As Xpo.XPObjectSpace = TryCast(View.ObjectSpace, Xpo.XPObjectSpace)
|
|
Dim _uow As New UnitOfWork(_ocur.Session.DataLayer)
|
|
Dim _DBCLogin As DBCLogin = TryCast(View.SelectedObjects(0), DBCLogin)
|
|
|
|
If _DBCLogin Is Nothing Then Exit Sub
|
|
|
|
Select Case _DBCLogin.SQLType_From
|
|
Case eSQLType.MSSQL
|
|
Select Case _DBCLogin.SQLType_To
|
|
Case eSQLType.MSSQL
|
|
UpdateModuleInfo(_DBCLogin, eSQLType.MSSQL, 1)
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DropForeignKeys(_DBCTables, eSQLType.MSSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DeleteSQLTable(_DBCTables, eSQLType.MSSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_From
|
|
InsertData(_DBCTables, _DBCLogin)
|
|
Next
|
|
Case eSQLType.MySQL
|
|
UpdateModuleInfo(_DBCLogin, eSQLType.MySQL, 1)
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DropForeignKeys(_DBCTables, eSQLType.MySQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DeleteSQLTable(_DBCTables, eSQLType.MySQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_From
|
|
InsertData(_DBCTables, _DBCLogin)
|
|
Next
|
|
Case eSQLType.PostgreSQL
|
|
UpdateModuleInfo(_DBCLogin, eSQLType.PostgreSQL, 1)
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DropForeignKeys(_DBCTables, eSQLType.PostgreSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DeleteSQLTable(_DBCTables, eSQLType.PostgreSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_From
|
|
InsertData(_DBCTables, _DBCLogin)
|
|
Next
|
|
End Select
|
|
Case eSQLType.MySQL
|
|
Select Case _DBCLogin.SQLType_To
|
|
Case eSQLType.MSSQL
|
|
UpdateModuleInfo(_DBCLogin, eSQLType.MSSQL, 1)
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DropForeignKeys(_DBCTables, eSQLType.MSSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DeleteSQLTable(_DBCTables, eSQLType.MSSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_From
|
|
InsertData(_DBCTables, _DBCLogin)
|
|
Next
|
|
Case eSQLType.MySQL
|
|
UpdateModuleInfo(_DBCLogin, eSQLType.MySQL, 1)
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DropForeignKeys(_DBCTables, eSQLType.MySQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DeleteSQLTable(_DBCTables, eSQLType.MySQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_From
|
|
InsertData(_DBCTables, _DBCLogin)
|
|
Next
|
|
Case eSQLType.PostgreSQL
|
|
UpdateModuleInfo(_DBCLogin, eSQLType.PostgreSQL, 1)
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DropForeignKeys(_DBCTables, eSQLType.PostgreSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DeleteSQLTable(_DBCTables, eSQLType.PostgreSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_From
|
|
InsertData(_DBCTables, _DBCLogin)
|
|
Next
|
|
End Select
|
|
Case eSQLType.PostgreSQL
|
|
Select Case _DBCLogin.SQLType_To
|
|
Case eSQLType.MSSQL
|
|
UpdateModuleInfo(_DBCLogin, eSQLType.MSSQL, 1)
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DropForeignKeys(_DBCTables, eSQLType.MSSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DeleteSQLTable(_DBCTables, eSQLType.MSSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_From
|
|
InsertData(_DBCTables, _DBCLogin)
|
|
Next
|
|
Case eSQLType.MySQL
|
|
UpdateModuleInfo(_DBCLogin, eSQLType.MySQL, 1)
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DropForeignKeys(_DBCTables, eSQLType.MySQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DeleteSQLTable(_DBCTables, eSQLType.MySQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_From
|
|
InsertData(_DBCTables, _DBCLogin)
|
|
Next
|
|
Case eSQLType.PostgreSQL
|
|
UpdateModuleInfo(_DBCLogin, eSQLType.PostgreSQL, 1)
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DropForeignKeys(_DBCTables, eSQLType.PostgreSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_To
|
|
DeleteSQLTable(_DBCTables, eSQLType.PostgreSQL)
|
|
Next
|
|
For Each _DBCTables As DBCTables In _DBCLogin.DBCTables_From
|
|
InsertData(_DBCTables, _DBCLogin)
|
|
Next
|
|
End Select
|
|
|
|
End Select
|
|
If File.Exists(_FileName) Then Process.Start(_FileName)
|
|
End Sub
|
|
Private Sub DropForeignKeys(_DBCTables As DBCTables, _SQLType As eSQLType)
|
|
Try
|
|
Dim cnn As Object
|
|
Dim cnn2 As Object
|
|
Dim cnn3 As Object
|
|
|
|
Dim sql As String = ""
|
|
Select Case _SQLType
|
|
Case eSQLType.MSSQL
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCTables.DBCLogin.SQLServer_To, _DBCTables.DBCLogin.SQLUser_To, _DBCTables.DBCLogin.SQLPassword_To, _DBCTables.DBCLogin.SQLDatabase_To)
|
|
|
|
sql = "SELECT Name, parent_obj FROM sysobjects WHERE xtype='F'"
|
|
|
|
cnn = New SqlConnection(_connstr_mssql)
|
|
cnn2 = New SqlConnection(_connstr_mssql)
|
|
cnn3 = New SqlConnection(_connstr_mssql)
|
|
cnn.Open()
|
|
Dim cmd As New SqlCommand(sql, TryCast(cnn, SqlConnection))
|
|
Dim dr As SqlDataReader = cmd.ExecuteReader
|
|
|
|
If dr.HasRows Then
|
|
While dr.Read
|
|
sql = "SELECT Name FROM sysobjects WHERE id='" + dr("parent_obj").ToString + "'"
|
|
cnn2.Open()
|
|
Dim cmd2 As New SqlCommand(sql, TryCast(cnn2, SqlConnection))
|
|
Dim dr2 As SqlDataReader = cmd2.ExecuteReader
|
|
If dr2.HasRows Then
|
|
While dr2.Read
|
|
sql = "ALTER TABLE dbo.[" + dr2("Name") + "] DROP CONSTRAINT " + dr("Name")
|
|
cnn3.Open()
|
|
Dim cmd3 As New SqlCommand(sql, TryCast(cnn3, SqlConnection))
|
|
cmd3.ExecuteReader()
|
|
cnn3.Close()
|
|
End While
|
|
End If
|
|
dr2.Close()
|
|
cnn2.Close()
|
|
End While
|
|
End If
|
|
dr.Close()
|
|
cnn.Close()
|
|
Case eSQLType.MySQL
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCTables.DBCLogin.SQLServer_To, _DBCTables.DBCLogin.SQLUser_To, _DBCTables.DBCLogin.SQLPassword_To, _DBCTables.DBCLogin.SQLDatabase_To)
|
|
|
|
sql = "SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='" + _DBCTables.DBCLogin.SQLDatabase_To + "'" + " AND TABLE_NAME='" + _DBCTables.TableName + "'" + " AND REFERENCED_TABLE_NAME is not null"
|
|
|
|
cnn = New MySqlConnection(_connstr_mysql)
|
|
cnn2 = New MySqlConnection(_connstr_mysql)
|
|
cnn.Open()
|
|
Dim cmd As New MySqlCommand(sql, TryCast(cnn, MySqlConnection))
|
|
Dim dr As MySqlDataReader = cmd.ExecuteReader()
|
|
|
|
If dr.HasRows Then
|
|
While dr.Read
|
|
sql = "ALTER TABLE " + _DBCTables.TableName + " DROP FOREIGN KEY " + dr("CONSTRAINT_NAME")
|
|
|
|
cnn2.Open()
|
|
Dim cmd2 As New MySqlCommand(sql, TryCast(cnn2, MySqlConnection))
|
|
' Dim dr2 As MySqlDataReader = cmd2.ExecuteReader
|
|
Dim _LineCount As Long = cmd2.ExecuteNonQuery
|
|
cnn2.Close()
|
|
End While
|
|
End If
|
|
dr.Close()
|
|
cnn.Close()
|
|
Case eSQLType.PostgreSQL
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCTables.DBCLogin.SQLServer_To, _DBCTables.DBCLogin.SQLUser_To, _DBCTables.DBCLogin.SQLPassword_To, _DBCTables.DBCLogin.SQLDatabase_To)
|
|
|
|
sql = "SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_CATALOG='" + _DBCTables.DBCLogin.SQLDatabase_To + "'" + _
|
|
" AND TABLE_CATALOG='" + _DBCTables.DBCLogin.SQLDatabase_To + "'" + " AND TABLE_NAME='" + _DBCTables.TableName + "'" + " AND CONSTRAINT_TYPE= 'FOREIGN KEY'"
|
|
|
|
cnn = New NpgsqlConnection(_connstr_postgre)
|
|
cnn2 = New NpgsqlConnection(_connstr_postgre)
|
|
cnn.Open()
|
|
Dim cmd As New NpgsqlCommand(sql, TryCast(cnn, NpgsqlConnection))
|
|
Dim dr As NpgsqlDataReader = cmd.ExecuteReader()
|
|
|
|
If dr.HasRows Then
|
|
While dr.Read
|
|
sql = "ALTER TABLE """ + _DBCTables.TableName + """ DROP CONSTRAINT """ + dr("CONSTRAINT_NAME") + """"
|
|
|
|
cnn2.Open()
|
|
Dim cmd2 As New NpgsqlCommand(sql, TryCast(cnn2, NpgsqlConnection))
|
|
Dim _LineCount As Long = cmd2.ExecuteNonQuery
|
|
cnn2.Close()
|
|
End While
|
|
End If
|
|
|
|
dr.Close()
|
|
cnn.Close()
|
|
End Select
|
|
Catch ex As Exception
|
|
WriteErrorLog("DROPFOREIGNKEY ERROR - " + _DBCTables.TableName + " " + ex.Message)
|
|
'MsgBox(ex.Message, MsgBoxStyle.OkOnly + MsgBoxStyle.Critical, "Hiba!")
|
|
End Try
|
|
End Sub
|
|
Private Sub UpdateModuleInfo(_DBCLogin As DBCLogin, _SQLType As eSQLType, _DBCType As Long)
|
|
Dim cnn As Object
|
|
Dim _Version As String = (System.Reflection.Assembly.GetExecutingAssembly.FullName.Split(New Char() {","})(1).Remove(0, 9)).Remove( _
|
|
(System.Reflection.Assembly.GetExecutingAssembly.FullName.Split(New Char() {","})(1).Remove(0, 9)).Length - 2, 2) + "00"
|
|
Try
|
|
Dim sql As String = ""
|
|
Select Case _SQLType
|
|
Case eSQLType.MSSQL
|
|
If _DBCType = 0 Then
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCLogin.SQLServer_From, _DBCLogin.SQLUser_From, _DBCLogin.SQLPassword_From, _DBCLogin.SQLDatabase_From)
|
|
ElseIf _DBCType = 1 Then
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
End If
|
|
sql = "UPDATE ModuleInfo SET Version = '" + _Version + "' WHERE Name = 'NuvolarModule'"
|
|
|
|
cnn = New SqlConnection(_connstr_mssql)
|
|
cnn.Open()
|
|
Dim cmd As New SqlCommand(sql, TryCast(cnn, SqlConnection))
|
|
Dim _LineCount As Long = cmd.ExecuteNonQuery
|
|
cnn.Close()
|
|
Case eSQLType.MySQL
|
|
If _DBCType = 0 Then
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCLogin.SQLServer_From, _DBCLogin.SQLUser_From, _DBCLogin.SQLPassword_From, _DBCLogin.SQLDatabase_From)
|
|
ElseIf _DBCType = 1 Then
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
End If
|
|
sql = "UPDATE ModuleInfo SET Version = '" + _Version + "' WHERE Name = 'NuvolarModule'"
|
|
|
|
cnn = New MySqlConnection(_connstr_mysql)
|
|
cnn.Open()
|
|
Dim cmd As New MySqlCommand(sql, TryCast(cnn, MySqlConnection))
|
|
Dim _LineCount As Long = cmd.ExecuteNonQuery
|
|
cnn.Close()
|
|
Case eSQLType.PostgreSQL
|
|
If _DBCType = 0 Then
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCLogin.SQLServer_From, _DBCLogin.SQLUser_From, _DBCLogin.SQLPassword_From, _DBCLogin.SQLDatabase_From)
|
|
ElseIf _DBCType = 1 Then
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
End If
|
|
sql = "UPDATE ""ModuleInfo"" SET ""Version"" = '" + _Version + "' where ""Name"" = 'NuvolarModule'"
|
|
|
|
cnn = New NpgsqlConnection(_connstr_postgre)
|
|
cnn.Open()
|
|
Dim cmd As New NpgsqlCommand(sql, TryCast(cnn, NpgsqlConnection))
|
|
Dim _LineCount As Long = cmd.ExecuteNonQuery
|
|
cnn.Close()
|
|
End Select
|
|
Catch ex As Exception
|
|
WriteErrorLog(cnn.ConnectionString + " " + ex.Message)
|
|
' MsgBox(ex.Message, MsgBoxStyle.OkOnly + MsgBoxStyle.Critical, "Hiba!")
|
|
End Try
|
|
|
|
End Sub
|
|
Private Sub InsertData(_DBCTables As DBCTables, _DBCLogin As DBCLogin)
|
|
Dim cnnFrom As Object
|
|
Dim cnnTo As Object
|
|
Dim sql As String = ""
|
|
Try
|
|
|
|
Select Case _DBCLogin.SQLType_From
|
|
Case eSQLType.MSSQL
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCLogin.SQLServer_From, _DBCLogin.SQLUser_From, _DBCLogin.SQLPassword_From, _DBCLogin.SQLDatabase_From)
|
|
sql = "SELECT "
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += _DBCColumns.ColumnName + ", "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2)
|
|
sql += " FROM " + _DBCTables.TableName
|
|
|
|
cnnFrom = New SqlConnection(_connstr_mssql)
|
|
cnnFrom.Open()
|
|
Dim cmd As New SqlCommand(sql, TryCast(cnnFrom, SqlConnection))
|
|
Dim dr As SqlDataReader = cmd.ExecuteReader
|
|
|
|
If dr.HasRows Then
|
|
Select Case _DBCLogin.SQLType_To
|
|
Case eSQLType.MSSQL
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
While dr.Read
|
|
cnnTo = New SqlConnection(_connstr_mssql)
|
|
cnnTo.Open()
|
|
|
|
sql = "INSERT INTO dbo.[" + _DBCTables.TableName + "] ("
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += _DBCColumns.ColumnName + ", "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ") VALUES ("
|
|
|
|
For i = 0 To dr.FieldCount - 1
|
|
If String.IsNullOrEmpty(dr(i).ToString) Then
|
|
sql += "null, "
|
|
Else
|
|
sql += "'" + dr(i).ToString + "', "
|
|
End If
|
|
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ")"
|
|
Dim cmdTo As New SqlCommand(sql, TryCast(cnnTo, SqlConnection))
|
|
Dim _InsertRow As Long = cmdTo.ExecuteNonQuery
|
|
cnnTo.Close()
|
|
End While
|
|
|
|
Case eSQLType.MySQL
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
While dr.Read
|
|
cnnTo = New MySqlConnection(_connstr_mysql)
|
|
cnnTo.Open()
|
|
sql = "INSERT INTO `" + _DBCTables.TableName + "` ("
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += "`" + _DBCColumns.ColumnName + "`, "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ") VALUES ("
|
|
|
|
For i = 0 To dr.FieldCount - 1
|
|
If String.IsNullOrEmpty(dr(i).ToString) Then
|
|
sql += "NULL, "
|
|
Else
|
|
If dr(i).ToString = "Fales" Then
|
|
sql += "'0', "
|
|
ElseIf dr(i).ToString = "True" Then
|
|
sql += "'1', "
|
|
Else
|
|
sql += "'" + dr(i).ToString + "', "
|
|
End If
|
|
|
|
End If
|
|
Next
|
|
|
|
sql = sql.Remove(sql.Length - 2, 2) + ");"
|
|
If sql.Contains("'File'") Then
|
|
sql = sql.Replace("'File'", "'[File]'")
|
|
End If
|
|
WriteErrorLog(sql, True)
|
|
Dim cmdTo As New MySqlCommand(sql, TryCast(cnnTo, MySqlConnection))
|
|
Dim _InsertRow As Long = cmdTo.ExecuteNonQuery
|
|
cnnTo.Close()
|
|
End While
|
|
Case eSQLType.PostgreSQL
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
While dr.Read
|
|
cnnTo = New NpgsqlConnection(_connstr_postgre)
|
|
cnnTo.Open()
|
|
|
|
sql = "INSERT INTO """ + _DBCTables.TableName + """ ("
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += """" + _DBCColumns.ColumnName + """, "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ") VALUES ("
|
|
|
|
For i = 0 To dr.FieldCount - 1
|
|
If String.IsNullOrEmpty(dr(i).ToString) Then
|
|
sql += "NULL, "
|
|
Else
|
|
sql += "'" + dr(i).ToString + "', "
|
|
End If
|
|
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ");"
|
|
Dim cmdTo As New NpgsqlCommand(sql, TryCast(cnnTo, NpgsqlConnection))
|
|
Dim _InsertRow As Long = cmdTo.ExecuteNonQuery
|
|
cnnTo.Close()
|
|
End While
|
|
End Select
|
|
End If
|
|
dr.Close()
|
|
cnnFrom.Close()
|
|
Case eSQLType.MySQL
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCLogin.SQLServer_From, _DBCLogin.SQLUser_From, _DBCLogin.SQLPassword_From, _DBCLogin.SQLDatabase_From)
|
|
sql = "SELECT "
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += _DBCColumns.ColumnName + ", "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2)
|
|
sql += " FROM " + _DBCTables.TableName
|
|
|
|
cnnFrom = New MySqlConnection(_connstr_mysql)
|
|
cnnFrom.Open()
|
|
Dim cmd As New MySqlCommand(sql, TryCast(cnnFrom, MySqlConnection))
|
|
Dim dr As MySqlDataReader = cmd.ExecuteReader
|
|
If dr.HasRows Then
|
|
Select Case _DBCLogin.SQLType_To
|
|
Case eSQLType.MSSQL
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
While dr.Read
|
|
cnnTo = New SqlConnection(_connstr_mssql)
|
|
cnnTo.Open()
|
|
|
|
sql = "INSERT INTO dbo.[" + _DBCTables.TableName + "] ("
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += _DBCColumns.ColumnName + ", "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ") VALUES ("
|
|
|
|
For i = 0 To dr.FieldCount - 1
|
|
If String.IsNullOrEmpty(dr(i).ToString) Then
|
|
sql += "null, "
|
|
Else
|
|
sql += "'" + dr(i).ToString + "', "
|
|
End If
|
|
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ")"
|
|
Dim cmdTo As New SqlCommand(sql, TryCast(cnnTo, SqlConnection))
|
|
Dim _InsertRow As Long = cmdTo.ExecuteNonQuery
|
|
cnnTo.Close()
|
|
End While
|
|
|
|
Case eSQLType.MySQL
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
While dr.Read
|
|
cnnTo = New MySqlConnection(_connstr_mysql)
|
|
cnnTo.Open()
|
|
|
|
sql = "INSERT INTO `" + _DBCTables.TableName + "` ("
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += "`" + _DBCColumns.ColumnName + "`, "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ") VALUES ("
|
|
|
|
For i = 0 To dr.FieldCount - 1
|
|
If String.IsNullOrEmpty(dr(i).ToString) Then
|
|
sql += "NULL, "
|
|
Else
|
|
sql += "'" + dr(i).ToString + "', "
|
|
End If
|
|
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ");"
|
|
If sql.Contains("`File`") Then
|
|
WriteErrorLog(sql)
|
|
End If
|
|
Dim cmdTo As New MySqlCommand(sql, TryCast(cnnTo, MySqlConnection))
|
|
Dim _InsertRow As Long = cmdTo.ExecuteNonQuery
|
|
cnnTo.Close()
|
|
End While
|
|
Case eSQLType.PostgreSQL
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
While dr.Read
|
|
cnnTo = New NpgsqlConnection(_connstr_postgre)
|
|
cnnTo.Open()
|
|
|
|
sql = "INSERT INTO """ + _DBCTables.TableName + """ ("
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += """" + _DBCColumns.ColumnName + """, "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ") VALUES ("
|
|
|
|
For i = 0 To dr.FieldCount - 1
|
|
If String.IsNullOrEmpty(dr(i).ToString) Then
|
|
sql += "NULL, "
|
|
Else
|
|
sql += "'" + dr(i).ToString + "', "
|
|
End If
|
|
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ");"
|
|
Dim cmdTo As New NpgsqlCommand(sql, TryCast(cnnTo, NpgsqlConnection))
|
|
Dim _InsertRow As Long = cmdTo.ExecuteNonQuery
|
|
cnnTo.Close()
|
|
End While
|
|
End Select
|
|
End If
|
|
Case eSQLType.PostgreSQL
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCLogin.SQLServer_From, _DBCLogin.SQLUser_From, _DBCLogin.SQLPassword_From, _DBCLogin.SQLDatabase_From)
|
|
sql = "SELECT "
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += """" + _DBCColumns.ColumnName + """, "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2)
|
|
sql += " FROM """ + _DBCTables.TableName + """"
|
|
|
|
cnnFrom = New NpgsqlConnection(_connstr_postgre)
|
|
cnnFrom.Open()
|
|
Dim cmd As New NpgsqlCommand(sql, TryCast(cnnFrom, NpgsqlConnection))
|
|
Dim dr As NpgsqlDataReader = cmd.ExecuteReader
|
|
|
|
If dr.HasRows Then
|
|
Select Case _DBCLogin.SQLType_To
|
|
Case eSQLType.MSSQL
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
While dr.Read
|
|
cnnTo = New SqlConnection(_connstr_mssql)
|
|
cnnTo.Open()
|
|
|
|
sql = "INSERT INTO dbo.[" + _DBCTables.TableName + "] ("
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += _DBCColumns.ColumnName + ", "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ") VALUES ("
|
|
|
|
For i = 0 To dr.FieldCount - 1
|
|
If String.IsNullOrEmpty(dr(i).ToString) Then
|
|
sql += "null, "
|
|
Else
|
|
sql += "'" + dr(i).ToString + "', "
|
|
End If
|
|
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ")"
|
|
Dim cmdTo As New SqlCommand(sql, TryCast(cnnTo, SqlConnection))
|
|
Dim _InsertRow As Long = cmdTo.ExecuteNonQuery
|
|
cnnTo.Close()
|
|
End While
|
|
|
|
Case eSQLType.MySQL
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
While dr.Read
|
|
cnnTo = New MySqlConnection(_connstr_mysql)
|
|
cnnTo.Open()
|
|
|
|
sql = "INSERT INTO `" + _DBCTables.TableName + "` ("
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += "`" + _DBCColumns.ColumnName + "`, "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ") VALUES ("
|
|
|
|
For i = 0 To dr.FieldCount - 1
|
|
If String.IsNullOrEmpty(dr(i).ToString) Then
|
|
sql += "NULL, "
|
|
Else
|
|
sql += "'" + dr(i).ToString + "', "
|
|
End If
|
|
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ");"
|
|
If sql.Contains("`File`") Then
|
|
WriteErrorLog(sql)
|
|
End If
|
|
Dim cmdTo As New MySqlCommand(sql, TryCast(cnnTo, MySqlConnection))
|
|
Dim _InsertRow As Long = cmdTo.ExecuteNonQuery
|
|
cnnTo.Close()
|
|
End While
|
|
Case eSQLType.PostgreSQL
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCLogin.SQLServer_To, _DBCLogin.SQLUser_To, _DBCLogin.SQLPassword_To, _DBCLogin.SQLDatabase_To)
|
|
While dr.Read
|
|
cnnTo = New NpgsqlConnection(_connstr_postgre)
|
|
cnnTo.Open()
|
|
|
|
sql = "INSERT INTO """ + _DBCTables.TableName + """ ("
|
|
For Each _DBCColumns As DBCColumns In _DBCTables.DBCColumns
|
|
sql += """" + _DBCColumns.ColumnName + """, "
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ") VALUES ("
|
|
|
|
For i = 0 To dr.FieldCount - 1
|
|
If String.IsNullOrEmpty(dr(i).ToString) Then
|
|
sql += "NULL, "
|
|
Else
|
|
sql += "'" + dr(i).ToString + "', "
|
|
End If
|
|
|
|
Next
|
|
sql = sql.Remove(sql.Length - 2, 2) + ");"
|
|
Dim cmdTo As New NpgsqlCommand(sql, TryCast(cnnTo, NpgsqlConnection))
|
|
Dim _InsertRow As Long = cmdTo.ExecuteNonQuery
|
|
cnnTo.Close()
|
|
End While
|
|
End Select
|
|
End If
|
|
End Select
|
|
Catch ex As Exception
|
|
WriteErrorLog("INSERT ERROR- " + _DBCTables.TableName + " - " + ex.Message)
|
|
End Try
|
|
End Sub
|
|
Private Sub DeleteSQLTable(_DBCTables As DBCTables, _SQLType As eSQLType)
|
|
Try
|
|
Dim cnn As Object
|
|
Dim sql As String = ""
|
|
Select Case _SQLType
|
|
Case eSQLType.MSSQL
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCTables.DBCLogin.SQLServer_To, _DBCTables.DBCLogin.SQLUser_To, _DBCTables.DBCLogin.SQLPassword_To, _DBCTables.DBCLogin.SQLDatabase_To)
|
|
cnn = New SqlConnection(_connstr_mssql)
|
|
sql = "delete from dbo.[" + _DBCTables.TableName + "]"
|
|
Dim cmd As New SqlCommand(sql, TryCast(cnn, SqlConnection))
|
|
cnn.Open()
|
|
Dim dr As Long = cmd.ExecuteNonQuery
|
|
cnn.Close()
|
|
Case eSQLType.MySQL
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCTables.DBCLogin.SQLServer_To, _DBCTables.DBCLogin.SQLUser_To, _DBCTables.DBCLogin.SQLPassword_To, _DBCTables.DBCLogin.SQLDatabase_To)
|
|
cnn = New MySqlConnection(_connstr_mysql)
|
|
sql = "delete from " + _DBCTables.TableName
|
|
Dim cmd As New MySqlCommand(sql, TryCast(cnn, MySqlConnection))
|
|
cnn.Open()
|
|
Dim _DeletedRowCount As Long = cmd.ExecuteNonQuery
|
|
cnn.Close()
|
|
Case eSQLType.PostgreSQL
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCTables.DBCLogin.SQLServer_To, _DBCTables.DBCLogin.SQLUser_To, _DBCTables.DBCLogin.SQLPassword_To, _DBCTables.DBCLogin.SQLDatabase_To)
|
|
cnn = New NpgsqlConnection(_connstr_postgre)
|
|
sql = "delete from """ + _DBCTables.TableName + """"
|
|
Dim cmd As New NpgsqlCommand(sql, TryCast(cnn, NpgsqlConnection))
|
|
cnn.Open()
|
|
Dim _DeletedRowCount As Long = cmd.ExecuteNonQuery
|
|
cnn.Close()
|
|
End Select
|
|
Catch ex As Exception
|
|
WriteErrorLog("DELETE ERROR - " + _DBCTables.TableName + " " + ex.Message)
|
|
End Try
|
|
End Sub
|
|
Private Function GetConnectionString(SQLType As eSQLType, Server As String, Uid As String, Pwd As String, Database As String) As String
|
|
Dim _cnn As String = ""
|
|
Select Case SQLType
|
|
Case eSQLType.MSSQL
|
|
_cnn = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User ID=" + Uid + ";Password=" + Pwd
|
|
Case eSQLType.MySQL
|
|
_cnn = "server=" + Server + ";Uid=" + Uid + ";Pwd=" + Pwd + ";Database=" + Database
|
|
Case eSQLType.PostgreSQL
|
|
_cnn = "Server=" + Server + ";User ID=" + Uid + ";Password=" + Pwd + ";Database=" + Database + ";"
|
|
End Select
|
|
Return _cnn
|
|
End Function
|
|
Function SelectCount(_DBCTables As DBCTables)
|
|
Dim _Count As Long = 0
|
|
Dim cnn As Object
|
|
Dim sql As String = ""
|
|
|
|
If _DBCTables.DBCType = 0 Then
|
|
Select Case _DBCTables.DBCLogin.SQLType_From
|
|
Case eSQLType.MSSQL
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCTables.DBCLogin.SQLServer_From, _DBCTables.DBCLogin.SQLUser_From, _DBCTables.DBCLogin.SQLPassword_From, _DBCTables.DBCLogin.SQLDatabase_From)
|
|
cnn = New SqlConnection(_connstr_mssql)
|
|
sql = "select db=count(*) from [" + _DBCTables.TableName + "]"
|
|
Dim cmd As New SqlCommand(sql, TryCast(cnn, SqlConnection))
|
|
cnn.Open()
|
|
Dim dr As SqlDataReader = cmd.ExecuteReader
|
|
If dr.HasRows Then
|
|
_Count = CType(dr("db"), Long)
|
|
End If
|
|
cnn.Close()
|
|
Case eSQLType.MySQL
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCTables.DBCLogin.SQLServer_From, _DBCTables.DBCLogin.SQLUser_From, _DBCTables.DBCLogin.SQLPassword_From, _DBCTables.DBCLogin.SQLDatabase_From)
|
|
cnn = New MySqlConnection(_connstr_mysql)
|
|
sql = "select count(*) db from `" + _DBCTables.TableName + "`"
|
|
Dim cmd As New MySqlCommand(sql, TryCast(cnn, MySqlConnection))
|
|
cnn.Open()
|
|
Dim dr As MySqlDataReader = cmd.ExecuteReader
|
|
If dr.HasRows Then
|
|
_Count = CType(dr("db"), Long)
|
|
End If
|
|
cnn.Close()
|
|
Case eSQLType.PostgreSQL
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCTables.DBCLogin.SQLServer_From, _DBCTables.DBCLogin.SQLUser_From, _DBCTables.DBCLogin.SQLPassword_From, _DBCTables.DBCLogin.SQLDatabase_From)
|
|
cnn = New NpgsqlConnection(_connstr_postgre)
|
|
sql = "delete from """ + _DBCTables.TableName + """"
|
|
Dim cmd As New NpgsqlCommand(sql, TryCast(cnn, NpgsqlConnection))
|
|
cnn.Open()
|
|
Dim dr As NpgsqlDataReader = cmd.ExecuteReader
|
|
If dr.HasRows Then
|
|
_Count = CType(dr("db"), Long)
|
|
End If
|
|
cnn.Close()
|
|
End Select
|
|
ElseIf _DBCTables.DBCType = 1 Then
|
|
Select Case _DBCTables.DBCLogin.SQLType_From
|
|
Case eSQLType.MSSQL
|
|
_connstr_mssql = GetConnectionString(eSQLType.MSSQL, _DBCTables.DBCLogin.SQLServer_To, _DBCTables.DBCLogin.SQLUser_To, _DBCTables.DBCLogin.SQLPassword_To, _DBCTables.DBCLogin.SQLDatabase_To)
|
|
cnn = New SqlConnection(_connstr_mssql)
|
|
sql = "select db=count(*) from [" + _DBCTables.TableName + "]"
|
|
Dim cmd As New SqlCommand(sql, TryCast(cnn, SqlConnection))
|
|
cnn.Open()
|
|
Dim dr As SqlDataReader = cmd.ExecuteReader
|
|
If dr.HasRows Then
|
|
_Count = CType(dr("db"), Long)
|
|
End If
|
|
cnn.Close()
|
|
Case eSQLType.MySQL
|
|
_connstr_mysql = GetConnectionString(eSQLType.MySQL, _DBCTables.DBCLogin.SQLServer_To, _DBCTables.DBCLogin.SQLUser_To, _DBCTables.DBCLogin.SQLPassword_To, _DBCTables.DBCLogin.SQLDatabase_To)
|
|
cnn = New MySqlConnection(_connstr_mysql)
|
|
sql = "select count(*) db from `" + _DBCTables.TableName + "`"
|
|
Dim cmd As New MySqlCommand(sql, TryCast(cnn, MySqlConnection))
|
|
cnn.Open()
|
|
Dim dr As MySqlDataReader = cmd.ExecuteReader
|
|
If dr.HasRows Then
|
|
_Count = CType(dr("db"), Long)
|
|
End If
|
|
cnn.Close()
|
|
Case eSQLType.PostgreSQL
|
|
_connstr_postgre = GetConnectionString(eSQLType.PostgreSQL, _DBCTables.DBCLogin.SQLServer_To, _DBCTables.DBCLogin.SQLUser_To, _DBCTables.DBCLogin.SQLPassword_To, _DBCTables.DBCLogin.SQLDatabase_To)
|
|
cnn = New NpgsqlConnection(_connstr_postgre)
|
|
sql = "delete from """ + _DBCTables.TableName + """"
|
|
Dim cmd As New NpgsqlCommand(sql, TryCast(cnn, NpgsqlConnection))
|
|
cnn.Open()
|
|
Dim dr As NpgsqlDataReader = cmd.ExecuteReader
|
|
If dr.HasRows Then
|
|
_Count = CType(dr("db"), Long)
|
|
End If
|
|
cnn.Close()
|
|
End Select
|
|
End If
|
|
Return _Count
|
|
End Function
|
|
#Region "OldCodes"
|
|
'Private Sub LoadTables_To_MySQL(_uow As UnitOfWork, _DBCLogin As DBCLogin)
|
|
' Dim _conn_mysql_tables As MySql.Data.MySqlClient.MySqlConnection = Nothing
|
|
' Dim _cmd_mysql_tables As New MySql.Data.MySqlClient.MySqlCommand
|
|
' Dim _dr_mysql_tables As MySql.Data.MySqlClient.MySqlDataReader
|
|
|
|
' Dim _conn_mysql_columns As MySql.Data.MySqlClient.MySqlConnection = Nothing
|
|
' Dim _cmd_mysql_columns As New MySql.Data.MySqlClient.MySqlCommand
|
|
' Dim _dr_mysql_columns As MySql.Data.MySqlClient.MySqlDataReader
|
|
|
|
' Dim _connstr As String = ""
|
|
' Dim _SQLQuery As String = ""
|
|
' Dim _DBCTables As DBCTables
|
|
' Dim _DBCColumns As DBCColumns
|
|
|
|
' Try
|
|
' '//Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword
|
|
' _connstr = " Uid=" & _DBCLogin.SQLUser_To & "; "
|
|
' _connstr &= " Pwd=" & _DBCLogin.SQLPassword_To & ";"
|
|
' _connstr &= " Server=" & _DBCLogin.SQLServer_To.Split(",")(0) & ";"
|
|
' _connstr &= " Port=" & _DBCLogin.SQLServer_To.Split(",")(1) & ";"
|
|
' _connstr &= " Database=" & _DBCLogin.SQLDatabase_To
|
|
|
|
' _conn_mysql_tables = New MySql.Data.MySqlClient.MySqlConnection(_connstr)
|
|
|
|
' _conn_mysql_tables.Open()
|
|
|
|
' _SQLQuery = "SELECT * FROM information_schema.TABLES WHERE TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA='" & _DBCLogin.SQLDatabase_To & "'"
|
|
|
|
' If _conn_mysql_tables.State = ConnectionState.Open Then
|
|
' _cmd_mysql_tables.CommandTimeout = 60
|
|
' _cmd_mysql_tables.Connection = _conn_mysql_tables
|
|
' _cmd_mysql_tables.CommandType = CommandType.Text
|
|
' _cmd_mysql_tables.CommandText = _SQLQuery
|
|
' _dr_mysql_tables = _cmd_mysql_tables.ExecuteReader(CommandBehavior.SingleResult)
|
|
|
|
' _WaitFormClass.InitWork(1, 1, _DBCLogin.DBCTables_From.Count)
|
|
' While _dr_mysql_tables.Read
|
|
' _WaitFormClass.DoWork()
|
|
|
|
' _DBCTables = New DBCTables(_uow)
|
|
' _DBCTables.DBCLogin = _DBCLogin
|
|
' _DBCTables.DBCType = 1
|
|
' _DBCTables.TableName = _dr_mysql_tables("TABLE_NAME")
|
|
' _DBCTables.DBCTableCount = SelectCountMySql(_DBCTables)
|
|
|
|
' _conn_mysql_columns = New MySql.Data.MySqlClient.MySqlConnection(_connstr)
|
|
' _conn_mysql_columns.Open()
|
|
|
|
' _SQLQuery = "SELECT * FROM information_schema.COLUMNS where TABLE_NAME='" & _dr_mysql_tables("TABLE_NAME").ToString & "' and TABLE_SCHEMA='" & _DBCLogin.SQLDatabase_To & "'"
|
|
|
|
' If _conn_mysql_columns.State = ConnectionState.Open Then
|
|
' _cmd_mysql_columns.CommandTimeout = 60
|
|
' _cmd_mysql_columns.Connection = _conn_mysql_columns
|
|
' _cmd_mysql_columns.CommandType = CommandType.Text
|
|
' _cmd_mysql_columns.CommandText = _SQLQuery
|
|
' _dr_mysql_columns = _cmd_mysql_columns.ExecuteReader(CommandBehavior.SingleResult)
|
|
|
|
|
|
' While _dr_mysql_columns.Read
|
|
' _DBCColumns = New DBCColumns(_uow)
|
|
' _DBCColumns.DBCTables = _DBCTables
|
|
' _DBCColumns.ColumnName = _dr_mysql_columns("COLUMN_NAME").ToString
|
|
' _DBCColumns.ColumnType = _dr_mysql_columns("DATA_TYPE").ToString
|
|
' _DBCColumns.ColumnLength = _dr_mysql_columns("COLUMN_TYPE").ToString
|
|
' End While
|
|
' _conn_mysql_columns.Close()
|
|
|
|
' End If
|
|
' End While
|
|
' _uow.CommitChanges()
|
|
' _WaitFormClass.Finalwork
|
|
' _conn_mysql_tables.Close()
|
|
' End If
|
|
' Catch ex As Exception
|
|
' MsgBox(ex.Message)
|
|
' Finally
|
|
|
|
' End Try
|
|
'End Sub
|
|
'Private Sub LoadTables_To_PgSQL(_uow As UnitOfWork, _DBCLogin As DBCLogin)
|
|
' Dim _conn_pgsql_tables As Npgsql.NpgsqlConnection
|
|
' Dim _cmd_pgsql_tables As Npgsql.NpgsqlCommand
|
|
' Dim _dr_pgsql_tables As Npgsql.NpgsqlDataReader
|
|
|
|
' Dim _conn_pgsql_columns As Npgsql.NpgsqlConnection
|
|
' Dim _cmd_pgsql_columns As New Npgsql.NpgsqlCommand
|
|
' Dim _dr_pgsql_columns As Npgsql.NpgsqlDataReader
|
|
|
|
' Dim _connstr As String = ""
|
|
' Dim _SQLQuery As String = ""
|
|
' Dim _DBCTables As DBCTables
|
|
' Dim _DBCColumns As DBCColumns
|
|
|
|
' Try
|
|
' 'Server=datacenter;User Id=root;Password=pcl718;Database=molcontrol;Encoding=UNICODE;
|
|
' _connstr = " Server=" & _DBCLogin.SQLServer_To.Split(",")(0) & ";"
|
|
' _connstr &= " User Id=" & _DBCLogin.SQLUser_To & "; "
|
|
' _connstr &= " Password=" & _DBCLogin.SQLPassword_To & ";"
|
|
' _connstr &= " Database=" & _DBCLogin.SQLDatabase_To
|
|
|
|
' _conn_pgsql_tables = New Npgsql.NpgsqlConnection(_connstr)
|
|
' _conn_pgsql_tables.Open()
|
|
|
|
' _SQLQuery = "select * from information_schema.TABLES WHERE TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA='public' and TABLE_CATALOG='" & _DBCLogin.SQLDatabase_To & "'"
|
|
|
|
' If _conn_pgsql_tables.State = ConnectionState.Open Then
|
|
' _cmd_pgsql_tables = New Npgsql.NpgsqlCommand(_SQLQuery, _conn_pgsql_tables)
|
|
' _cmd_pgsql_tables.CommandTimeout = 60
|
|
' _cmd_pgsql_tables.Connection = _conn_pgsql_tables
|
|
' _cmd_pgsql_tables.CommandType = CommandType.Text
|
|
' _cmd_pgsql_tables.CommandText = _SQLQuery
|
|
' _dr_pgsql_tables = _cmd_pgsql_tables.ExecuteReader(CommandBehavior.SingleResult)
|
|
|
|
' _WaitFormClass.InitWork(1, 1, _DBCLogin.DBCTables_From.Count)
|
|
' While _dr_pgsql_tables.Read
|
|
' _WaitFormClass.DoWork()
|
|
|
|
' _DBCTables = New DBCTables(_uow)
|
|
' _DBCTables.DBCLogin = _DBCLogin
|
|
' _DBCTables.DBCType = 2
|
|
' _DBCTables.TableName = _dr_pgsql_tables("TABLE_NAME")
|
|
' _DBCTables.DBCTableCount = SelectCountPgSql(_DBCTables)
|
|
|
|
' _conn_pgsql_columns = New Npgsql.NpgsqlConnection(_connstr)
|
|
' _conn_pgsql_columns.Open()
|
|
|
|
' _SQLQuery = "select * from information_schema.COLUMNS where TABLE_NAME='" & _dr_pgsql_tables("TABLE_NAME").ToString & "' and TABLE_CATALOG='" & _DBCLogin.SQLDatabase_To & "'"
|
|
|
|
' If _conn_pgsql_columns.State = ConnectionState.Open Then
|
|
' _cmd_pgsql_columns.CommandTimeout = 60
|
|
' _cmd_pgsql_columns.Connection = _conn_pgsql_columns
|
|
' _cmd_pgsql_columns.CommandType = CommandType.Text
|
|
' _cmd_pgsql_columns.CommandText = _SQLQuery
|
|
' _dr_pgsql_columns = _cmd_pgsql_columns.ExecuteReader(CommandBehavior.SingleResult)
|
|
|
|
|
|
' While _dr_pgsql_columns.Read
|
|
' _DBCColumns = New DBCColumns(_uow)
|
|
' _DBCColumns.DBCTables = _DBCTables
|
|
' _DBCColumns.ColumnName = _dr_pgsql_columns("COLUMN_NAME").ToString
|
|
' _DBCColumns.ColumnType = _dr_pgsql_columns("DATA_TYPE").ToString
|
|
|
|
' 'If _DBCColumns.ColumnType = "character" Then
|
|
' ' _DBCColumns.ColumnLength = _dr_pgsql_columns("CHARACTER_MAXIMUM_LENGTH").ToString
|
|
' 'End If
|
|
' 'If _DBCColumns.ColumnType = "integer" Or _DBCColumns.ColumnType = "double precision" Then
|
|
' ' _DBCColumns.ColumnLength = _dr_pgsql_columns("NUMERIC_PRECISION").ToString
|
|
' 'End If
|
|
' End While
|
|
' _conn_pgsql_columns.Close()
|
|
|
|
' End If
|
|
' End While
|
|
' _uow.CommitChanges()
|
|
' _WaitFormClass.Finalwork
|
|
' _conn_pgsql_tables.Close()
|
|
' End If
|
|
' Catch ex As Exception
|
|
' MsgBox(ex.Message)
|
|
' Finally
|
|
|
|
' End Try
|
|
'End Sub
|
|
#End Region
|
|
End Class
|