Procédure stockée et paramètre output
Bonjour,
Je ne sais pas trop s'il s'agit d'un bug dans mon code vb ou dans mon code t-sql alors je ne sais pas trop dans quel forum poster. Mais vu qu'à priori, mon code t-sql est correct, je vais partir du principe que c'est un bug dans mon application.
Le fait que j'ai un objet que je veux sauver en DB et récupérer son Id.
Voici le code de ma couche DAL utilisé pour sauver l'objet en question :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| Public Shared Sub SaveArea(area As ANTI_MALI_DTO.Area)
Using cmd As SqlCommand = CreateSpCommand("UP_AREA_SAVE")
cmd.Parameters.Add(CreateParameter("@ARE_ID", area.Id))
cmd.Parameters.Add(CreateParameter("@ARE_NAME", area.Name, 5))
cmd.Parameters.Add(CreateParameter("@ARE_X", area.X))
cmd.Parameters.Add(CreateParameter("@ARE_Y", area.Y))
cmd.Parameters.Add(CreateParameter("@FLO_ID", area.Floor.Id))
Dim newId As SqlParameter = CreateOutputParameter("@NEW_ID", SqlDbType.Int)
cmd.Parameters.Add(newId)
ExecuteNonQuery(cmd)
If area.Id = 0 Then
area.Id = CInt(newId.Value)
End If
End Using
End Sub |
Dans l'application, lorsqu'un nouvel objet est créé, il reçoit l'id 0. Et donc à la fin de la sub ci-dessus, si l'id de l'objet vaut 0, je lui affecte la valeur du paramètre @NEW_ID de type INT et de direction OUTPUT. Seulement cette valeur est toujours à NULL et cela pose bien évidemment problème.
J'ai du mal à voir ce que je fais de travers.
Ci dessous, le code de la classe DAL_BASE où se trouve les méthodes et fonctions utilisées dans le code ci-dessus :
Code:
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
| Imports System.Configuration
Imports System.Reflection
Imports ANTI_MALI_DTO
Imports System.Data.SqlClient
Public Class Base
Private Shared Function CreateConnection() As SqlConnection
Dim cnx As New SqlConnection
'Open the configuration file using the dll location
Dim myDllConfig As Configuration = ConfigurationManager.OpenExeConfiguration(Assembly.GetExecutingAssembly.Location)
' Get the appSettings section
Dim myDllConfigAppSettings As AppSettingsSection = DirectCast(myDllConfig.GetSection("appSettings"), AppSettingsSection)
' return the desired field
'myDllConfigAppSettings.Settings("MyConnectionString").Value
cnx.ConnectionString = myDllConfigAppSettings.Settings("MyConnectionString").Value
Return cnx
End Function
Protected Shared Function CreateSpCommand(spName As String) As SqlCommand
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = spName
cmd.Connection = CreateConnection()
Return cmd
End Function
Protected Shared Function CreateParameter(name As String, value As Integer) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.Int
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As Short) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.SmallInt
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As Byte) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.TinyInt
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As Byte(), size As Integer) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.VarBinary
param.Size = size
param.Value = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As String, size As Integer) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.VarChar
param.Size = size
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As DateTime) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.DateTime2
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As Boolean) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.Bit
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As Decimal) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.Decimal
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateOutputParameter(name As String, type As SqlDbType) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Output
param.SqlDbType = type
param.ParameterName = name
Return param
End Function
Protected Shared Function CreateOutputParameter(name As String, type As SqlDbType, size As Integer) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Output
param.ParameterName = name
param.SqlDbType = type
param.Size = size
Return param
End Function
Protected Shared Function GetSingleDTO(Of T As DTOBase)(ByRef command As SqlCommand) As T
Dim dto As T = Nothing
Try
command.Connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
If reader.HasRows Then
reader.Read()
Dim parser As Parser = DTOParserFactory.GetParser(GetType(T))
parser.PopulateOrdinals(reader)
dto = DirectCast(parser.PopulateDTO(reader), T)
reader.Close()
'Else
' ' Whever there's no data, we return null.
' dto = Nothing
End If
Catch e As Exception
Throw New Exception("Error populating data", e)
Finally
command.Connection.Close()
command.Connection.Dispose()
End Try
' return the DTO, it's either populated with data or null.
Return dto
End Function
Protected Shared Function GetDTOList(Of T As DTOBase)(ByRef command As SqlCommand) As List(Of T)
Dim dtoList As New List(Of T)()
Try
command.Connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
If reader.HasRows Then
' Get a parser for this DTO type and populate
' the ordinals.
Dim parser As Parser = DTOParserFactory.GetParser(GetType(T))
parser.PopulateOrdinals(reader)
' Use the parser to build our list of DTOs.
While reader.Read()
Dim dto As T = Nothing
dto = DirectCast(parser.PopulateDTO(reader), T)
dtoList.Add(dto)
End While
reader.Close()
Else
' Whenever there's no data, we return null.
dtoList = New List(Of T)
End If
Catch e As Exception
Throw e
Finally
command.Connection.Close()
command.Connection.Dispose()
End Try
Return dtoList
End Function
Protected Shared Sub ExecuteNonQuery(cmd As SqlCommand)
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
End Sub
Protected Shared Sub ExecuteQuery(cmd As SqlCommand)
cmd.Connection.Open()
cmd.ExecuteReader()
cmd.Connection.Close()
End Sub
End Class |
Comme ça je suis certain que vous avez tout ce que vous pourriez vouloir comme détail supplémentaire.
Une dernière chose néanmoins, le code de la procédure stockée :
Code:
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
| CREATE PROCEDURE [dbo].[UP_AREA_SAVE]
@ARE_ID INT,
@ARE_NAME VARCHAR(5),
@ARE_X DECIMAL(5,2),
@ARE_Y DECIMAL(5,2),
@FLO_ID TINYINT,
@NEW_ID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF @ARE_ID = 0
BEGIN
INSERT INTO dbo.T_AREA_ARE(ARE_NAME, ARE_X, ARE_Y, FLO_ID)
VALUES(@ARE_NAME, @ARE_X, @ARE_Y, @FLO_ID);
SET @NEW_ID = SCOPE_IDENTITY();
END
ELSE
UPDATE dbo.T_AREA_ARE
SET ARE_NAME = @ARE_NAME,
ARE_X = @ARE_X,
ARE_Y = @ARE_Y
WHERE
ARE_ID = @ARE_ID
END |
Je suis le nez dessus depuis 8h ce matin et je ne trouve pas la moindre erreur... Ca va être encore un truc gros comme un éléphant dans un couloir je parie...
Merci d'avance à ceux qui prendront le temps de jeter un oeil.
