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
| Imports System.Data.SQLite
Imports System.IO
Module modSQLITE
Public Database As String
Dim CON As New SQLiteConnection
'
Public Sub OpenDataBase()
Try
CON.ConnectionString = My.Settings.Conn
CON.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
'
Public Sub CloseDatabase()
CON.Close()
End Sub
Public Structure m_INDIVIDU
Dim ID As Integer
Dim Nom As String
Dim Age As Integer
Dim Membre As Boolean
Dim Photo As Image
Public Sub New(ByVal Idx As Integer)
ID = Idx
Nom = ""
Age = 0
Membre = False
Photo = New Bitmap(10, 10) 'on initialise avec une image quelconque
End Sub
End Structure
Public Sub DBaddINDIVIDU(ByVal INDIVIDU As m_INDIVIDU)
Try
Dim strSQL As String = "INSERT INTO INDIVIDU VALUES (@ID,@Nom,@Age,@Membre,@Photo)"
Dim cmd = New SQLiteCommand(strSQL, CON)
cmd.Parameters.AddWithValue("@ID", INDIVIDU.ID)
cmd.Parameters.AddWithValue("@Nom", INDIVIDU.Nom)
cmd.Parameters.AddWithValue("@Age", INDIVIDU.Age)
cmd.Parameters.AddWithValue("@Membre", INDIVIDU.Membre)
cmd.Parameters.AddWithValue("@Photo", ImageToByteArray(INDIVIDU.Photo))
cmd.ExecuteNonQuery()
cmd.Dispose()
Catch ex As Exception
MessageBox.Show(ex.message)
End Try
End Sub
Public Sub DBupdateINDIVIDU(ByVal INDIVIDU As m_INDIVIDU)
Try
Dim strSQL As String = "UPDATE INDIVIDU SET Nom=@Nom,Age=@Age,Membre=@Membre,Photo=@Photo WHERE ID=@ID"
Dim cmd = New SQLiteCommand(strSQL, CON)
cmd.Parameters.AddWithValue("@ID", INDIVIDU.ID)
cmd.Parameters.AddWithValue("@Nom", INDIVIDU.Nom)
cmd.Parameters.AddWithValue("@Age", INDIVIDU.Age)
cmd.Parameters.AddWithValue("@Membre", INDIVIDU.Membre)
cmd.Parameters.AddWithValue("@Photo", ImageToByteArray(INDIVIDU.Photo))
cmd.ExecuteNonQuery()
cmd.Dispose()
Catch ex As Exception
MessageBox.Show(ex.message)
End Try
End Sub
Public Function DBgetINDIVIDU(ByVal Idx As Integer) As m_INDIVIDU
Dim strSQL As String = "SELECT * FROM INDIVIDU WHERE ID= " & idx
Dim INDIVIDU As New m_INDIVIDU
Dim cmd = New SQLiteCommand(strSQL, CON)
Dim DR As SQLiteDataReader = cmd.ExecuteReader
'
While (DR.Read())
INDIVIDU.ID = DR(0)
INDIVIDU.Nom = DR(1)
INDIVIDU.Age = DR(2)
INDIVIDU.Membre = DR(3)
INDIVIDU.Photo = ByteArrayToImage(DR(4))
End While
DR.Close()
cmd.Dispose()
Return INDIVIDU
End Function
Public Sub DBdeleteINDIVIDU(ByVal Idx As Integer)
Dim strSQL As String = "DELETE FROM INDIVIDU WHERE ID= " & idx
Dim cmd = New SQLiteCommand(strSQL, CON)
cmd.ExecuteNonQuery()
cmd.Dispose()
End Sub
Public Function DBNewIndexINDIVIDU() As Integer
Dim NewID As Integer = 1
Dim cmd = New SQLITECommand("SELECT MAX(ID) FROM INDIVIDU", CON)
Try
Dim DR As SQliteDataReader = cmd.ExecuteReader
While (DR.Read())
NewID = DR(0)
End While
DR.Close()
Return NewID + 1
Catch ex As Exception
Return NewID
End Try
End Function
Public Sub DBremplirListe(ByRef LST As ListBox) 'remarquez le ByRef
Dim cmd = New SQLiteCommand("SELECT ID,Nom FROM INDIVIDU", CON)
Dim DR As SQLiteDataReader = cmd.ExecuteReader
LST.Items.Clear()
While (DR.Read())
'DR(1) est le nom DR(0) est ID
LST.Items.Add(DR(1) & " |" & DR(0))
End While
DR.Close()
End Sub
'------------
Public Function ImageToByteArray(ByVal img As Image) As Byte()
Dim stream As New MemoryStream
img.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg)
Return stream.ToArray
End Function
Public Function ByteArrayToImage(ByVal ByteArray As Byte()) As Image
Dim stream As New MemoryStream(ByteArray, 0, ByteArray.Length)
Return Image.FromStream(stream, True)
End Function
End Module |
