Recipe 10.11 Creating DataSet Relationships from SQL Server Relationships

Problem

You need to create relationships between DataTable objects within your DataSet at runtime based on the relationships that are defined in your SQL Server database.

Solution

Use INFORMATION_SCHEMA views and system tables to create relationships automatically at runtime.

The schema of table TBL1011a used in this solution is shown in Table 10-5.

Table 10-5. TBL1011a schema

Column name	Data type	Length	Allow nulls?
a	int	4	No
b	int	4	No
c	int	4	No

The schema of table TBL1011b used in this solution is shown in Table 10-6.

Table 10-6. TBL1011b schema

Column name	Data type	Length	Allow nulls?
d	int	4	No
e	int	4	No
a2	int	4	No
b2	int	4	No

The sample code creates a DataSet containing the Orders table and Order Details table from the Northwind sample database. The tables TBL1011a and TBL1011b—related through a multicolumn key—are also added to the DataSet. Next, the result set of a query of the INFORMATION_SCHEMA views are examined to determine the relationships specified in the data source between the tables in the DataSet. DataRelation objects are created in the DataSet for the identified relationships.

The C# code is shown in Example 10-11.

Example 10-11. File: AutoDataRelationForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;

//  . . . 

DataSet ds = new DataSet( );

SqlDataAdapter da;

