Один из читателей моего сайта попросил в комментариях к статье «Как сохранять изображения в базу данных MS SQL и извлекать изображения из базы данных» написать статью по работе с SQLite. Отдохнув в отпуске и написав несколько статей по другой тематике решил написать статью и по работе с SQLite в C#.
Рассмотрим основные методы работы с базой данных SQLite: SELECT, CREATE, INSERT, UPDATE, DELETE. Чтобы материал не был обыденным мы рассмотрим добавление изображений и файлов в базу данных SQLite, а также их чтение из базы данных.
Сразу хочу сказать, что синтаксис похож на запросы MS SQL, а также подключение к базе данных происходит аналогично. Поэтому если вы работаете в C# с базами MS SQL, то вы прямо сейчас уже сможете самостоятельно работать с SQLite немного изменив свои запросы в коде.
Для работы с SQLite нам необходимо к проекту подключить пять NuGet пакетов, которые показаны на картинке:

Однако достаточно подключить один пакет, который выделен на картинке, и остальные пакеты подтянутся и установятся автоматически.
Прописываем для работы с SQLite:
using System.Data.SQLite;
Дополнительно к проекту подключаем:
using System; using System.IO; using System.Drawing; using System.Diagnostics; using System.Collections.Generic;
Далее по тексту будет код без методов, который вы прописываете у себя в нужных вам методах.
Создание базы данных SQLite
if (!File.Exists(@"C:\TestDB.db")) { SQLiteConnection.CreateFile(@"C:\TestDB.db); }
Создание таблицы в базе данных SQLite
Создадим таблицу, с которой будем в дальнейшем работать.
using (SQLiteConnection Connect = new SQLiteConnection(@"Data Source=C:\TestDB.db; Version=3;")) { string commandText = "CREATE TABLE IF NOT EXISTS [dbTableName] ( [id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [image] BLOB, [image_format] VARCHAR(10), " + "[image_name] NVARCHAR(128), [file] BINARY, [file_format] VARCHAR(10), [file_name] NVARCHAR(128))"; SQLiteCommand Command = new SQLiteCommand(commandText, Connect); Connect.Open(); Command.ExecuteNonQuery(); Connect.Close(); }
Добавление изображения в базу данных SQLite
string imgPath = @"C:\image-01.png"; FileInfo _imgInfo = new FileInfo(imgPath); long _numBytes = _imgInfo.Length; FileStream _fileStream = new FileStream(imgPath, FileMode.Open, FileAccess.Read); BinaryReader _binReader = new BinaryReader(_fileStream); byte[] _imageBytes = _binReader.ReadBytes((int)_numBytes); string imgFormat = Path.GetExtension(imgPath).Replace(".", "").ToLower(); string imgName = Path.GetFileName(imgPath).Replace(Path.GetExtension(imgPath), ""); using (SQLiteConnection Connect = new SQLiteConnection(@"Data Source=C:\TestDB.db; Version=3;")) { string commandText = "INSERT INTO [dbTableName] ([image], [image_format], [image_name]) VALUES(@image, @format, @name)"; SQLiteCommand Command = new SQLiteCommand(commandText, Connect); Command.Parameters.AddWithValue("@image", _imageBytes); Command.Parameters.AddWithValue("@format", imgFormat); Command.Parameters.AddWithValue("@name", imgName); Connect.Open(); Command.ExecuteNonQuery(); Connect.Close(); }
Извлечение изображения из базы данных SQLite
List<byte[]> _imageList = new List<byte[]>(); List<string> _imgFormatList = new List<string>(); using (SQLiteConnection Connect = new SQLiteConnection(@"Data Source=C:\TestDB.db; Version=3;")) { Connect.Open(); SQLiteCommand Command = new SQLiteCommand { Connection = Connect, CommandText = @"SELECT * FROM [dbTableName] WHERE [image_format] NOT NULL" }; SQLiteDataReader sqlReader = Command.ExecuteReader(); byte[] _dbImageByte = null; string _dbImageFormat = null; while (sqlReader.Read()) { _dbImageByte = (byte[])sqlReader["image"]; _imageList.Add(_dbImageByte); _dbImageFormat = sqlReader["image_format"].ToString().TrimStart().TrimEnd(); _imgFormatList.Add(_dbImageFormat); } Connect.Close(); } if (_imageList.Count == 0) { return; } byte[] _imageBytes = _imageList[0]; MemoryStream ms = new MemoryStream(_imageBytes); Image _newImg = Image.FromStream(ms); string _imgFormat = _imgFormatList[0]; string _newImageSaved = @"C:\image-02." + _imgFormat; if (_imgFormat == "jpeg" || _imgFormat == "jpg") _newImg.Save(_newImageSaved, System.Drawing.Imaging.ImageFormat.Jpeg); else if (_imgFormat == "png") _newImg.Save(_newImageSaved, System.Drawing.Imaging.ImageFormat.Png); else if (_imgFormat == "bmp") _newImg.Save(_newImageSaved, System.Drawing.Imaging.ImageFormat.Bmp); else if (_imgFormat == "gif") _newImg.Save(_newImageSaved, System.Drawing.Imaging.ImageFormat.Gif); else if (_imgFormat == "ico") _newImg.Save(_newImageSaved, System.Drawing.Imaging.ImageFormat.Icon); else if (_imgFormat == "tiff" || _imgFormat == "tif") _newImg.Save(_newImageSaved, System.Drawing.Imaging.ImageFormat.Tiff);Добавление любого файла в базу данных SQLite
byte[] _fileBytes = null; string filePath = @"C:\music-01.mp3"; FileInfo _fileInfo = new FileInfo(filePath); long _numBytes = _fileInfo.Length; FileStream _fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read); BinaryReader _binReader = new BinaryReader(_fileStream); _fileBytes = _binReader.ReadBytes((int)_numBytes); string fileFormat = Path.GetExtension(filePath).Replace(".", "").ToLower(); string fileName = Path.GetFileName(filePath).Replace(Path.GetExtension(filePath), ""); using (SQLiteConnection Connect = new SQLiteConnection(@"Data Source=C:\TestDB.db; Version=3;")) { string commandText = "INSERT INTO [dbTableName] ([file], [file_format], [file_name]) VALUES(@file, @format, @name)"; SQLiteCommand Command = new SQLiteCommand(commandText, Connect); Command.Parameters.AddWithValue("@file", _fileBytes); Command.Parameters.AddWithValue("@format", fileFormat); Command.Parameters.AddWithValue("@name", fileName); Connect.Open(); Command.ExecuteNonQuery(); Connect.Close(); }Извлечение файла из базы данных SQLite
Комментариев в коде в начале будет минимум, так как здесь код аналогичный коду извлечения изображения из базы данных, а вот вторая часть кода отличается.
List<byte[]> _fileList = new List<byte[]>(); List<string> _fileFormatList = new List<string>(); using (SQLiteConnection Connect = new SQLiteConnection(@"Data Source=C:\TestDB.db; Version=3;")) { Connect.Open(); SQLiteCommand Command = new SQLiteCommand { Connection = Connect, CommandText = @"SELECT * FROM [dbTableName] WHERE [file_format] NOT NULL" }; SQLiteDataReader sqlReader = Command.ExecuteReader(); byte[] _dbFileByte = null; string _dbFileFormat = null; while (sqlReader.Read()) { _dbFileByte = (byte[])sqlReader["file"]; _fileList.Add(_dbFileByte); _dbFileFormat = sqlReader["file_format"].ToString().TrimStart().TrimEnd(); _fileFormatList.Add(_dbFileFormat); } Connect.Close(); } if (_fileList.Count == 0) { return; } byte[] _fileBytes = _fileList[0]; string _fileFormat = _fileFormatList[0]; string _newFileSaved = @"C:\music-01." + _fileFormat; FileStream fileStream = new FileStream(_newFileSaved, FileMode.Create, FileAccess.ReadWrite); BinaryWriter binWriter = new BinaryWriter(fileStream); binWriter.Write(_fileBytes); binWriter.Close();С помощью кода добавления и извлечения файла можно писать и считывать из базы данных любые файлы включая изображения.
Обновление записи в базе данных SQLite
using (SQLiteConnection Connect = new SQLiteConnection(@"Data Source=C:\TestDB.db; Version=3;")) { string commandText = "UPDATE [dbTableName] SET [file_name] = @value WHERE [id] = @id"; SQLiteCommand Command = new SQLiteCommand(commandText, Connect); Command.Parameters.AddWithValue("@value", "Новое имя файла"); Command.Parameters.AddWithValue("@id", 1); Connect.Open(); Int32 _rowsUpdate = Command.ExecuteNonQuery(); MessageBox.Show("Обновлено строк: " + _rowsUpdate); Connect.Close(); }Удаление записи в базе данных SQLite
using (SQLiteConnection Connect = new SQLiteConnection(@"Data Source=C:\TestDB.db; Version=3;")) { string commandText = "DELETE FROM [dbTableName] WHERE [id] = @id LIMIT 1"; SQLiteCommand Command = new SQLiteCommand(commandText, Connect); Command.Parameters.AddWithValue("@id", textBoxDeleteData.Text); Connect.Open(); Int32 _rowsUpdate = Command.ExecuteNonQuery(); MessageBox.Show("Удалено строк: " + _rowsUpdate); Connect.Close(); }Заключение
Как видим есть похожий код (DELETE и UPDATE), а есть отличающийся (SELECT сравните с другими).
На этом всё. На основании приведённых примеров вы сможете освоить работу с SQLite в C#.
Прикладываю проект с программой, которая может производить все описанные выше операции.
Ну это не совсем то, что надо и кстати если написать Version=’3′ работать не будет, хотя соединится. Надо писать providerName=»System.Data.SQLite» тогда будет…. к сожалению с Entity этот метод не работает, но как-то должен. Пока не знаю как. Как раз это и ищу.
Большое спасибо за проделанную работу! Все очень доступно объяснили.
Здравствуйте.
Возникла проблема, в программе кнопки аля «создать базу данных» не работают
Надо выбрать Изображение и Файл. Или же изменить поведение программы, чтобы не блокировались кнопки, если не выбраны Изображение и Файл, для этого измените поведение в методе «timer1_Tick».
Пример классный. Но не совсем понятно, почему для изображений использован тип BLOB, а для файлов BINARY. В чем разница?
А еще непонятно, зачем ставить пакеты EntityFramework, если он тут нигде не используется.
Или они тянутся по умолчанию и с этим ничего не поделать?
Можно использовать BINARY. С большим размером могут возникнуть проблемы.
В SQLite для хранения больших данных я использую BLOB (относительно больших).
При установке System.Data.SQLite остальные пакеты подтягивается автоматически.
Спасибо! Очень выручили!
Огромное спасибо! Просто бомбезный пример!
Всё ещё актуально, огромное спасибо, взял за основу работу с файлами и картинками.
Спасибо. Пригодилось.
Спасибо! Все очень подробно и понятно.
Большое спасибо. Хороший готовый небольшой проект.