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
A cura di: Il Totem