Validate for a valid SQL string

Question

Is there a way (or existing library, not necessarily built into .NET) in C# that performs simple SQL string validation?

Scenario: Building update statement to reduce load on SQL load vs. individual statements. In case the string build does something "odd", like ending with a comma for instance, I'd like to be able to validate that the string is correct.

Answer 1

If you want to validate the SQL using SQL Server, you can do so by adding

SET PARSEONLY ON

before your script, and then

SET PARSEONLY OFF

after your script.

---

If you need to avoid going to the database, then maybe you can use the assemblies that Microsoft Visual Studio Team System 2008 Database Edition GDR includes (they have code that handles SQL parsing and script generation). If you don't have the database edition or team suite, you can download the trial version to get the assemblies.

Here is a link I found where someone is using these assemblies.

Answer 2

This third party component does a pretty good job of validating sql without going to the server. SqlParser

Answer 3

I've noticed that if you declare a new System.Data.SqlClient.SqlConnection object and set the ConnectionString field to a syntactically invalid connection string, it will throw exception, so this would be one way of checking.

System.Data.SqlClient.SqlConnection foo
        = new System.Data.SqlClient.SqlConnection();
foo.ConnectionString = "not a valid connection string"; //exception thrown here