Récup et boucler sur des enregistrements sous sql server!

Bonjour,

Souhaite-tu une méthode amateur ou une méthode professionnel ?

Moi, je code par couche.

Je crée un objet (une classe), une liste de cette objet, une requête SQL
(une classe), un unité d'accès aux données UAD(fr) ou CDA(en - Control Data Acces).

Voici une exemple d'objet de la Table X

#Region "--- Importation des classes externes ---"

Imports System.Runtime.Serialization
Imports System.Data.Objects.DataClasses
Imports System.ComponentModel

#End Region

''' <summary>
''' Données Élémentaire de la Table X
''' </summary>
''' <historique>
''' *************************************************************************************
'''  Projet        Date          Développeur          Description
''' *************************************************************************************
''' [100000]       2015-11-21    Kalissi              Création initial <br />
''' </historique>
''' <remarks>
''' Structure de données représentant la Table X ( Données Élementaire = De )
''' </remarks>
<EdmEntityTypeAttribute(NamespaceName:="PriojetX.DonneeElementaire.TableX", Name:="DeTableX")> _
<DataContractAttribute(IsReference:=True)> _
<Serializable(), CLSCompliant(True)> _
Public Class DeTableX

#Region "--- Attributs ---"

    ' La méta-instruction permet de masquer l'attribut dans l'intellissence
    ' et de ne voir que la propriétés

    <Browsable(False), EditorBrowsable(EditorBrowsableState.Never)> _
    Private zCol1 As String = String.Empty

    <Browsable(False), EditorBrowsable(EditorBrowsableState.Never)> _
    Private zCol2 As String = String.Empty

    <Browsable(False), EditorBrowsable(EditorBrowsableState.Never)> _
    Private zCol3 As String = String.Empty

    <Browsable(False), EditorBrowsable(EditorBrowsableState.Never)> _
    Private zCol4 As String = String.Empty

    <Browsable(False), EditorBrowsable(EditorBrowsableState.Never)> _
    Private zCol5 As String = String.Empty

    <Browsable(False), EditorBrowsable(EditorBrowsableState.Never)> _
    Private zColY As String = String.Empty

#End Region

#Region "--- Propriétés ---"

    <EdmScalarPropertyAttribute()> _
    <DataMemberAttribute()> _
    Public Property Col1 As String
        Get
            Return Me.zCol1
        End Get
        Set(value As String)
            Me.zCol1 = value
        End Set
    End Property

    <EdmScalarPropertyAttribute()> _
    <DataMemberAttribute()> _
    Public Property Col2 As String
        Get
            Return Me.zCol2
        End Get
        Set(value As String)
            Me.zCol2 = value
        End Set
    End Property

    <EdmScalarPropertyAttribute()> _
    <DataMemberAttribute()> _
    Public Property Col3 As String
        Get
            Return Me.zCol3
        End Get
        Set(value As String)
            Me.zCol3 = value
        End Set
    End Property

    <EdmScalarPropertyAttribute()> _
    <DataMemberAttribute()> _
    Public Property Col4 As String
        Get
            Return Me.zCol4
        End Get
        Set(value As String)
            Me.zCol4 = value
        End Set
    End Property

<EdmScalarPropertyAttribute()> _
    <DataMemberAttribute()> _
    Public Property Col5 As String
        Get
            Return Me.zCol5
        End Get
        Set(value As String)
            Me.zCol5 = value
        End Set
    End Property

    <EdmScalarPropertyAttribute()> _
    <DataMemberAttribute()> _
    Public Property ColY As String
        Get
            Return Me.zColY
        End Get
        Set(value As String)
            Me.zColY = value
        End Set
    End Property

#End Region

