8Bsu3Nuq

1 Public Shared Function GeneraBackupSQL(nomeFile As String, db As Database) As Boolean
2        Dim fs As FileStream
3        Dim writer As StreamWriter
4        Dim reader As DbDataReader
5
6        Try
7            fs = File.OpenWrite(nomeFile)
8            writer = New StreamWriter(fs)
9            writer.AutoFlush = True
10        Catch ex As Exception
11            If fs IsNot Nothing Then fs.Close()
12            UltimoMessaggioErrore = ex.Message
13            Return False
14        End Try
15
16        'Carico il nome delle tabelle
17        Dim query As String = "SHOW TABLES"
18        Dim tabellaTabelle As DataTable = db.EseguiComandoSelect(query)
19        If tabellaTabelle Is Nothing OrElse tabellaTabelle.Rows.Count = 0 Then
20            writer.Close()
21            Return False
22        End If
23
24        Try
25            writer.WriteLine("DROP DATABASE IF EXISTS `" & Database.NomeDatabase & "`;")
26            writer.WriteLine("CREATE DATABASE `" & Database.NomeDatabase & "`;")
27            writer.WriteLine("USE `" & Database.NomeDatabase & "`;")
28        Catch ex As Exception
29            UltimoMessaggioErrore = ex.Message
30            writer.Close()
31            Return False
32        End Try
33
34        For Each rigaTabella As DataRow In tabellaTabelle.Rows
35            Try
36                'Scrivo il comando DROP
37                writer.WriteLine("DROP TABLE IF EXISTS `" & rigaTabella(0) & "`;")
38
39                'Carico il comando di creazione
40                query = "SHOW CREATE TABLE " & rigaTabella(0)
41                Dim tabellaCreate As DataTable = db.EseguiComandoSelect(query)
42                If tabellaCreate Is Nothing Then
43                    writer.Close()
44                    Return False
45                End If
46
47                writer.WriteLine(tabellaCreate.Rows(0)(1) & ";")
48
49                'Carico il numero di righe della tabella
50                query = "SELECT COALESCE(COUNT(*), 0) AS somma FROM " & rigaTabella(0)
51                Dim tabellaNumeroRighe As DataTable = db.EseguiComandoSelect(query)
52                If tabellaNumeroRighe Is Nothing OrElse tabellaNumeroRighe.Rows.Count = 0 Then
53                    writer.Close()
54                    Return False
55                End If
56                Dim numeroRighe As Long = tabellaNumeroRighe.Rows(0)("somma")
57
58                'Carico le colonne
59                query = "SHOW COLUMNS IN " & rigaTabella(0)
60                Dim tabellaColonne As DataTable = db.EseguiComandoSelect(query)
61                If tabellaColonne Is Nothing OrElse tabellaColonne.Rows.Count = 0 Then
62                    writer.Close()
63                    Return False
64                End If
65
66                Dim colonne As New List(Of ColonnaDatabase)()
67                For Each rigaColonna As DataRow In tabellaColonne.Rows
68                    colonne.Add(New ColonnaDatabase(rigaColonna("Field"), rigaColonna("Type"), rigaColonna("Key") = "PRI",
69                                                    rigaColonna("Null") = "YES", If(Not rigaColonna.IsNull("Default"), rigaColonna("Default"), Nothing)))
70                Next
71
72                'Eseguo il dump della tabella
73                If Not db.DumpTabella(rigaTabella(0), numeroRighe, colonne, writer) Then
74                    writer.Close()
75                    Return False
76                End If
77
78            Catch ex As Exception
79                UltimoMessaggioErrore = ex.Message
80                If reader IsNot Nothing Then reader.Close()
81                writer.Close()
82                Return False
83            End Try
84        Next
85
86        Try
87            writer.Flush()
88            writer.Close()
89        Catch ex As Exception
90            UltimoMessaggioErrore = ex.Message
91            Return False
92        End Try
93
94        Return True
95    End Function