In this post I have taken the previous series of posts on the simple ToDo application and made a few improvements. Here is a list of things I didn’t like about the previous program.

1. We need to limit the number of characters the user may enter
2. We really don’t need a Refresh button if we automatically refresh the list as needed.
3. We should trap and handle database errors in the SQLiteDataAccess file.

I have made some changes and here it what it looks like now. The Refresh button is no longer needed. Click on the screenshot below.

To limit the number of characters a user can enter, use MaxLength=”17″ to limit it to 17 characters. More work will need to be done however because user may still be able to paste in more than the limit.

To automatically refresh the list, we call LoadToDoList() after the users Adds, Save (an edit) or Deletes. Here is the code in the C# code-behind file MainWindow.xaml.cs. In the code below I have added some if statements around the calls to the SQLiteDataAccess class’ methods. For example, if we cannot connect to the database to add a new record, we want to display a message and leave it at that. If we do add the record, we need to refresh the list by calling LoadToDoList().

using System;
using System.Collections.Generic;
using System.Windows;
using System.Windows.Controls;
using ToDoList2.Models;
namespace ToDoList2
{
    public partial class MainWindow : Window
    {
        List<ToDo> todos = new List<ToDo>();

        public MainWindow()
        {
            InitializeComponent();
            LoadToDoList();
        }
        private void LoadToDoList()
        {
            todos = SQLiteDataAccess.LoadToDo();
            WireUpToDoList();
        }
        private void WireUpToDoList()
        {
            // attach the in-memory list to the ListBox
            listToDoListBox.ItemsSource = null; // important to first make null
            listToDoListBox.ItemsSource = todos;
        }
        private void BtnAddToDo_Click(object sender, RoutedEventArgs e)
        {
            ToDo td = new ToDo();
            td.Name = txtboxName.Text;
            td.Status = txtboxStatus.Text;

            if (!string.IsNullOrEmpty(txtboxName.Text.Trim()) &&
                  !string.IsNullOrEmpty(txtboxStatus.Text.Trim()))
            {
                if (SQLiteDataAccess.SaveToDo(td))
                {
                    txtboxName.Text = "";
                    txtboxStatus.Text = "";
                    LoadToDoList();  // new line of code version 2
                }
            }
        }
        private void ListToDoListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            ListBox lb = sender as ListBox;
            // user can only select one row at a time, but here we must use
            // SelectedItems (plural) because of the template.
            foreach (object o in lb.SelectedItems)
            {
                txtboxEditId.Text = (o as ToDo).Id.ToString();
                txtboxEditName.Text = (o as ToDo).Name;
                txtboxEditStatus.Text = (o as ToDo).Status;
            }
        }
        private void BtnSaveToDo_Click(object sender, RoutedEventArgs e)
        {
            // We must update the record in the database with the 
            // object 
            var td = new ToDo();
            td.Id = Convert.ToInt32(txtboxEditId.Text);
            td.Name = txtboxEditName.Text;
            td.Status = txtboxEditStatus.Text;
            if(SQLiteDataAccess.UpdateToDo(td))
            {
                LoadToDoList();  // new line of code version 2
            }
        }
        private void BtnDeleteToDo_Click(object sender, RoutedEventArgs e)
        {
            var td = new ToDo();
            td.Id = Convert.ToInt32(txtboxEditId.Text);
            td.Name = txtboxEditName.Text;
            MessageBoxResult r = MessageBox.Show("Are you sure you want to permanently delete " + td.Name + "?", "Delete To Do",
                MessageBoxButton.OKCancel, MessageBoxImage.Warning);
            if (r == MessageBoxResult.OK)
            {
                if (SQLiteDataAccess.DeleteToDo(td.Id))
                {
                    txtboxEditId.Text = "";
                    txtboxEditName.Text = "";
                    txtboxEditStatus.Text = "";
                    LoadToDoList();  // new line of code version 2
                }
            }
        }
    }
}

To improve the error trapping, I have inserted some try-catch blocks in the SQLiteDataAccess.cs file. Also, I have changed some of the methods to return a boolean instead of returning nothing (void). In this way the calling routine knows if the operation was successful or not.

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using ToDoList2.Models;
using Dapper;
using System.Data.SQLite;
using System;
using System.Windows;
namespace ToDoList2
{
    class SQLiteDataAccess
    {
        public static List<ToDo> LoadToDo()
        {
            try
            {
                using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
                {   // using Dapper
                    var output = conn.Query<ToDo>("select * from ToDos order by Id desc", new DynamicParameters());
                    return output.ToList();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "ToDo2 LoadToDo Error", MessageBoxButton.OK,MessageBoxImage.Warning);
                return new List<ToDo> { };
            }
        }
        public static bool SaveToDo(ToDo todo)
        {
            try
            {
                using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
                {
                    conn.Execute("insert into ToDos (Name, Status) values (@Name, @Status)", todo);
                    return true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "ToDo2 InsertToDo Error", MessageBoxButton.OK, MessageBoxImage.Warning);
                return false;
            }
        }
        public static bool UpdateToDo(ToDo todo)
        {
            try
            {
                using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
                {
                    conn.Execute("update ToDos set Name = @Name, Status = @Status where Id = @Id", todo);
                    return true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "ToDo2 UpdateToDo Error", MessageBoxButton.OK, MessageBoxImage.Warning);
                return false;
            }
        }
        public static bool DeleteToDo(int todo)
        {
            try
            {
                using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
                {
                    conn.Execute("delete from ToDos where id = @Id", new { Id = todo });
                    return true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "ToDo2 DeleteToDo Error", MessageBoxButton.OK, MessageBoxImage.Warning);
                return false;
            }
        }
        private static string LoadConnectionString(string id = "Default")
        {
            // go to App.config and return the string called "Default"
            // need to add Reference: ConfigurationManager and using System.Configuration
            return ConfigurationManager.ConnectionStrings[id].ConnectionString;
        }
    }
}