Rows' count for a result-set


This assertion counts rows of a result-set and compares it to an expectation. The possible comparisons are equal, equal, more-than and less-than.

System under test

The system-under-test is a query, please reports to other tests for more information about how to define a query. More info here.

Assertion

Predefined value

The assertion consists in an xml element named row-count.

<assert>
<row-count>
...
</row-count>
</assert>

In this element, you must also specify an the comparison that you want to apply equal, more-than and less-than. You must also specify the reference to compare.

<assert>
<row-count>
<less-than>10</less-than>
</row-count>
</assert>

For the comparisons more-than and less-than, you can slightly change the behavior by adding the attribute or-equal and setting its value to true.

<assert>
<row-count>
<less-than or-equal="true">10</less-than>
</row-count>
</assert>

Variable

It’s possible to dynamically define the value that is used in the comparison. To achieve this you must use a global variable as explained there.

<variables>
<variable name="maximum">
<script language="c-sharp">10*10*10</script>
</variable>
</variables>

<assert>
<row-count>
<less-than or-equal="true">@maximum</less-than>
</row-count>
</assert>

Note that if this variable is a percentage it must be returned as a string (double quotes)!

<variables>
<variable name="maximum">
<script language="c-sharp">"50%"</script>
</variable>
</variables>

Row-count of another result-set

It’s possible to compare the row-count of the first result-set defined in the system-under-test to the row-count of a second result-set defined in the assertion to achieve this, use the projection row-count and define your second result-set with the syntax 2.0.

<row-count>
<more-than or-equal="true">
<projection type="row-count">
<resultSet>
<query connectionString="@conn-Other">
<![CDATA[
select Age, *
from Employee
where Age=50
]]>

</query>
</resultSet>
</projection>
<more-than>
</row-count>

Full example

<test name="Count of SalesTerritory is less-than or equal to 3" uid="0001">
<system-under-test>
<execution>
<query connectionString="Data Source=mhknbn2kdz.database.windows.net;Initial Catalog=AdventureWorks2012;User Id=sqlfamily;password=sqlf@m1ly">
select
[Name], [CountryRegionCode]
from
[Sales].[SalesTerritory]
where
[Group]='Europe'
</query>
</execution>
</system-under-test>
<assert>
<row-count>
<less-than or-equal="true">3</less-than>
</row-count>
</assert>
</test>