[.NET MAUI] SQLITE 사용하기

MAUI 에서 SQLite 를 사용하는 방법입니다.

1. Nuget 설치

sqlite-net-pcl
SQLitePCLRaw.bundle_green
SQLitePCLRaw.core
SQLitePCLRaw.provider.dynamic_cdecl
SQLitePCLRaw.provider.sqlite3

2. Model 정의 (StudentModel.cs)

using SQLite;

namespace Maui.SqliteTest.Models
{
    public class StudentModel
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Age { get; set; }
    }
}

3. Helper 정의 (StudentHelper.cs)

using Maui.SqliteTest.Models;

using SQLite;

namespace Maui.SqliteTest.Helpers
{
    public class StudentHelper
    {
        private SQLiteAsyncConnection dbConnection;

        private async Task ConnectionDB()
        {
            if (this.dbConnection == null)
            {
                string dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "Student.db3");
                this.dbConnection = new SQLiteAsyncConnection(dbPath);

                // Create Table
                await this.dbConnection.CreateTableAsync<StudentModel>();
            }
        }

        public async Task<int> AddStudent(StudentModel studentModel)
        {
            await ConnectionDB();
            return await this.dbConnection.InsertAsync(studentModel);
        }

        public async Task<int> DeleteStudent(StudentModel studentModel)
        {
            await ConnectionDB();
            return await this.dbConnection.DeleteAsync(studentModel);
        }

        public async Task<List<StudentModel>> GetStudentList()
        {
            await ConnectionDB();
            var studentList = await dbConnection.Table<StudentModel>().ToListAsync();
            return studentList;
        }

        public async Task<int> UpdateStudent(StudentModel studentModel)
        {
            await ConnectionDB();
            return await this.dbConnection.UpdateAsync(studentModel);
        }
    }
}

Helper 를 통해 데이터 CRUD 처리가 됩니다.

4. View 구성 (MainPage.xaml / MainPage.xaml.cs)

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage
    x:Class="Maui.SqliteTest.MainPage"
    xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
    xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml">
    
    <ContentPage.Resources>
        <Style TargetType="Grid">
            <Setter Property="VisualStateManager.VisualStateGroups">
                <VisualStateGroupList>
                    <VisualStateGroup x:Name="CommonStates">
                        <VisualState x:Name="Normal" />
                        <VisualState x:Name="Selected">
                            <VisualState.Setters>
                                <Setter Property="BackgroundColor" Value="Gray" />
                            </VisualState.Setters>
                        </VisualState>
                    </VisualStateGroup>
                </VisualStateGroupList>
            </Setter>
        </Style>
    </ContentPage.Resources>
    
    <Grid
        Padding="15,15"
        RowDefinitions=" 50,50,50,*,50"
        VerticalOptions="FillAndExpand">

        <Grid Grid.Row="0" ColumnDefinitions="100,*">
            <Label
                Grid.Column="0"
                Text="Name"
                VerticalOptions="Center"
                VerticalTextAlignment="Center" />
            <Entry
                Grid.Column="1"
                BackgroundColor="LightGray"
                HorizontalTextAlignment="Center"
                Text="{Binding Name, Mode=TwoWay}"
                VerticalOptions="Center"
                VerticalTextAlignment="Center" />
        </Grid>

        <Grid Grid.Row="1" ColumnDefinitions="100,*">
            <Label
                Grid.Column="0"
                Text="Age"
                VerticalOptions="Center"
                VerticalTextAlignment="Center" />
            <Entry
                Grid.Column="1"
                BackgroundColor="LightGray"
                HorizontalTextAlignment="Center"
                Keyboard="Numeric"
                Text="{Binding Age, Mode=TwoWay}"
                VerticalOptions="Center"
                VerticalTextAlignment="Center" />
        </Grid>

        <HorizontalStackLayout Grid.Row="2" HorizontalOptions="End">
            <Button
                Grid.Row="2"
                Margin="0,5"
                Command="{Binding SaveCommand}"
                CornerRadius="0"
                WidthRequest="100">
                <Button.Triggers>
                    <DataTrigger
                        Binding="{Binding IsUpdate}"
                        TargetType="Button"
                        Value="True">
                        <Setter Property="Text" Value="Update" />
                    </DataTrigger>
                    <DataTrigger
                        Binding="{Binding IsUpdate}"
                        TargetType="Button"
                        Value="False">
                        <Setter Property="Text" Value="Add" />
                    </DataTrigger>
                </Button.Triggers>
            </Button>
        </HorizontalStackLayout>

        <CollectionView
            x:Name="myCollectionView"
            Grid.Row="3"
            ItemsSource="{Binding Students}"
            SelectedItem="{Binding SelectStudent}"
            SelectionChangedCommand="{Binding ChangeSelectStudentCommand}"
            SelectionChangedCommandParameter="{Binding SelectedItem, Source={x:Reference myCollectionView}}"
            SelectionMode="Single">

            <CollectionView.ItemTemplate>
                <DataTemplate>
                    <Border StrokeThickness="1">
                        <Grid
                            Margin="5"
                            Padding="10"
                            ColumnDefinitions="100,*">
                            <Label
                                Grid.Column="0"
                                FontAttributes="Bold"
                                FontSize="18"
                                Text="{Binding Name}"
                                VerticalOptions="Center"
                                VerticalTextAlignment="Center" />
                            <Label
                                Grid.Column="1"
                                FontAttributes="Bold"
                                FontSize="18"
                                Text="{Binding Age}"
                                VerticalOptions="Center"
                                VerticalTextAlignment="Center" />
                        </Grid>
                    </Border>
                </DataTemplate>
            </CollectionView.ItemTemplate>
        </CollectionView>

        <HorizontalStackLayout
            Grid.Row="4"
            HorizontalOptions="End"
            VerticalOptions="End">
            <Grid ColumnDefinitions="*,*">
                <Button
                    Grid.Column="0"
                    Margin="0,5"
                    Command="{Binding DeleteCommand}"
                    CornerRadius="0"
                    Text="All Delete"
                    WidthRequest="100" />
            </Grid>
        </HorizontalStackLayout>

    </Grid>
