Applicando i concetti del capitolo scorso, ho scritto un piccolo esempio pratico di applicazione basata su database, ossia un semplicissimo gestionale per organizzare la tabella Customers. L'interfaccia è questa:


Il controllo vuoto è una ListView con View=Details e HideSelection=False. Le tre textbox hanno un tag associato: la prima ha tag "FirstName", la seconda "LastName", la terza "Address" e la quarta "PhoneNumber". Ecco il codice:

Imports MySql.Data.MySqlClient

Class Form1
    Private Conn As MySqlConnection

    'Esegue una query sul database, quindi carica i
    'risultati nella listview
    Private Sub LoadData(ByVal Query As String)
        Dim Command As New MySqlCommand

        Command.CommandText = Query
        Command.Connection = Conn

        Dim Reader As MySqlDataReader = Command.ExecuteReader()

        If lstRecords.Columns.Count = 0 Then
            For I As Int32 = 0 To Reader.FieldCount - 1
                lstRecords.Columns.Add(Reader.GetName(I))
            Next
        End If

        Dim L As ListViewItem
        Dim S(Reader.FieldCount - 1) As String

        lstRecords.Items.Clear()
        Do While Reader.Read()
            For I As Int32 = 0 To S.Length - 1
                If Not Reader.IsDBNull(I) Then
                    S(I) = Reader.GetString(I)
                Else
                    S(I) = ""
                End If
            Next
            L = New ListViewItem(S)
            lstRecords.Items.Add(L)
        Loop

        Reader.Close()
        Command.Dispose()
        Command = Nothing
    End Sub

    Private Sub LoadData()
        Me.LoadData("SELECT * FROM Customers")
    End Sub

    'Scorciatoia per eseguire una query velocemente
    Private Function ExecuteQuery(ByVal Query As String) As Int32
        Dim Command As New MySqlCommand(Query, Conn)
        Dim Result As Int32 = Command.ExecuteNonQuery()

        Command.Dispose()
        Command = Nothing

        Return Result
    End Function

    Private Sub Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Conn = New MySqlConnection("Server=localhost; Database=appdata; Uid=root; Pwd=root;")

        Try
            Conn.Open()
        Catch ex As Exception
            Conn.Close()
            MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Me.Close()
        End Try

        LoadData()
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        If ExecuteQuery(String.Format("INSERT IGNORE INTO Customers VALUES(null, '{0}', '{1}', '{2}', '{3}');", txtFirstName.Text, txtLastName.Text, txtAddress.Text, txtPhoneNumber.Text)) Then
            MessageBox.Show("Cliente aggiunto!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
            LoadData()
        End If
    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        If lstRecords.SelectedIndices.Count = 0 Then
            MessageBox.Show("Nessun record selezionato!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        Dim ID As Int32 = CType(lstRecords.SelectedItems(0).SubItems(0).Text, Int32)
        Dim Query As New System.Text.StringBuilder()

        'L'istruzione UPDATE aggiorna i campi della tabella
        'specificata usando i valori posti dopo la clausola
        'SET. Solo i record che rispettano i vincoli imposti
        'dalla clausola WHERE vengono modificati
        Query.Append("UPDATE Customers SET")
        For Each T As TextBox In New TextBox() {txtFirstName, txtLastName, txtAddress, txtPhoneNumber}
            Query.AppendFormat(" {0} = '{1}',", T.Tag.ToString(), T.Text)
        Next
        'Rimuove l'ultima virgola...
        Query.Remove(Query.Length - 1, 1)

        Query.AppendFormat(" WHERE ID = {0};", ID)

        ExecuteQuery(Query.ToString())
        Query = Nothing
        LoadData()
    End Sub

    Private Sub btnFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFilter.Click
        Dim Query As New System.Text.StringBuilder()
        Dim Conditions As New List(Of String)

        Query.Append("SELECT * FROM Customers")

        'La scrittura:
        ' Field LIKE '%Something%'
        'equivarrebbe teoricamente a:
        ' Field.Contains(Something)
        For Each T As TextBox In New TextBox() {txtFirstName, txtLastName, txtAddress, txtPhoneNumber}
            If Not String.IsNullOrEmpty(T.Text) Then
                Conditions.Add(String.Format("WHERE {0} LIKE '%{1}%'", T.Tag.ToString(), T.Text))
            End If
        Next

        If Conditions.Count >= 1 Then
            Query.AppendFormat(" {0}", Conditions(0))
            If Conditions.Count > 1 Then
                For I As Int32 = 1 To Conditions.Count - 1
                    Query.AppendFormat(" AND {0}", Conditions(I))
                Next
            End If
        End If
        Query.Append(";")

        LoadData(Query.ToString())
        Query = Nothing
    End Sub

    Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        If Conn.State <> ConnectionState.Closed Then
            Conn.Close()
        End If
    End Sub

    Private Sub btnReload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReload.Click
        LoadData()
    End Sub

    Private Sub lstRecords_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstRecords.SelectedIndexChanged
        If lstRecords.SelectedItems.Count = 0 Then
            Exit Sub
        End If

        Dim Selected As ListViewItem = lstRecords.SelectedItems(0)
        txtFirstName.Text = Selected.SubItems(1).Text
        txtLastName.Text = Selected.SubItems(2).Text
        txtAddress.Text = Selected.SubItems(3).Text
        txtPhoneNumber.Text = Selected.SubItems(4).Text
    End Sub
End Class