Is it a programmatic way to get SQL keywords (reserved words)

Question

I need to validate the Name of a SQL column, which is created programmatically...

There whould be 2 validation rules:

1. The Name shouldn't be a C# keyword
2. The Name shouldn't be a SQL keyword (SQL Server 2008 R2)

---

The solution for 1st rule it's nice:

The CSharpCodeProvider class has the IsValidIdentifier method which makes the implementation of validation easy.

(ex:

string myColumnName = "blabla";
var isValid = _cSharpCodeProvider.IsValidIdentifier(myColumnName);

)

---

The solution for 2nd rule it's a litle verbose:

The only way I found doing google searches is to take the keywords from MSDN - Reserved Keywords (Transact-SQL) SQL Server 2008 R2

To build a string[] property which will return all these keywords...

(ex:

public static class SqlReservedKeywords {

     public static string[] SqlServerReservedKeywords {
        get { return SqlServerKeywords; }
     }

     private static readonly string[] SqlServerKeywords = new[] { 
        "ADD","EXISTS","PRECISION",   

    //. . .

        "EXEC","PIVOT","WITH",
        "EXECUTE","PLAN","WRITETEXT"
     };
}

//External code

var isValid = SqlReservedKeywords.SqlServerReservedKeywords.Contains(myColumnName);

)

---

Can you advice me about implementantion of 2nd validation rule. Is it a good practice? Maybe it exist another way to implement which i didn't found by googling...

Answer 1

Reserved words are a moving target. If the dbms doesn't expose them through a public interface, there isn't usually a good programmatic way to get to them.

If you don't want to guard them with brackets, you risk incorporating symbols that are not reserved in your currently used version of SQL Server, but are reserved in some future version.

I think your best bet is to use the quoting mechanism your dbms provides, since it's designed to deal with exactly this problem. For SQL Server, that means square brackets.

Answer 2

Since there is a function you can call for C#, the real question is how to do the lookup for SQL Reserved words. The way you implemented look up here is NOT the most efficient C#. You should use a HashSet -- quick untested code example follows:

public static class SqlReservedKeywords {

   public bool isReserved(string in)
   {
      return SqlServerKeywords.Contains(in.ToUpper());
   }

   private static HashSet<string> SqlServerKeywords = new HashSet<string>();

   static SqlReservedKeywords() 
   {
      SqlServerKeywords.Add("ADD");
      SqlServerKeywords.Add("EXISTS");
      SqlServerKeywords.Add("PRECISION");

   //. . .

      SqlServerKeywords.Add("EXEC");
      SqlServerKeywords.Add("PIVOT");
      SqlServerKeywords.Add("WITH");
      SqlServerKeywords.Add("EXECUTE");
      SqlServerKeywords.Add("PLAN");
      SqlServerKeywords.Add("WRITETEXT");
   }   
}

Here is a nice article (by @theburningmonk) showing how fast HashSet is when using Contains

(For those that don't want to click, HashSet is zero)

http://theburningmonk.com/2011/03/hashset-vs-list-vs-dictionary/

Answer 3

Generally, the approach looks correct. Getting keywords for any given language involves a (hopefully small) bit of trial and error due to anything undocumented but the main source is always the language specification itself. I don't know of any languages that come with their own validators but that's not to say they don't exist.

Visual Studio itself has a set of xml files that help it do the validation for any given language. If you were developing an IDE, you might have a table that looked something like;

Keyword     | MatchWithRegEx | Color
------------+----------------+---------
for         | \wfor          | #FF0000

...you get the idea. In your case, you just want to filter out possible problem keywords so that they don't throw an exception. Allowing an exception to be thrown and catching and handling it specifically is a valid methodology albeit not a very clean one.

As for your case, the only real tweak I'd make is not having the list of keywords buried into the program at compile time but instead store the list in an external file which is loaded at your application's starting point. This allows some flexibility if you forget anything or need to support later versions of a language without requiring a rebuild of your application.