C# Dataset MS Access и все все все

Всем привет.
Писал небольшую программку для работы с БД МС Аццесс(далее просто бд), научился нескольким интересным вещам, так что сейчас расскажу все по порядку.


Прежде всего нам надо каким-то образом соединиться с базой данных.
Для соединения с бд у шарпа есть такой объект как OleDbConnection.

string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb";
OleDbConnection conn = new OleDbConnection(constr);

В строке констр, у нас есть параметр датасорс. КОгда я писал проект, я использовал filedialog, но ничего вам не мешает использовать какой-то ини файл, или вообще путь прямо в программном коде.
Кстати для других SQLов имеется объект SqlConnection.

"Ну и чего дальше-то?"- наверное спрашиваете вы.

А дальше мы должны загрузить каким-то образом наши таблицы, дабы работать с ними. И вот здесь вступает в действие наш герой, наша лучезарная звезда: его Величество DataSet. Я могу восхвалять эту штуку ещё очень долго, так как она сильно облегчает работу с бд. Суть в том что в датасет загружаются только те данные которые нужны.

Но как же заполнить это чудо из чудес? Для этих целей существует объект OleDbDataAdapter. С его помощью вся информация из базы переходит в датасет. Но для того что бы с чем-то работать, данный объект должен знать что брать, а что нет.
Для этого формируем строку с запросом(которая теоретически может быть и batch query, но на практике аццесс таких дел не поддерживает).

DataSet ds = new DataSet();
string q = "SELECT * FROM testTable";
da = new OleDbDataAdapter(q, conn);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.fill(ds,"testTable");

У меня было 7 таблиц поэтому я создавал 7 датаадапторов и ими заполнял датасет по очереди.

Если же ваша субд позволяет делать батч запрос, и вы хотите потом нормально обращаться к своей таблице, то вам пригодится такая штука как TableMappings у объект OleDbDataAdapter. Если вы сразу загружаете несколько таблиц, то первая будет названа как Table1, вторая Table2 и так далее, поэтому нужно сказать адаптеру при помощи маппинга, как на самом деле называется такая или такая таблица.

Ну заполнили таблицу, пол работы сделано. Теперь, если у нас несколько таблиц и если действительно надо, стоит сказать датасету, что у нас некоторые таблицы состоят в отношениях(родственных или ещё каких). Поможет нам в этом объект DataRelation и метод DataSet.Relations.Add(string name, parent column, child column).

Так, понаписал понаписал, да и не понятно ничего.
Данный объект должен иметь имя, колонка родительская и колонка потомка(детская).
Простите, если кому ухо коробит, не знаю как ещё перевести.

Родительская колонка содержится в таблице, откуда будем получать какие-то данные связанные именно с этой колонкой. А колонка потомка, та колонка, которая содержит ссылку на первую колонку. Замудрено написано, привожу пример:
в университете имеется несколько предметов и эти предметы ведутся на разных языках. Возьмем простой вариант, 1 ко многим. То есть один язык, на несколько предметов.

Таблица с предметами у нас содержит колонки id name и language_id. Так вот language_id будет child column, а колонка id в таблице languages(с языками) родительская.
Если мы задаем отношения между колонками, то потом сможем обратиться к рядам: GetChildRow или GetParentRow, а к отдельным элементам например так GetChildROw().ItemArray[2].toString();

Собственно хватит болтать, давай работать.

Основые объекты которые я использовал при работе с Датасетом это: DataTable и DataRow.

Самый простой способ отобразить нашу таблицу - использовать объект датагридвью. Выглядит примерно так:

dataGridView1.DataSource = ds.Tables["someTableName"];

В датагридвью загрузиться таблица из датасета.
Если у нас программа служебная, то этого вполне хватит.

Чтобы использовать датарелейшн для отображения, гридвью не прокатит. То есть вы рискуете получить таблицу целиком из ForeignKey ов, что не тру.
Я где-то читал что объект ДатаГрид из ВПФ позволяет сделать отображение нормальным, но я не уверен, так что дерзайте кому интересно.
Тут я вышел из положения при помощи листвью. Конечно никакого датабиндинга не ждите, но зато вместо айдишников у вас будет нормальный читаемый текст.

Выглядеть это должно примерно так:

DataTable dt = ds.Tables["event"];

listView1.Columns.Clear();
listView1.Items.Clear();

listView1.Columns.Add("ID");
listView1.Columns.Add("Name");
listView1.Columns.Add("Surname");
listView1.Columns.Add("Date");


