Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
[ Team LiB ] Recipe 6.9 Retrieving Constraints from a SQL Server Database Problem You need to programmatically define constraints in a DataSet and retrieve constraint information defined in a SQL Server database. Solution Use the INFORMATION_SCHEMA views and SQL Server system tables to get information about primary keys, foreign keys, and check constraints. | Team LiB Recipe 6.9 Retrieving Constraints from a SQL Server Database Problem You need to programmatically define constraints in a DataSet and retrieve constraint information defined in a SQL Server database. Solution Use the INFORMATION_SCHEMA views and SQL Server system tables to get information about primary keys foreign keys and check constraints. The sample code contains one event handler Get Constraints Button.Click Uses a SQL select statement to load the specified constraint information primary key foreign key or check constraint from the INFORMATION_SCHEMA views into a DataTable. The C code is shown in Example 6-27. Example 6-27. File ConstraintForm.cs Namespaces variables and constants using System using System.Configuration using System.Data using System.Data.SqlClient using System.Data.OleDb private const String GETPRIMARYKEYCONSTRAINTS SELECT tc.CONSTRAINT_NAME tc.TABLE_NAME kcu.COLUMN_NAME kcu.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME kcu.CONSTRAINT_NAME WHERE tc.CONSTRAINT_TYPE PRIMARY KEY ORDER BY tc.TABLE_NAME kcu.COLUMN_NAME kcu.ORDINAL_POSITION private const String GETFOREIGNKEYCONSTRAINTS SELECT rc.CONSTRAINT_NAME rc.UPDATE_RULE rc.DELETE_RULE kcuP.TABLE_NAME ParentTable kcuC.TABLE_NAME ChildTable kcuP.COLUMN_NAME ParentColumn kcuC.COLUMN_NAME ChildColumn FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuP ON rc.UNIQUE_CONSTRAINT_NAME kcuP.CONSTRAINT_NAME LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuC ON rc.CONSTRAINT_NAME kcuC.CONSTRAINT_NAME AND kcuP.ORDINAL_POSITION kcuC.ORDINAL_POSITION ORDER BY kcuP.TABLE_NAME kcuC.TABLE_NAME kcuP.ORDINAL_POSITION private const String GETCHECKCONSTRAINTS SELECT tc.TABLE_NAME tc.CONSTRAINT_NAME cc.CHECK_CLAUSE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON tc.CONSTRAINT_NAME cc.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE CHECK .
