Картинка блога

В этом посте я расскажу о моем адаптере для SQLite, который использовался в проекте YoutubeView.

SQLite — это база данных, которая сохранаяется в одном файле. Такой способ подходит, наприемер, для оконных приложений (где в силу каких-либо причин не хочется использовать Access).

CRUD — сокращение от Create, Read, Update, Delete. Это набор класс, работающий с данными базы данных.

С первого раза мне потребовалось некоторое время, чтобы найти нужные классы и связать все воедино. Это теперь я знаю, что кроме платных адаптеров, есть еще бесплатный от Mono и существует частичная поддержка NHibernate. Совсем недавно на мои глаза попался еще один инткрксный проект C#SQLite (не требующий Unmanaged dll). В моем случае, для сохранения простых данных, это может стать бессмысленным перебором.

Установка SQLite в проект.

Для начала работы потребуется два файла:

  • system.data.sqlite из http://sqlite.phxsoftware.com/
  • sqlite3.dll из http://www.sqlite.org/download.html

Файл sqlite3.dll должен находится в папке проекта, а System.Data.Sqlite в зависимостях (References). Если у вас машина 64x не забудте изменить метод компиляции.

Теперь нужно создать базу, для этого можно использовать SQLite Admin

Адаптер SQLite

Я храню ссылку на базу в файле конфигурации:



Мой основной адаптер ничего не делает, кроме того, что хранит соединение к нашей базе:

AbstractAdapter class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Configuration;

namespace DataStorage
{
public abstract class AbstractAdapter : IDisposable
{
static readonly string ConnectionString = ConfigurationManager.AppSettings["DBConnection"];
protected SQLiteConnection conn = new SQLiteConnection(ConnectionString);

public AbstractAdapter()
{
conn.Open();
}
public void Dispose()
{
if (conn.State != (ConnectionState.Closed | ConnectionState.Broken))
{
conn.Close();
}

}
}
}

SQLite CRUD Adapter и CRUD index Attribute

Так как SQLite не поддерживает мета информацию, по этому нужно использовать индексы полей.

CrudIndexAttribute class:

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

namespace DataStorage
{
[AttributeUsage(AttributeTargets.Field)]
public class CrudIndexAttribute : Attribute
{
public int FieldIndex=-1;
public string KeyName;
}
[AttributeUsage(AttributeTargets.Class)]
public class CrudEntityAttribute : Attribute
{
public string TableName;
}
}

CrudAdapter class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataStorage;
using System.Reflection;
using System.Data;
using System.Data.SQLite;

