Dot Net Tricks

<%-- 
Name: CommandDumper.cst
Author: Robert Hinojosa
Description: Dumps the essentials of all of the stored procedures in a given database.
--%>
<%@ CodeTemplate Language="C#" TargetLanguage="Text" Src="" Inherits="" Debug="False" Description="Template description here." %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="System.Data" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Assembly Name="SchemaExplorer.SqlSchemaProvider" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Text" %>

<% 
    
    // You can also just use a DatabaseSchema property to set all this stuff through the property grid.
    // I'm doing this to show how it can be done programatically
    string connectionString = "server=(local);database=Northwind;Integrated Security=true;";
    SchemaExplorer.SqlSchemaProvider provider = new SchemaExplorer.SqlSchemaProvider();
    DatabaseSchema db = new DatabaseSchema(provider, connectionString);
    
    Response.IndentLevel = 1;
    Response.WriteLine("Database Name:", db.Name);
    string currentCommand = "";
    string currentCommandProc = "";
    string exceptionMessage = "\n\n//TODO: I can't create sql objects when doing discovery, please skip me.{0}\n\nException:\n{1}\n\nProcedure: \n{2}";
    string generalException = "\n\n//TODO: An error occured. {0}\n\nException:\n{1}\n\nProcedure: \n{2}";
    
    try
    {
        //Loop through the Procedures
        foreach(CommandSchema proc in db.Commands)
        {
            try
            {
                Response.IndentLevel = 2;
                currentCommand = proc.Name;
                currentCommandProc = proc.CommandText;
                
                //Loop through all input params, you would probably iterate through all of the output params as well.
                StringBuilder sb = new StringBuilder();
                for(int i=0;i<proc.AllInputParameters.Count;i++)
                    sb.AppendFormat("{0},", proc.AllInputParameters[ i ].Name);
                    
                Response.WriteLine("- Procedure Name: {0} - Params: {1}", proc.Name, sb.ToString().TrimEnd(','));
                
                int idx = 0;
                //Since a procedure can have more than 1 result set, display them all
                foreach(CommandResultSchema result in proc.CommandResults)
                {
                    idx++;
                    Response.IndentLevel = 3;
                    Response.WriteLine("+ Returned Result Set #: {0}", idx);
                    
                    //iterate through columns of result set and their type
                    foreach(CommandResultColumnSchema column in result.Columns)
                    {
                        Response.IndentLevel = 4;
                        Response.WriteLine("* Name: {0} - Type: {1}({2})", column.Name, column.NativeType, column.Size);
                    }
                }
            }
            catch(Exception exc)
            {
                //Things such as temporary tables aren't good for discovery since codesmith isn't actually executing the proc, 
                //That object does not exist, and usually an exception is thrown.
                //Consider using in memory Table Variables instead.  
                Response.WriteLine(exceptionMessage, currentCommand, exc.ToString(), currentCommandProc);
            }
        }
    }
    catch(Exception exc)
    {
        Response.WriteLine(generalException, currentCommand, exc.ToString(), currentCommandProc);
    }
%>