How to parse Sql Script with dapper? (SET PARSEONLY ON + Dapper)
I want to check and parse a sql script, so I searched for this and I found something like this
SET PARSEONLY ON
SELECT * FROM [dbo].[Categories] --Query To Parse
I am using dapper so I am writing a method like this
public bool IsValidSqlScript(string sqlScript)
{
using (SQLConnection)
{
using (SQLTransaction)
{
var status = SQLConnection.Execute("SET PARSEONLY ON " + sqlScript);
// OR
// var status = SQLConnection.Query("SET PARSEONLY ON " + sqlScript);
}
}
return status;
}
How can I get the status, and if there are any errors, get the error list as well?
SET PARSEONLY ON
SELECT * FR OM [dbo].[Categories] --Query To Parse
>>> false
>>> Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'FR'.
+3
source to share
1 answer
You are close, but you have a problem with your status variable. You declare it inside a SQLTransaction using statement and then try to return it outside of that scope.
You want to use a try / catch block to execute your request so you know when the value sqlScript
is valid or not. If you enter a catch block, it will be invalid, and if you don't, then it will be valid.
The code should look something like this:
public bool IsValidSqlScript(string sqlScript)
{
bool status;
try
{
using (SQLConnection)
{
using (SQLTransaction)
{
SQLConnection.Execute("SET PARSEONLY ON " + sqlScript);
}
}
status = true;
}
catch(Exception e)
{
status = false;
)
return status;
}
0
source to share