namespace DataStorage
{
public class CrudAdapter : AbstractAdapter where T : class, new()
{
string tableName;
int primaryKeyCount;
Dictionary fieldsHash;

public CrudAdapter()
{
var ttype = typeof(T);
if (ttype.GetCustomAttributes(typeof(CrudEntityAttribute), false).Count() == 0) throw new InvalidCastException("CrudEntityAttribute is not pressent in " + ttype + " entity");
var classAttr = (CrudEntityAttribute)ttype.GetCustomAttributes(typeof(CrudEntityAttribute), false).First();
if (String.IsNullOrEmpty(classAttr.TableName)) throw new InvalidCastException("TableName is not set for " + ttype);
tableName = classAttr.TableName;

var fields = ttype.GetFields(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public)
.Where((x) => { return x.GetCustomAttributes(typeof(CrudIndexAttribute), false).Count() > 0; });
if (fields.Count() == 0) throw new InvalidCastException("No CrudIndexAttribute in " + ttype + " entity");

fieldsHash = new Dictionary();
foreach (FieldInfo field in fields)
{
var attr = (CrudIndexAttribute)field.GetCustomAttributes(typeof(CrudIndexAttribute), false).First();
if (attr.FieldIndex<0) throw new InvalidCastException("No index is set for "+field+" in " + ttype + " entity"); if(!String.IsNullOrEmpty(attr.KeyName))primaryKeyCount++; fieldsHash.Add(field, attr); } if (primaryKeyCount == 0) throw new InvalidCastException("No primary keys in " + ttype + " entity"); fieldsHash = fieldsHash.OrderBy((x) => x.Value.FieldIndex).ToDictionary((x) => x.Key, (x)=>x.Value);

int indexer = 0;
foreach (var fld in fieldsHash)
{
if (fld.Value.FieldIndex != indexer++) throw new Exception("Invalid indexing in " + fld.Value);
}
}

public List GetAllEntries()
{
var command = conn.CreateCommand();
command.CommandText = "Select * from ["+tableName+"]";
var reader = command.ExecuteReader();

List output = new List();
var ttype = typeof(T);

while (reader.Read())
{
output.Add(CreateEntity(reader));
}
reader.Close();
return output;
}
public T GetEntity(params object[] key)
{
if (primaryKeyCount != key.Count()) throw new InvalidOperationException("Invalid amount of primary keys should be " + primaryKeyCount);
var command = conn.CreateCommand();

command.CommandText = "Select * from [" + tableName + "] where" + BuildKeyClosure(key);
var reader = command.ExecuteReader();

T ret = null;
if (reader.Read()) ret = CreateEntity(reader);
reader.Close();
return ret;
}
public void Delete(T t)
{
var command = conn.CreateCommand();
command.CommandText = "delete from [" + tableName + "] where" + BuildKeyClosure(t);
int cnt = command.ExecuteNonQuery();
if (cnt > 2) throw new Exception("Ops more than one record deleted");
}

public T SaveOrUpdate(T t)
{
Delete(t);

StringBuilder sb = new StringBuilder();
foreach (var fld in fieldsHash)
{
if (sb.Length > 0) sb.Append(",");
sb.Append("'" + fld.Key.GetValue(t) + "'");
}
var command = conn.CreateCommand();
command.CommandText = String.Format("insert into [{0}] values ({1})",
tableName, sb.ToString());
command.ExecuteNonQuery();

return t;
}

private T CreateEntity(SQLiteDataReader reader)
{
T t = new T();
foreach (var fld in fieldsHash)
{
fld.Key.SetValue(t, Convert.ChangeType(reader.GetValue(fld.Value.FieldIndex), fld.Key.FieldType));
}
return t;
}

private string BuildKeyClosure(T t)
{
var key = new object[primaryKeyCount];
StringBuilder sb = new StringBuilder();
int indexer = 0;
foreach (var fld in fieldsHash)
{
if (!String.IsNullOrEmpty(fld.Value.KeyName))
{
sb.Append(" " + fld.Value.KeyName + "=='" + fld.Key.GetValue(t) + "'");
indexer++;
}
}
return sb.ToString();
}
private string BuildKeyClosure(object[] key)
{
StringBuilder sb = new StringBuilder();
int indexer = 0;

foreach (var fld in fieldsHash)
{
if (!String.IsNullOrEmpty(fld.Value.KeyName))
{
sb.Append(" " + fld.Value.KeyName + "=='" + key[indexer] + "'");
indexer++;
}
}
return sb.ToString();
}
}
}

Создаем Entity и Entity Adapter

Это рабочий пример из YouTubeConveter, на таблицу filestorage с тремя полями:

  • name — String, Primary Key.
  • isComplete — Int.
  • FileName — String.

IsComplete должен хранить булево значение, однако в SQLite есть некоторые проблемы с этим типом.

DownloadEntity class:

[CrudEntity(TableName="filestorage")]
public class DownloadEntity
{
[CrudIndex(FieldIndex = 0, KeyName="name")]
public string Name;

[CrudIndex(FieldIndex = 1)]
private int isCompleted;

[CrudIndex(FieldIndex = 2)]
public string FileName;

public bool IsCompleted
{
get { return isCompleted > 0; }
set { isCompleted = value?1:0; }
}
}

