This is another in an occasional series of short articles about working with SQL Server data in a .Net application, to explain how to work with SQL Server data, what is possible, and how to work with the issues that can come up. This article combines the information presented in two previous articles in the series.

In the article on Reading Schema Information in ADO.Net  I explored the options for reading the schema information for a SQL Server database using ADO.Net objects. In the article on Building a DataSet with Code I showed how to build a data set in code that includes the keys and relationships of a SQL Server database. In this article I will put the two ideas together and show how you could build a data set in code that mirrors the schema information from any SQL Server database you connect to.

Sample Application

I created a simple console application to try out the options for reading a schema and using it to build a DataSet. The .zip file with the code for the test is included below. I added a class called SchemaTest with one method, BuildDatabaseFromSchema(). The namespace SchemaToDataSetPart2 comes from the name I gave to the project. Here is the code for the basic class:

using System;
using System.Data;
using System.Data.SqlClient;
namespace SchemaToDataSetPart2
{
    class SchemaTest
    {
        public void BuildDatabaseFromSchema()
        {
            // Test code goes here
        }
    }
}

To run the test code I added code to the Main() method to create an instance of my class and run the test.

        static void Main(string[] args)
        {
            SchemaTest stst = new SchemaTest();
            stst.BuildDatabaseFromSchema();
        }

The sample database is the same one I used in another article in the series, SQL Server From .Net: Building a DataSet with Code.

The script for the tables and relations in the sample database is also included.

Recreating a SQL Server Database

As described in the previous article, the SqlConnection object has a GetSchema() method that retrieves metadata information from whatever SQL Server database you are connected to. Now we are going to read that information and use it to build an equivalent DataSet object in .Net that reproduces the tables and relationships of the original database.

The first step is to create a connection to the SQL Server database, as outlined in the previous articles in the series:

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=(local);Initial Catalog=testDBExamples;Integrated Security=True";
conn.Open();

This code creates the SqlConnection object we will use to connect to the database. The first line creates a new SqlConnection object I am calling “conn.” The next line initializes the connection string that specifies that the database is local, includes the name of the default database, my sample database, and specifies Windows Integrated Security. The last line opens the connection. For more information about the SqlConnection object and the options available see the previous article in the series: SQL Server From .Net: Making a Connection. 

Next we will retrieve the metadata using the GetSchema() method of the SqlConnection object. This code gets the Tables, Columns, and IndexColumns collections for the database:

DataTable tableNames = conn.GetSchema("Tables");
DataTable cols = conn.GetSchema("Columns");
DataTable ixcols = conn.GetSchema("IndexColumns");

In this code, DataTable objects are returned that represent some of the collections of metadata that are available through the GetSchema() method. The list of collections is described in the BOL documentation. Some of the collections apply to all ADO.Net data sources and some apply only to SQLServer databases. The collections of schemas that are defined for all data sources are described here and the collections specific to SQL Server are described in this article.

The next step is to create the test data set. This code uses the constructor to create a DataSet object that I am calling “testData”:

DataSet testData = new DataSet();

Then we can use nested “foreach” loops to spin through the schema tables we retrieved above, to add the tables and their columns and relationships to the data set. The outer loop uses the data returned in the “Tables” collection to add all the tables, and the inner loop builds the columns in each table.

This is the beginning of the outer loop for the tables:

foreach (DataRow tdr in tableNames.Rows)
{
    string tName = tdr["TABLE_NAME"].ToString();
    if (tName.Length >= 3 && tName.Substring(0, 3) == "sys")
        continue;
    testData.Tables.Add(tName);   // Create the table
    // (Select and add the columns here)
}

The foreach statement goes through the rows of the DataTable with the “Tables” schema and makes available a DataRow object called “tdr” that can be used to access the values in the columns in the row. Then we use the DataRow object to retrieve the name of the table from the TABLE_NAME field of the Tables schema DataTable. The “if” statement first checks that names are at least 3 characters long.  If the name is not at least 3 characters in length the Substring() function in the second part of the “If” statement will fail. The second part of the “if” statement checks whether the table name starts with the letters “sys” to determine if it is a system-defined table. If the name starts with “sys” we use the “continue” statement to skip the schema row and continue with the next. This code is of course assuming that there are no user tables with “sys” as the first three letters of the name.

If we got through the check for system tables, the next line uses the Add() method of the Tables collection of our test database to create a table using the table name from the schema.

The next step is to add the columns to the table. Within the outer foreach loop that creates the tables we will add this code to select the rows from the Columns collection that relate to the table we just added.

DataRow[] tableCols = cols.Select("TABLE_NAME = '" + tName + "'");

