Popularity
0.9
Stable
Activity
7.8
Declining
1
2
0

Description

Nuget package Dapper.QX makes inline SQL more powerful and testable via the Query class. Get the convenience, safety and capability of Dapper with dynamic criteria, tracing, and full text queries.

Programming language: C#
Tags: Testing     Dapper     Sql-productivity    

Dapper.QX alternatives and similar packages

Based on the "Testing" category

Do you think we are missing an alternative of Dapper.QX or a related project?

Add another 'Testing' Package

README

Build status Nuget

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

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.

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

Testing

Make query classes testable with the ITestableQuery interface. This approach catches invalid SQL, but does not assert any particular query results.

public class MyQuery : Query<MyResultClass>, ITestableQuery
{
    // same code above omitted

    // implement GetTestCases method to return every parameter combination you need to test
    public IEnumerable<ITestableQuery> GetTestCases()
    {
        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" };
    }

    // implement TestExecute the same way always
    public IEnumerable<dynamic> TestExecute(IDbConnection connection)
    {
        return TestExecuteHelper(connection);
    }
}

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

Debugging

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.

img

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.


Please see also my Crud library Dapper.CX, Dapper.QX's companion library.