Welcome to another article in our series on working with SQL Server data in a .Net application. In this article, we will explore how to work with SQL Server schema information and build a DataSet in code that mirrors the schema of any SQL Server database.

In a previous article, we discussed how to read schema information in ADO.Net and how to build a DataSet with code. Now, we will combine these concepts to create a data set that reflects the tables and relationships of a SQL Server database.

To demonstrate this, we will use a sample console application. The code for the application is included in the article, along with the script for the sample database. You can download the code and follow along.

The first step is to establish a connection to the SQL Server database. We create a SqlConnection object and set the connection string to specify the database we want to connect to. Then, we open the connection.

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

Once the connection is established, we can retrieve the schema information using the GetSchema() method of the SqlConnection object. We can retrieve information about tables, columns, and index columns.

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

With the schema information in hand, we can now create a DataSet object that will mirror the schema of the SQL Server database. We use nested foreach loops to iterate through the schema tables and add the tables, columns, and relationships to the data set.

DataSet testData = new DataSet();

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
}

Within the outer loop, we select the columns for each table and add them to the corresponding table in the data set.

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

We can also specify the data types and nullability of the columns using the information from the schema.

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;
}

After adding the columns, we can specify the primary key column(s) for each table.

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;

To add the foreign key constraints, we need to query the system schema views to retrieve the necessary information.

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();

    while (relRdr.Read())
    {
        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);
    }
}

Finally, don’t forget to close the connection when you are finished:

conn.Close();

Now, you have a DataSet that mirrors the tables, columns, and relationships of the SQL Server database. You can use this DataSet to work with the data, add records, and enforce relationships and constraints.

Thank you for reading this article. If you have any questions or suggestions for future topics, please let us know in the comments.