The Select() method of the DataTable object applies a filter and returns a DataRow array with the resulting rows from the table. The filter here specifies the column name from the “Columns” collection, “TABLE_NAME,”  that specifies the table the column belongs to. The table name we retrieved from the Tables collection is included by using the variable “tName.” The condition is applied to the table with the Columns collection to retrieve a list of DataRow objects that includes only the rows of the Columns collection that match the table name we are creating.

Next we can add the inner foreach loop that loops through the DataRows we selected from the Columns collection to retrieve the information to create the columns of the table. This code goes within the loop above after the line that selects the columns for the table and before the closing “}”:

foreach (DataRow cdr in tableCols)
{
    string cName = cdr["COLUMN_NAME"].ToString();
    testData.Tables[tName].Columns.Add(cName);
}

This foreach loop retrieves the column name from the schema and uses the Add() method of the Columns collection to create a column by that name and add it to the Columns collection of the table we created above.

Next, still within the foreach loop that creates the columns, I used a switch statement to show how to designate different data types for the column. This example only includes four of the possible data types, if you use others you will need to add them here:

switch (cdr["DATA_TYPE"].ToString()) 
{
    case "varchar":
      testData.Tables[tName].Columns[cName].DataType = typeof(string);
      testData.Tables[tName].Columns[cName].MaxLength = Convert.ToInt32(cdr["CHARACTER_MAXIMUM_LENGTH"]);
      break;
    case "int":
      testData.Tables[tName].Columns[cName].DataType = typeof(int);
      break;
    case "decimal":
      testData.Tables[tName].Columns[cName].DataType = typeof(decimal);
      break;
   case "date":
      testData.Tables[tName].Columns[cName].DataType = typeof(DateTime);
      break;
}

Since the column is now part of the definition, we use the complete specification of the column by name in the collection of columns, in the table within the collection of tables in the test DataSet.

To specify whether the column can accept NULL values, we can use the “IS_NULLABLE” column in the schema to set the corresponding property in the column in our test DataSet:

if (cdr["IS_NULLABLE"].ToString() == "YES")
    testData.Tables[tName].Columns[cName].AllowDBNull = true;
else
    testData.Tables[tName].Columns[cName].AllowDBNull = false;

After the inner loop to create all the columns, we can add the following code to specify the primary key column(s) for the table. Note we are still within the foreach loop that creates the tables.

DataRow[] keyDefs = ixcols.Select("table_name = '" + tName + "'"); 
DataColumn[] keys = new DataColumn[keyDefs.Length];
for (int i=0; i < keyDefs.Length; ++i)
{
    keys = testData.Tables[tName].Columns[keyDefs["column_name"].ToString()];
}
testData.Tables[tName].PrimaryKey = keys;

The first line uses the Select() method of the DataTable with the IndexColumns collection to select the indexes related to the current table we are creating. Since there can be multiple columns contributing to the key, you need to supply an array of DataColumn objects containing the key column(s). The next line creates this collection using the number of rows returned by the Select(), which should be equal to the number of fields contributing to the key.  Next is a “for” loop that collects references to all the columns included in the primary key of the table into the keys array. Then the last line assigns the resulting collection of rows to the PrimaryKey collection of the table in the data set.

Adding the Foreign Keys

Although there is a collection in the schema called “ForeignKeys,“  the data returned does not specify the column names, so to add the foreign key constraints to our data set I created this query of the system schema views. Thanks to Phil Factor for the excellent Stairway series on the metadata collections at the SSC website, Stairway to Exploring Database Metadata. where I got the information to build this query.

Select fkeys.name As keyName, parent.name As parentTable, pcol.name as parentColumn,
    ref.name As referencedTable, rcol.name As referencedColumn, fkeys.delete_referential_action_desc,
    fkeys.update_referential_action_desc
from sys.foreign_keys fkeys
  Join sys.foreign_key_columns fccol
    On fkeys.object_id = fccol.constraint_object_id
  Join sys.objects parent
      On fkeys.parent_object_id = parent.object_id
  Join sys.objects ref
      On fkeys.referenced_object_id = ref.object_id
  Join sys.columns pcol
      On fccol.parent_object_id = pcol.object_id
         And fccol.parent_column_id = pcol.column_id
  Join sys.columns rcol
    On fccol.referenced_object_id = rcol.object_id
       And fccol.referenced_column_id = rcol.column_id;

Then I added code to create a SqlCommand object and a SqlDataReader to send this query and access the foreign key information. This code goes after the foreach loop that adds all the tables, so that all the tables are defined when we add the relations between them.

