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.

Do you mean you are trying to validate the SQL statement?? Your scenario doesn't seem to shed any light. — Michael Bray
– Michael Bray, Commented Jan 13, 2010 at 5:20
Yeps, that's what I said hehe. I clarified the scenario though. — Kyle Rosendo
– Kyle Rosendo, Commented Jan 13, 2010 at 5:32

Gabriel McAdams · Accepted Answer · 2010-01-13 17:01:09Z

4

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.

edited Jan 13, 2010 at 17:01

answered Jan 13, 2010 at 5:26

Gabriel McAdams

58.5k12 gold badges66 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kyle Rosendo Over a year ago

Surely this still sends data off to SQL though for the validation? I am more looking for something to validate on the logic tier, not on the data tier.

Gabriel McAdams Over a year ago

Yes, but what could be more valid than using SQL Server to do the validation? You could even use a local server to speed it up if you want.

Kyle Rosendo Over a year ago

I think that using a local SQL server for only validation is overkill to the nth degree. Right now, it still sends off to SQL and will return with an error if there's a problem. I want to skip the middle step and validate (at least to a decent degree) on the logic tier.

Gabriel McAdams Over a year ago

I added to my answer, a solution that might work. Check it out.

Gabriel McAdams Over a year ago

Have you seen my modified answer? I really think it's the solution to your problem.

|

Craig · Accepted Answer · 2010-01-14 05:28:05Z

0

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

answered Jan 14, 2010 at 5:28

Craig

36.9k35 gold badges122 silver badges203 bronze badges

Comments

sth · Accepted Answer · 2010-07-13 01:13:58Z

-1

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

edited Jul 13, 2010 at 1:13

sth

231k56 gold badges288 silver badges370 bronze badges

answered Jul 12, 2010 at 13:52

Vivian River

32.6k64 gold badges211 silver badges327 bronze badges

2 Comments

Oliver Over a year ago

As you already wrote, this works only for the ConnectionString, but not for the SQL statement itself.

Vivian River Over a year ago

oops, you're right. He said "SQL String", not "SQL Connection String"

Collectives™ on Stack Overflow

Validate for a valid SQL string

3 Answers 3

7 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

2 Comments

Linked

Related