#Region "--- Constructeur ---"

    ''' <summary>
    ''' Constructeur facultatif
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub New()

    End Sub

    ''' <summary>
    ''' Surcharge du constructeur avec paramètres de remplissage
    ''' </summary>
    ''' <param name="pCol1"></param>
    ''' <param name="pCol2"></param>
    ''' <param name="pCol3"></param>
    ''' <param name="pCol4"></param>
    ''' <param name="pCol5"></param>
    ''' <param name="pColY"></param>
    ''' <remarks></remarks>
    Public Sub New(ByVal pCol1 As String, _
                   ByVal pCol2 As String, _
                   ByVal pCol3 As String, _
                   ByVal pCol4 As String, _
                   ByVal pCol5 As String, _
                   ByVal pColY As String)

        Me.zCol1 = pCol1
        Me.zCol2 = pCol2
        Me.zCol3 = pCol3
        Me.zCol4 = pCol4
        Me.zCol5 = pCol5
        Me.zColY = pColY

    End Sub

#End Region

End Class

Ensuite je crée ma classe requête :

Exemple :

<Serializable()> _
Public Class ProjetX_SQL

#Region " Région Propriétés "

    Friend Shared ReadOnly Property ObtenirElements(ByVal pCritereLower As Int64, ByVal pCritereUpper As Int64) As String
        Get
            Dim ReqSQL As String = String.Empty
            ReqSQL = "SELECT * FROM TableX TBA WHERE ((TBA.Col1 > " & "'" & pCritereLower & "'" & ") AND " & _
                                                     "(TBA.Col1 < " & "'" & pCritereUpper & "'" & "))" & _
                                                     " ORDER BY TBA.Col1"
            Return ReqSQL
        End Get

    End Property
#End Region

End Class

Vient enfin l'unité d'accès au données :

#Region "--- Importation des classes externes ---"

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.DataRow

#End Region

<Serializable()> _
Public NotInheritable Class ProjetX_UAD
    Implements IDisposable

#Region "--- Attributs ---"

    Private zConnexion As New OleDb.OleDbConnection()

#End Region

#Region "--- Propriétés ---"

    Public Property Connexion As OleDb.OleDbConnection
        Get
            Return Me.zConnexion
        End Get
        Set(value As OleDb.OleDbConnection)
            Me.zConnexion = value
        End Set
    End Property

#End Region

#Region "--- Constructeur ---"

    Public Sub New(ByVal BaseDeDonnee As String)

        MyBase.new()

        Try
            ' Parametrage de la chaine de connection
            ' Voir http://www.connexionstring.com pour les détails de votre chaine de connexion pour SQL Serveur
            Connexion.ConnectionString = "Provider=Microsoft.Jet.OleDB.4.0;" & "Data Source= " & BaseDeDonnee & ";"
            Connexion.Open()

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

    End Sub

#End Region

