Отношения между таблицами

Реляционная база данных — это совокупность взаимосвязанных таблиц, каждая из которых содержит информацию об объектах определенного типа.

Объект DataRelation

DataRelation показывает отношение дочерний-родительский между двумя объектами DataTable, которые находятся в объекте DataSet. Для создания отношения нужно указать имя отношения, ссылаемый столбец родительской таблицы и ссылающийся столбец дочерней таблицы.

Создание отношения

Отношение может создавать ограничения Unique и AllowDBNull на родительской таблице и ограничение ForeignKeyConstraint на дочерней таблице. Ограничение PrimaryKey не создается!

static void Main(string[] args)
        {
            string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ShopDB;Integrated Security=True";
            string commandString = "SELECT * FROM Products; SELECt * FROM OrderDetails;";

            DataSet shopDB = new DataSet("ShopDB");

            SqlDataAdapter adapter = new SqlDataAdapter(commandString, connectionString);

            adapter.Fill(shopDB); // заполнение DataSet

            DataTable products = shopDB.Tables[0];      // получение ссылки на таблицу Products
            DataTable orderDetails = shopDB.Tables[1];  // получение ссылки на таблицу OrderDetails

            // создание отношения между таблицами Products и OrderDetails
            var ProductsOrderDetailsRel = new DataRelation("Products_OrderDetails",             // имя отношения 
                                                            products.Columns["ProdID"],         // поле родительской таблицы
                                                            orderDetails.Columns["ProdID"],     // поле дочерней таблицы
                                                            true);                              // создавать/не создавать ограничения

            // после созания ограничения его нужно добавить в коллекцию Relations объекта DataSet, в которой содержаться таблицы
            // без этого шага отношение не будет работать
            shopDB.Relations.Add(ProductsOrderDetailsRel);

            Console.WriteLine("Products primary key columns number: " + products.PrimaryKey.Length);          // объект DataRelation не добавляет ограничение первичного ключа
            Console.WriteLine("ProdID column Unique= " + products.Columns["ProdID"].Unique);                //столбцу родительской таблицы добавлено ограничение на уникальность 
            Console.WriteLine("ProdID column AllowDBNull= " + products.Columns["ProdID"].AllowDBNull);      //столбцу родительской таблицы добавлено ограничение на уникальность 

            var orderDetailsConstraint = orderDetails.Constraints[0] as ForeignKeyConstraint;
            Console.WriteLine("OrderDetails foreign key constraint name: " + orderDetailsConstraint.ConstraintName); // добавлено ограничение ForeignKeyConstraint
        }

static void Main(string[] args)

{

string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ShopDB;Integrated Security=True";

string commandString = "SELECT * FROM Products; SELECt * FROM OrderDetails;";

DataSet shopDB = new DataSet("ShopDB");

SqlDataAdapter adapter = new SqlDataAdapter(commandString, connectionString);

adapter.Fill(shopDB); // заполнение DataSet

DataTable products = shopDB.Tables[0]; // получение ссылки на таблицу Products

DataTable orderDetails = shopDB.Tables[1]; // получение ссылки на таблицу OrderDetails

// создание отношения между таблицами Products и OrderDetails

var ProductsOrderDetailsRel = new DataRelation("Products_OrderDetails", // имя отношения

products.Columns["ProdID"], // поле родительской таблицы

orderDetails.Columns["ProdID"], // поле дочерней таблицы

true); // создавать/не создавать ограничения

// после созания ограничения его нужно добавить в коллекцию Relations объекта DataSet, в которой содержаться таблицы

// без этого шага отношение не будет работать

shopDB.Relations.Add(ProductsOrderDetailsRel);

Console.WriteLine("Products primary key columns number: " + products.PrimaryKey.Length); // объект DataRelation не добавляет ограничение первичного ключа

Console.WriteLine("ProdID column Unique= " + products.Columns["ProdID"].Unique); //столбцу родительской таблицы добавлено ограничение на уникальность

Console.WriteLine("ProdID column AllowDBNull= " + products.Columns["ProdID"].AllowDBNull); //столбцу родительской таблицы добавлено ограничение на уникальность

var orderDetailsConstraint = orderDetails.Constraints[0] as ForeignKeyConstraint;

Console.WriteLine("OrderDetails foreign key constraint name: " + orderDetailsConstraint.ConstraintName); // добавлено ограничение ForeignKeyConstraint

}

