Ok, yes, I work for Codesmith! Let's put that aside for a second, since i've been a huge Codesmith fan long before I became a proud Codesmith Employee, that's what makes my job that much better, because I truly believe in this product 110%!
It's soo easy to use codesmith that quite honestly, if you're not having Codesmith generate at least 25% of your work, in the future, i'm certain you will wake up one day and realize how much time you could have saved and think of me, ok, maybe not me, but this post ![Wink [;)]](/emoticons/emotion-5.gif)
.
Time for a Trick!
It was asked in the community how to navigate through all of the procedures in a database, so in 10 mins, I have created a nice little stored procedure dumper and explicitly used only the SchemaExplorer API, to show how to use as much of it as possible.
<%--
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);
}
%>
|