C# Aktualizacja bazy Access


(Marcin Obala) #1

Witam

Piszę program, który wczytuje dane z bazy accessa. Chciałbym się dowiedzieć jak dobrze aktualizować bazę danych. Zwykłe zapytanie np. select * from tabela, wczytanie tego do DataTable a następnie aktualizacja to jest z automatu wykonywane. Jednak co jeśli dam zapytanie:

select Tab1.Kol1, Tab2.Kol1 from Tab1, Tab2 where Tab1.ID=Tab2.ID;

I teraz wczytuje to do DataGridView i przykładowo dodaje rekord i chcę żeby zaktualizowane zostały obie tabele.

Czytałem coś o InsertCommand ale nie bardzo to jeszcze widzę. Czy mógłby ktoś podać przykład tego InsertCommand dla powyższego przykładu?

Pozdrawiam


(Tomek Matz) #2

Masz wygenerowany plik .xsd? Czy raczej DataSet tworzysz ręcznie w kodzie?


(Marcin Obala) #3

Nie tworzę w ogóle. Tworzę DataTable, BindingSource ręcznie w kodzie. Do BindingSource jako dataSource podaje DataTable a następnie do gridView.DataSource daje BindingSource.

Adapter.Fill(dataTable);

bindingSource.DataSource = dataTable;

dataGridView.DataSource = bindingSource;

tak w ogóle to mój pierwszy przykład nie jest zbytnie trafiony. Bardziej chodziło mi o coś takiego: pierwsza tabela UCZNIOWIE

ID_uczeń

Uczeń

ID_wychowawca

druga tabela

ID_Wychowawca

nazwisko

i teraz chodzi o to że wczytuje nazwisko ucznia i nazwisko jego wychowawcy. Następnie dodaje nowego ucznia i podaje nazwisko wychowawcy. I tutaj magia ma się stać. Zamiast nazwiska do tabeli Uczniowie ma zostać wpisane ID Wychowawcy


(Tomek Matz) #4

W porządku. To jeszcze powiedz mi tylko jak tworzysz adapter i tu ponawiam pytanie ... masz wygenerowany plik xsd (w projekcie), czy tworzysz go ręcznie w kodzie?


(Marcin Obala) #5
dAdapter = new OleDbDataAdapter(Query, connString);

cBuilder = new OleDbCommandBuilder(dAdapter);

dTable = new DataTable();

dAdapter.Fill(dTable);

bSource = new BindingSource();

bSource.DataSource = dTable;

GridView1.DataSource = bSource;

I nie tworze pliku xsd - nie jest generowany w projekcie ani nie jest tworzony w kodzie (chyba, że ja nie wiem, że jest tworzony).

edit:

Jeśli nikt nie ma pomysłu jak rozwiązać to w zapytaniu to chciałbym zapytać czy zna ktoś łatwy sposób zbindowania (brak mi lepszego słowa), jednej columny do drugiej. Czyli wczytuje dane do DataTable następnie jedną kolumnę ustawiam jako indeks a drugą jako wartości dla poszczególnych indeksów. Kolumna jest oznaczona jako PRIMARY KEY więc na pewno nie powtórzą się indeksy.


(Tomek Matz) #6

Sorka, że wczoraj nie odpisałem, ale jak napisałeś, że robisz wszystko ręcznie to mi się odechciało :slight_smile: Przecież w tym temacie http://forum.dobreprogramy.pl/datagridview-comboboxcolumn-dla-istniejacego-obiektu-t454139.html tłumaczyłem Ci, że pewne rzeczy można wygenerować i oszczędzić sobie pisania kodu. No ale jeśli chcesz to pisać samodzielnie, to niech tak będzie. Dziś usiądę do tego kodu i go poprawię, i wrzucę Ci jak to powinno wyglądać.

-- Dodane 26.07.2011 (Wt) 19:51 --

Wszystkie poniższe klasy musisz dodać do projektu. Na potrzeby przykładu trochę zmieniłem strukturę bazy danych:

Tabela Uczen -> Kolumny: ID_uczen, Imie, Nazwisko, ID_wychowawca

