Getting the Schema of a Stored Procedure Results Set

Visual Studio is pretty good at discovering the schema of a stored procedure if the procedure doesn't use temporary tables and returns only one result set. You can drag the stored procedure on to a DataSet or Linq to Sql Design Surface and it is generated for you. In real life, stored procedures don’t fit into that neat box.

Since I am lazy and don’t like to build DataSets by hand, I wrote this little class. Note that I am actually executing the T-SQL command. Any side effect of the stored procedure will take place; this is acceptable in my environment, it may not be in yours.

I am getting less information from this method than I get from static sources like SQL Server’s SMO; for example, I am not getting the length of strings. I am getting enough information to do what I want to do.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace TsqlDesriptionUtil
{
    public class TsqlCommandSchema
    {
        public static List<TableDescription> GetFieldDescription(
            string conString, string commandString)
        {
            var rVal = new List<TableDescription>();
            int tableNumber = 1;
            using (var con = new SqlConnection(conString))
            {
                using (var com = new SqlCommand(commandString, con))
                {
                    con.Open();
                    // WARNING: I am executing the command, beware of side effects!
                    SqlDataReader reader = 
                        com.ExecuteReader(CommandBehavior.CloseConnection);
                    // Table Loop
                    do
                    {
                        var fieldDescriptionList = new List<FieldDescription>();
                        // Field Loop
                        for (int i = 0; i < reader.FieldCount; ++i)
                        {
                            Type fieldType = reader.GetProviderSpecificFieldType(i);
                            fieldDescriptionList.Add(
                                new FieldDescription(reader.GetName(i), 
                                reader.GetDataTypeName(i), reader.GetFieldType(i)));
                        }
                        // T-SQL doesn't give table names, so I will make up my own
                        rVal.Add(new TableDescription(string.Format(
                            "Table{0}", tableNumber++), fieldDescriptionList));
                    } while (reader.NextResult());
                    con.Close();
                }
            }
            return rVal;
        }
    }
    public class TableDescription
    {
        public TableDescription(string tableName, List<FieldDescription> fields)
        {
            this.TableName = tableName;
            this.Fields = fields;
        }
        public string TableName { get; set; }
        public List<FieldDescription> Fields { get; set; }
        public int Count
        {
            get
            {
                if (this.Fields != null)
                    return this.Fields.Count;
                else
                    throw new Exception("Fields not loaded");
            }
        }
    }
    public class FieldDescription
    {
        public FieldDescription(string fieldName, string dataTypeName, Type fieldType)
        {
            this.FieldName = fieldName;
            this.DataTypeName = dataTypeName;
            this.FieldType = fieldType;
        }
        public string FieldName { get; set; }
        public string DataTypeName { get; set; }
        public Type FieldType { get; set; }
        public string XsdTypeName
        {
            get
            {
                Type t = this.FieldType;
                return getXsdTypeNameForType(t);
            }
        }

        private static string getXsdTypeNameForType(Type t)
        {
            string rVal = string.Empty;
            //.NET Framework type XML Schema (XSD) type
            switch (t.FullName)
            {
                case "System.Boolean": rVal = "Boolean"; break;
                case "System.Byte": rVal = "unsignedByte"; break;
                case "System.Byte[]": rVal = "base64Binary"; break;
                case "System.DateTime": rVal = "dateTime"; break;
                case "System.Decimal": rVal = "decimal"; break;
                case "System.Double": rVal = "Double"; break;
                case "System.Int16": rVal = "short"; break;
                case "System.Int32": rVal = "int"; break;
                case "System.Int64": rVal = "long"; break;
                case "System.SByte": rVal = "Byte"; break;
                case "System.String": rVal = "string"; break;
                case "System.String[]": rVal = "ENTITIES"; break;
                case "System.TimeSpan": rVal = "duration"; break;
                case "System.UInt16": rVal = "unsignedShort"; break;
                case "System.UInt32": rVal = "unsignedInt"; break;
                case "System.UInt64": rVal = "unsignedLong"; break;
                case "System.Uri": rVal = "anyURI"; break;
                case "System.Xml.XmlQualifiedName": rVal = "QName"; break;
            }
            return rVal;
        }
    }
}

I compiled the above C# source file into an assembly. Here is a xUnit test that demonstrates its use.

[Fact(DisplayName = "Run SP 001")]
public void RunSp001()
{
    string conString = @"Data Source=localhost;Initial Catalog=My_Db;" + 
        "Integrated Security=True";
    string commandString = "EXEC  GetBigTableDataUseTempTab 101";
    List<TableDescription> rVal = 
        TsqlCommandSchema.GetFieldDescription(conString, commandString);

    foreach (var item in rVal) 
    {
        Console.WriteLine(string.Format("{0}: {1}", item.TableName, item.Count));
        foreach(var field in item.Fields)
            Console.WriteLine(
                string.Format("\t{0}\t{1}\t{2}\t{3}", 
                    field.FieldName, 
                    field.DataTypeName, 
                    field.FieldType, 
                    field.XsdTypeName));
    }
}