O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required