SQLite в WPF
Работа с SQLite
SQLite является одной из наиболее используемых систем управления базами данных. Главным преимуществом SQLite является то, что для базы данных не нужно сервера. База данных представляет собой обычный локальный файл, который мы можем перемещать вместе с главным файлом приложения. Кроме того, для запросов к базе данных мы можем использовать стандартные выражения языка SQL, которые равным образом с некоторыми изменениями могут применяться и в других СУБД как Oracle, MS SQL Server, MySQL, Postgres и т.д.
Еще одним преимуществом является широкое распространение SQLite - область применения охватывает множество платфрм и технологий: WPF, Windows Forms, UWP, Xamarin, Android, iOS и т.д. И если мы, скажем, захотим создать копию приложения для ОС Android, то базу данных мы можем перенести в новое приложение такой, как она определена для приложения на WPF.
Итак, создадим новый проект WPF, который назовем SQLiteApp. В первую очередь нам надо добавить функциональность SQLite в проект. Для этого мы можем загрузить нужный нам пакет со страницы https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki , и инсталлятор установить все необходимые библиотеки в глобальный кэш сборок (GAC), откуда мы можем выбирать их для проекта.
Либо мы можем добавить SQLite в проект через пакетный менеджер Nuget. Итак, выберем этот способ, перейдем к Nuget, нажав в проекте правой кнопкой на узел References и выбрав в открывшемся контекстном меню пункт Manage NuGet Packages...:
В окне менеджера Nuget введем в окно поиска "SQLite", и менеджер отобразит нам ряд результатов. Из них нам надо установить пакет под названием System.Data.SqlClient:
Данный пакет зависит от трех других пакетов, которые также будут установлены:
- System.Data.SqlClient.Core
- System.Data.SqlClient.Linq
- System.Data.SqlClient.EF6
Вместе с последним пакетом также будет добавлен Entity Framework. Таким образом, мы сможем работать с базой данных SQLite через ORM-инструмент Entity Framework.
В отличие от работы с MS SQL Server по отношению к SQLite EF 6 не поддерживает автоматическое создание базы данных и ее таблиц через Code First в соответствии со структурой моделей приложения. И в этом случае нам самим надо создавать баз данных и ее таблицы.
Для работы с SQLite установим специальный инструмент - DB Browser for SQLite
Откроем DB Browser for SQLite и для создания новой базы данных нажмем на кнопку New Database. В оттрывшемся окне выберем место расположения файла базы данных и укажем для нее имя, например, mobiles.
Далее нам будет предложено добавить в базу данных таблицы. Определим для таблицы имя "Phones" и добавим четыре столбца Id, Title, Company, Price, как на скриншоте:
Стоит обратить внимание, что столбец Id определен здесь как первичный ключ.
После добавления таблицы мы сможем добавить вручную через DB Browser for SQLite какие-либо данные.
И далее добавим созданную нами базу данных в наш проект. После добавления для файла базы данных установим опцию "Copy if newer", чтобы файл копировался при компиляции в каталог приложения:
Для поддержки SQLite необходимо изменить конфигурацию проекта, которая располагается в файле App.config. После добавления пакетов данный файл также автоматически изменяется. Однако все же нам необходимо внести в него некоторые изменения. Так, изменим его следующим образом:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 |
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=.\mobiles.db" providerName="System.Data.SQLite" />
</connectionStrings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6" />
</startup>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
<parameter value="v11.0" />
</parameters>
</defaultConnectionFactory>
<providers>
<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6"/>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
<add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
</DbProviderFactories>
</system.data>
</configuration> |
Прежде всего здесь определена строка подключения:
|
1
2
3 |
<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=.\mobiles.db" providerName="System.Data.SQLite" />
</connectionStrings> |
Так как созданный нами файл базы данных называется mobiles (к которому DB Browser for SQLite по умолчанию добавляет расширение db), то в качестве источника данных здесь указан именно "mobiles.db".
Другой важный момент - установка в узле <providers> провайдера, который будет использоваться для подключения:
|
1 |
<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6"/> |
Теперь определим в проекте класс, объекты которого будут храниться в базе данных:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49 |
using System.ComponentModel;
using System.Runtime.CompilerServices;
namespace SQLiteApp
{
public class Phone : INotifyPropertyChanged
{
private string title;
private string company;
private int price;
public int Id { get; set; }
public string Title
{
get { return title; }
set
{
title = value;
OnPropertyChanged("Title");
}
}
public string Company
{
get { return company; }
set
{
company = value;
OnPropertyChanged("Company");
}
}
public int Price
{
get { return price; }
set
{
price = value;
OnPropertyChanged("Price");
}
}
public event PropertyChangedEventHandler PropertyChanged;
public void OnPropertyChanged([CallerMemberName]string prop = "")
{
if (PropertyChanged != null)
PropertyChanged(this, new PropertyChangedEventArgs(prop));
}
}
} |
При ручном определении модели надо учитывать, что эта модель должна соответствовать таблице из БД. Так, в бд мы создали таблицу Phones, в которой есть целочисленные столбцы Id и Price, соответственно в классе Phone определены свойства Id и Price, представляющие целые числа. То же самое со строковыми столбцами Title и Company, которые по имени и типу данных соответствуют свойствам Title и Company в классе Phone.
И еще один важный момент: Entity Framework по умолчанию применяет некоторые условности при наименовании моделей и таблиц в БД. Так, название таблицы должно представлять название модели во множественном числе в соответствии с правилами английского языка. Так, у нас таблица называется Phones, а класс Phone. То есть условности соблюдены.
Для взаимодействия с базой данных нам нужен еще один комопнент - контекст данных. Добавим в проект новый класс, который назовем ApplicationContext:
|
1
2
3
4
5
6
7
8
9
10
11
12 |
using System.Data.Entity;
namespace SQLiteApp
{
public class ApplicationContext : DbContext
{
public ApplicationContext():base("DefaultConnection")
{
}
public DbSet<Phone> Phones { get; set; }
}
} |
Класс контекста должен наследоваться от DbContext. В конструкторе класса в конструктор базового класса передаем название подключения. В данном случае название должно совпадать с названием подключения из App.config.
И для взаимодействия с таблицей Phones здесь также определено одноименное свойство Phones.
Теперь все готово для работы с базой данных. Стандартный набор операций по работе с БД включает получение объектов, добавление, изменение и удаление. Для получения и просмотра списка объектов из бд мы будем использовать главное окно. А для добавления и изменения создадим новое окно.
Итак, добавим в проект новое окно, которое назовем PhoneWindow.xaml. В итоге общая конфигурация проекта у нас будет выглядеть следующим образом:
В коде xaml у страницы PhoneWindow.xaml определим следующее содержимое:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41 |
<Window x:Class="SQLiteApp.PhoneWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation "
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml "
xmlns:d="http://schemas.microsoft.com/expression/blend/2008 "
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006 "
xmlns:local="clr-namespace:SQLiteApp"
mc:Ignorable="d"
Title="PhoneWindow" Height="200" Width="300">
<Window.Resources>
<Style TargetType="TextBlock">
<Setter Property="Margin" Value="8" />
</Style>
<Style TargetType="TextBox">
<Setter Property="Margin" Value="8" />
</Style>
</Window.Resources>
<Grid>
<Grid.RowDefinitions>
<RowDefinition />
<RowDefinition />
<RowDefinition />
<RowDefinition />
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="100" />
<ColumnDefinition />
</Grid.ColumnDefinitions>
<TextBlock Text="Модель" />
<TextBlock Text="Производитель" Grid.Row="1" />
<TextBlock Text="Цена" Grid.Row="2" />
<TextBox Text="{Binding Title}" Grid.Column="1" />
<TextBox Text="{Binding Company}" Grid.Column="1" Grid.Row="1" />
<TextBox Text="{Binding Price}" Grid.Column="1" Grid.Row="2" />
<StackPanel HorizontalAlignment="Center" Orientation="Horizontal" Grid.Row="3" Grid.Column="1">
<Button IsDefault="True" Click="Accept_Click" MinWidth="60" Margin="5">OK</Button>
<Button IsCancel="True" MinWidth="60" Margin="5">Отмена</Button>
</StackPanel>
</Grid>
</Window> |
Здесь определены три поля ввода для каждого свойства модели Phone и две кнопки для сохранения и отмены.
В коде PhoneWindow.xaml.cs определим контекст для этой страницы:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 |
using System.Windows;
namespace SQLiteApp
{
public partial class PhoneWindow : Window
{
public Phone Phone { get; private set; }
public PhoneWindow(Phone p)
{
InitializeComponent();
Phone= p;
this.DataContext = Phone;
}
private void Accept_Click(object sender, RoutedEventArgs e)
{
this.DialogResult = true;
}
}
} |
Данное окно будет диалоговым. Через конструктор оно будет получать объект Phone, который устанавливается в качестве контекста данных.
В коде xaml у главного окна MainWindow определим вывод списка телефонов и набор кнопок для управления этим списком:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31 |
<Window x:Class="SQLiteApp.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation "
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml "
xmlns:d="http://schemas.microsoft.com/expression/blend/2008 "
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006 "
xmlns:local="clr-namespace:SQLiteApp"
mc:Ignorable="d"
Title="MainWindow" Height="300" Width="425">
<Grid>
<Grid.RowDefinitions>
<RowDefinition />
<RowDefinition Height="50" />
</Grid.RowDefinitions>
<ListBox x:Name="phonesList" ItemsSource="{Binding}">
<ListBox.ItemTemplate>
<DataTemplate>
<StackPanel>
<TextBlock Text="{Binding Title}" FontSize="16" />
<TextBlock Text="{Binding Company}" FontSize="12" />
<TextBlock Text="{Binding Price}" FontSize="13" />
</StackPanel>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
<StackPanel Grid.Row="1" Orientation="Horizontal" HorizontalAlignment="Center">
<Button Content="Добавить" Margin="10" Click="Add_Click" />
<Button Content="Изменить" Margin="10" Click="Edit_Click" />
<Button Content="Удалить" Margin="10" Click="Delete_Click" />
</StackPanel>
</Grid>
</Window> |
В коде C# у этого окна пропишем обработчики кнопок, через которые будем взаимодействовать с базой данных SQLite:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71 |
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Windows;
namespace SQLiteApp
{
public partial class MainWindow : Window
{
ApplicationContext db;
public MainWindow()
{
InitializeComponent();
db = new ApplicationContext();
db.Phones.Load();
this.DataContext = db.Phones.Local.ToBindingList();
}
// добавление
private void Add_Click(object sender, RoutedEventArgs e)
{
PhoneWindow phoneWindow = new PhoneWindow(new Phone());
if (phoneWindow.ShowDialog() == true)
{
Phone phone = phoneWindow.Phone;
db.Phones.Add(phone);
db.SaveChanges();
}
}
// редактирование
private void Edit_Click(object sender, RoutedEventArgs e)
{
// если ни одного объекта не выделено, выходим
if (phonesList.SelectedItem == null) return;
// получаем выделенный объект
Phone phone = phonesList.SelectedItem as Phone;
PhoneWindow phoneWindow = new PhoneWindow(new Phone
{
Id = phone.Id,
Company = phone.Company,
Price = phone.Price,
Title = phone.Title
});
if (phoneWindow.ShowDialog() == true)
{
// получаем измененный объект
phone = db.Phones.Find(phoneWindow.Phone.Id);
if(phone!=null)
{
phone.Company = phoneWindow.Phone.Company;
phone.Title = phoneWindow.Phone.Title;
phone.Price = phoneWindow.Phone.Price;
db.Entry(phone).State = EntityState.Modified;
db.SaveChanges();
}
}
}
// удаление
private void Delete_Click(object sender, RoutedEventArgs e)
{
// если ни одного объекта не выделено, выходим
if (phonesList.SelectedItem == null) return;
// получаем выделенный объект
Phone phone = phonesList.SelectedItem as Phone;
db.Phones.Remove(phone);
db.SaveChanges();
}
}
} |
Выражение db.Phones.Load() загружает данные из таблицы Phones в локальный кэш контекста данных. И затем список загруженных объектов устанавливается в качестве контекста данных:
|
1 |
this.DataContext = db.Phones.Local.ToBindingList(); |
Для добавления вызывается метод Add:
|
1
2 |
db.Phones.Add(phone);
db.SaveChanges(); |
Для удаления - метод Remove:
|
1
2 |
db.Phones.Remove(phone);
db.SaveChanges(); |
При изменении мы передаем в PhoneWindow копию выбранного объекта. Если мы передавали бы сам выделенный объект, то все изменения на форме автоматически синхронизировались со списком, и не было бы смысла в кнопке отмены.
После получения измененного объекта мы находим его в базе данных и устанавливаем у него состояние Modified, после чего сохраняем все изменения:
|
1
2
3
4
5
6
7
8
9 |
phone = db.Phones.Find(phoneWindow.Phone.Id);
if(phone!=null)
{
phone.Company = phoneWindow.Phone.Company;
phone.Title = phoneWindow.Phone.Title;
phone.Price = phoneWindow.Phone.Price;
db.Entry(phone).State = EntityState.Modified;
db.SaveChanges();
} |
Запустим приложение. И добавим какой нибудь объект:
И после добавления объект отобразится в списке:
