How parse Sql Script with dapper? (SET PARSEONLY ON + Dapper)

Question

How parse Sql Script with dapper? (SET PARSEONLY ON + Dapper)

c# dapper parsing sql sql-server

Question

I want to check & parse sql script so I searched for that and I found something like this

SET PARSEONLY ON 
SELECT * FROM [dbo].[Categories]  --Query To Parse

I use dapper, so I write 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 I can get status and if exists any errors get error`s list also ???

SET PARSEONLY ON
SELECT * FR OM [dbo].[Categories]  --Query To Parse

>>> false
>>> Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'FR'.

Hamed F

View more on Stack Overflow

Michael · Accepted Answer · 12/5/2014 3:05:00 PM

You're close, but you've got an issue with your status variable. You declare it inside your SQLTransaction using statement and then you try and return it outside of that scope.

You'll want to use a try/catch block to execute your query, that way you'll know when the sqlScript is valid or not. If you enter the catch block, it's invalid, and if you don't then it is valid.

The code should look similar to the following...

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;
}

How parse Sql Script with dapper? (SET PARSEONLY ON + Dapper)

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator