.Net PetaPoco default query parameters

Question

Given a query string like this

    SELECT * FROM articles WHERE article_id=@0 OR article_title=@1

1. Determine total number of parameters (may be regex ?)
2. Pass a default value for each parameter

I'm trying to build a testing framework for hardcoded query strings. The goal is to fire a blank query against the DB and see if it fails. I preferably want to use PetaPoco but open to others.

Answer 1

You will need to also know the type of the value the parameter is (integer, string, etc)

If you are building the framework you should allow the user the add the query statement and the parameters needed with type, range of values and default value

Answer 2

The trick is to regex replace the parameters with NULL. As far as SQL Server is concerned, this is a well formed query. So it will execute it fine but it is not logically correct (ie Column=NULL and Column IS NULL are not the same). If you have a typo, it will detect it.

public class DbQueryTester : DbEntity
{
    Regex m_rgx = new Regex(@"@\d+\s*");
    public bool TryQuery(string qry, out Exception ex)
    {
        try
        {
            string newQry = m_rgx.Replace(qry, "NULL ");
            Fetch<object>(newQry);
        }
        catch (Exception tmp)
        {
            ex = tmp;
            return false;
        }
        ex = null;
        return true;
    }
}