</ContentPage>

using Maui.SqliteTest.ViewModels;

namespace Maui.SqliteTest
{
    public partial class MainPage : ContentPage
    {
        public MainPage()
        {
            InitializeComponent();

            this.BindingContext = new MainViewModel();
        }
    }
}

5. ViewModel 구성 (MainViewModel.cs)

using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Runtime.CompilerServices;
using System.Windows.Input;

using Maui.SqliteTest.Helpers;
using Maui.SqliteTest.Models;

namespace Maui.SqliteTest.ViewModels
{
    public class MainViewModel : INotifyPropertyChanged
    {
        #region INotifyPropertyChanged
        public event PropertyChangedEventHandler PropertyChanged;
        protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
        }
        #endregion

        #region Fields
        StudentHelper studentHelper = new StudentHelper();
        #endregion

        #region Properties
        private bool isUpdate = false;

        public bool IsUpdate
        {
            get
            {
                return this.isUpdate;
            }
            set
            {
                this.isUpdate = value;
                OnPropertyChanged();
            }
        }

        private string name;

        public string Name
        {
            get
            {
                return this.name;
            }
            set
            {
                this.name = value;
                OnPropertyChanged();
            }
        }

        private string age;

        public string Age
        {
            get
            {
                return this.age;
            }
            set
            {
                this.age = value;
                OnPropertyChanged();
            }
        }

        private ObservableCollection<StudentModel> students;

        public ObservableCollection<StudentModel> Students
        {
            get
            {
                if (this.students == null)
                {
                    this.students = new ObservableCollection<StudentModel>();
                }

                return this.students;
            }
            set
            {
                this.students = value;
                OnPropertyChanged();
            }
        }

        private StudentModel selectStudent;

        public StudentModel SelectStudent
        {
            get
            {
                return this.selectStudent;
            }
            set
            {
                this.selectStudent = value;
                OnPropertyChanged();
            }
        }
        #endregion

        #region Commands
        public ICommand ChangeSelectStudentCommand => new Command<StudentModel>(p => OnChangeSelectStudentCommand(p));
        public ICommand SaveCommand => new Command(p => OnSaveCommand());
        public ICommand DeleteCommand => new Command(p => OnDeleteCommand());
        #endregion

        // Constructor
        #region MainViewModel
        public MainViewModel()
        {
            GetStudentList();
        }
        #endregion

        // Command Methods
        #region OnSaveCommand
        private async void OnSaveCommand()
        {
            if (this.IsUpdate)
            {
                this.SelectStudent.Name = this.Name;
                this.SelectStudent.Age = this.Age;

                int i = await studentHelper.UpdateStudent(this.SelectStudent);

                await Application.Current.MainPage.DisplayAlert("Update", "Update Complete", "ok");

                this.IsUpdate = false;
            }
            else
            {
                StudentModel newStudent = new StudentModel();
                newStudent.Name = this.name;
                newStudent.Age = this.age;

                int i = await studentHelper.AddStudent(newStudent);

                await Application.Current.MainPage.DisplayAlert("Save", "Save Complete", "ok");
            }

            this.Name = "";
            this.Age = "";

            GetStudentList();
        }
        #endregion
        #region OnDeleteCommand
        private async void OnDeleteCommand()
        {
            foreach (var student in this.Students)
            {
                await studentHelper.DeleteStudent(student);
            }

            this.IsUpdate = false;

            this.Name = "";
            this.Age = "";
        }
        #endregion
        #region OnChangeSelectStudentCommand
        private void OnChangeSelectStudentCommand(StudentModel studentModel)
        {
            this.IsUpdate = true;

            if (this.SelectStudent != null)
            {
                this.Name = this.SelectStudent.Name;
                this.Age = this.SelectStudent.Age;
            }
        }
        #endregion

        // Methods
        #region GetStudentList
        private async void GetStudentList()
        {
            var students = await studentHelper.GetStudentList();

            if (students != null)
            {
                this.Students.Clear();

                foreach (var student in students)
                {
                    this.Students.Add(student);
                }
            }
        }
        #endregion
    }
}

실행결과

[Source]

https://github.com/kei-soft/KJunBlog/tree/master/Maui.SqliteTest