Geek Answers Handbook

This is a programmatic way to retrieve SQL keywords (reserved words)

I need to check the SQL Column Name , which is created programmatically ...

There should be 2 validation rules:

  • The name should not be a C # keyword
  • Name must not be a SQL keyword (SQL Server 2008 R2)

The solution for the 1st rule is good:

The CSharpCodeProvider class has an IsValidIdentifier method that simplifies the implementation of validation.

(ex:

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

)

Solution for the second rule: litle verbose:

The only way I found doing a search on Google is to take keywords from MSDN - SQL Server 2008 R2 reserved keywords (Transact-SQL)

To create a string [] property that will return all these keywords ...

(eg:

 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 advise me on the implementation of the second validation rule. Is this a good practice? Maybe this is another implementation method that I did not find in googling ...

+6
source share
3 answers

Since there is a function that you can call for C #, the real question is how to search for SQL reserved words. The way you implemented does not look like the most efficient C #. You should use HashSet - an example of quick unchecked code:

 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 good article (@theburningmonk) showing how fast a HashSet when using Contains

(For those who do not want to click, the HashSet is zero)

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

+3
source

Reserved words are a moving target. If dbms does not open them through an open interface, there is usually no good software way to get to them.

If you do not want to protect them with parentheses, you run the risk of including characters that are not reserved in your current version of SQL Server, but are reserved in some future version.

I believe that it is best to use the citation mechanism that your dbms provides, since it is designed to solve this very problem. For SQL Server, this means square brackets.

+4
source

As a rule, the approach looks right. Getting keywords for any given language is associated with a (apparently small) bit of trial and error due to something undocumented, but the language specification itself is always the main source. I do not know a single language that comes with their own validators, but this does not mean that they do not exist.

Visual Studio itself has a set of xml files that help you validate for any language. If you were developing an IDE, you might have a table that looks something like this:

 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 the exception to be thrown and catching and handling it specifically is a valid methodology, although not very clean.

For your case, the only real tweak that I would make is not that the list of keywords entered into the program at compile time will instead save the list in an external file that is downloaded to the application’s starting point. This allows a bit of flexibility if you forget something or need to support later versions of the language without requiring re-creating your application.

+1
source

More articles:


All Articles