Data table windows form

После получения данных из базы данных через SqlDataAdapter в DataSet мы можем локально работать с этими данными вне зависимости от наличия подключения. Более того если нам даже и не надо использовать никакую базу данных, но при этом мы хотим иметь удобный функционал для работы с данными в виде наборов таблиц, то мы также можем воспользоваться классом DataSet.

Объект DataSet содержит таблицы, которые представлены типом DataTable . Таблица, в свою очередь, состоит из столбцов и строк. Каждый столбец представляет объект DataColumn , а строка — объект DataRow . Все данные строки хранятся в свойстве ItemArray , который представляет массив объектов — значений отдельных ячеек строки. Например, получим все таблицы и выведем их содержимое:

Теперь рассмотрим, как мы можем работать с объектами DataSet и DataTable без какой-либо базы данных. Например, создадим вручную в DataSet несколько таблиц и определим их структуру:

Разберем весь код. Сначала создаются объекты DataSet и DataTable, в конструктор которых передается название. Затем создается четыре столбца. Каждый столбец в конструкторе принимает два параметра: имя столбца и его тип.

Причем для столбца Id устанавливается, что значения этого столбца должны иметь уникальное значение, не должны принимать null, и их значение при добавлении нового объекта будет инкрементироваться на единицу. То есть фактически это стандартный столбец Id, как в большинстве баз данных.

Далее создается еще три столбца, при этом для столбца Discount устанавливается свойство Expression , указывающее на выражение, которое будет использоваться для вычисления значения столбца:

То есть в данном случае значение столбца Discount равно значению столбца Price, помноженного на 0.2.

Затем устанавливается первичный ключ для таблицы с помощью свойства PrimaryKey:

В роли первичного ключа выступает столбец Id. Но мы также можем использовать набор различных столбцов для создания составного ключа.

После определения схемы таблицы в нее добавляются две строки:

Значения в метод booksTable.Rows.Add можно передать как напрямую в виде массива объектов, так и в виде объекта DataRow. При этом нам надо передать ровно столько значений, сколько в таблице столбцов. Однако поскольку первый столбец Id устанавливается через автоинкремент, мы можем передать значение null — оно все равно будет игнорироваться. Также мы можем опустить последний параметр для столбца Discount, так как его значение вычисляется с помощью выражения «Price * 0.2». Более того мы даже можем опустить значение для третьего столбца Price, так как у него установлено свойство DefaultValue , которое устанавливает значение по умолчанию, если значение отсутствует:

И в конце идет перебор строк таблицы.

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

Получив строку по индексу, можно изменить ее ячейки:

И также можно удалять строку:

Используя метод Select() объекта DataTable мы легко можем найти строки, которые соответствуют определенному критерию. Например, получим строки, в которых цена больше 120:

Data Sources Supported by Windows Forms

Traditionally, data binding has been used within applications to take advantage of data stored in databases. With Windows Forms data binding, you can access data from databases as well as data in other structures, such as arrays and collections, so long as certain minimum requirements have been met.

Structures to Bind To

In Windows Forms, you can bind to a wide variety of structures, from simple objects (simple binding) to complex lists such as ADO.NET data tables (complex binding). For simple binding, Windows Forms supports binding to the public properties on the simple object. Windows Forms list-based binding generally requires that the object support the IList interface or the IListSource interface. Additionally, if you are binding with through a BindingSource component, you can bind to an object that supports the IEnumerable interface. For more information about interfaces related to data binding, see Interfaces Related to Data Binding.

The following list shows the structures you can bind to in Windows Forms.

BindingSource
A BindingSource is the most common Windows Forms data source and acts a proxy between a data source and Windows Forms controls. The general BindingSource usage pattern is to bind your controls to the BindingSource and bind the BindingSource to the data source (for example, an ADO.NET data table or a business object). The BindingSource provides services that enable and improve the level of data binding support. For example, Windows Forms list based controls such as the DataGridView and ComboBox do not directly support binding to IEnumerable data sources however, you can enable this scenario by binding through a BindingSource. In this case, the BindingSource will convert the data source to an IList.

Simple objects
Windows Forms supports data binding control properties to public properties on the instance of an object using the Binding type. Windows Forms also supports binding list based controls, such as a ListControl to an object instance when a BindingSource is used.

array or collection
To act as a data source, a list must implement the IList interface; one example would be an array that is an instance of the Array class. For more information on arrays, see How to: Create an Array of Objects (Visual Basic).

In general, you should use BindingList when you create lists of objects for data binding. BindingList is a generic version of the IBindingList interface. The IBindingList interface extends the IList interface by adding properties, methods and events necessary for two-way data binding.

IEnumerable
Windows Forms controls can be bound to data sources that only support the IEnumerable interface if they are bound through a BindingSource component.

