System. Data. constraintexception: constraints cannot be enabled. One or more rows contain values that violate non-null, unique, or foreign key constraints.

For example, a table in a database has three columns, and one column cannot be empty. The other two columns are allowed to be empty, after I find all the data in this table, fill it into a datatable, now I am viewing the columns of this datatable and find that the allowdbnull values of each column are true. I want to obtain information about whether the columns in the database are empty. What should I do? Thank you!

By default, datatable has constraints, such as tablea, column A, column B, column C, and column D. where A and D are not empty, and a is the only primary key. Table tableb, column H, J, K, L. column H and column J cannot be empty. If the join query results contain non-empty columns, A, D, H, and J, the column in The result set found by the left join has a null value, or if column A is not unique, assigning the result set to the able will trigger

System. data. constraintexception: If this exception is not handled, the program will not be terminated. If the datagridview is bound, only a red-white exclamation mark will be displayed in the data cell, prompting that the column is not empty, or the column is not unique. Generally, you can use the able in dataset to set the enableconstraint attribute of dataset to false, or remove the constraint of the datatable.

The following are some information about the network.

ADO. NET provides the following two objects for retrieving relational data and storing it in memory: Dataset AndDatareader. DatasetProvides a relational representation of data in the memory, a set of data including some tables (these tables contain data, sort the data and constrain the data), and the relationship between tables. Datareader provides a fast, forward-only, read-only data stream from the database.
When using DatasetDataadapter (or commandbuilder) is often used to interact with the data source. When using DatasetYou can also use dataview DatasetData Application sorting in AndFilter. You can also DatasetInherit, create a strong type DatasetUsed to split tables and rows AndColumns are published as strong object attributes.
When designing an application, consider the level of functionality required by the application to determine the use DatasetOr datareader.
Use Dataset:
R is used to navigate between multiple discrete tables in the result.
R operations come from multiple data sources (for example, from multiple databases and an XML file) AndData of a workbook.
R exchanges data between layers or uses XML Web Services. Unlike datareader, DatasetCan be passed to the remote client.
R reuse the same set of records to improve performance (such as sorting, searching, or filtering data) through caching ).
R each record requires a large amount of processing. Extended processing of each row returned by datareader will prolong the necessary time to serve the datareader connection, which affects the performance.
R uses XML operations to operate data, such as extensible style sheet language conversion (XSLT conversion) or XPath query.

Use datareader in applications in the following cases:
R does not need to cache data.
The result set to be processed by R is too large to be stored in the memory.
Once R needs to access data in a forward-only and read-only manner.
Note FillingDatasetThe dataadapter uses datareader. Therefore, use dataadapter to replaceDatasetThe performance is reduced.DatasetMemory usageAndFillDatasetLoop. Generally, this performance improvement is symbolic. Therefore, design decisions should be based on the required functions.

When we use the getschematable method of sqldatareader, we use the following code to print the table structure.

Datatable TB = reader. getschematable ();

Foreach (datarow dtrow in TB. Rows ){
Foreach (datacolumn dtcol in TB. columns ){
Console. Write ("\ t" + dtcol. columnname + "=" + dtrow [dtcol]);
}
Console. Write ("\ n ");
}

Note: This method does not return a data table that we operate on, but a structure table that describes the column metadata, similar to the following:

Column name column No. Column Length Column Type (...)
First column
Column 2

That is to say, the rows attribute of the returned table object is the set of all columns in the table we operate, and the columns attribute is the set of all column features.

These column features, called column metadata, have the following fields

For the getschematable method, metadata about each column is returned in the following order:

Datareader column description
The name of the columnname column; it may not be unique. If the name cannot be determined, a null value is returned. This name always reflects the recent renaming of columns in the current view or command text.
The sequence number of the columnordinal column. It has zero bookmarked columns (if any) for rows. Other columns are numbered from the beginning. This column cannot contain null values.
Maximum possible length of the columnsize column. For columns with a fixed-length data type, it is the size of the data type.
Numericprecision if providertype is a numeric data type, it is the maximum precision of the column. The accuracy depends on the column definition. If providertype is not a numeric data type, it is null.
Numericscale if providertype is dbtype_decimal or dbtype_numeric, it is the number of digits to the right of the decimal point. Otherwise, it is null.
Isunique true: any two rows in the base table (the table returned by basetablename) cannot have the same value in this column. If this column is a key, or if there is a unique type constraint that applies only to this column, isunique must be true. False: In the base table, this column can contain duplicate values. The default value of this column is false.
Iskey true: This column is a column that uniquely identifies a group of columns in a row set. A group of columns whose iskey is set to true must uniquely identify a row in the row set. This column is not required to be the smallest column set. This set of columns can be generated from the primary key, unique constraint, or unique index of the base table. False: This column is not required to uniquely identify the row.
Baseservername the name of the Microsoft SQL server instance used by sqldatareader.
Basecatalogname contains the name of the directory in the data storage area of the column. If the name of the base directory cannot be determined, the value is null. The default value of this column is null.
The name of the column in The basecolumnname data storage area. If an alias is used, it may be different from the column name returned in the columnname column. If the base column name cannot be determined, or if the row set column is exported from the column in the data storage area but not equal to this column, it is null. The default value of this column is null.
Baseschemaname contains the name of the schema in the data storage area of the column. If the name of the base architecture cannot be determined, the value is null. The default value of this column is null.
Basetablename contains the name of the table or view in the data storage area of the column. If the base table name cannot be determined, it is null. The default value of this column is null.
Datatype maps to the. NET Framework type of the column.
Allowdbnull is set if the user can set this column to null, or if the provider cannot determine whether the user can set this column to null. Otherwise, this value is not set. A column may contain null values even if it cannot be set to null.
Indicates the Data Type of the providertype column. If the column data types of different rows are different, it must be an object. This column cannot contain null values.
Isaliased: true if the column name is an alias; otherwise, false.
Isexpression: true if this column is an expression; otherwise, false.
Isidentity: true if this column is the ID column; otherwise, false.
Isautoincrement true: This column is assigned a value to the new row in a fixed increment. False: The column is not assigned a value to the new row in a fixed increment. The default value of this column is false.
Isrowversion: this value is set if the column contains the unchanged row identifier that cannot be written and there are no meaningful values except the row ID.
Ishidden: true if the column is hidden; otherwise, false.
Islong is set if the column contains a binary long object (BLOB) that contains very long data. Very long data is defined for the provider.
Isreadonly: true if the column cannot be modified; otherwise, false.