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>