Получение связанных данных

После создания связи между таблицами можно просматривать связанные данные, для чего у объекта DataRow есть ряд методов.

GetChildRows – этот метод позволяет получить массив DataRow[] дочерних записей относительно текущей строки.

GetParentRow – этот метод предназначен для получения единственной родительской строки относительно дочерней. Используется для таблиц со связью один ко многим.

GetParentRows – этот метод предназначен для получения массива
DataRow[] родительских строк относительно текущей строки.
Используется для таблиц со связью многие ко многим.

  static void Main(string[] args)
        {
            string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ShopDB;Integrated Security=True";
            string commandString = "SELECT * FROM Customers; SELECT * FROM Orders;";

            DataSet shopDB = new DataSet("ShopDB");

            SqlDataAdapter adapter = new SqlDataAdapter(commandString, connectionString);

            adapter.Fill(shopDB);
            DataTable customers = shopDB.Tables[0];
            DataTable orders = shopDB.Tables[1];

            shopDB.Relations.Add("Customers_Orders", customers.Columns["CustomerNo"], orders.Columns["CustomerNo"]);

            foreach (DataRow ordersRow in orders.Rows)
            {
                var customerRow = ordersRow.GetParentRow("Customers_Orders"); // метод GetParrentRow возвращает одну строку

                Console.WriteLine("OrderId: "+ordersRow["OrderID"]+"\n"+
                                  "OrderDate: "+ ordersRow["OrderDate"]+"\n"+
                                  "CustomerName: " + customerRow[2] +" "+ customerRow[1] +" "+ customerRow[3]);

                Console.WriteLine();
            }
        }

static void Main(string[] args)