Tabela Wychowawca -> Kolumny: ID_wychowawca, Imie, Nazwisko

MyOleDbTableDataAdapter.cs

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.OleDb;


namespace DataAdapterDemov1.DAL

{

    public abstract class MyOleDbTableDataAdapter

    {

        private OleDbCommandBuilder commandbuilder = null;

        private OleDbDataAdapter dataAdapter = null;

        protected OleDbConnection connection = null;

        protected OleDbTransaction transaction = null;

        protected List selectCommandList = null;


        public MyOleDbTableDataAdapter(OleDbCommand selectCommand)

        {

            connection = selectCommand.Connection;

            selectCommandList = new List() { selectCommand };

            dataAdapter = new OleDbDataAdapter(selectCommand);

            ClearBeforeFill = true;


            GenerateMissingCommands();

        }


        public bool ClearBeforeFill { get; set; }


        protected virtual void GenerateMissingCommands()

        {

            commandbuilder = new OleDbCommandBuilder(dataAdapter);

            dataAdapter.UpdateCommand = commandbuilder.GetUpdateCommand();

            dataAdapter.InsertCommand = commandbuilder.GetInsertCommand();

            dataAdapter.DeleteCommand = commandbuilder.GetDeleteCommand();

        }


        public OleDbTransaction BeginTransaction()

        {

            try

            {

                if (connection.State == ConnectionState.Closed)

                    connection.Open();


                transaction = connection.BeginTransaction();

                return transaction;

            }

            catch (Exception ex)

            {

                if (connection.State == ConnectionState.Open)

                    connection.Close();


                throw ex;

            }

        }


        public void CommitTransaction()

        {

            try

            {

                transaction.Commit();

            }

            catch (Exception ex)

            {

                throw ex;

            }

            finally

            {

                if (connection.State == ConnectionState.Open)

                    connection.Close();

            }

        }


        public void RollbackTransaction()

        {

            try

            {

                transaction.Rollback();

            }

            catch (Exception ex)

            {

                throw ex;

            }

            finally

            {

                if (connection.State == ConnectionState.Open)

                    connection.Close();

            }

        }


        public void AssignTransaction()

        {

            AssignTransaction(transaction);

        }


        public void AssignTransaction(OleDbTransaction transaction)

        {

            this.transaction = transaction;

            dataAdapter.SelectCommand.Transaction = transaction;

            dataAdapter.UpdateCommand.Transaction = transaction;

            dataAdapter.InsertCommand.Transaction = transaction;

            dataAdapter.DeleteCommand.Transaction = transaction;

        }


        public virtual int Fill(DataTable dataTable)

        {

            return Fill(dataTable, selectCommandList[0]);

        }


        protected virtual int Fill(DataTable dataTable, OleDbCommand selectCommand)

        {

            try

            {

                if (ClearBeforeFill)

                    dataTable.Clear();


                dataAdapter.SelectCommand = selectCommand;

                AssignTransaction();


                return dataAdapter.Fill(dataTable);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }


        public virtual int SaveChanges(DataTable table)

        {

            try

            {

                return dataAdapter.Update(table);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }


        public void Dispose()

        {

            if (commandbuilder != null)

                commandbuilder.Dispose();

            if (transaction != null)

                transaction.Dispose();

            if (connection != null)

                connection.Dispose();

            if (dataAdapter != null)

                dataAdapter.Dispose();

            if (selectCommandList != null)

                selectCommandList.GetEnumerator().Dispose();

        }

    }

}

UczenTableDataAdapter.cs

using System;

using System.Data;

using System.Data.OleDb;


namespace DataAdapterDemov1.DAL

{

    public class UczenTableDataAdapter : MyOleDbTableDataAdapter

    {

        private static string _SQL_SELECT = "select ID_uczen, Imie, Nazwisko, ID_wychowawca from Uczen";

        private static string _SQL_SELECT_BY_TEACHER_ID = "select ID_uczen, Imie, Nazwisko, ID_wychowawca from Uczen where (ID_wychowawca=?) or ((ID_wychowawca Is Null) and (IIf(IsNull(?),0,1)=0))";


