Datenbankzugriff mittels DataAdapter

using System;
using System.Data;
using System.Data.OleDb;

namespace Chapter5
{

    public class DataAdapterSample
    {

        public static void Main()
        {
            //----- Connection erzeugen
            OleDbConnection conn = new OleDbConnection("provider=SQLOLEDB; " +
                " data source=(local)\\SQLEXPRESS; database=NETBOOK;" +
                "user id=sa; password=sa;");

            //----- Daten und Schema laden
            DataSet ds = LoadData(conn);

            //----- Daten ausgeben
            Print(ds);

            //----- Relation aus Datenbank lesen und in DataSet definieren
            DefineRelation(ds, conn);

            //----- Relation verwenden um zu jeder Person die Kontakte auszugeben
            PrintContactsOfPersons(ds);

            //----- Daten als XML Datei speichern
            ds.WriteXml("data.xml");
        }

        static DataSet LoadData(OleDbConnection conn)
        {
            DataSet ds = new DataSet("PersonContacts");
            IDbDataAdapter adapter = new OleDbDataAdapter();
            //----- SELECT-Command erzeugen und setzen 
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT * FROM Person; SELECT * FROM Contact";
            adapter.SelectCommand = cmd;
            //----- im DataSet befinden sich noch keine Tabellen, also fge sie bei Fill hinzu!
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            //----- Automatisch erzeugte Tabellen umbenennen
            adapter.TableMappings.Add("Table", "Person");
            adapter.TableMappings.Add("Table1", "Contact");
            //----- Daten aus der Datenbank laden
            adapter.Fill(ds);
            if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
            if (adapter is IDisposable) ((IDisposable)adapter).Dispose();
            return ds;
        }

        static void DefineRelation(DataSet ds, OleDbConnection conn)
        {
            DataTable schemaTable = null;
            //---- Fremdschluesselbeziehungen von Datenbank lesen
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            //---- Referentielle Integritaet im DataSet erstellen
            if (schemaTable != null)
            {
                foreach (DataRow r in schemaTable.Rows)
                {
                    DataTable parentTab = ds.Tables[r["PK_TABLE_NAME"].ToString()];
                    DataTable childTab = ds.Tables[r["FK_TABLE_NAME"].ToString()];
                    DataRelation rel = new DataRelation(
                        //Name der Beziehung im DataSet
                        parentTab.TableName + "_" + childTab.TableName,
                        // Vatertabelle, Spalte des Primaerschluessels
                        parentTab.Columns[r["PK_COLUMN_NAME"].ToString()],
                        //Abhaengige Tabelle, Spalte des Fremdschluessels
                        childTab.Columns[r["FK_COLUMN_NAME"].ToString()]);
                    ds.Relations.Add(rel);
                }
            }
        }

        static void Print(DataSet ds)
        {
            Console.WriteLine("DataSet {0}:", ds.DataSetName);
            Console.WriteLine();
            foreach (DataTable t in ds.Tables)
            {
                Print(t);
                Console.WriteLine();
            }
        }

        static void Print(DataTable t)
        {
            //---- Tabellenkopf
            Console.WriteLine("Tabelle {0}:", t.TableName);
            foreach (DataColumn col in t.Columns)
            {
                Console.Write(col.ColumnName + "|");
            }
            Console.WriteLine();
            for (int i = 0; i < 40; i++) { Console.Write("-"); }
            Console.WriteLine();

            //---- Daten
            int nrOfCols = t.Columns.Count;
            foreach (DataRow row in t.Rows)
            {
                for (int i = 0; i < nrOfCols; i++)
                {
                    Console.Write(row[i]); Console.Write("|");
                }
                Console.WriteLine();
            }
        }

        static void PrintContactsOfPersons(DataSet ds)
        {
            Console.WriteLine("Kontakte von Personen:");
            Console.WriteLine("--------------------------------------"); 

            DataTable persons = ds.Tables["Person"];
            foreach (DataRow person in persons.Rows)
            {
                Console.WriteLine("Person: {0} {1}", person["FirstName"], person["Name"]);
                foreach (DataRow personContact in person.GetChildRows("Person_Contact")) {
                    Console.WriteLine("  Email: {0}, Phone {1}", personContact["EMail"], personContact["Phone"]);
                }
            }


        }

    }

}