Question à propos des DTO's

**Kropernic** · 03/06/2013, 10h43

Envoyé par rv26t

J'avais fait quelques tests avec la classe Person et la DAL, je peux te montrer si cela t'intéresse.

A ce niveau-là, je pense que je devrais arriver à me débrouiller.

Par contre, je suis en train d'écrire la couche DAL d'un nouveau projet et j'ai une question pour les cas d'héritage.

Soit une classe DAL_A et une classe DAL_B ou DAl_B hérite de DAL_A.

Dans la classe DAL_A, j'ai plusieurs fonctions faisant des requêtes sur la DB avec DTO_A comme type de retour.

Du coup, dans DAL_B, je vais également avoir ces fonctions vu que cette classe hérite de DAL_A mais j'aimerais que le type de retour soit DTO_B.

Un simple transtype suffit-il ? J'ai encore du mal avec ça. Quelle est la bonne manière de faire ?

**rv26t** · 04/06/2013, 08h49

As-tu des propriétés supplémentaires dans DTO_B par rapport à DTO_A ?
Là ça me semble bizarre.

Je ne vois pas trop ce que tu veux faire.

**rv26t** · 04/06/2013, 09h01

Personnellement,

J'ai une bibliothéque de classe DataAccessLayer qui contient toutes les méthodes génériques d'accés aux base de données (quel que soit le type de base)

Dans une appli j'ai un ensemble de classe dédié à l'accés aux données qui utilise cette bibliothéque.