        public const string _COLUMN_NAME_ID = "ID_uczen";

        public const string _COLUMN_NAME_NAME = "Imie";

        public const string _COLUMN_NAME_SURNAME = "Nazwisko";

        public const string _COLUMN_NAME_ID_TEACHER = "ID_wychowawca";


        public UczenTableDataAdapter(string connectionString)

            : base(new OleDbCommand(_SQL_SELECT, new OleDbConnection(connectionString)))

        { }


        public UczenTableDataAdapter(OleDbConnection connection)

            : base(new OleDbCommand(_SQL_SELECT, connection))

        { }


        protected override void GenerateMissingCommands()

        {

            base.GenerateMissingCommands();


            OleDbCommand selectCommand = new OleDbCommand(_SQL_SELECT_BY_TEACHER_ID, connection);

            selectCommand.Parameters.Add("ID_wychowawca", OleDbType.LongVarWChar);

            selectCommand.Parameters.Add("IsNull_ID_wychowawca", OleDbType.LongVarWChar);

            selectCommandList.Add(selectCommand);

        }


        public virtual int FillByTeacherID(DataTable dataTable, int? teacherID)

        {

            try

            {

                OleDbCommand selectCommand = selectCommandList[1];


                if (teacherID.HasValue)

                {

                    selectCommand.Parameters[0].Value = teacherID.Value;

                    selectCommand.Parameters[1].Value = teacherID.Value;

                }

                else

                {

                    selectCommand.Parameters[0].Value = DBNull.Value;

                    selectCommand.Parameters[1].Value = DBNull.Value;

                }


                return Fill(dataTable, selectCommand);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

    }

}

WychowawcaTableDataAdapter.cs

using System;

using System.Data.OleDb;

using System.Text;


namespace DataAdapterDemov1.DAL

{

    public class WychowawcaTableDataAdapter : MyOleDbTableDataAdapter

    {

        private static string _SQL_SELECT = "select ID_wychowawca, Imie, Nazwisko from Wychowawca";


        public const string _COLUMN_NAME_ID = "ID_wychowawca";

        public const string _COLUMN_NAME_NAME = "Imie";

        public const string _COLUMN_NAME_SURNAME = "Nazwisko";


        public WychowawcaTableDataAdapter(string connectionString)

            : base(new OleDbCommand(_SQL_SELECT, new OleDbConnection(connectionString)))

        { }


        public WychowawcaTableDataAdapter(OleDbConnection connection)

            : base(new OleDbCommand(_SQL_SELECT, connection))

        { }

    }

}

CustomWychowawcaTableDataAdapter.cs

using System;

using System.Data.OleDb;


namespace DataAdapterDemov1.DAL

{

    public class CustomWychowawcaTableDataAdapter : MyOleDbTableDataAdapter

    {

        private static string _SQL_SELECT = "select ID_wychowawca, Imie + ' ' + Nazwisko As Wychowawca from Wychowawca";


        public const string _COLUMN_NAME_ID = "ID_wychowawca";

        public const string _COLUMN_NAME_FULL_NAME = "Wychowawca";


        public CustomWychowawcaTableDataAdapter(string connectionString)

            : base(new OleDbCommand(_SQL_SELECT, new OleDbConnection(connectionString)))

        { }


        public CustomWychowawcaTableDataAdapter(OleDbConnection connection)

            : base(new OleDbCommand(_SQL_SELECT, connection))

        { }

    }

}

Form1.cs

using System;

using System.Windows.Forms;

using DataAdapterDemov1.DAL;

using System.Data;


namespace DataAdapterDemov1

{

    public partial class Form1 : Form

    {

        private UczenTableDataAdapter uczenTableDataAdapter;

        private CustomWychowawcaTableDataAdapter customWychowawcaTableDataAdapter;

        private DataTable uczenDataTable;

        private DataTable customWychowawcaDataTable;


        public Form1()

        {

            InitializeComponent();

        }


        private void Form1_Load(object sender, EventArgs e)

