Nuget package Dapper.QX makes inline SQL more powerful and testable via the Query<TResult> class. Get the convenience, safety and capability of Dapper with dynamic criteria, tracing, and full text queries. From the Wiki: Why Dapper.QX?

public class MyQuery : Query<MyResultClass>
{
    public MyQuery() : base(
        @"SELECT * 
        FROM [whatever]
        {where}
        ORDER BY [something]")
    
    [Where("[SomeDate]>=@minDate")]
    public DateTime? MinDate { get; set; }
    
    [Where("[SomeDate]<=@maxDate")]
    public DateTime? MaxDate { get; set; }
    
    [Case("0", "[AssignedTo] IS NULL")]
    [Case("-1", "[AssignedTo] IS NOT NULL")]
    [Where("[AssignedTo]=@assignedTo")]
    public string AssignedTo { get; set; }
}

Run your query like this:

using var cn = GetConnection();
var data = await new MyQuery() 
{
    MinDate = DateTime.Now, 
    MaxDate = DateTime.Now.AddDays(30),
    AssignedTo = "somebody"
}.ExecuteAsync(cn);

In the example above GetConnection is a fictional method -- you will need to provide your own method that returns an IDbConnection that works in your project. Read on below for an alternate syntax that lets you omit the using block.

Use {where} or {andWhere} tokens to indicate where dynamic criteria is inserted. Mix and match Where and Case attributes on query class properties to control what criteria is injected. Learn about more attributes Dapper.QX offers.

Note that you can omit the using block if you use the Execute* overloads that accept a Func<IDbConnection> instead of IDbConnection. This assumes you still have a method in your project that returns IDbConnection. Adapting the example above, this would look like this:

var data = await new MyQuery() 
{
    MinDate = DateTime.Now, 
    MaxDate = DateTime.Now.AddDays(30),
    AssignedTo = "somebody"
}.ExecuteAsync(GetConnection);

This approach makes sense when you have just one query to run, and you don't need the database connection for anything else.

To help you build C# result classes for any SQL query, I offer a free tool Postulate.Zinger.

Make query classes testable by basing them on TestableQuery. This approach catches invalid SQL, but does not assert any particular query results. See the wiki topic for more info.

Note that you can also use the interface ITestableQuery directly if you wish, but you must implement TestExecute yourself. There's normally no reason to do this, since I use the same implementation everywhere. Therefore, I recommend using the abstract class TestableQuery instead of ITestableQuery.

public class MyQuery : TestableQuery<MyResultClass>
{
    // same code above omitted
  
    // implement GetTestCasesInner method to return every parameter combination you need to test
    protected IEnumerable<ITestableQuery> GetTestCasesInner()
    {
        yield return new MyQuery() { MinDate = DateTime.Now };
        yield return new MyQuery() { MaxDate = DateTime.Now };
        yield return new MyQuery() { AssignedTo = "0" };
        yield return new MyQuery() { AssignedTo = "-1" };
        yield return new MyQuery() { AssignedTo = "anyone" };
    }
}

Now, in your unit test project, use the QueryHelper.Test method for each of your queries. A good way to test queries on a SQL Server localdb instance is to use my SqlServer.LocalDb.Testing package. You can see how it's used in Dapper.QX's own tests.

[TestClass]
public class QueryTests
{
    private SqlConnection GetConnection()
    {
      // implement as needed 
    }

    [TestMethod]
    public void MyQuery() => QueryHelper.Test<MyQuery>(GetConnection);    
}

To pass a TVP, create public property on your query class of type DataTable and add the [TableType] attribute with the name of the UDT in SQL Server it maps to. In this example, Source is the TVP property:

internal class SimpleTvpExample : Query<int>
{
    public SimpleTvpExample() : base(
        @"DECLARE @list [IdList];
        INSERT INTO @list ([Id]) SELECT [Id] FROM @source;
        SELECT [Id] FROM @list")
    {
    }

    [TableType("[dbo].[IdList]")]
    public DataTable Source { get; set; }
}

Set the Source property like any other parameter. You can create a DataTable dynamically with the ToDataTable extension method, as in the test:

var qry = new SimpleTvpExample() { Source = new int[] { 1, 2, 3 }.ToDataTable() };

You can inject multiple WHERE clauses in a query by adding a prefix on the {where} and {andWhere} tokens, making them like {prefix:where} or {prefix:andWhere}. See issue 24 for more info.

To help you debug resolved SQL, place a breakpoint on any of the Execute* calls, and step over that line. Look in the Debug Output window to see the resolved SQL along with any parameter declarations. You can paste this directly into SSMS and execute.

Note the extra indent you're seeing in the SQL is because of whitespace in the sample query's source file from where I took this screenshot. In the source file, the SQL is stored with a verbatim string, so the indent is preserved.

Note also at this time, TVPs are not included in the debug output.