ADO.NET data objects
ADO.NET provides a number of data structures suitable for binding to. Each varies in its sophistication and complexity.

DataColumn. A DataColumn is the essential building block of a DataTable, in that a number of columns comprise a table. Each DataColumn has a DataType property that determines the kind of data the column holds (for example, the make of an automobile in a table describing cars). You can simple-bind a control (such as a TextBox control’s Text property) to a column within a data table.

DataTable. A DataTable is the representation of a table, with rows and columns, in ADO.NET. A data table contains two collections: DataColumn, representing the columns of data in a given table (which ultimately determine the kinds of data that can be entered into that table), and DataRow, representing the rows of data in a given table. You can complex-bind a control to the information contained in a data table (such as binding the DataGridView control to a data table). However, when you bind to a DataTable, you are a really binding to the table’s default view.

DataView. A DataView is a customized view of a single data table that may be filtered or sorted. A data view is the data «snapshot» used by complex-bound controls. You can simple-bind or complex-bind to the data within a data view, but be aware that you are binding to a fixed «picture» of the data rather than a clean, updating data source.

DataSet. A DataSet is a collection of tables, relationships, and constraints of the data in a database. You can simple-bind or complex-bind to the data within a dataset, but be aware that you are binding to the default DataViewManager for the DataSet (see the next bullet point).

DataViewManager. A DataViewManager is a customized view of the entire DataSet, analogous to a DataView, but with relations included. With a DataViewSettings collection, you can set default filters and sort options for any views that the DataViewManager has for a given table.

Handling DataTable Events

The DataTable object provides a series of events that can be processed by an application. The following table describes DataTable events.

Event	Description
Initialized	Occurs after the EndInit method of a DataTable is called. This event is intended primarily to support design-time scenarios.
ColumnChanged	Occurs after a value has been successfully changed in a DataColumn.
ColumnChanging	Occurs when a value has been submitted for a DataColumn .
RowChanged	Occurs after a DataColumn value or the RowState of a DataRow in the DataTable has been changed successfully.
RowChanging	Occurs when a change has been submitted for a DataColumn value or the RowState of a DataRow in the DataTable .
RowDeleted	Occurs after a DataRow in the DataTable has been marked as Deleted .
RowDeleting	Occurs before a DataRow in the DataTable is marked as Deleted .
TableCleared	Occurs after a call to the Clear method of the DataTable has successfully cleared every DataRow .
TableClearing	Occurs after the Clear method is called but before the Clear operation begins.
TableNewRow	Occurs after a new DataRow is created by a call to the NewRow method of the DataTable .
Disposed	Occurs when the DataTable is Disposed . Inherited from MarshalByValueComponent.

Most operations that add or delete rows do not raise the ColumnChanged and ColumnChanging events. However, the ReadXml method does raise ColumnChanged and ColumnChanging events, unless the XmlReadMode is set to DiffGram or is set to Auto when the XML document being read is a DiffGram .

Data corruption can occur if data is modified in a DataSet from which the RowChanged event is raised. No exception will be raised if such data corruption occurs.

Additional Related Events

The Constraints property holds a ConstraintCollection instance. The ConstraintCollection class exposes a CollectionChanged event. This event fires when a constraint is added, modified, or removed from the ConstraintCollection .

The Columns property holds a DataColumnCollection instance. The DataColumnCollection class exposes a CollectionChanged event. This event fires when a DataColumn is added, modified, or removed from the DataColumnCollection . Modifications that cause the event to fire include changes to the name, type, expression or ordinal position of a column.

The Tables property of a DataSet holds a DataTableCollection instance. The DataTableCollection class exposes both a CollectionChanged and a CollectionChanging event. These events fire when a DataTable is added to or removed from the DataSet .

Changes to DataRows can also trigger events for an associated DataView. The DataView class exposes a ListChanged event that fires when a DataColumn value changes or when the composition or sort order of the view changes. The DataRowView class exposes a PropertyChanged event that fires when an associated DataColumn value changes.

Sequence of Operations

Here is the sequence of operations that occur when a DataRow is added, modified, or deleted:

Create the proposed record and apply any changes.

Check constraints for non-expression columns.

Raise the RowChanging or RowDeleting events as applicable.

Set the proposed record to be the current record.

Update any associated indexes.

Raise ListChanged events for associated DataView objects and PropertyChanged events for associated DataRowView objects.

Evaluate all expression columns, but delay checking any constraints on these columns.

Raise ListChanged events for associated DataView objects and PropertyChanged events for associated DataRowView objects affected by the expression column evaluations.

Raise RowChanged or RowDeleted events as applicable.

Check constraints on expression columns.