La création de l'instance d'accés aux données (pour toute l'appli)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Imports DataTransfertObject
Imports DataAccessLayer
 
Public Class clsDALAppli
    Protected Shared DALApp As clsDataAccessLayer
    ''' <summary>Constructeur standard.</summary>
    ''' <remarks>Identifiant standard de la base de données dans le fichier de config.</remarks>
    Public Sub New()
        CreDAL("WindowsApplication2.My.MySettings.PersonnesConnectionString")
    End Sub
 
    ' Instancie la DAL
    Private Sub CreDAL(ByVal IdBDD As String)
        Try
            DALApp = New clsDataAccessLayer(IdBDD)
 
        Catch aex As ApplicationException
' gestion des erreurs ...
    End Sub
 
End Class

La gestion des personnes (pour test)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Imports System.Data.OleDb
Imports DataAccessLayer.clsDataAccessLayer
Imports DataTransfertObject
 
Public Class PersonDB
    Inherits clsDALAppli
 
    Private Const K_TITRE_RETRY As String = "Réessayer la lecture ?"
 
    Public Function GetPersonId(ByVal PersonId As String) As PersonDTO
        Const K_REQUETTE As String = "select * from personnes where PersonId = ?"
        Try
            DALApp.AjouteParam("PersonId", DbType.String, PersonId)
            Return DALApp.GetSingleDTO(Of DTOParser_Person)(K_REQUETTE)
        Catch ex As Exception
            If MessageBox.Show(ex.Message, K_TITRE_RETRY, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = vbYes Then
                Return DALApp.GetSingleDTO((New DTOParser_Person).GetType.AssemblyQualifiedName, K_REQUETTE)
            End If
        End Try
        Return Nothing
    End Function
 
    Public Function GetPersonByEmail(ByVal email As String) As PersonDTO
        Const K_REQUETTE As String = "select * from personnes where email = ?"
        Try
            DALApp.AjouteParam("email", DbType.String, email)
            Return DALApp.GetSingleDTO(Of DTOParser_Person)(K_REQUETTE)
        Catch ex As Exception
            If MessageBox.Show(ex.Message, K_TITRE_RETRY, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = vbYes Then
                Return DALApp.GetSingleDTO((New DTOParser_Person).GetType.AssemblyQualifiedName, K_REQUETTE)
            End If
        End Try
        Return Nothing
    End Function
 
    Public Function GetAll() As List(Of DTOBase) 'TODO voir probleme type générique
        Try
            Return DALApp.GetListDTO(Of DTOParser_Person)("select * from personnes")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Return Nothing
    End Function
 
    ' avec le DTO 1 seul paramètre
    Public Sub EnregPerson(ByVal UnePersonne As PersonDTO, ByVal Ins As Boolean)
        Const K_REQUETTE_INSERT As String = "insert into personnes ([PersonId], [City], [Email], [ImAddress], [ImType], [LanguageId], [NamePers], [NickName], [Password], [PhoneHome], [PhoneMobile], [State], [TimeZoneId], [UtcCreated], [UtcModified], [ZipCode], [SpeakFrench]) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "
        Dim req As String = "update personnes set City=?, [Email]=?, [ImAddress]=?, [ImType]=?, [LanguageId]=?, [NamePers]=?, [NickName]=?, [Password]=?, [PhoneHome]=?, [PhoneMobile]=?, [State]=?, [TimeZoneId]=?, [UtcCreated]=?, [UtcModified]=?, [ZipCode]=?,SpeakFrench=? where PersonId = ?"
        Dim res As Integer
        If Ins Then req = K_REQUETTE_INSERT
        Try
            DALApp.EffaceListParam()
            If Ins Then DALApp.AjouteParam("PersonId", DbType.Int32, UnePersonne.PersonId)
            DALApp.AjouteParam("City", DbType.String, UnePersonne.City)
            DALApp.AjouteParam("Email", DbType.String, UnePersonne.Email)
            DALApp.AjouteParam("ImAddress", DbType.String, UnePersonne.ImAddress)
            DALApp.AjouteParam("ImType", DbType.Int32, UnePersonne.ImType)
            DALApp.AjouteParam("LanguageId", DbType.Int32, UnePersonne.LanguageId)
            DALApp.AjouteParam("NamePers", DbType.String, UnePersonne.NamePers)
            DALApp.AjouteParam("NickName", DbType.String, UnePersonne.NickName)
            DALApp.AjouteParam("Password", DbType.String, UnePersonne.Password)
            DALApp.AjouteParam("PhoneHome", DbType.String, UnePersonne.PhoneHome)
            DALApp.AjouteParam("PhoneMobile", DbType.String, UnePersonne.PhoneMobile)
            DALApp.AjouteParam("State", DbType.String, UnePersonne.State)
            DALApp.AjouteParam("TimeZoneId", DbType.Int32, UnePersonne.TimeZoneId)
            DALApp.AjouteParam("UtcCreated", DbType.Date, "20/05/2013")
            DALApp.AjouteParam("UtcModified", DbType.Date, "31/05/2013")
            DALApp.AjouteParam("ZipCode", DbType.Int32, 6)
            DALApp.AjouteParam("SpeakFrench", DbType.Boolean, True)
            If Not Ins Then DALApp.AjouteParam("PersonId", DbType.Int32, UnePersonne.PersonId)
 
            DALApp.ExecuterCommande(req, res)
        Catch ex As Exception
 
        End Try
 
    End Sub
 
End Class

Ma partie accés Base de données est très simplifié.

Mais j'ai un seul DTO (pour la personne qui transite)

**Kropernic** · 04/06/2013, 09h05

Pour donner un exemple concret, je suis sur un projet pour le magasin où je bosse où le but est de répertorié (entre autre), ce qu'on retrouve par terre comme étiquettes ou antivols.

Au niveau DB, j'ai les 3 tables suivantes :

T_OBJECT_OBJ :
- OBJ_ID INT
- OBJ_DATE DATETIME2
- ARE_ID INT (Id de la zone (AREA) dans laquelle l'objet a été trouvé)
T_ANTITHEFT_ANT :
- OBJ_ID INT
- ANT_COUNT TINYINT
T_LABEL_LAB :
- OBJ_ID INT
- LAB_AMOUNT DECIMAL

Un objet a donc un ID et est retrouvé dans une zone et à une date précise.
Un objet peut être un antivol ou une étiquette.
Pour les antivols, on note le nombre qu'on a retrouvé.
Pour une étiquette, on note le montant de l'étiquette.

J'ai donc voulu reproduire cette héritage dans mes classes.
Voici ce que j'ai fait jusqu'ici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Public Class FoundObject
    Inherits DTOBase
 
    Public Property Id As Integer
    Public Property FoundDate As DateTime
    Public Property Area As Area
 
    Public Sub New(id As Integer, foundDate As DateTime, area As Area)
        Me.Id = id
        Me.FoundDate = foundDate
        Me.Area = area
    End Sub
End Class
 
 
Public Class Antitheft
    Inherits FoundObject
 
    Public Property Count As Integer
 
    Public Sub New(id As Integer, foundDate As DateTime, area As Area, count As Integer)
        MyBase.New(id, foundDate, area)
        Me.Count = count
    End Sub
End Class
 
Public Class Label
    Inherits FoundObject
 
    Public Property Amount As Decimal
 
    Public Sub New(id As Integer, foundDate As DateTime, area As Area, amount As Decimal)
        MyBase.New(id, foundDate, area)
        Me.Amount = amount
    End Sub
End Class

Pour faire la classe disons BLL_FoundObject, pas de souci. Mais je perds les pédales dès que je fais hérité BLL_Lable de BLL_FoundObject.

**Kropernic** · 04/06/2013, 09h08

Et voici ma couche DAL pour les DTO cités (ANTI_MALI est le nom du projet) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Imports ANTI_MALI_DTO
Imports System.Data.SqlClient
 
Public Class Base
    Private Shared Function CreateConnection() As SqlConnection
        Dim cnx As New SqlConnection
        Dim server As String = Nothing
        Using sr As New IO.StreamReader("\db.dat")
            server = sr.ReadLine
        End Using
        cnx.ConnectionString = "Data Source=" & server & ";Initial Catalog=ANTI_MALI;Integrated Security=True"
        cnx.ConnectionString = ""
        Return cnx
    End Function
 
    Public 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
 
    Public 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
 
    Public Shared Function CreateParameter(name As String, value As Int16) 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
 
    Public 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
 
    Public 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
 
    Public 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
 
    Public 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
 
    Public 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
 
    Public 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
 
    Public 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 = Nothing
            End If
        Catch e As Exception
            Throw e
        Finally
            command.Connection.Close()
            command.Connection.Dispose()
        End Try
 
        Return dtoList
    End Function
 
End Class

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Imports System.Data.SqlClient
 
Public Class FoundObject
    Inherits Base
 
    Public Shared Function SelectObjectById(id As Integer) As ANTI_MALI_DTO.FoundObject
        Dim foundObj As ANTI_MALI_DTO.FoundObject
        Using cmd As SqlCommand = CreateSpCommand("UP_OBJECT_SELECT")
            cmd.Parameters.Add(CreateParameter("@OBJ_ID", id))
            foundObj = GetSingleDTO(Of ANTI_MALI_DTO.FoundObject)(cmd)
        End Using
        Return foundObj
    End Function
 
    Public Shared Function SelectObjectsByDate(_date As DateTime) As List(Of ANTI_MALI_DTO.FoundObject)
        Dim foundObjs As List(Of ANTI_MALI_DTO.FoundObject)
        Using cmd As SqlCommand = CreateSpCommand("UP_OBJECT_SELECT")
            cmd.Parameters.Add(CreateParameter("@OBJ_DATE", _date))
            foundObjs = GetDTOList(Of ANTI_MALI_DTO.FoundObject)(cmd)
        End Using
        Return foundObjs
    End Function
 
    Public Shared Function SelectObjectsByArea(areaId As Integer) As List(Of ANTI_MALI_DTO.FoundObject)
        Dim foundObjs As List(Of ANTI_MALI_DTO.FoundObject)
        Using cmd As SqlCommand = CreateSpCommand("UP_OBJECT_SELECT")
            cmd.Parameters.Add(CreateParameter("@ARE_ID", areaId))
            foundObjs = GetDTOList(Of ANTI_MALI_DTO.FoundObject)(cmd)
        End Using
        Return foundObjs
    End Function
 
    Public Shared Function SelectObjects() As List(Of ANTI_MALI_DTO.FoundObject)
        Dim foundObjs As List(Of ANTI_MALI_DTO.FoundObject)
        Using cmd As SqlCommand = CreateSpCommand("UP_OBJECT_SELECT")
            foundObjs = GetDTOList(Of ANTI_MALI_DTO.FoundObject)(cmd)
        End Using
        Return foundObjs
    End Function
End Class

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Imports System.Data.SqlClient
 
Public Class Antitheft
    Inherits Base
 
    Public Shared Function SelectAntitheftById(id As Integer) As ANTI_MALI_DTO.Antitheft
        Dim antitheft As ANTI_MALI_DTO.Antitheft
        Using cmd As SqlCommand = CreateSpCommand("UP_ANTITHEFT_SELECT")
            cmd.Parameters.Add(CreateParameter("@OJB_ID", id))
            antitheft = GetSingleDTO(Of ANTI_MALI_DTO.Antitheft)(cmd)
        End Using
        Return antitheft
    End Function
 
    Public Shared Function SelectAntitheftsByDate(_date As DateTime) As List(Of ANTI_MALI_DTO.Antitheft)
        Dim antithefts As List(Of ANTI_MALI_DTO.Antitheft)
        Using cmd As SqlCommand = CreateSpCommand("UP_ANTITHEFT_SELECT")
            cmd.Parameters.Add(CreateParameter("@OBJ_DATE", _date))
            antithefts = GetDTOList(Of ANTI_MALI_DTO.Antitheft)(cmd)
        End Using
        Return antithefts
    End Function
 
    Public Shared Function SelectAntitheftsByArea(areaId As Integer) As List(Of ANTI_MALI_DTO.Antitheft)
        Dim antithefts As List(Of ANTI_MALI_DTO.Antitheft)
        Using cmd As SqlCommand = CreateSpCommand("UP_ANTITHEFT_SELECT")
            cmd.Parameters.Add(CreateParameter("@ARE_ID", areaId))
            antithefts = GetDTOList(Of ANTI_MALI_DTO.Antitheft)(cmd)
        End Using
        Return antithefts
    End Function
 
    Public Shared Function SelectAntithefts() As List(Of ANTI_MALI_DTO.Antitheft)
        Dim antithefts As List(Of ANTI_MALI_DTO.Antitheft)
        Using cmd As SqlCommand = CreateSpCommand("UP_ANTITHEFT_SELECT")
            antithefts = GetDTOList(Of ANTI_MALI_DTO.Antitheft)(cmd)
        End Using
        Return antithefts
    End Function
 
End Class

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Imports System.Data.SqlClient
 
Public Class LABEL
    Inherits Base
 
    Public Shared Function SelectLabelById(id As Integer) As ANTI_MALI_DTO.Label
        Dim label As ANTI_MALI_DTO.Label
        Using cmd As SqlCommand = CreateSpCommand("UP_LABEL_SELECT")
            cmd.Parameters.Add(CreateParameter("@OJB_ID", id))
            label = GetSingleDTO(Of ANTI_MALI_DTO.Label)(cmd)
        End Using
        Return label
    End Function
 
    Public Shared Function SelectLabelsByDate(_date As DateTime) As List(Of ANTI_MALI_DTO.Label)
        Dim labels As List(Of ANTI_MALI_DTO.Label)
        Using cmd As SqlCommand = CreateSpCommand("UP_LABEL_SELECT")
            cmd.Parameters.Add(CreateParameter("@OBJ_DATE", _date))
            labels = GetDTOList(Of ANTI_MALI_DTO.Label)(cmd)
        End Using
        Return labels
    End Function
 
    Public Shared Function SelectLabelsByArea(areaId As Integer) As List(Of ANTI_MALI_DTO.Label)
        Dim labels As List(Of ANTI_MALI_DTO.Label)
        Using cmd As SqlCommand = CreateSpCommand("UP_LABEL_SELECT")
            cmd.Parameters.Add(CreateParameter("@ARE_ID", areaId))
            labels = GetDTOList(Of ANTI_MALI_DTO.Label)(cmd)
        End Using
        Return labels
    End Function
 
    Public Shared Function SelectLabels() As List(Of ANTI_MALI_DTO.Label)
        Dim labels As List(Of ANTI_MALI_DTO.Label)
        Using cmd As SqlCommand = CreateSpCommand("UP_LABEL_SELECT")
            labels = GetDTOList(Of ANTI_MALI_DTO.Label)(cmd)
        End Using
        Return labels
    End Function
 
End Class

J'avais commencé en faisant hérité les classes Label et Antitheft de FoundObject mais alors les noms de méthodes ne me plaisent pas et le type retourné n'est pas le bon non plus... Du coup, j'ai supprimé cet héritage et j'ai fait hérité directement de la classe Base.

Question à propos des DTO's

VB.NET

Discussions similaires

Partager

Partager