Работа с sqlite в С#. Часть 2. Опыты. Выводы.

Итак, дошло время до практической части. Которая меня, скажем прямо, огорчила.
Так как хотелось использовать в своем проекте LinQ, а при попытке создать dbml-файлик штатными средствами студии выскакивает окошечко с ошибочкой (см. рис. ниже), то будем генерировать dbml с помощью DbMetal, о котором упоминалось в предыдущей части цикла.
Для начала создадим нашу базу данных, которая будет состоять из двух таблиц - Journal, главная таблица, в которую будут записываться факты поставки по конкретной дате, и таблица Items, в которой будут детали поставок по конктретной дате.

CREATE TABLE "journal" ("ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "Date" DATETIME DEFAULT CURRENT_DATE);
CREATE TABLE "items" ("ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "journalID" INTEGER NOT NULL , "supplier" VARCHAR DEFAULT supplier1, "store" VARCHAR DEFAULT store1, "amount" FLOAT DEFAULT 0)

Теперь сгенерируем dbml файл и подключим его в проект. Для генерации dbml напишем небольшой batch-файл, чтобы ручками впредь не выполнять эту операцию. Во-первых, в папке, где наша база, нужно расположить DbMetal.exe и библиотеки, которые он использует. Там же должен располагаться make_dbml.bat со следующим содержимым:

@echo on
DbMetal.exe -dbml:sample.dbml -provider:Sqlite -conn="data source=sample.sqlite"
dbMetal.exe -code:sample.designer.cs -namespace:SampleDataContext -provider:Sqlite -conn="data source=sample.sqlite"

Добавляем сгенерированный sample.dbml в проект (правый клик по проекту - Add->Existing item...).
Так как мы пишем тестовое приложение, которое, естественно, не может затронуть все аспекты, скажу о некоторых отрицательных моментах, с которыми мне довелось столкнуться. Во-первых, не поддерживается тип Boolean. Долго пришлось возиться в поисках проблемы - резко началось рушиться приложение, а оказалось, всего-навсего, не sqlite не поддерживает тип Boolean, который использовался в одной из таблиц базы данных. И, хоть альтернативу логическому типу очень легко найти (использовать тот же varchar (1)), все же очень неприятно. Во-вторых, (но это уже проблема не самого sqlite), при dbmetal почему-то не генерирует представления :( Вот так просто нас лишили крутой возможности. Опять же, ручками все делается. Но как-то неспортивно. После ковыряния в исходниках dbmetal, которые лежат в открытом доступе на svn, и перекомпиляции с включеной опцией views, стали генерироваться пустые классы. На этом решено было не тратить больше время на всякую ерунду. Ведь существуют платные хорошо работающие альтернативы, если кому надо.
И так, вернемся к нашему приложению. Его задача - отображать по выбранной дате все поставки за этот день. ГУИ будет таким:
TreeView - для отображения списка дат
ListView - для отображения поставок по датам
Теперь, собственно, будем все это оживлять.
Создадим класс DBClass, который будет заведовать работой с бд. Код:

class DBClass
{
private Main _connection = null;

public DBClass()
{
_connection = new Main(new XSqlConnection("Data Source=sample.sqlite"));
}

public List GetJournal()
{
List items = new List();
items = _connection.Journal.ToList();
return items;
}

public List GetItems(int journalId)
{
List items = new List();
items = _connection.Items.Where(i => i.JournalID == journalId).ToList();
return items;
}

public Items AddItem(Items item)
{
_connection.Items.InsertOnSubmit(item);
_connection.SubmitChanges();
return item;
}

public Journal AddJournal(DateTime date)
{
Journal item = new Journal() { Date = date };
_connection.Journal.InsertOnSubmit(item);
_connection.SubmitChanges();
return item;
}

public void DeleteItem(int id)
{
_connection.Items.DeleteOnSubmit(
_connection.Items.SingleOrDefault(i => i.ID == id)
);
_connection.SubmitChanges();
}

public void DeleteJournal(int id)
{
_connection.Journal.DeleteOnSubmit(
_connection.Journal.SingleOrDefault(i => i.ID == id)
);
_connection.SubmitChanges();
}
}

Теперь, все, что нам остается - брать и использовать методы класса в форме.
Инициализируем наше дерево:

private void InitTree()
{
foreach (Journal item in db.GetJournal())
{
AddJournalDate(item);
}
}

private void AddJournalDate(Journal item)
{
TreeNode node = new TreeNode(item.Date.Value.ToShortDateString());
node.Tag = item.ID;
tvJournal.Nodes[0].Nodes.Add(node);
}

Теперь, напишем обработчик события NodeMouseDoubleClick для отображения поставок по выбранной дате:

private void tvJournal_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
{
if (tvJournal.SelectedNode != null && tvJournal.SelectedNode.Tag != null)
{
int journalId = 0;
if (int.TryParse(tvJournal.SelectedNode.Tag.ToString(), out journalId))
{
lvItems.Items.Clear();
foreach (Items item in db.GetItems(journalId))
{
AddItem(item);
}
}
}
}
private void AddItem(Items item)
{
ListViewItem lvi = new ListViewItem(new string[] { item.Supplier, item.Store, item.Amount.ToString() });
lvi.Tag = item.ID;
lvItems.Items.Add(lvi);
}

Теперь научим наше приложение добавлять дату в список и удалять дату. Для удаления даты добавим триггер в таблицу Journal:

CREATE TRIGGER "main"."OnDelete" DELETE ON journal BEGIN Delete From items Where items.journalID = deleted.ID; END

Код обработки клика для кнопки "Удалить выбранную дату" выглядит так:

private void button2_Click(object sender, EventArgs e)
{
int journalId = (int)tvJournal.SelectedNode.Tag;
db.DeleteJournal(journalId);
tvJournal.Nodes[0].Nodes.Remove(tvJournal.SelectedNode);
}

Ниже приведены обработчики событий клика по кнопкам "Добавить поставку" и "Удалить выбранную поставку" соответственно:

private void addItemToJournal_Click(object sender, EventArgs e)
{
int journalId = 0;
if (int.TryParse(tvJournal.SelectedNode.Tag.ToString(), out journalId))
{
Items item = new Items() {
Amount = float.Parse( tbAmount.Text ),
Store = tbStore.Text,
Supplier = tbSupplier.Text,
JournalID = journalId
};
AddItem(db.AddItem(item));
}
}

private void DeleteItemFromJournal_Click(object sender, EventArgs e)
{
if (lvItems.SelectedItems.Count > 0)
{
int itemID = (int)lvItems.SelectedItems[0].Tag;
db.DeleteItem(itemID);
lvItems.Items.Remove(lvItems.SelectedItems[0]);
}
}

Вот и все. Поигрались и хватит :) Весь рассмотренный функционал можно воплотить и через ADO.NET, без пляски с бубном вокруг LinQ, но вы же сами знаете, что так не интересно :)
Исходник можно скачать тут.