        {

            customWychowawcaTableDataAdapter = new CustomWychowawcaTableDataAdapter(Properties.Settings.Default.bazaConnectionString);

            uczenTableDataAdapter = new UczenTableDataAdapter(Properties.Settings.Default.bazaConnectionString);


            customWychowawcaDataTable = new DataTable();

            uczenDataTable = new DataTable();

            refreshButton_Click(null, null);


            GenerateDataGridViewColumns();

            bindingSource1.DataSource = uczenDataTable;

        }


        private void Form1_FormClosing(object sender, FormClosingEventArgs e)

        {

            uczenDataTable.Dispose();

            customWychowawcaDataTable.Dispose();

        }


        private void refreshButton_Click(object sender, EventArgs e)

        {

            try

            {

                customWychowawcaTableDataAdapter.Fill(customWychowawcaDataTable);

                uczenTableDataAdapter.Fill(uczenDataTable);

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }


        private void SaveButton_Click(object sender, EventArgs e)

        {

            try

            {

                uczenTableDataAdapter.BeginTransaction();

                uczenTableDataAdapter.AssignTransaction();

                uczenTableDataAdapter.SaveChanges(uczenDataTable);

                uczenTableDataAdapter.CommitTransaction();

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

                uczenTableDataAdapter.RollbackTransaction();

            }

            finally

            {

                refreshButton_Click(null, null);

            }

        }


        private void GenerateDataGridViewColumns()

        {

            dataGridView1.AutoGenerateColumns = false;

            dataGridView1.Columns.Clear();


            for (int i = 0; i < uczenDataTable.Columns.Count; i++)

            {

                if (uczenDataTable.Columns[i].ColumnName.CompareTo(UczenTableDataAdapter._COLUMN_NAME_ID_TEACHER) == 0)

                {

                    DataGridViewComboBoxColumn column = new DataGridViewComboBoxColumn();

                    column.DataPropertyName = UczenTableDataAdapter._COLUMN_NAME_ID_TEACHER;

                    column.HeaderText = "Wychowawca";

                    column.Name = UczenTableDataAdapter._COLUMN_NAME_ID_TEACHER;

                    column.DataSource = customWychowawcaDataTable;

                    column.DisplayMember = CustomWychowawcaTableDataAdapter._COLUMN_NAME_FULL_NAME;

                    column.ValueMember = CustomWychowawcaTableDataAdapter._COLUMN_NAME_ID;


                    dataGridView1.Columns.Add(column);

                }

                else

                {

                    DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn();

                    column.DataPropertyName = uczenDataTable.Columns[i].ColumnName;

                    column.HeaderText = uczenDataTable.Columns[i].ColumnName;

                    column.Name = uczenDataTable.Columns[i].ColumnName;


                    if (uczenDataTable.Columns[i].ColumnName.CompareTo(UczenTableDataAdapter._COLUMN_NAME_ID) == 0)

                        column.Visible = false;


                    dataGridView1.Columns.Add(column);

                }

            }

        }

    }

}

app.config

<?xml version="1.0" encoding="utf-8" ?>





            connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Projects\DataAdapterDemo\Database\baza.accdb" />

-- Dodane 27.07.2011 (Śr) 18:00 --

Wprowadziłem parę zmian w kodzie :slight_smile:


(Marcin Obala) #7

Kod bardzo przydatny :wink:

A powiedz mi jak mam DataTable to czy mogę z niego wygenerować podDataTable który będzie zawierał dane na podstawie jakiegoś zapytania? Czy muszę lecieć po wierszach i sprawdzać warunek i kopiować do nowego datatable ? Chodzi mi o to co mówiłeś odnośnie wczytania całej bazy danych (bądź części) i operować na niej w pamięci.


(Tomek Matz) #8

W takim przypadku bardzo użyteczna okazuje się kontrolka BindingSource. Dlatego jako źródło danych DataGridView lepiej podawać tą kontrolkę niż bezpośrednio kolekcję obiektów/obiekt DataTable. Kontrolka BindingSource posiada właściwość Filter, którą możesz traktować jak warunek WHERE w instrukcji Select. Przykładowo w tym powyższym kodzie dodaj sobie w zdarzeniu Click przycisku Refresh taką linijkę kodu: bindingSource1.Filter = "ID_wychowawca is null"; albo bindingSource1.Filter = "ID_wychowawca = 1"; lub coś w tym stylu i zobacz jakie wyniki zostaną wyświetlone w DataGridView. Wyniki zostaną przefiltrowane, ale w pamięci cały czas będzie trzymana pełna kopia tabeli. Jeśli usuniesz filtr z BindingSource, to wszystkie rekordy będą ponownie widoczne.


(Marcin Obala) #9

Tylko że ja chcę zrobić coś takiego

do BindingSource zostanie wpisany dataTable oryginalnej tabeli (chodzi o tabelę Karty - masz bazę). Chcę żeby w bazie była aktualizowana tylko ta tabela. Następnie chcę do dataGridView dodać ręcznie kolumny które będą taką jakby otoczką. W tabeli są te maszyny, modele i czynności a w karcie tylko czynności (nie wiem czy dobrze bazę rozplanowałem ale tak już jest i trudno..). Chcę teraz zrobić coś na zasadzie że wybieram model z comboboxa, następnie w drugim comboboxie pojawiają się tylko te maszyny do ktorych została przypisana jakaś czynność dla danego modelu. Tak więc wpisywanie jako dataSource zmodyfikowanej tabeli to raczej nie to bo będzie trzeba pisać ręcznie zapytanie aktualizujące tabelę. Albo ja po prostu jeszcze za mało wiem :stuck_out_tongue_winking_eye:


(Tomek Matz) #10

O czymś podobnym już rozmawialiśmy (choć może wtedy się nie zrozumieliśmy). To się nazywa Master/Detail. Będziesz miał na formie kontrolkę ComboBox i DataGridView. Nawet udało mi się znaleźć konkretny przykład pod Twój przypadek:

A jeśli chodzi o tą zmodyfikowaną tabelę to też nie jest problem. Możesz sobie przygotować swój CustomTableDataAdapter, który będzie dziedziczył po MyOleDbTableDataAdapter (coś podobnego jak robiłem z CustomWychowawca). Ten adapter będzie miał przypisany Select, który będzie wyciągał tylko to co Cię interesuje. W klasie przeciążysz sobie metodę GenerateMissingCommands i przypiszesz swoje instrukcje Insert/Update/Delete dostosowane do tego adapter-a. A jeśli będziesz musiał aktualizować dwie tabele, to przeciążysz sobie metodę SaveChanges i tam napiszesz kod, który będzie aktualizował dwie tabele :slight_smile:


(Marcin Obala) #11

Mam CustomTableDataAdapter :wink: przydał mi się do czegoś innego :wink: Teraz tylko mnie ciekawi czy da się zmienić datasource poszczególnych komórek a nie całej kolumny. Czyli w wierszu 1 kolumna 1 jest wybrane A więc ma się wyświetlić w wierszu 1, kolumna 2 wartości 1, 2, 3. Natomiast w drugim wierszu w kolumnie 1 wybieram B więc w wiersz 2, kolumna 2 wyświetli się 7,8,9


(Tomek Matz) #12

Mam przed oczami strukturę tej Twojej bazy danych, ale nie widzę tego co usiłujesz zrobić. Jedno jest pewne ... nie jest możliwe podpięcie dwóch tabel pod jedną kontrolkę DataGridView. Możesz poszukać sposobu obejścia tego pod hasłem master/detail with one datagridview, ale nie wiem czy znajdziesz coś konkretnego. Ja nic takiego nigdy nie robiłem.


(Marcin Obala) #13

Nie chodzi wcale o połączenie tylko wyświetlanie zwykłe.

Do datagridView wpisuje tabelę Karty.

Następnie do datagridview dodaje 2 kolumny z drugiej tabeli które będą służyły tylko pomocniczo. Mam w kartach pracownika i czynność. Pracownik coś tam robił. Czynności są określone w innej tabeli. Czynności są podpinane pod maszynę i model. Czyli Model1 tworzony jest na 3 maszynach, z tego na każdej z 3 maszyn jest są po dwie czynności. Chcę teraz w Kartach mieć taką pomoc.

Wybieram pracownika, wybieram jaki model (tutaj w combobox byłyby wszystkie modele), następnie w kolejnej kolumnie wyświetli tylko te maszyny na których jest robiony dany model, jak wybiorę to w następnej kolumnie wyświetlą się czynności dla danego modelu, dla danej maszyny czyli w combobox miałbym tylko 2 rzeczy do wyboru.

Rozumiem też że mogę mieć po prostu błędnie skonstruowaną bazę danych i ciężko z aktualnie rozplanowaną coś zrobić.


(Tomek Matz) #14

No ja nie mogę zrozumieć dlaczego Ty chcesz to wszystko mieć w jednym DataGridView. Wrzucasz sobie na Form-ę kontrolkę ComboBox, w której będziesz wyświetlać pracowników. Wrzucasz sobie na Form-ę drugiego CombBox-a, który będzie wyświetlał modele, na których pracownik wybrany w pierwszym ComboBox-ie wykonywał jakieś czynności. Trzecią kontrolką na Form-ie będzie DataGridView, który wyświetli te czynności, cenę i ewentualnie informacje o maszynach/maszynie w zależności od wybranego pracownika i modelu, i to w zasadzie tyle. Reasumując na Form-ie będziesz mieć trzy kontrolki, jedna zależna od drugiej i to jest właśnie Master/Detail.


(Marcin Obala) #15

Piszę teraz z zapytaniem czy ktoś wie jak wyciągnąć wartość wyświetlaną z tego nieszczęsnego DataGridViewComboBoxColumn?

Chodzi o to że BindingSource.Filter nie działa z kolumnami ComboBox - w każdym bądź razie u mnie. Zrobiłem sobie swoje własne filtrowanie jednak nie mogę filtrować po kolumnie ComboBox. Czy wie ktoś jak wyciągnąć wartość ? Ponieważ jeśli dam dataGridView.Rows[0].Cells[0].Value to dostaje wartość ValueMember a ja chciałbym dostać czysty tekst z DisplayMember.

:edit:

Już mam heh.

DataGridViewComboBoxCell.EditedFormattedValue;


(Tomek Matz) #16

Sprostuję tylko to co napisałeś, bo trochę namieszałeś :stuck_out_tongue: BindingSource.Filter używa się do filtrowania rekordów/elementów obiektu ustawionego jako DataSource tej kontrolki. W przykładzie umieszczonym przeze mnie wyżej w DataGridView znajduje się kolumna typu DataGridViewComboBoxColumn, a wyświetlane są w niej imię i nazwisko wychowawcy. Żeby przefiltrować rekordy wyświetlane w DataGridView po danych wychowawcy to należy filtrować BindingSource po ID_Wychowawcy (przykłady podawałem wyżej, np. bindingSource1.Filter = "ID_wychowawca = 1":wink:. Filtrowanie wyników DataGridView po nazwie wyświetlanej w kontrolce DataGridViewComboBoxColumn, a nie po ID to zły pomysł. Nazwa/Etykieta może się zmienić, a ID w tabeli pełniącej rolę słownika nie powinno się zmieniać.


(Marcin Obala) #17

Tutaj chodzi o taki zwykły filtr. Jest lista osób. Wpisuje Marcin i wyświetla mi wszystkich Marcinów :wink: nie muszę znać ich numerów ID. Albo chce wszystkich których wychowawcą jest Pani Piekielna więc wpisuje Piekielna i też wyświetla :wink: Chodzi o to że jest jeden textBox do filtrowania który odsiewa rekordy sprawdzając wartości we wszystkich komórkach.


(Tomek Matz) #18

Rozumiem. O tym nie pomyślałem. Ale jednak wciąż stawiałbym na BindingSource (w końcu między innymi od tego ta kontrolka jest). Mógłbyś sobie napisać metodę, która wyciągnie wszystkie ID ze słownika, które mogą odnosić się do tekstu wpisanego przez użytkownika, a następnie mając już te ID stworzyć odpowiedni warunek i przypisać go do właściwości Filter. Jak już pisałem Filter można traktować jak klauzulę WHERE instrukcji SELECT, a więc można w nim używać np. OR.