Post
Ask Question

Encode sqlserver reserved keywords

There are several reserved keywords of SqlServer. Sometime, we need to use these in our code.
For example 'INDEX' is a reserved keywords of Sqlserver, if we use the same in our application as properties or with an ORM like NHibernate or FluentNHibernate then we could get an exception of reserved keywords.
 
The general solution is just to put SQL Brancket [] to tell the compiler that this is not a reserved keyword and treat this as a normal name for my application/code. This task looks very simple but sometime it is hard to replace the things (eg. if we are using almost all reserved keywords):).
 
For this I have written a simple method and an extension method so, we just need to use this method and all will be done.
 
Here is complete code: 
  1. public static class EncodeSqlReservered  
  2. {  
  3.     public static string EncodeKeywordWithBraces(string keywordToEncode)  
  4.     {  
  5.         return string.Format("[{0}]", keywordToEncode);  
  6.     }  
  7.   
  8.   
  9.     public static string EncodeToBraces(this string keywordToEncode)  
  10.     {  
  11.         return string.Format("[{0}]", keywordToEncode);  
  12.     }  
  13.   
  14.   
  15. }  
Lets discuss one example where we are getting exception with FluentNHibernate mapping, here is the mapping code:
 
  1. public class ServerDataMap : ClassMap<ServerData>  
  2.     {  
  3.         public ServerDataMap()  
  4.         {  
  5.             Table("ServerData");  
  6.   
  7.             Id(x => x.Id, "Id").GeneratedBy.Identity().UnsavedValue(0);  
  8.   
  9.             Map(x => x.Index);  
  10.             Map(x => x.EndDate);  
  11.             Map(x => x.OrderNumber);  
  12.             Map(x => x.IsDirty);  
  13.             Map(x => x.IP).Length(11);  
  14.             Map(x => x.Type).Length(1);  
  15.             Map(x => x.RecordIdentifier);  
  16.   
  17.         }  
  18.     }  
 This is throwing exception:
  1. Map(x => x.Index);  
 We can use the above in two ways:
 
1. Using normal method:
 
  1. Map(x => EncodeSqlReservered.EncodeKeywordWithBraces(x.Index));  
 
2.  Using extension method:
  1. Map(x => x.Index.EncodeToBraces());  
Now, this looks much easier :)