// Add the Orders and Order Details tables to the DataSet.
da = new SqlDataAdapter("SELECT * FROM Orders",
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.Fill(ds, ORDERS_TABLE);
da = new SqlDataAdapter("SELECT * FROM [Order Details]",
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.Fill(ds, ORDERDETAILS_TABLE);

// Add the TBL1011a and TBL1101b tables to the DataSet.
da = new SqlDataAdapter("SELECT * FROM TBL1011a",
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.Fill(ds, PARENTMULTICOLKEYTABLE);
da = new SqlDataAdapter("SELECT * FROM TBL1011b",
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.Fill(ds, CHILDMULTICOLKEYTABLE);

StringBuilder result = new StringBuilder( );

String sqlText = "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 rc.CONSTRAINT_NAME, kcuP.ORDINAL_POSITION";

// Create the connection and command to retrieve constraint information.
SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand(sqlText, conn);

// Fill the DataReader with constraint information.
conn.Open( );
SqlDataReader reader = cmd.ExecuteReader( );

String prevConstraintName = "";
String constraintName = "";
String parentTableName = "";
String childTableName = "";
bool updateCascade = false;
bool deleteCascade = false;
String relationName = "";

// Arrays to store related columns from constraints in DataReader
ArrayList parentColsAL = new ArrayList( );
ArrayList childColsAL = new ArrayList( );
DataColumn[] parentCols;
DataColumn[] childCols;

DataRelation dr;

bool isRecord = false;
// Iterate over the constraint collection for the database.
do
{
    // Read the next record from the DataReader.
    isRecord = reader.Read( );

    // Store the current constraint as the previous constraint name 
    // to handle multicolumn-based relations.
    prevConstraintName = constraintName;

    // Get the current constraint name.
    constraintName = isRecord ? reader["CONSTRAINT_NAME"].ToString( ) : "";

    // If the constraint name has changed and both tables exist,
    // create a relation based on the previous constraint column(s).
    if (prevConstraintName != "" &&
        constraintName != prevConstraintName &&
        ds.Tables.Contains(parentTableName) &&
        ds.Tables.Contains(childTableName))
    {
        // Create the parent and child column arrays.
        parentCols = new DataColumn[parentColsAL.Count];
        parentColsAL.CopyTo(parentCols);
        childCols = new DataColumn[childColsAL.Count];
        childColsAL.CopyTo(childCols);

        // Create the relation name based on the constraint name.
        relationName = prevConstraintName.Replace("FK_","RELATION_");

        // Create the relation and add it to the DataSet.
        dr = new DataRelation(relationName, parentCols, childCols,
            true);
        ds.Relations.Add(dr);
        // Set the cascade update and delete rules.
        dr.ChildKeyConstraint.UpdateRule =
            updateCascade ? Rule.Cascade : Rule.None;
        dr.ChildKeyConstraint.DeleteRule =
            deleteCascade ? Rule.Cascade : Rule.None;

        // Clear the parent and child column arrays for the previous
        // constraint.
        parentColsAL.Clear( );
        childColsAL.Clear( );

        result.Append("Added relationship " + relationName +
            " to DataSet." + Environment.NewLine);
    }                

    if (isRecord)
    {
        // Store the current parent and child table names.
        parentTableName = reader["ParentTable"].ToString( );
        childTableName = reader["ChildTable"].ToString( );
        // Store the cascade update and delete for the current
        // constraint.
        updateCascade = (reader["UPDATE_RULE"].ToString( ) ==
            "CASCADE");
        deleteCascade = (reader["DELETE_RULE"].ToString( ) ==
            "CASCADE");

        // Add the parent and child column for the current constraint
        // to the ArrayLists, if both parent and child are in DataSet.
        if (ds.Tables.Contains(parentTableName) &&
            ds.Tables.Contains(childTableName))
        {
            parentColsAL.Add(ds.Tables[parentTableName].Columns[
                reader["ParentColumn"].ToString( )]);
            childColsAL.Add(ds.Tables[childTableName].Columns[
                reader["ChildColumn"].ToString( )]);
        }
    }
} while(isRecord);

// Close the DataReader and connection.
reader.Close( );
conn.Close( );

resultTextBox.Text = result.ToString( );

Discussion

There is no ADO.NET data provider method that automatically returns information about table relationships that are defined in a database. To get the relation information, information views in SQL Server must be queried.

The information required to reconstruct relationships between tables requires a query that pulls together information from two different information views, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE, and requires two joins into the latter table to obtain required information for both unique and foreign key constraints. The REFERENTIAL_CONSTRAINTS table contains a row for each foreign key constraint in the database. The KEY_COLUMN_USAGE table contains one row for each row constrained as a key in the database.

The solution starts by loading a DataSet with two sets of tables. These tables are the Orders and Order Details tables from Northwind and a pair of sample tables—TBL0011a and TBL0011b—which demonstrate retrieving relation information for tables related on more than one column.

Next, the query to retrieve the data relationship information is constructed. The SQL statement used is:

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 rc.CONSTRAINT_NAME, kcuP.ORDINAL_POSITION

This statement retrieves the constraint information needed to create the relations in the DataSet based on the schema information in the database. Specifically, the columns returned are shown in Table 10-7.

Table 10-7. Relation query columns

Column	Description
CONSTRAINT_NAME	Name of the constraint
UPDATE_RULE	NO ACTION or CASCADE
DELETE_RULE	NO ACTION or CASCADE
ParentTable	Name of the parent table in the relationship
ChildTable	Name of the child table in the relationship
ParentColumn	Name of the column in the parent table
ChildColumn	Name of the column in the child table

For relationships that are based on more than one column, there will be more than one row in the result set that must be combined to create the DataRelation object in the DataSet. Notice that the statement groups the results that are returned by the constraint name, grouping all records related to a single relation. The result set is ordered by the ORDINAL_POSITION field that defines the order of the columns in the relation. When iterating over the query results, if both the parent and child names are contained in the result set, a relationship has been identified and processing continues. For those relationships, the column names for the parent and child tables are loaded into arrays allowing relations based on multiple columns to be created.

Once all of the columns for a relation have been loaded (this is determined by a change in the constraint name and the names of the parent and child tables), the DataRelation is created in the DataSet based on the parent and child column names in the arrays. The update and delete cascade rules are set for the relation. Although not necessary, the sample names the relation based on the name of the constraint without the FK_ prefix. Once the DataRelation is created, processing of the result set resumes to determine the remaining relations.