Здравствуйте, уважаемые любители программировать, Сегодня я хочу затронуть тему использования баз данных под управлением SQLite, указать на имеющиеся проблемы этого драйвера, и рассказать, как эти проблемы можно решить в VB.NET.
Сразу оговорюсь, что существует множество способов использования SQLite в среде .NET, имеется множество обёрток разных производителей и разного года выпуска. Я остановился на том, который самым нормальным образом интегрируется в область имен System.Data. Его вы можете найти тут.
И так. Первым делом необходимо добавить ссылку на нужную нам библиотеку. Если вы скачали установочный пакет, то после инсталляции вы сможете найти ссылку в вкладке .NET окна добавления ссылок, если нет, то можно добавить его через Brows(посмотр). Но в последнем случае вы лишаете себя возможности проектировать базу данных в IDE студии. Альтернативным вариантом проектирование структуры базы данных на SQLitе может стать, не поверите, браузер FireFox и его дополнение SQLite Manager.
После подключения библиотеки уже можно использовать все возможности SQLite по работе с базой данных. Я не люблю использовать стандартные методы работы с базами в .NET через “визуальные компонеты”, DataSet-ы и прочие удобства, так как они, сугубо по моему мнению, не позволяют работать с данными гибко. Поэтому продемонстрирую подключение и работу с базой данных через программный код. Если Вы уже программировали подобным образом приложения с использованием баз данных, наверное, вы не найдёте для себя ничего нового, или, может быть, предложите даже более лучший вариант.
И так, подключение к базе данных выглядит следующим образом:
Dim _baseName As String = IO.Path.GetDirectoryName(Application.ExecutablePath) & "\MyDataBase.MyExtention"
Dim _Connection As SQLite.SQLiteConnection = New Data.SQLite.SQLiteConnection("Data Source = " + _baseName + ";Version=3;")
' Открытие соединения с базой данных
_Connection.Open()
Как Вы видите, ничего сложного. Необходимо сказать лишь о том, что файл самой базы должен лежать возле исполняемой программы, то есть в папке Debug и Release в процессе отладки или сборки. Название файла и его расширение MyDataBase.MyExtention полностью вымышленные и могут быть заменены на другие.
Перейдем к основным возможностям, выполнению запросов. Запросы по сути бывают двух типов: первые “исполняемые” или “командные”, словом те, которые что-то добавляют, что-то изменяют в самой базе, но не возвращают никаких данных; и второй тип, это обычный SELECT, который должен вернуть некоторое количество данных из базы.
Для того чтобы выполнить “командный” запрос достаточно использовать следующие строки:
Dim command As SQLite.SQLiteCommand = New SQLite.SQLiteCommand(SQL, _Connection) command.ExecuteNonQuery()
где SQL текст с запросом на добавление, обновление или замену. Запрос на выборку также будет выполнен, но результаты его вы не увидите.
Следующий код демонстрирует выполнение запросов на выборку:
Dim command As SQLite.SQLiteCommand = New SQLite.SQLiteCommand(SQL, _Connection) Dim reader As SQLite.SQLiteDataReader = command.ExecuteReader()
Использовать Reader не всегда удобно, хотя это может оказаться более быстрым способом, я же предпочитаю, в зависимости от представления выбранных данных использовать его тремя разными способами, помещая результат в боле удобные структуры и объекты.
Первый вид выборки, это выборка, которая возвращает всего одно значение. Такая выборка может быть, например, в запросах вида SELECT count(*) FROM…. Получить это единственное значение просто:
Dim res as integer = reader(0)
Второй тип выборки получает в качестве результата всего одну запись, такое тоже часто встречается. Это делаю так:
While reader.Read() Dim flast = reader.FieldCount - 1 Dim res As New Dictionary(Of String, Object) For i = 0 To flast Dim name As String = reader.GetName(i) Dim value As Object = reader(i) res.Add(name, value) Next End While
Ну и третий вид выборки, когда возвращается несколько записей и несколько строк:
Dim res As New ArrayList While (reader.Read()) Dim flast = reader.FieldCount - 1 Dim rec As New Dictionary(Of String, Object) For i = 0 To flast Dim name As String = reader.GetName(i) Dim value As Object = reader(i) rec.Add(name, value) Next res.Add(rec) End While
В любом случае в переменной res накапливаются результаты выборки которые можно использовать неоднократно в в разных частях программы. Для удобства, выполнение всех трех типов запросов выборки можно разместить их в одной функции.
Ну и не стоит забывать о завершении соединения:
_Connection.Close()
Если Вам нет времени осмысливать эту информацию, и вы считаете что копипаст, в данный момент, это лучший вариант, вот Вам ссылка на исходник класса реализующий эти возможности.
Теперь поговорим о проблемах, которые у Вас могли или могут возникнуть при использовании SQLite. Как вы понимаете слово Lite нам открытым текстом намекает на то, что это не полноценная СУБД, и ждать от нее маленьких чудес не стоит. К таким чудесам можно отнести функцию NOW() или LIKE с регистронезависимым использованием кириллицы в кодировке UTF8, поиск правильного формата строки для записи дат и прочее.
Начнем с менее проблемного формата строки для даты. Даты могут быть записаны только в таком формате “yyyy-MM-dd HH:mm:ss” и чтобы это сделать можно воспользоваться следующей строчкой для преобразования:
SomeDateTime.ToString("yyyy-MM-dd HH:mm:ss")
Теперь о более сложной проблеме: проблеме регистра для русского и прочих не английских языков при выборке данных с использованием оператора LIKE или функций lower и upper. К моему удивлению и великому сожалению разработчики SQLite по причинам с неясной мотивацией не реализовали регистронезависимое сравнение UTF строк (за исключением первых 128 символов). Но предвидя тот факт, что могут найтись более гениальные или усердные программисты, они предоставили функцию, которая позволяет расширять или переопределять функционал запросов. Таким образом можно написать свою функцию LIKE или только upper(lower) , которые будут работать как надо. Таким же образом можно разработать свою функцию NOW(), IF, CONCAT и прочее.
Я не буду подробно останавливаться на синтаксисе и его описании, просто приведу пример переопределения пользовательских функций, возможно вам их будет достаточно, возможно вы добавите что-то свое. По подобию это будет сделать очень просто:
Imports System.Data.SQLite
<SQLiteFunction(Name:="lower", Arguments:=1, FuncType:=FunctionType.Scalar)> _
Public Class LCase : Inherits SQLiteFunction
Public Overrides Function Invoke(ByVal args() As Object) As Object
If (args.Length = 0) OrElse (args(0) Is Nothing) Then Return Nothing
Return TryCast(args(0), String).ToLower
End Function
End Class
<SQLiteFunction(Name:="upper", Arguments:=1, FuncType:=FunctionType.Scalar)> _
Public Class UCase : Inherits SQLiteFunction
Public Overrides Function Invoke(ByVal args() As Object) As Object
If (args.Length = 0) OrElse (args(0) Is Nothing) Then Return Nothing
Return TryCast(args(0), String).ToUpper
End Function
End Class
<SQLiteFunction(Name:="CONCAT", Arguments:=3, FuncType:=FunctionType.Scalar)> _
Public Class CONCAT : Inherits SQLiteFunction
Public Overrides Function Invoke(ByVal args() As Object) As Object
If (args.Length = 0) OrElse (args(0) Is Nothing) Then Return Nothing
If (args.Length = 1) OrElse (args(1) Is Nothing) Then Return Nothing
If (args.Length = 2) OrElse (args(2) Is Nothing) Then Return Nothing
Return Strings.Format(CLng(args(0)), TryCast(args(1), String)) & " - " & TryCast(args(2), String)
End Function
End Class
<SQLiteFunction(Name:="NOW", Arguments:=0, FuncType:=FunctionType.Scalar)> _
Public Class DateNow : Inherits SQLiteFunction
Public Overrides Function Invoke(ByVal args() As Object) As Object
Return Now.ToString("yyyy-MM-dd HH:mm:ss")
End Function
End Class
