Click here to Skip to main content
15,886,812 members
Please Sign up or sign in to vote.
4.20/5 (2 votes)
I am trying to write a function named QueryChecker.

The purpose of function is to check the query through MS SQL server.
Here is example of my code:

string expectedQuery = GenrerateQuery();

SqlDataAdapter ndaGlobalClass = new SqlDataAdapter(expectedQuery, cn2);


In general expectedQuery is generated through a method. I want to test the expectedQuery is correct or not but i don't want to execute it in the sql server. Is it possible ?

If the query is not correct then shows the appropriate wrong message that is instructed by SQL server.
Posted
Comments
phil.o 30-Apr-14 3:48am    
Downvote mitigated; don't know why someone gave you a 1 on this.

 
Share this answer
 
Comments
phil.o 30-Apr-14 3:50am    
It will indicate a syntax error, but not if there is a typo in a table name, for example.
Not really practical, I'm afraid.
The trouble is that a "correct" SQL query can fail if the table or any of the column names is wrong, as well as if the syntax is wrong - so your "check it out" method would have to understand SQL syntax properly, and be capable of parsing and checking it, and also know the tables and their layouts including datatypes. Otherwise this valid SQL query:
SQL
UPDATE MyTable SET MyCol=@VAL
could still fail if:
1) There is no "MyTable" table
2) There is no "MyCol" column in "MyTable"
3) You fail to provide the "@VAL" parameter
3) The datatype that you prove for the parameter does not match the column. For example, if the column is DATETIME and your provide a string containing "Hello!".

You could probably do it, but it's an enormous amount of work, and unlikely to be particularly helpful.
 
Share this answer
 
Comments
_Asif_ 30-Apr-14 3:48am    
Well there could be a workaround for this scenario. The work around is to use SQL server again for parsing the query but not executing the query. I have found a good article that explains the mechanism. check this out.

http://blog.sqlauthority.com/2014/01/20/sql-server-how-to-validate-syntax-and-not-execute-statement-an-unexplored-debugging-tip/
sachi Dash 30-Apr-14 7:26am    
It doesn't matter what kind of error it is ! I need to show this message what sql server shows after getting any wrong.
You could try to execute your query in a SqlTransaction that you will discard at the end.
This should give something like:
C#
string expectedQuery = GenerateQuery();
using (SqlConnection con = GenerateSqlConnection()) {
   con.Open();
   using (SqlTransaction tran = con.BeginTransaction("TestTransaction")) 
   using (SqlCommand cmd = new SqlCommand(expectedQuery, con)) {
      cmd.Transaction = tran;
      try {
         cmd.ExecuteNonQuery();
      }
      catch (Exception ex) {
         // If you get here there was an issue with your query
      }
      finally {
         tran.RollBack();
      }
   }
}

Hope this helps.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900