C#: Keeping your stored procedure names maintainable

Hello World,Necessity is the mother of invention (cliched but very true) – as your hair starts greying, (and hairline begins to recede 😉 ) you suddenly start to realize the weight of these adages.
One such necessity arose out of my observation that stored procedure names were scattered all across a codebase I was working with. If you ever wanted to change the name of a stored procedure used some context, you had to search the entire codebase and replace all occurances. I did not like this approach and came up with the below approach:

  1. Create a type that holds stored procedure names grouped by module
  2. Read the name of stored procs from this type everywhere else. Now if a change is required it is in one sinlge place

A sample is given below:

   1:      /// <summary>

   2:      /// Holds the names of stored procedures used by the application
   3:      /// </summary>
   4:      public static class Commands
   5:      {
   6:          /// <summary> Holds the commands to fetch configuration data </summary>
   7:          public static class Config
   8:          {
   9:              /// <summary> The Command to Get a list of websites that are to be processed </summary>
  10:              public const string GetWebsites = "dbo.GetSomeConfig";
  12:              /// <summary> The command to get website specific configuration by it's name </summary>
  13:              public const string GetWebsiteConfiguration = "dbo.GetSomeMoreConfig";
  14:          }
  16:          /// <summary> Holds the commands to fetch Auth data </summary>
  17:          public static class Authentication
  18:          {
  19:              /// <summary> The command to fetch auth info </summary>
  20:              public const string GetAuthenticationType = "dbo.GetAuthType";
  22:              /// <summary> The command to validate user </summary>
  23:              public const string ValidateUser = "dbo.ValidateUser";
  25:                  }
  27:          /// <summary> Holds the commands to fetch related data </summary>
  28:          public static class AnotherModule
  29:          {
  30:                        // More names here
  31:                        ...
  32:          }
  33:      }

Now code that looked like this:

   1:  var cmdConfigSettings = new SqlCommand("dbo.GetSomeConfig", connString)
Will look like this:
   1:  var cmdConfigSettings = new SqlCommand(Commands.Config.GetWebsites, connString)

Happy Coding!

Posted in .NET, C#

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Comic for July 25, 2017
    Dilbert readers - Please visit Dilbert.com to read this feature. Due to changes with our feeds, we are now making this RSS feed a link to Dilbert.com.
%d bloggers like this: