Класс DataSet служит для создания частичной или полной копии базы данных в оперативной памяти. DataSet содержит в своем наборе коллекцию DataTable, и позволяет устанавливать и контролировать связи между таблицами. Используя раздельные объекты DataTable, не связанные набором, невозможно создать отношения между таблицами. DataSet не может получать сведения из базы об отношениях, но позволяет вручную создавать необходимые связи между таблицами.
Посредником между базой данных и экземплярами DataSet выступает класс SqlDataAdapter, который выполняет команды запросов к базе данных. Метод SqlDataAdapter.Fill(...) заполняет DataSet данными из базы, метод SqlDataAdapter.Update(...) сохраняет в базе изменения, произведённые в наборе данных. Сохранение данных происходит на основании свойств DataRow.RowState строк таблиц входящих в набор DataSet.
Обновление базы происходит при следующих состояниях строк, отмеченных константами перечисления DataRowState:
- Added - новая строка добавлена в таблицу.
- Deleted - строка помечена как удаленная из таблицы.
- Modified - строка имеет изменения в одной или нескольких ячейках.
Для проверки, есть ли в пределах коллекции таблиц DataSet изменения состояния строк, предназначен метод DataSet.HasChanges(DataRowState rowStates). При состоянии строки DataRowState=Unchanged обновление не происходит. Поэтому, для необходимости вынужденного обновления базы, необходимо устанавливать соответствующие свойства строк DataRow.RowState вручную.
Закрепление изменений во всём наборе производится вызовом метода DataSet.AcceptChanges(), отмену изменений - методом DataSet.RejectChanges(). Процедуру принятия и отмену изменений можно конкретизировать на уровне таблиц ( DataTable.AcceptChanges() и DataTable.RejectChanges() ) и даже строк ( DataRow.AcceptChanges() и DataRow.RejectChanges() ).
DataSet получает коллекцию таблиц, заполняемых значениями из базы данных. Объект класса автоматически не получает актуальные названия таблиц, взамен они приобретают имена Table, Table1, Table2 и т.д. Чтобы объект DataSet манипулировал исходными названиями таблиц требуется заполнение карты SqlDataAdapter.TableMappings сопоставления имён таблиц. Метод TableMappings.Add("TableN", "требуемое_название") позволяет иметь в DataSet названия таблиц идентичные в исходной базе данных.
Методы получения данных из базы и сохранения изменений обратно в базу:
public static DataSet GetData(string querystring, List? parameters = null)
{
using SqlConnection connection = new(ConnectionString);
SqlDataAdapter adapter = GetAdapter(connection, querystring, parameters);
DataSet dataSet = new("Catalog");
// Карта сопоставления анонимных названий таблиц и названий для набора DataSet.
adapter.TableMappings.Add("Table", Constants.ParentTableName);
adapter.TableMappings.Add("Table1", Constants.ChildTableName);
adapter.Fill(dataSet);
return dataSet;
}
public static void SaveData(DataTable dt, string querystring, List? parameters = null)
{
using SqlConnection connection = new(ConnectionString);
SqlDataAdapter adapter = GetAdapter(connection, querystring, parameters);
// Сохранение изменений в базу данных.
int i = adapter.Update(dt);
// Звук успешного обновления - для режима тестирования.
if (i > 0) Console.Beep(3000, 100);
}
Настройки связей между таблицами хранятся в свойстве DataSet.Relations, представляющим коллекцию объектов класса DataRelation. Каждый объект DataRelation хранит ссылки на столбец родительской таблицы и столбец дочерней таблицы содержащихся в коллекции таблиц. Такое построение дает DataSet владеть информацией об отношениях "своих" таблиц и возможность перемещения между связанными таблицами.
Метод-конструктор DataRelation для создания связанных столбцов:
// Конструктор класса DataRelation
public DataRelation (
// Необязательное название данного отношения
string? relationName,
// Столбец родительской таблицы
System.Data.DataColumn parentColumn,
// Столбец дочерней таблицы
System.Data.DataColumn childColumn
);
К сожалению посредством SqlDataAdapter.Fill(...) связи между таблицами не загружаются из базы данных в DataSet. Необходимо вручную определять отношения между таблицами. При этом в исходной базе данных необязательно должны быть установлены отношения между таблицами. Связи между таблицами можно построить программным способом. Иногда это может быть преимуществом, так как позволяет оперативно включать и отменять связи между таблицами базы данных свойством DataSet.EnforceConstraints.
При создании связей программно необходимо иметь либо пустые таблицы, либо ячейки связанных столбцов должны иметь одинаковые значения. Например, внешний ключ TypeId дочерней таблицы должен быть идентичен одному из первичных ключей Id родительской таблицы, в противном случае при создании связей выбросится исключение:
System.ArgumentException: "This constraint cannot be enabled as not all values have corresponding parent values." ("Это ограничение нельзя включить, так как не все значения имеют соответствующие родительские значения")
После корректировки значений связи заработают как ожидается.
Программный код установки связи между таблицами и настройки правил ограничения внешнего ключа:
void SetRelationConstraint(DataSet ds)
{
// Таблицы между которыми будет сформирована связь.
// Первичный ключ родительской таблицы будет являться
// внешним ключом дочерней таблицы.
DataTable parentTable = ds.Tables[Constants.ParentTableName];
DataTable childTable = ds.Tables[Constants.ChildTableName];
// В базе данных эти отношения не определены.
// первичный ключ
DataColumn parentColumn = parentTable.Columns["Id"];
// внешний ключ
DataColumn childColumn = childTable.Columns["TypeId"];
// Определение связи между столбцами.
DataRelation relation = new("FK_Product_Type", parentColumn, childColumn, true);
ds.Relations.Add(relation);
// Определение правила действия при удалении строки родительской таблицы.
ForeignKeyConstraint foreignKeyConstraint =
(ForeignKeyConstraint)childTable.Constraints["FK_Product_Type"];
// Запрет удаления строки с первичным ключом пока в дочерней таблице
// есть строки с идентичным внешним ключом.
foreignKeyConstraint.DeleteRule = Rule.None;
. . . .
}
Скрипт создания базы данных для приложения, версия SQL Server 2016 (SQL Server 2016 Express Edition) и выше. При необходимости можно изменить уровень совместимости для создания базы в требуемой версии SQL Server. Скопируйте текст скрипта и вставьте в окно запроса SQL Server Management Studio, запустите скрипт. В базе данных не определены отношения и ограничения, кроме первичных ключей, они определяются только в программном коде приложения. Место сохранения файлов базы данных можно изменить в FILENAME = N'D:Catalog.mdf' и FILENAME = N'D:Catalog_log.ldf'.
USE [master]
GO
CREATE DATABASE [Catalog]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Catalog', FILENAME = N'D:Catalog.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'Catalog_log', FILENAME = N'D:Catalog_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [Catalog] SET COMPATIBILITY_LEVEL = 130
GO
USE [Catalog]
GO
CREATE TABLE [dbo].[Product] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[TypeId] INT NULL,
[Name] NVARCHAR (MAX) NULL,
[SKU] INT NOT NULL,
[SKUType] INT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
CREATE TABLE [dbo].[Type] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (MAX) NOT NULL,
CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET IDENTITY_INSERT [dbo].[Product] ON
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (1, 4, N'Пылесос', 1, 1)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (3, 7, N'Телевизор', 2, 1)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (4, 2, N'Шкаф', 3, 3)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (5, 3, N'Компьютер Acer-256', 4, 10)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (6, 6, N'Смартфон A-21', 5, 323)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (7, 6, N'Телефон GF-39A', 6, 122)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (8, 2, N'Компьютерный стол АА-90', 9, 2)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (9, 2, N'Письменный стол "Заря"', 10, 1)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (10, 2, N'Стул кожанный "Стиль"', 11, 4)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (11, 3, N'Моноблок ELEM-50', 12, 3)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (12, 3, N'Компьютер ИГРА-400', 13, 4)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (13, 1, N'DT1207', 14, 1)
INSERT [dbo].[Product] ([Id], [TypeId], [Name], [SKU], [SKUType]) VALUES (14, 1, N'OP1205', 15, 2)
SET IDENTITY_INSERT [dbo].[Product] OFF
GO
SET IDENTITY_INSERT [dbo].[Type] ON
INSERT [dbo].[Type] ([Id], [Name]) VALUES (1, N'Аккумуляторы')
INSERT [dbo].[Type] ([Id], [Name]) VALUES (2, N'Мебель')
INSERT [dbo].[Type] ([Id], [Name]) VALUES (3, N'Компьютеры')
INSERT [dbo].[Type] ([Id], [Name]) VALUES (4, N'Бытовая техника')
INSERT [dbo].[Type] ([Id], [Name]) VALUES (5, N'Смартфоны')
INSERT [dbo].[Type] ([Id], [Name]) VALUES (6, N'Телефоны')
INSERT [dbo].[Type] ([Id], [Name]) VALUES (7, N'Телевизоры')
SET IDENTITY_INSERT [dbo].[Type] OFF
GO
USE [master]
GO
ALTER DATABASE [Catalog] SET READ_WRITE
GO
DataSetRelationsTables-vs17.zip