CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworkado.net
King Chan
King Chan

Reputation: 4292

Is there a way to test a SQL Statement to see if is executable in C#?

I am not asking to test the SQL Statement by parsing it.

I mean something similiar to execute the SQL Statement with ADO.NET, so it will actually run in SQL Server to see if the fields name correct, table names correct, stynxs correct, data type corrects etc.

The previous developer done this by executes the SQL Statement with ADO.NET and check if is throw execption. But the data return is a large data set, which is wasting the resources. So is there anyway I can ask SQL Server to perform a statement check for me in C#?

Upvotes: 4

Views: 646

Answers (2)

Sebastian Meine
Sebastian Meine

Reputation: 11773

The problem with PARSEONLY is, that it only checks the syntax structure. It does not resolve table and column names. So

SET PARSEONLY ON
GO
SELECT nocolumn FROM no.notable;
GO
SET PARSEONLY OFF

runs without error.

There is no clean way to achieve what you are looking for but you can try the ROWCOUNT setting:

SET ROWCOUNT 1;

BEGIN TRAN
SELECT * FROM sys.objects;-- Your statement
ROLLBACK

SET ROWCOUNT 0;

The ROWCOUNT setting stops execution after reaching n rows. The explicit transaction makes sure that no changes get applied to that database. You could control the transaction from you C# code as well.

Th reason this is not ideal is that there are operations that require SQL Server to do all the work before even returning a single row. An example is the ORDER BY clause.

Upvotes: 1

Jared Shaver
Jared Shaver

Reputation: 1339

Try SET PARSEONLY ON. http://msdn.microsoft.com/en-us/library/ms178629.aspx

Upvotes: 8

Related Questions