WPF SQLite ToDo CRUD Data Access Class

 in WPF  tagged WPF SQLite ToDo CRUD by
This entry is part 5 of 6 in the series WPF SQLite ToDo CRUD

WPF SQLite ToDo CRUD

This post lists the data access class that the code-behind uses to talk to the database. In this class we have our SQL strings and the Dapper methods that use those strings.

We’ve got a few static methods here that we can use to work with the SQLite database with the help of the micro-ORM Dapper. Here we get back to our SQL code and our CRUD database manipulations. You can see in the list of methods below that the first four represent Read, Create, Update and Delete (of CRUD). The last one gets the connection string. The first four are employing the C# using keyword so that the memory will automatically be released during garbage collection.

  • public static List LoadToDo()
  • public static void SaveToDo(ToDo todo)
  • public static void UpdateToDo(ToDo todo)
  • public static void DeleteToDo(int todo)
  • private static string LoadConnectionString(string id = “Default”)

Here is the SQLiteDataAccess.cs file listing.

view source
print?
1using Dapper;
2using System;
3using System.Collections.Generic;
4using System.Configuration;
5using System.Data;
6using System.Linq;
7using System.Data.SQLite;
8namespace ToDoList
9{
10    class SQLiteDataAccess
11    {
12        public static List<ToDo> LoadToDo()
13        {
14            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
15            {   // using Dapper
16                var output = conn.Query<ToDo>("select * from ToDos order by Id desc", new DynamicParameters());
17                return output.ToList();
18            }
19        }
20        public static void SaveToDo(ToDo todo)
21        {
22            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
23            {
24                conn.Execute("insert into ToDos (Name, Status) values (@Name, @Status)", todo);
25            }
26        }
27        public static void UpdateToDo(ToDo todo)
28        {
29            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
30            {
31                conn.Execute("update ToDos set Name = @Name, Status = @Status where Id = @Id", todo);
32            }
33        }
34        public static void DeleteToDo(int todo)
35        {
36            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
37            {
38                conn.Execute("delete from ToDos where id = @Id", new { Id = todo } );
39            }
40        }
41        private static string LoadConnectionString(string id = "Default")
42        {
43            // go to App.config and return the string called "Default"
44            // need to add Reference: ConfigurationManager and using System.Configuration
45            return ConfigurationManager.ConnectionStrings[id].ConnectionString;
46        }
47    }
48}
Series Navigation<< WPF SQLite ToDo CRUD CodeWPF ToDo Version 2 >>