我們今天先將sqlite套件加入我們的專案中

在我們的Todo專案右鍵選取打開套件

搜尋sqlite

我們這邊使用的是SQLitePCLRaw

SQLitePCLRaw 是一個可移植類庫 (PCL)並可用於多平台，並對 SQLite 進行原始訪問

找到這些套件，並點擊安裝
sqlite-net-pcl
SQLitePCLRaw.bundle_green
SQLitePCLRaw.core
SQLitePCLRaw.provider.dynamic_cdecl
SQLitePCLRaw.provider.sqlite3

接著我們創建一個新資料夾model並添加TodoItemModel.cs

TodoItemModel.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Todo.Models
{
    public class TodoItemModel
    {
        public int TodoId { get; set; }
        public string Title { get; set; }
        public bool IsDone { get; set; }
    }
}

接著，我們創建services資料夾並且創建介面ITodoService與服務TodoService

ITodoService

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Todo.Models;

namespace Todo.Services
{
    public interface ITodoService
    {
        Task<List<TodoItemModel>> GetTodos();
        Task<int> AddTodo(TodoItemModel todoItemModel);
        Task<int> DeleteTodo(TodoItemModel todoItemModel);
        Task<int> UpdateTodo(TodoItemModel todoItemModel);
    }
}

TodoService

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Todo.Models;

namespace Todo.Services
{
    public class TodoService : ITodoService
    {
        public Task<int> AddTodo(TodoItemModel todoItemModel)
        {
            throw new NotImplementedException();
        }

        public Task<List<TodoItemModel>> GetTodos()
        {
            throw new NotImplementedException();
        }

        Task<int> ITodoService.DeleteTodo(TodoItemModel todoItemModel)
        {
            throw new NotImplementedException();
        }

        Task<int> ITodoService.UpdateTodo(TodoItemModel todoItemModel)
        {
            throw new NotImplementedException();
        }
    }
}

在service裡面加入創建db方法

namespace Todo.Services
{
    public class TodoService : ITodoService
    {
        private SQLiteAsyncConnection _connection;

        public TodoService()
        {
            _setDb();
        }

        private async void _setDb()
        {
            if (_connection == null) {
                string dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "db.sqlite");
                _connection = new SQLiteAsyncConnection(dbPath);
                await _connection.CreateTableAsync<TodoItemModel>();
            }
        }


        public Task<int> AddTodo() {
			...
		

接著來實作方法

		...

        public Task<int> AddTodo(TodoItemModel todoItemModel)
        {
            return _connection.InsertAsync(todoItemModel);
        }

        public async Task<List<TodoItemModel>> GetTodos()
        {
            var todos = await _connection.Table<TodoItemModel>().ToListAsync();
            return todos;
        }

        Task<int> ITodoService.DeleteTodo(TodoItemModel todoItemModel)
        {
            return _connection.DeleteAsync(todoItemModel);
        }

        Task<int> ITodoService.UpdateTodo(TodoItemModel todoItemModel)
        {
            return _connection.UpdateAsync(todoItemModel);
        }
		
		...

接著，我們回到MauiProgram.cs將我們的service加入:
builder.Services.AddSingleton<ITodoService, TodoService>();

MauiProgram.cs

using Todo.Services;

namespace Todo;

public static class MauiProgram
{
	public static MauiApp CreateMauiApp()
	{
		var builder = MauiApp.CreateBuilder();
		builder
			.UseMauiApp<App>()
			.ConfigureFonts(fonts =>
			{
				fonts.AddFont("OpenSans-Regular.ttf", "OpenSansRegular");
				fonts.AddFont("OpenSans-Semibold.ttf", "OpenSansSemibold");
			});

		// Add TodoItem Service
		builder.Services.AddSingleton<ITodoService, TodoService>();

		return builder.Build();
	}
}

今天先完成與我們的db做連接，並且實現功能，明天接著測試功能並將資料綁定到MAUI的view上

Todo\Services\ITodoService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Todo.Models;

namespace Todo.Services
{
    public interface ITodoService
    {
        Task<List<TodoItemModel>> GetTodos();
        Task<int> AddTodo(TodoItemModel todoItemModel);
        Task<int> DeleteTodo(TodoItemModel todoItemModel);
        Task<int> UpdateTodo(TodoItemModel todoItemModel);
    }
}

Todo\Services\TodoService.cs

using SQLite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Todo.Models;

namespace Todo.Services
{
    public class TodoService : ITodoService
    {
        private SQLiteAsyncConnection _connection;

        public TodoService()
        {
            _setDb();
        }

        private async void _setDb()
        {
            if (_connection == null) {
                Console.WriteLine("Creating New db");
                string dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "db.sqlite");
                _connection = new SQLiteAsyncConnection(dbPath);
                await _connection.CreateTableAsync<TodoItemModel>();
            }
            Console.WriteLine("Already setting db");
        }


        public Task<int> AddTodo(TodoItemModel todoItemModel)
        {
            return _connection.InsertAsync(todoItemModel);
        }

        public async Task<List<TodoItemModel>> GetTodos()
        {
            var todos = await _connection.Table<TodoItemModel>().ToListAsync();
            return todos;
        }

        Task<int> ITodoService.DeleteTodo(TodoItemModel todoItemModel)
        {
            return _connection.DeleteAsync(todoItemModel);
        }

        Task<int> ITodoService.UpdateTodo(TodoItemModel todoItemModel)
        {
            return _connection.UpdateAsync(todoItemModel);
        }
    }
}

Todo\MauiProgram.cs

using Todo.Services;

namespace Todo;

public static class MauiProgram
{
	public static MauiApp CreateMauiApp()
	{
		var builder = MauiApp.CreateBuilder();
		builder
			.UseMauiApp<App>()
			.ConfigureFonts(fonts =>
			{
				fonts.AddFont("OpenSans-Regular.ttf", "OpenSansRegular");
				fonts.AddFont("OpenSans-Semibold.ttf", "OpenSansSemibold");
			});

		// Add TodoItem Service
		builder.Services.AddSingleton<ITodoService, TodoService>();

		return builder.Build();
	}
}