DownloadAdapter class:

public class DownloadAdapter : CrudAdapter
{
}

Доступ к таблице происходит с помощью методов адаптера — GetAllEntries, GetEntity, Delete и SaveOrUpdate.

CrudTest class:

public CrudTest()
{
var crud = new CrudAdapter();

var ret = crud.GetAllEntries();
var rett = crud.GetEntity("test");

crud.SaveOrUpdate(rett);

ret = crud.GetAllEntries();
}

Сохранение параметров приложения

Еще один полезный класс в YouTubeConverter, это SettingsManager. Этот класс хранит настройки приложения и использует всоего рода кеш. Запись настроек это ключ-значение, типа String:

public class SettingsAdapter : AbstractAdapter
{
bool CacheNeedsUpdate = true;
Dictionary settings = new Dictionary();

public string this[string index]
{
get
{
return this.GetSetting(index);
}
set
{
SetSetting(index, value);
}
}

public Dictionary Settings
{
get
{
if (CacheNeedsUpdate)
{
var command = conn.CreateCommand();
command.CommandText = "Select * from [settings]";

var reader = command.ExecuteReader();
settings.Clear();
while (reader.Read())
{
settings.Add(reader.GetString(0), reader.GetString(1));
}
reader.Close();
CacheNeedsUpdate = false;
}
return settings;
}
set
{
if (CacheNeedsUpdate) { var z = Settings; }
foreach (KeyValuePair setting in value)
{
if (GetSetting(setting.Key, true) != setting.Value)
{
SetSetting(setting.Key, setting.Value);
}
}
settings = value;
}
}

public void SetSetting(string key, string value)
{
var command = conn.CreateCommand();
if (GetSetting(key) != null)
{
settings[key] = value;
command.CommandText = "update [settings] set [value]=\"" + value + "\" where [name]=\"" + key + "\"";
}
else
{
settings.Add(key, value);
command.CommandText = "insert into settings ([name],[value]) values (\"" + key + "\",\"" + value + "\")";
}
command.ExecuteNonQuery();
}

public string GetSetting(string key)
{
return GetSetting(key, false);
}
private string GetSetting(string key, bool noCache)
{
if (CacheNeedsUpdate || noCache)
{
var command = conn.CreateCommand();
//XXX: shoul be changed to property
command.CommandText = "Select [value] from [settings] where name=\"" + key + "\"";
return command.ExecuteScalar() as String;
}
return settings[key];
}
}

Доставать и обновлять параметры можно с помощью методов — SetSetting и GetSetting.

Метки:, ,

5 комментариев в “CRUD на SQLite”

  1. «Так как SQLite не поддерживает мета информацию…»
    мне казалось, что сборка system.data.sqlite от http://sqlite.phxsoftware.com/ обеспечивает полную совместимость с ADO.NET 2.0, в которой есть стандартные средства для получения метаинфомации(описания таблиц, колонок, индексов etc). Или я ошибаюсь?

  2. Возможно мы говорим о разных библиотеках. Я использовал Managed обложку на нативный sqlite.dll.

  3. У вас в коде присутствует пространство имен System.Data.SQLite. Значит, теоритически должно получиться что то вроде

    SQLiteConnection.GetSchema("COLUMNS", new string[ { null, null, "MyTableName", null });
    SQLiteConnection.GetSchema("ForeignKeys", new string[ { null, null, "MyTableName", null });

  4. Проверил в youtubeconverter. Версия SQLLite 1.0.60.0. Вы правы, схему получить можно. Спасибо, за дополнение.

  5. Спасибо большое за статью. Долгое время пытался сделать универсальный класс, поддерживающий автоматическое чтение и сохранение в базу данных, но у меня получалось не очень красивое решение. Ваш пример намного проще и удобнее в работе — просто добавь атрибуты и всё. 🙂 Только не увидел реализации Create и Delete. Но это уже мелочи — доделать легко.