using (SqlCommand relCmd = new SqlCommand())
{
    relCmd.Connection = conn;
    relCmd.CommandText = "Select fkeys.name As keyName, parent.name As parentTable, pcol.name as parentColumn, ref.name As referencedTable, "
    + " rcol.name As referencedColumn, fkeys.delete_referential_action_desc, fkeys.update_referential_action_desc "
    + " from sys.foreign_keys fkeys Join sys.foreign_key_columns fccol On fkeys.object_id = fccol.constraint_object_id "
    + " Join sys.objects parent On fkeys.parent_object_id = parent.object_id "
    + " Join sys.objects ref On fkeys.referenced_object_id = ref.object_id "
    + " Join sys.columns pcol On fccol.parent_object_id = pcol.object_id And fccol.parent_column_id = pcol.column_id "
    + " Join sys.columns rcol On fccol.referenced_object_id = rcol.object_id And fccol.referenced_column_id = rcol.column_id";
    SqlDataReader relRdr = relCmd.ExecuteReader();
}

The first line creates a “using” block that creates a SqlCommand object. The  SqlCommand object “relCmd” is defined for the entire block, that extends through the final “}” in the example. Once the using block is finished the SqlCommand object will automatically be disposed.

After the using statement we assign the SqlConnection to the SqlCommand object and specify the text of the T-SQL command that specifies the query. Then the ExecuteReader() method of the command object returns a SqlDataReader object we are calling relRdr.

Next I added a while loop to process the records from the result of the query one at a time. This is still within the using block of the SqlCommand.

while (relRdr.Read())
{
    // use the table and column names to create the relation
    string relName = relRdr["keyName"].ToString();
    string paremtTbl = relRdr["parentTable"].ToString();
    string parentCol = relRdr["parentColumn"].ToString();
    string refTbl = relRdr["referencedTable"].ToString();
    string refCol = relRdr["referencedColumn"].ToString();
    DataRelation fk2 = new DataRelation(relName, testData.Tables[paremtTbl].Columns[parentCol], testData.Tables[refTbl].Columns[refCol]);
    testData.Relations.Add(fk2);
}

The condition of the while loop will be true as long as there are rows to be read from the collection of foreign keys.

Within the while loop the values in each row can be accessed by using the column names as an index into the data reader columns. I assigned the values from the columns to string variables to make the row that creates the DataRelation object easier to read. The DataRelation object, called fk2, is created with the relationship name from the schema data. In the next row the relation is added to the Relations collection of the DataSet object. The table columns of the source and destination tables are specified using the table names as indexes into the Tables collection of the DataSet and the column names as indexes into the Columns collection.

After that (still within the while loop) we get a reference to the ChildKeyConstraint object of the DataRelation we just created to set the update and delete rules.  For each I used a switch on the value of the referential action description:

ForeignKeyConstraint fkc2 = fk2.ChildKeyConstraint;
switch (relRdr["delete_referential_action_desc"].ToString())
{
    case "NO_ACTION":
        fkc2.DeleteRule = Rule.None;
        break;
    case "CASCADE":
        fkc2.DeleteRule = Rule.Cascade;
        break;
    case "SET_NULL":
        fkc2.DeleteRule = Rule.SetNull;
        break;
    case "SET_DEFAULT":
        fkc2.DeleteRule = Rule.SetDefault;
        break;
}
switch (relRdr["update_referential_action_desc"].ToString())
{
    case "NO_ACTION":
        fkc2.UpdateRule = Rule.None;
        break;
    case "CASCADE":
        fkc2.UpdateRule = Rule.Cascade;
        break;
    case "SET_NULL":
        fkc2.UpdateRule = Rule.SetNull;
        break;
    case "SET_DEFAULT":
        fkc2.UpdateRule = Rule.SetDefault;
        break;
}

The switch statements in this example use values in the columns that specify the delete and update rules to set the corresponding property in the foreign key constraint we just created.  The first switch statement sets the DeleteRule property based on the value in the "delete_referential_action_desc" column, and the second uses the value in the "update_referential_action_desc" to set the UpdateRule property.

After you are finished with the connection you should close it to release the resources for other users:

conn.Close();

Now we have a DataSet that mirrors the tables, columns and relationships of the SQL Server database. In my previous article, SQL Server From .Net: Building a DataSet with Code http://www.sqlservercentral.com/articles/ADO.NET/171331/

I show how to work with the resulting DataSet, add data and react to violations of relationships and constraints.

There is a Problem

If you run this code to translate a database that has key constraints that include more than one column you will encounter a problem. To solve this you need to use a different form of the constructor for the DataRelation object that accepts an array of columns.

I have included my solution in the sample code provided. It is a bit complicated so I will not include the explanation here, but you should be able to tell how it works from the sample code and the comments. Let me know in the discussion if you can think of a better way to handle this.

Conclusion

This has been an exploration of the different ways the schema data from an SQL Server database can be accessed from a .Net program and used to build a DataSet using the schema definitions.

Thanks for your interest. If you have questions or suggestions for other topics in this series, please respond in the discussion.