Public Class AdapterClienti
    Implements IClienti

    Protected WithEvents _ad As SqlClient.SqlDataAdapter
    Protected _cn As SqlClient.SqlConnection
    Protected _Select As SqlClient.SqlCommand

    Protected Overridable Sub InitConnection()
        Me._cn = New SqlClient.SqlConnection("Password=miapassword;Persist Security Info=True;User ID=sa;Initial Catalog=DBGestionale;Data Source=127.0.0.1")
    End Sub

    Protected Overridable Sub InitAdapter()
        Me._ad = New SqlClient.SqlDataAdapter
        Dim tm As New System.Data.Common.DataTableMapping
        With tm
            .SourceTable = "Table"
            .DataSetTable = "Clienti"
            .ColumnMappings.Add("Codice", "Codice")
            .ColumnMappings.Add("RagioneSociale", "RagioneSociale")
            Me._ad.TableMappings.Add(tm)
        End With
    End Sub

    Protected Overridable Sub InitSelectCommand()
        Dim sSQL As String = "SELECT Codice, RgioneSociale FROM DBGestionale.dbo.Clienti " & _
                            "WHERE (dbo.PICO.CKY_CNT=@Codice OR @Codice='') AND dbo.RUDT.CDS_CNT_RAGSOC Like @RagioneSociale
        Me._Select = New SqlClient.SqlCommand(sSQL)
        Me._Select.CommandType = CommandType.Text
        Me._Select.Connection = Me.Connection
        Me._Select.Parameters.Add("@Codice", SqlDbType.VarChar, 0)
        Me._Select.Parameters.Add("@RagioneSociale", SqlDbType.VarChar, 0)
        Me._ad.SelectCommand = Me._Select
    End Sub

    Protected Property Connection() As SqlClient.SqlConnection Implements IClienti.Connection
        Get
            If Me._cn Is Nothing Then
                Me.InitConnection()
            End If
            Return Me._cn
        End Get
        Set(ByVal value As SqlClient.SqlConnection)
            Me._cn = value
            If (Not (Me.Adapter.SelectCommand) Is Nothing) Then
                Me.Adapter.SelectCommand.Connection = Me._cn
            End If
        End Set
    End Property

    Private ReadOnly Property Adapter() As SqlClient.SqlDataAdapter
        Get
            If (Me._ad Is Nothing) Then
                Me.InitAdapter()
            End If
            Return Me._ad
        End Get
    End Property

    Protected ReadOnly Property SelectCommand() As SqlClient.SqlCommand
        Get
            If (Me._Select Is Nothing) Then
                Me.InitSelectCommand()
            End If
            Return Me._Select
        End Get
    End Property

    Sub New()
        MyBase.New()
    End Sub

    Private Overloads Function Fill(ByVal Table As ClientiMxl) As Integer
        Me.Adapter.SelectCommand = Me.SelectCommand
        Table.Clear()
        Return Me.Adapter.Fill(Table)
    End Function

    Public Overridable Function GetClienti(ByVal Filtro As IFiltroClienti) As List(Of ICliente) Implements IClienti.GetClienti
        'Crea command e applica i filtri
        Me.Adapter.SelectCommand = Me.SelectCommand
        Me.Adapter.SelectCommand.Parameters.Item("@Codice").Value = Filtro.Codice
        Me.Adapter.SelectCommand.Parameters.Item("@RagioneSociale").Value = LikeString(Filtro.RagioneSociale)
        'Popola la tabella 
        Dim Table As ClientiGestionale = New ClientiGestionale
        Me.Adapter.Fill(Table)
        'Crea variabile per valore di ritorno
        Dim ret As New List(Of ICliente)
        'Itera sulla tabella per create valore di ritorno
        For Each r As ICliente In Table.Rows
            ret.Add(CType(r, ICliente))
        Next
        Return ret
    End Function

    Private Function LikeString(ByVal s As String) As String
        'Aggiunge "%" per le stringhe utilizzati nei parametri con operatori Like
        s = IIf(s.StartsWith("%"), s, String.Concat("%", s))
        s = IIf(s.EndsWith("%"), s, String.Concat(s, "%"))
        Return s
    End Function

    Protected Class ClientiGestionale
        Inherits System.Data.DataTable
        Implements System.Collections.IEnumerable

        Private _ColonnaCodice As System.Data.DataColumn
        Protected ReadOnly Property ColonnaCodice() As System.Data.DataColumn
            Get
                Return Me._ColonnaCodice
            End Get
        End Property

        Private _ColonnaRagioneSociale As System.Data.DataColumn
        Protected ReadOnly Property ColonnaRagioneSociale() As System.Data.DataColumn
            Get
                Return Me._ColonnaRagioneSociale
            End Get
        End Property

        Protected Friend Sub New()
            MyBase.New()
            Me.TableName = "Clienti"
            Me.BeginInit()
            Me.InitClass()
            Me.EndInit()
        End Sub

        Private Sub InitClass()
            Me._ColonnaCodice = New System.Data.DataColumn("Codice", GetType(String), Nothing, MappingType.Element)
            MyBase.Columns.Add(Me._ColonnaCodice)
            Me._ColonnaRagioneSociale = New System.Data.DataColumn("RagioneSociale", GetType(String), Nothing, MappingType.Element)
            MyBase.Columns.Add(Me._ColonnaRagioneSociale)
        End Sub

        Protected Overrides Function NewRowFromBuilder(ByVal builder As Global.System.Data.DataRowBuilder) As Global.System.Data.DataRow
            Return New ClienteMxl(builder)
        End Function

        Protected Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
            Return Me.Rows.GetEnumerator
        End Function

        Protected Class ClienteGestionale
            Inherits System.Data.DataRow
            Implements ICliente

            Private Tabella As ClientiGestionale

            Public ReadOnly Property Codice() As String Implements ICliente.Codice
                Get
                    Return IIf(Me(Me.Tabella.ColonnaCodice) Is DBNull.Value, String.Empty, Me(Me.Tabella.ColonnaCodice).ToString)
                End Get
            End Property

            'Public Property RagioneSociale() As String
            Public ReadOnly Property RagioneSociale() As String Implements ICliente.RagioneSociale
                Get
                    Return IIf(Me(Me.Tabella.ColonnaRagioneSociale) Is DBNull.Value, String.Empty, Me(Me.Tabella.ColonnaRagioneSociale).ToString)
                End Get
                'Set(ByVal value As String)
                '    Me(Me.Tabella.ColonnaRagioneSociale) = value
                'End Set
            End Property

            Protected Friend Sub New(ByVal builder As System.Data.DataRowBuilder)
                MyBase.New(builder)
                Me.Tabella = CType(Me.Table, ClientiGestionale)
            End Sub
        End Class
    End Class
End Class