{

string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ShopDB;Integrated Security=True";

string commandString = "SELECT * FROM Customers; SELECT * FROM Orders;";

DataSet shopDB = new DataSet("ShopDB");

SqlDataAdapter adapter = new SqlDataAdapter(commandString, connectionString);

adapter.Fill(shopDB);

DataTable customers = shopDB.Tables[0];

DataTable orders = shopDB.Tables[1];

shopDB.Relations.Add("Customers_Orders", customers.Columns["CustomerNo"], orders.Columns["CustomerNo"]);

foreach (DataRow ordersRow in orders.Rows)

{

var customerRow = ordersRow.GetParentRow("Customers_Orders"); // метод GetParrentRow возвращает одну строку

Console.WriteLine("OrderId: "+ordersRow["OrderID"]+"\n"+

"OrderDate: "+ ordersRow["OrderDate"]+"\n"+

"CustomerName: " + customerRow[2] +" "+ customerRow[1] +" "+ customerRow[3]);

Console.WriteLine();

}

Правила удаления родительских строк

С помощью свойства DeleteRule ограничения ForeignKeyConstraint возможно задать действие, которое будет выполняться применительно к объектам, связанным с этим ограничением при удалении строки.

Это значит что свойство DeleteRule определяет, что произойдет со
столбцами в дочерней таблице при удалении строки из родительской таблицы.

Свойство DeleteRule принимает один из элементов перечисления Rule, определенном в пространстве имен System.Data

Правила обновления родительских строк

С помощью свойства UpdateRule ограничения ForeignKeyConstraint возможно задать действие, которое будет выполняться применительно к объектам, связанным с этим ограничением при изменении строки.

Это значит что свойство DeleteRule определяет, что произойдет со столбцами в дочерней таблице при изменении строки из родительской таблицы.
Свойство UpdateRule принимает один из элементов перечисления Rule, определенном в пространстве имен System.Data

// создание правил для удаления и обновления данных связанных строк 
private void Form1_Load(object sender, EventArgs e)
        {
            DataSet shopDB = new DataSet();

            shopDB.ReadXmlSchema(@"D:\ADO.NET\DATA\ShopDbSchema.xml");
            shopDB.ReadXml(@"D:\ADO.NET\DATA\ShopDBData.xml");

            shopDB.AcceptChanges();

            DataTable customers = shopDB.Tables["Customers"];
            DataTable orders = shopDB.Tables["Orders"];
            DataTable orderDetails = shopDB.Tables["OrderDetails"];

            var FK_Customers_Orders = orders.Constraints["Customers_Orders"] as ForeignKeyConstraint;
            // при удалении/изменении строки из таблицы Customers будут удаляться/изменяться все связанные строки из таблицы Orders
            FK_Customers_Orders.DeleteRule = Rule.Cascade; 
            FK_Customers_Orders.UpdateRule = Rule.Cascade;
            
            var FK_Orders_OrderDetails = orderDetails.Constraints["Orders_OrderDetails"] as ForeignKeyConstraint;
            FK_Orders_OrderDetails.DeleteRule = Rule.Cascade; //при удалении строки из таблицы Customers будет ошибка

            label1.Text = customers.TableName;
            dataGridView1.DataSource = customers; // связывание таблицы customers c элементом управления dataGridView1

            label2.Text = orders.TableName;
            dataGridView2.DataSource = orders; // связывание таблицы orders c элементом управления dataGridView2

            label3.Text = orderDetails.TableName;
            dataGridView3.DataSource = orderDetails;
        }

// создание правил для удаления и обновления данных связанных строк

private void Form1_Load(object sender, EventArgs e)

{

DataSet shopDB = new DataSet();

shopDB.ReadXmlSchema(@"D:\ADO.NET\DATA\ShopDbSchema.xml");

shopDB.ReadXml(@"D:\ADO.NET\DATA\ShopDBData.xml");

shopDB.AcceptChanges();

DataTable customers = shopDB.Tables["Customers"];

DataTable orders = shopDB.Tables["Orders"];

DataTable orderDetails = shopDB.Tables["OrderDetails"];

var FK_Customers_Orders = orders.Constraints["Customers_Orders"] as ForeignKeyConstraint;

// при удалении/изменении строки из таблицы Customers будут удаляться/изменяться все связанные строки из таблицы Orders

FK_Customers_Orders.DeleteRule = Rule.Cascade;

FK_Customers_Orders.UpdateRule = Rule.Cascade;

var FK_Orders_OrderDetails = orderDetails.Constraints["Orders_OrderDetails"] as ForeignKeyConstraint;

FK_Orders_OrderDetails.DeleteRule = Rule.Cascade; //при удалении строки из таблицы Customers будет ошибка

label1.Text = customers.TableName;

dataGridView1.DataSource = customers; // связывание таблицы customers c элементом управления dataGridView1

label2.Text = orders.TableName;

dataGridView2.DataSource = orders; // связывание таблицы orders c элементом управления dataGridView2

label3.Text = orderDetails.TableName;

dataGridView3.DataSource = orderDetails;

}

Перечисление System.Data.Rule

Перечисление Rule определяет действие, которое должно выполнено для обеспечения ограничения ForeignKeyConstraint
Элементы перечисления Rule:

None – при удалении родительской строки никаких действий для дочерних строк не применять.
Cascade – при удалении родительской строки удалять все связанные дочерние строки.
SetNull – при удалении родительской строки связанным дочерним строкам присвоить значение DBNull.Value
SetDefault – при удалении родительской строки связанным дочерним строкам присвоить значение по умолчанию