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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
| Imports System.Data.OleDb
Public Class Form1
Private Mt As Form1Metier
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Mt = New Form1Metier("C:\Git Projets\baseaccess\BaseAccess\bin\Debug\BDD\TEST.accdb")
Dim dt As DataTable = Mt.LireTable1
End Sub
End Class
Public Class Form1Metier
Inherits Connexion
Public Sub New(ByVal Fichier As String)
MyBase.New(Fichier) 'ascenseur
End Sub
Public Function LireTable1() As DataTable
Return DataReaderToDataTable(Execute("Select * from Table1"), "Table1")
End Function
Sub MajTable1(ByRef MyTable As DataTable)
Dim lstRemoved As Integer() = MyTable.Rows.Cast(Of DataRow)().Where(Function(g) g.RowState = DataRowState.Deleted).Select(Function(t) Integer.Parse(t("Id", DataRowVersion.Original).ToString())).ToArray()
If (lstRemoved.Count() > 0) Then
Execute($"DELETE FROM {MyTable.TableName} WHERE [ID] in ({String.Join(",", lstRemoved)})")
End If
For Each d As DataRow In MyTable.Rows.Cast(Of DataRow)().Where(Function(g) g.RowState = DataRowState.Modified OrElse g.RowState = DataRowState.Added)
Select Case d.RowState
Case DataRowState.Modified
UpdateTable1(d("ID").ToString, d("NOM").ToString, d("Prénom").ToString, MyTable.TableName)
Case DataRowState.Added
InsertTable1(d("NOM").ToString, d("Prénom").ToString, MyTable.TableName)
End Select
'If d.RowState <> DataRowState.Added Then d.AcceptChanges() Else d.SetAdded(True)
d.AcceptChanges()
Next
MyTable = LireTable1()
End Sub
Private Sub UpdateTable1(ID As String, Nom As String, Prénom As String, Table As String)
Dim prm() As ParamCmd = {New ParamCmd, New ParamCmd, New ParamCmd}
With prm(0)
.Name = "@Nom" : .Size = 255 : .Type = ConstType.adWChar : .Value = Nom
End With
With prm(1)
.Name = "@Prénom" : .Size = 255 : .Type = ConstType.adWChar : .Value = Prénom
End With
With prm(2)
.Name = "@ID" : .Size = 16 : .Type = ConstType.adBigInt : .Value = ID
End With
Execute($"UPDATE [{Table}] SET [Nom] =?,[Prénom]=? WHERE [Id]=?", prm)
End Sub
Sub InsertTable1(Nom As String, Prénom As String, Table As String)
Dim prm() As ParamCmd = {New ParamCmd, New ParamCmd}
With prm(0)
.Name = "@Nom" : .Size = 255 : .Type = ConstType.adWChar : .Value = Nom
End With
With prm(1)
.Name = "@Prénom" : .Size = 255 : .Type = ConstType.adWChar : .Value = Prénom
End With
Execute($"Insert into [{Table}] ([Nom],[Prénom]) Values(?,?)", prm)
End Sub
End Class
Public Class Connexion
Public Structure ParamCmd
Public Name As String
Public Type As ConstType
Public Direction As ConstDirection
Public Size As Long
Public Value As String
End Structure
Public Enum ConstDirection
adParamUnknown = 0 ' Indique que la direction du paramètre est inconnue.
adParamInput = 1 ' Par défaut. Indique que le paramètre représente un paramètre d'entrée.
adParamOutput = 2 ' Indique que le paramètre représente un paramètre de sortie.
adParamInputOutput = 3 ' Indique que le paramètre représente à la fois un paramètre d’entrée et de sortie.
adParamReturnValue = 4 ' Indique que le paramètre représente une valeur de retour.
End Enum
Public Enum ConstType
adEmpty = 0 ' Indicates an eight-byte signed integer (DBTYPE_I8).
adSmallInt = 2 ' Indicates a binary value (DBTYPE_BYTES).
adInteger = 3 ' Indicates a Boolean value (DBTYPE_BOOL).
adSingle = 4 ' Indicates a null-terminated character string (Unicode) (DBTYPE_BSTR).
adDouble = 5 ' Indicates a four-byte chapter value that identifies rows in a child rowset (DBTYPE_HCHAPTER).
adCurrency = 6 ' Indicates a string value (DBTYPE_STR).
adDate = 7 ' Indicates a currency value (DBTYPE_CY). Currency is a fixed-point number with four digits to the right of the decimal point. It is stored in an eight-byte signed integer scaled by 10,000.
adBSTR = 8 ' Indicates a date value (DBTYPE_DATE). A date is stored as a double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.
adIDispatch = 9 ' Indicates a date value (yyyymmdd) (DBTYPE_DBDATE).
adError = 10 ' Indicates a time value (hhmmss) (DBTYPE_DBTIME).
adBoolean = 11 ' Indicates a date/time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
adVariant = 12 ' Indicates an exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
adIUnknown = 13 ' Indicates a double-precision floating-point value (DBTYPE_R8).
adDecimal = 14 ' Specifies no value (DBTYPE_EMPTY).
adTinyInt = 16 ' Indicates a 32-bit error code (DBTYPE_ERROR).
adUnsignedTinyInt = 17 ' Indicates a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILETIME).
adUnsignedSmallInt = 18 ' Indicates a globally unique identifier (GUID) (DBTYPE_GUID).
adUnsignedInt = 19 ' Indicates a pointer to an IDispatch interface on a COM object (DBTYPE_IDISPATCH).
adBigInt = 20 ' Indicates a four-byte signed integer (DBTYPE_I4).
adUnsignedBigInt = 21 ' Indicates a pointer to an IUnknown interface on a COM object (DBTYPE_IUNKNOWN).
adFileTime = 64 ' Indicates a long binary value.
adGUID = 72 ' Indicates a long string value.
adBinary = 128 ' Indicates a long null-terminated Unicode string value.
adChar = 129 ' Indicates an exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).
adWChar = 130 ' Indicates an Automation PROPVARIANT (DBTYPE_PROP_VARIANT).
adNumeric = 131 ' Indicates a single-precision floating-point value (DBTYPE_R4).
adUserDefined = 132 ' Indicates a two-byte signed integer (DBTYPE_I2).
adDBDate = 133 ' Indicates a one-byte signed integer (DBTYPE_I1).
adDBTime = 134 ' Indicates an eight-byte unsigned integer (DBTYPE_UI8).
adDBTimeStamp = 135 ' Indicates a four-byte unsigned integer (DBTYPE_UI4).
adChapter = 136 ' Indicates a two-byte unsigned integer (DBTYPE_UI2).
adPropVariant = 138 ' Indicates a one-byte unsigned integer (DBTYPE_UI1).
adVarNumeric = 139 ' Indicates a user-defined variable (DBTYPE_UDT).
adVarChar = 200 ' Indicates a binary value.
adLongVarChar = 201 ' Indicates a string value.
adVarWChar = 202 ' Indicates an Automation Variant (DBTYPE_VARIANT).
adLongVarWChar = 203 ' Indicates a numeric value.
adVarBinary = 204 ' Indicates a null-terminated Unicode character string.
adLongVarBinary = 205 ' Indicates a null-terminated Unicode character string (DBTYPE_WSTR).
End Enum
Public Enum CommAdo
dCmdUnspecified = -1 ' Does not specify the command type argument.
adCmdText = 1 ' Evaluates CommandText as a textual definition of a command or stored procedure call.
adCmdTable = 2 ' Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.
adCmdStoredProc = 4 ' Evaluates CommandText as a stored procedure name.
adCmdUnknown = 8 ' Default. Indicates that the type of command in the CommandText property is not known.
adCmdFile = 256 ' Evaluates CommandText as the file name of a persistently stored Recordset. Used with Recordset.Open or Requery only.
adCmdTableDirect = 512 ' Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only. To use the Seek method, the Recordset must be opened with adCmdTableDirect. This value cannot be combined with the ExecuteOptionEnum value adAsyncExecute.
End Enum
Private cnx As OleDbConnection = Nothing
Private _Fichier As String
Protected Sub New(ByVal Fichier As String)
_Fichier = Fichier
End Sub
Protected Function OpenConnexion()
'"Provider=Microsoft.ACE.OLEDB.14.0
Dim GenereCSTRING As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _Fichier & ";"
cnx = New OleDbConnection(GenereCSTRING)
Try
cnx.Open()
Return True
Catch ex As Exception
Return ex.Message
End Try
End Function
Protected Function Execute(ByVal Sql As String) As OleDbDataReader
If cnx Is Nothing Then OpenConnexion()
Using cmd As New OleDb.OleDbCommand(Sql, cnx)
Try
Return cmd.ExecuteReader()
Catch ex As Exception
Return Nothing
End Try
End Using
End Function
Protected Function Execute(ByVal Sql As String, ByVal Param As Object) As OleDbDataReader
If cnx Is Nothing Then OpenConnexion()
Using cmd As New OleDb.OleDbCommand(Sql, cnx)
For Each c As Object In Param
If c.Size <> 0 Then cmd.Parameters.Add(c.Name, c.Type, c.Size).Value = c.Value
Next
Try
Return cmd.ExecuteReader()
Catch ex As Exception
Return Nothing
End Try
End Using
End Function
Protected Function DataReaderToDataTable(DR As OleDbDataReader) As DataTable
Using TD As DataTable = New DataTable
TD.Load(DR)
Return TD
End Using
End Function
Protected Function DataReaderToDataTable(DR As OleDbDataReader, TableName As String) As DataTable
Using TD As DataTable = New DataTable
TD.Load(DR)
TD.TableName = TableName
Return TD
End Using
End Function
Public Sub CloseConnexion()
cnx.Close()
cnx.Dispose()
cnx = Nothing
End Sub
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class |
Comment faire prendre en charge la fenêtre sources de données par mon projet ?
Répondre avec citation
Envoyé par Thumb down
, je vais tester. 
Partager