foreach (DataRow row in dt.Rows)
{
string[] subitems = new string[4];

subitems[0] = row["id"].ToString();
subitems[1] = row.GetParentRow("EventIniciatorName").ItemArray[2].ToString();
subitems[2] = row.GetParentRow("EventIniciatorSurname").ItemArray[3].ToString();
subitems[3] = ((DateTime)row["meeting_date"]).ToShortDateString();
ListViewItem item = new ListViewItem(subitems);
listView1.Items.Add(item);
}

listView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
listView1.Columns[0].Width = 0;

Теперь разберем пример.
Сначала очищаем на всякий случай колонки и список итемов.
Далее добавляем наименования колонок. Далее для каждого ряда из нашей таблицы делаем определенные махинации:
1. загружаем айдишник записи, мне он понадобился так как надо было менять запись в бд, а выцеплять данные по полям слишком круто.
2. берем имя и фамилию из перент ряда. В скобках указано название отношения.
3. Получаем дату, и преобразуем её как шортдейтстринг.
Кстати время суток в datetimepicker всегда соответсвует вашему на компьютере, так что если будете сравнивать какие-либо даты с датой в datetimepicker, всегда учитывайте это.


Далее мы добавляем в наш листвью итем.
В конце вызываем метод ресайзколумнс со значением выравнивания по величине заголовка.
Также установим ширину первого элемента как 0, чтобы его не было видно.

Все, наш листвью заполнен. Чтобы с листвью удобней было работать значение View установить в Details, Gridlinesв и Fullrowsselect в True.
При работе с листвью мы используем массив SelectedItems(если у нас Multiselect true, иначе только SelectedItems[0]),
у каждого из элементов данного массива имеется массив Subitems и у каждого из SubItems имеется Text.

О чем ещё могу рассказать? О заполнении Combobox из Datatable.
На этом примере станет понятно, что такое DataBinding и с чем его едят.
Что бы заполнить Combobox из таблицы мы должны указать таблицу датасорсом комбобокса. Но там же 100500 колонок в таблице, а мне нужна всего одна, что делать?
Ну на этот случай имееются свойства ValueMember и DisplayMember. Первый отвечает за значение, которое приобретает комбобокс, а второй за то что мы видим.

iniciator.DisplayMember = "surname";
edit_iniciator.ValueMember = "id";
edit_iniciator.DataSource = ds.Tables["persons"];

Думаю объяснять не надо.


Вносим новый записи в таблицу так:

DataRow row = ds.Tables["event"].NewRow();
row["iniciator"] = add_iniciator.SelectedValue;
row["manager_id"] = add_manager.SelectedValue;

Кстати, удаляем при помощи row.Delete();


О боже мой! Чуть не забыл. Вот мы тут наш датасет весь почистили заполнили по 10 раз, надо бы все в базу записать.

Делаем двумя способами. Либо сами прописываем команду, либо просим сделать за нас это объект OleDbCommandBuilder

DataRow row = ds.Tables["event"].Rows.Find(int.Parse(listView1.SelectedItems[0].SubItems[0].Text));
row.Delete();
OleDbCommand delCMD = new OleDbCommand("delete from event where id = @id", conn);
delCMD.Parameters.Add(new OleDbParameter("@id", int.Parse(listView1.SelectedItems[0].SubItems[0].Text)));
da.DeleteCommand = delCMD;
da.Update(ds, "event");

Наш датаадаптер должен иметь 4 команды(CRUD). В примере выше я написал делит сам. Главное не забыть потом список по новой заполнить.

Пример Update.

OleDbCommandBuilder builder = new OleDbCommandBuilder(da);
DataRow row = ds.Tables["event"].Rows.Find(int.Parse(listView1.SelectedItems[0].SubItems[0].Text));
row["iniciator_id"] = int.Parse(edit_iniciator.SelectedValue.ToString());
row["our_manager_id"] = int.Parse(edit_manager.SelectedValue.ToString());
da.Update(ds, "event");

Тут ещё проще. Создаем объект коммандбилдер. Редактируем ряд и обновляем. Войля.



Парочка лирических отступлений.

1. Фильтрацию я делал цветом, подумал эстетичнее и проще. Собственно это было так. Если все фильтры выключены, то ничего не делал. Иначе заполнял все фильтровочным цветом, а потом просматривая все записи и фильтры снимал выделение с тех записей которые не подходят по своим данным под условие.
2. Если у вас 3 комбобокса должны ссылаться на 1 таблицу, но в них вы выбираете разные значения, то для каждого комбобокса нужна отдельная таблица, иначе никак.
3. Если влом лепить свой класс, не забудьте о структурах: просто и удобно.


В принципе на этом пока все, если что вспомню ещё, позже допишу. А так, удачи всем в начинаниях. Помните, для программиста нет мало кода, бывает много простых задач, за которые лень браться.