Documenting SQL Stored Procs in C# Code

Introduction

I'm writing code that uses ADO.NET to connect to and use a SQL Server database. As is typical of most SQL Server databases, this one has several stored procedures. I wanted to come up with a way to allow the C# code to document the fact that there are stored procedures, including the parameter list for said stored procedure. The following is my solution.

The Code

I have a static class in my project called Globals that contains all the crap that doesn't really belong anyplace in particular, but that needs to be generally accessible from anywhere in the application. My idea was to create a property that uses the stored procedure's actual name, and that returns that name to the calling method. Of course, it would be a simple matter to do something like this:

public static string sp_GetResponsesForQuestion
{ 
    get 
    {
        return "sp_GetResponsesForQuestion"; 
    } 
}

That's not very cool, not to mention it can get kinda tedious, so I decided I needed to simply return the property name from its getter. I exploited the StackTrace object by getting the first StackFrame in the list of frames, retrieving the method name, and stripping the "get_" from the beginning of the name.

Now, all of my stored proc name properties can return their names without using hard-wired string values. With appropriate comments (that still have to be manually added), my property looks like this:

/// <summary> 
/// <para>Gets the responses for the specified wquestion. Parameters:</para>
/// <para>&#160;</para>
/// <para>- @questionID int</para>
/// <para>- @date date=NULL</para>
/// <para>&#160;</para>
/// <para>If your parameter list doesn't include a date, ALL responses for the 
/// question are returned. Otherwise, only responses for the specified date are returned.
/// </para>
/// </summary>
public static string sp_GetResponsesForQuestion 
{ 
    get { return new StackTrace().GetFrame(0).GetMethod().Name.Replace("get_", ""); } 
}

The end result is that I can now just use the property names with intellisense to ensure that I'm using the correct stored proc (the following code snippet is from my own code and is only presented to serve as an example):

SqlParameter[] parameters = new SqlParameters[]
{
    new SqlParameter("@questionID", this.QuestionID)
};
this.ProcessData(Common.DBObject2.GetData(Globals.sp_GetResponsesForQuestion, parameters);

The Tips

0. Use properties to document your stored procs.
1. Use the .NET framework to generalize your code (using StackTrace to get the property name inside its own getter).
2. You can put line breaks in intellisense comments by using the <para> tag, and if you need a blank line, enclose a non-terminating space (#160) within a para tag.

History

• 5^th October, 2015 - Initial publication (and an update to include an intellisense tip)