Click here to Skip to main content
15,845,115 members
Articles / Programming Languages / SQL
Tip/Trick

Documenting SQL Stored Procs in C# Code

Rate me:
Please Sign up or sign in to vote.
4.40/5 (16 votes)
5 Oct 2015CPOL2 min read 16.6K   12   4
Yes, you CAN document your 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:

C#
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:

C#
/// <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):

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

The Tips

  1. Use properties to document your stored procs.
  2. Use the .NET framework to generalize your code (using StackTrace to get the property name inside its own getter).
  3. 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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Paddedwall Software
United States United States
I've been paid as a programmer since 1982 with experience in Pascal, and C++ (both self-taught), and began writing Windows programs in 1991 using Visual C++ and MFC. In the 2nd half of 2007, I started writing C# Windows Forms and ASP.Net applications, and have since done WPF, Silverlight, WCF, web services, and Windows services.

My weakest point is that my moments of clarity are too brief to hold a meaningful conversation that requires more than 30 seconds to complete. Thankfully, grunts of agreement are all that is required to conduct most discussions without committing to any particular belief system.

Comments and Discussions

 
GeneralMy vote of 5 Pin
linuxjr7-Oct-15 14:19
professionallinuxjr7-Oct-15 14:19 
SuggestionCareful about SP naming Pin
Master.Man19807-Oct-15 5:28
Master.Man19807-Oct-15 5:28 
GeneralRe: Careful about SP naming Pin
#realJSOP7-Oct-15 5:43
mve#realJSOP7-Oct-15 5:43 
GeneralRe: Careful about SP naming Pin
Master.Man19807-Oct-15 5:59
Master.Man19807-Oct-15 5:59 
Quote:
Naming collisions are possible in pretty much ANY programming environment
Yes. But, It's better to minimize the possibility, if there is an already known issue.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.