Changes to expression columns never raise DataTable events. Changes to expression columns only raise DataView and DataRowView events. Expression columns can have dependencies on multiple other columns, and can be evaluated multiple times during a single DataRow operation. Each expression evaluation raises events, and a single DataRow operation can raise multiple ListChanged and PropertyChanged events when expression columns are affected, possibly including multiple events for the same expression column.

Do not throw a NullReferenceException within the RowChanged event handler. If a NullReferenceException is thrown within the RowChanged event of a DataTable , then the DataTable will be corrupted.

Example

The following example demonstrates how to create event handlers for the RowChanged , RowChanging , RowDeleted , RowDeleting , ColumnChanged , ColumnChanging , TableNewRow , TableCleared , and TableClearing events. Each event handler displays output in the console window when it is fired.

DataTable Constraints

You can use constraints to enforce restrictions on the data in a DataTable, in order to maintain the integrity of the data. A constraint is an automatic rule, applied to a column or related columns, that determines the course of action when the value of a row is somehow altered. Constraints are enforced when the System.Data.DataSet.EnforceConstraints property of the DataSet is true. For a code example that shows how to set the EnforceConstraints property, see the EnforceConstraints reference topic.

There are two kinds of constraints in ADO.NET: the ForeignKeyConstraint and the UniqueConstraint. By default, both constraints are created automatically when you create a relationship between two or more tables by adding a DataRelation to the DataSet. However, you can disable this behavior by specifying createConstraints = false when creating the relation.

ForeignKeyConstraint

A ForeignKeyConstraint enforces rules about how updates and deletes to related tables are propagated. For example, if a value in a row of one table is updated or deleted, and that same value is also used in one or more related tables, a ForeignKeyConstraint determines what happens in the related tables.

The DeleteRule and UpdateRule properties of the ForeignKeyConstraint define the action to be taken when the user attempts to delete or update a row in a related table. The following table describes the different settings available for the DeleteRule and UpdateRule properties of the ForeignKeyConstraint.

Rule setting	Description
Cascade	Delete or update related rows.
SetNull	Set values in related rows to DBNull.
SetDefault	Set values in related rows to the default value.
None	Take no action on related rows. This is the default.

A ForeignKeyConstraint can restrict, as well as propagate, changes to related columns. Depending on the properties set for the ForeignKeyConstraint of a column, if the EnforceConstraints property of the DataSet is true, performing certain operations on the parent row will result in an exception. For example, if the DeleteRule property of the ForeignKeyConstraint is None, a parent row cannot be deleted if it has any child rows.

You can create a foreign key constraint between single columns or between an array of columns by using the ForeignKeyConstraint constructor. Pass the resulting ForeignKeyConstraint object to the Add method of the table’s Constraints property, which is a ConstraintCollection. You can also pass constructor arguments to several overloads of the Add method of a ConstraintCollection to create a ForeignKeyConstraint.

When creating a ForeignKeyConstraint, you can pass the DeleteRule and UpdateRule values to the constructor as arguments, or you can set them as properties as in the following example (where the DeleteRule value is set to None).

AcceptRejectRule

Changes to rows can be accepted using the AcceptChanges method or canceled using the RejectChanges method of the DataSet, DataTable, or DataRow. When a DataSet contains ForeignKeyConstraints, invoking the AcceptChanges or RejectChanges methods enforces the AcceptRejectRule. The AcceptRejectRule property of the ForeignKeyConstraint determines which action will be taken on the child rows when AcceptChanges or RejectChanges is called on the parent row.

The following table lists the available settings for the AcceptRejectRule.

Rule setting	Description
Cascade	Accept or reject changes to child rows.
None	Take no action on child rows. This is the default.

Example

The following example creates a ForeignKeyConstraint, sets several of its properties, including the AcceptRejectRule, and adds it to the ConstraintCollection of a DataTable object.

UniqueConstraint

The UniqueConstraint object, which can be assigned either to a single column or to an array of columns in a DataTable, ensures that all data in the specified column or columns is unique per row. You can create a unique constraint for a column or array of columns by using the UniqueConstraint constructor. Pass the resulting UniqueConstraint object to the Add method of the table’s Constraints property, which is a ConstraintCollection. You can also pass constructor arguments to several overloads of the Add method of a ConstraintCollection to create a UniqueConstraint. When creating a UniqueConstraint for a column or columns, you can optionally specify whether the column or columns are a primary key.

You can also create a unique constraint for a column by setting the Unique property of the column to true. Alternatively, setting the Unique property of a single column to false removes any unique constraint that may exist. Defining a column or columns as the primary key for a table will automatically create a unique constraint for the specified column or columns. If you remove a column from the PrimaryKey property of a DataTable, the UniqueConstraint is removed.

The following example creates a UniqueConstraint for two columns of a DataTable.