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
| Public Sub ImportUserDB()
' importe les données de DB_CURRENT dans une nouvelle structure extraite de DBresource '
' crée le fichier DB_TEMPFILENAME (en écrasant l'existant) à partir du fichier DBresource enregistré dans les resources
' importe les données de la bdd DB_CURRENT dans DB_TEMPFILENAME
' renomme DB_TEMPFILENAME en DB_CURRENT (en écrasant le précédent)
' supprime DB_TEMPFILENAME
Try
Dim nodeGessica As Microsoft.Win32.RegistryKey
nodeGessica = My.Computer.Registry.CurrentUser.CreateSubKey("Software").CreateSubKey(Application.ProductName)
Dim DB_FullPath As String = nodeGessica.GetValue("FileBdd")
Dim DB_FolderPath As String = DB_FullPath.Substring(0, DB_FullPath.Length - DB_CURRENT.Length - 1)
nodeGessica.Close()
File.WriteAllBytes(Path.Combine(DB_FolderPath, DB_TEMPFILENAME), My.Resources.DBresource )
Dim conOldDB As New SQLiteConnection(strDatabaseCon)
Dim conNewDB As New SQLiteConnection("Data source = " & Chr(34) & Path.Combine(DB_FolderPath, DB_TEMPFILENAME) & Chr(34))
Dim cmdNewDB, cmdOldDB As SQLiteCommand
Dim readNewDB As SQLiteDataReader
conNewDB.Open()
conOldDB.Open()
' Récupération des noms de table. Quelques tables ne sont pas importées
Dim sql_listTables As String = "SELECT name FROM sqlite_master WHERE type ='table' AND name<>'sqlite_sequence' AND name<>'APPSETTINGS' AND name<>'t_Couleurs' AND name <>'t_Format' AND name <> 't_Pays' ORDER BY name"
Dim ListTables As New List(Of String)
cmdNewDB = New SQLiteCommand(sql_listTables, conNewDB)
readNewDB = cmdNewDB.ExecuteReader
Do While readNewDB.Read()
ListTables.Add(readNewDB.GetString(0))
Loop
Dim nbTables As Integer = ListTables.Count
' on importe le contenu de chaque table de DB_TEMPFILENAME dans DB_CURRENT
For Each table As String In ListTables 'on liste toutes les tables de DB_TEMPFILENAME
' Si table existe dans DB_CURRENT on transfère son contenu dans DB_TEMPFILENAME
If ExistTable(table, conOldDB) Then
Dim sql_read As String
Dim sql_write As New System.Text.StringBuilder
sql_read = "SELECT * FROM " & table
cmdOldDB = New SQLiteCommand(sql_read, conOldDB)
Dim adOldDB As New SQLiteDataAdapter(cmdOldDB)
Dim dsOldDB As New DataSet
adOldDB.Fill(dsOldDB, table)
cmdOldDB.Dispose()
cmdNewDB = New SQLiteCommand(sql_read, conNewDB)
Dim adNewDB = New SQLiteDataAdapter(cmdNewDB)
Dim dsNewDB = New DataSet
adNewDB.Fill(dsNewDB, table)
Dim sColonne As New System.Text.StringBuilder
For Each oColonne As DataColumn In dsNewDB.Tables(table).Columns
If dsOldDB.Tables(table).Columns.Contains(oColonne.ColumnName) Then
sColonne.Append(oColonne.ColumnName & ", ")
End If
Next
sColonne.Insert(0, " (")
sColonne.Remove(sColonne.Length - 2, 2)
sColonne.Append(") ")
For Each oLigne As DataRow In dsOldDB.Tables(table).Rows
Dim sValue As New System.Text.StringBuilder
For Each oColonne As DataColumn In dsNewDB.Tables(table).Columns
If dsOldDB.Tables(table).Columns.Contains(oColonne.ColumnName) Then
sValue.Append(If(IsDBNull(oLigne(oColonne.ColumnName)), "NULL", "'" & Replace(oLigne(oColonne.ColumnName), "'", "''") & "'") & ", ")
End If
Next
sValue.Insert(0, " (")
sValue.Remove(sValue.Length - 2, 2)
sValue.Append(") ")
sql_write.Append(" INSERT INTO ")
sql_write.Append(table)
sql_write.Append(sColonne.ToString)
sql_write.Append("VALUES ")
sql_write.Append(sValue.ToString)
sql_write.Append(" ;")
sql_write.Append(Environment.NewLine)
Next
cmdNewDB = New SQLiteCommand(sql_write.ToString, conNewDB)
cmdNewDB.ExecuteNonQuery()
cmdNewDB.Dispose()
Else
dProg += (1 / nbTables) * 100
End If
Next
conNewDB.Close()
conOldDB.Close()
' Recopie le fichier DB_TEMPFILENAME en DB_CURRENT et supprime le DB_TEMPFILENAME
File.Copy(Path.Combine(DB_FolderPath, DB_TEMPFILENAME), DB_FullPath, True)
File.Delete(Path.Combine(DB_FolderPath, DB_TEMPFILENAME))
Catch ex As Exception
MessageBox.Show(ex.ToString, "ImportUserDB")
End Try
End Sub |