#Region "--- Région Destructeur ---"

    ''' <summary>
    ''' Permet de libérer les ressources internes à la classe courante
    ''' </summary>
    ''' <remarks><pre>
    ''' </pre></remarks>
    Public Sub Dispose() Implements IDisposable.Dispose
        If (Not Connexion Is Nothing) Then
            Connexion.Close()
            Connexion.Dispose()
            Connexion = Nothing
        End If
    End Sub

#End Region

#Region "--- Méthodes ---"

    Public Function ExecuterLectureEVN(ByVal pCritereLower As Int64, pCritereUpper As Int64) As IList(Of DeTableX)

        Dim LsTbX As IList(Of DeTableX) = Nothing
        Dim MonAdapteur As OleDbDataAdapter = Nothing
        Dim MonDataTable As DataTable = Nothing
        Dim MonDataSet As New DataSet()

        Try
            Dim reqSQL As String = ProjetX_SQL.ObtenirElements(pCritereLower, pCritereUpper)
            MonAdapteur = New OleDb.OleDbDataAdapter(reqSQL, Connexion)
            MonAdapteur.Fill(MonDataSet, "EvnProc")
            MonDataTable = MonDataSet.Tables("EvnProc")

            LsTbX = MonDataTable.ConvertirDT_VersDE()

        Catch ex As Exception
            MessageBox.Show(ex.ToString)

        Finally
            Connexion.Close()

        End Try

        Return LsTbX

    End Function

#End Region

End Class

Ensuite, if suffit d'utiliser les méthodes LinQ pour obtenir et faire ce que tu veux. :-)

Dans un formulaire, ou un service d'affaire on instancie l'UAD,
on lance un appel, et on manipule la liste à son aise.

Exemple :

#Region "--- Importation des classes externes ---"

Imports System.ComponentModel

#End Region

''' <summary>
''' Formulaire Principal
''' </summary>
''' <historique>
''' *************************************************************************************
'''  Projet        Date          Développeur          Description
''' *************************************************************************************
''' [100000]       2015-11-21    Kalissi              Création initial <br />
''' </historique>
''' <remarks></remarks>
Public Class ProjetX

#Region "--- Attributs ---"

    <Browsable(False), EditorBrowsable(EditorBrowsableState.Never)> _
    Private zInstanceUAD As ProjetX_UAD

    <Browsable(False), EditorBrowsable(EditorBrowsableState.Never)> _
    Private zBaseDeDonnee As String

    <Browsable(False), EditorBrowsable(EditorBrowsableState.Never)> _
    Private zListeDonnees As IList(Of DeTableX)

#End Region

#Region "--- Propriétés ---"

    Private Property BaseDeDonnee As String
        Get
            Return Me.zBaseDeDonnee
        End Get
        Set(value As String)
            Me.zBaseDeDonnee = value
        End Set
    End Property

    Private ReadOnly Property InstanceUAD As ProjetX_UAD
        Get
            If (Me.zInstanceUAD Is Nothing) Then
                Me.zInstanceUAD = New ProjetX_UAD(BaseDeDonnee)
            End If
            Return Me.zInstanceUAD
        End Get
    End Property

    Private Property ListeDonnees As IList(Of DeTableX)
        Get
            Return Me.zListeDonnees
        End Get
        Set(value As IList(Of DeTableX))
            Me.zListeDonnees = value
        End Set
    End Property

#End Region

#Region "--- Constructeur ---"

    Public Sub New()

        ' Cet appel est requis par le concepteur.
        InitializeComponent()

        ' Ajoutez une initialisation quelconque après l'appel InitializeComponent().

        ' Ajoutez ici l'alimentation servant à la chaine de connexion

        BaseDeDonnees = ObtenirInformation()

    End Sub

#End Region

#Region "--- Méthodes ---"

#Region "--- Évènements Boutons ---"

    Private Sub btnQuitter_Click(sender As System.Object, e As System.EventArgs) Handles btnQuitter.Click
        Me.Close()
    End Sub

    Private Sub btnLecture_Click(sender As System.Object, e As System.EventArgs) Handles btnLecture.Click

        Dim Param1 As Int64 = 0 : Dim Param2 As Int64 = 200
        Dim LstTmp1 As IList(Of DeTableX) = Nothing
        Dim LstTmp2 As IList(Of DeTableX) = Nothing
        Dim ChaineReponse As String = String.Empty
        Dim Entite As DeTableX = Nothing
        Dim Resultat As String = String.Empty

        ListeDonnees = InstanceUAD.ExecuterLecture(Param1, Param2)
        If (ListeDonnees IsNot Nothing) Then
            ' Exemple 1 - Méthode LinQ
            LstTmp1 = (From EntX As DeTableX In ListeDonnees Select EntX Order By EntX.Col1 Ascending).ToList

            ' Exemple 2 - Méthode LinQ
            LstTmp2 = (From EntY As DeTableX In ListeDonnees Select EntY Where EntY.Col1 < "101" Order By EntY.Col1 Descending).ToList

            Dim Limite As Int32 = LstTmp1.Count - 1
            For Boucle As Int32 = 0 To Limite
                Entite = LstTmp2.Item(Boucle)
                ChaineReponse = String.Concat(Entite.Col1, ";")
            Next
            Resultat = ChaineReponse.Substring(0, ChaineReponse.Length - 1)

            If (Not (String.IsNullOrEmpty(Resultat))) Then
                Me.txtResultat.Text = Resultat
            End If

        End If

    End Sub

#End Region

#End Region

End Class

Et voilà le travail :-)

Bonne continuité.

K