1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
|
Public Function CopyTableDataV1p1(ByVal sFromConnection As String, ByVal sToConnection As String) As Boolean
Dim sctSchemaTableTABLES As DataTable
Dim ocmMyFromOleDBCommand As New OleDb.OleDbCommand
Dim drMyFromOleDataReader As OleDb.OleDbDataReader
Dim iTables As Integer
Dim sFromCommand As String
Dim arColumns As New ArrayList
Dim arColumnTypes As New ArrayList
Dim sctSchemaTableCOLUMNS As DataTable
Dim myRow As DataRow
Dim myCol As DataColumn
Dim ocmMyToOleDBCommand As New OleDb.OleDbCommand
Dim drMyToOleDataReader As OleDb.OleDbDataReader
Dim sToCommand As String
Dim sToCommandFinal As String
Dim iColumns As Integer
'ouverture base de donnée source
Try
myFromOleBDConnexion.ConnectionString = sFromConnection
myFromOleBDConnexion.Open()
Catch ex As OleDb.OleDbException
MessageBox.Show("Erreur d'ouverture Base de Donnée source. Erreur : <" & ex.Message & ">" & vbCrLf & sFromConnection.ToString, "CopyTableDataV1p1", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
CopyTableDataV1p1 = False
GoTo ExitFunction
End Try
'ouverture base de donnée cible
Try
myToOleBDConnexion.ConnectionString = sToConnection
myToOleBDConnexion.Open()
Catch ex As OleDb.OleDbException
MessageBox.Show("Erreur d'ouverture Base de Donnée cible. Erreur : <" & ex.Message & ">" & vbCrLf & sToConnection.ToString, "CopyTableDataV1p1", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
CopyTableDataV1p1 = False
GoTo ExitFunction
End Try
'récupération du schéma BDD (liste des tables)
sctSchemaTableTABLES = myFromOleBDConnexion.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
'pour chaque table
Try
For iTables = 0 To sctSchemaTableTABLES.Rows.Count - 1
With sctSchemaTableTABLES.Rows(iTables)
'ouverture de la table et du datareader
sFromCommand = "SELECT * FROM " & .Item(2)
With ocmMyFromOleDBCommand
.Connection = myFromOleBDConnexion
.CommandType = CommandType.Text
.CommandText = sFromCommand
drMyFromOleDataReader = .ExecuteReader()
End With
'récupération du schéma de la table
sctSchemaTableCOLUMNS = drMyFromOleDataReader.GetSchemaTable()
arColumns.Clear()
arColumnTypes.Clear()
For Each myRow In sctSchemaTableCOLUMNS.Rows
arColumns.Add(myRow(0).ToString)
arColumnTypes.Add(myRow(5).ToString.ToLower)
Next
'construire le SQL INSERT
sToCommand = "INSERT INTO " & .Item(2) & " ("
'pour chaque colonne les noms de colonnes
For iColumns = 0 To arColumns.Count - 1
sToCommand &= arColumns(iColumns).ToString & ", "
Next iColumns
sToCommand = sToCommand.Substring(0, sToCommand.Length - 2) & ") VALUES ("
'copie des données
Do While drMyFromOleDataReader.Read
sToCommandFinal = sToCommand
'pour chaque colonne les data
For iColumns = 0 To arColumns.Count - 1
Select Case arColumnTypes(iColumns).ToString
Case "system.int32"
sToCommandFinal &= IIf(IsDBNull(drMyFromOleDataReader.Item(iColumns)), "NULL", drMyFromOleDataReader.Item(iColumns).ToString) & ", "
Case "system.string"
sToCommandFinal &= IIf(IsDBNull(drMyFromOleDataReader.Item(iColumns)), "NULL", "'" & drMyFromOleDataReader.Item(iColumns).ToString) & "', "
Case "system.datetime"
sToCommandFinal &= IIf(IsDBNull(drMyFromOleDataReader.Item(iColumns)), "NULL", "'" & Format(CDate(drMyFromOleDataReader.Item(iColumns).ToString), "MM/dd/yyyy hh:mm:ss")) & "', "
Case "system.double"
sToCommandFinal &= IIf(IsDBNull(drMyFromOleDataReader.Item(iColumns)), "NULL", drMyFromOleDataReader.Item(iColumns).ToString.Replace(",", ".")) & ", "
Case Else 'pour d'autres types
End Select
Next iColumns
sToCommandFinal = sToCommandFinal.Substring(0, sToCommandFinal.Length - 2) & ")"
'utiliser le SQL INSERT
With ocmMyToOleDBCommand
.Connection = myToOleBDConnexion
.CommandType = CommandType.Text
.CommandText = sToCommandFinal
drMyFromOleDataReader = .ExecuteReader()
End With
drMyToOleDataReader.Close()
Loop
'fin de lecture de la table
drMyFromOleDataReader.Close()
End With
Next
Catch ex As OleDb.OleDbException
MessageBox.Show("Erreur pendant opération de copie. Erreur : <" & ex.Message & ">" & vbCrLf & sToConnection.ToString, "CopyTableDataV1p1", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
CopyTableDataV1p1 = False
GoTo ExitFunction
End Try
CopyTableDataV1p1 = True
ExitFunction:
'fermeture base de donnée source
If Not myFromOleBDConnexion Is Nothing Then
Try
myFromOleBDConnexion.Close()
Catch ex As Exception
MessageBox.Show("Erreur de fermeture Base de Donnée source. Erreur : <" & ex.Message & ">", "CopyTableDataV1p1", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
CopyTableDataV1p1 = False
End Try
End If
'fermeture base de donnée cible
If Not myToOleBDConnexion Is Nothing Then
Try
myToOleBDConnexion.Close()
Catch ex As Exception
MessageBox.Show("Erreur de fermeture Base de Donnée cible. Erreur : <" & ex.Message & ">", "CopyTableDataV1p1", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
CopyTableDataV1p1 = False
End Try
End If
End Function |
