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">
      <result-set>
        <query connection-string="@conn-Other">
          <![CDATA[
           select Age, *
           from Employee
           where Age=50
          ]]>
        </query>
      </result-set>
    </projection>
  <more-than>
</row-count>

Full example

<test name="Count of SalesTerritory is less-than or equal to 3" uid="0001">
   <system-under-test>
    <result-set>
      <query connection-string="...">
        select
          [Name], [CountryRegionCode]
        from
          [Sales].[SalesTerritory]
        where
          [Group]='Europe'
      </query>
    </result-set>
   </system-under-test>
   <assert>
    <row-count>
      <less-than or-equal="true">3</less-than>
    </row-count>
   </assert>
</test>