{"id":1876,"date":"2014-10-03T00:00:00","date_gmt":"2014-10-03T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/acceptance-testing-with-fitnesse-database-fixtures-project-overview\/"},"modified":"2021-05-11T15:56:13","modified_gmt":"2021-05-11T15:56:13","slug":"acceptance-testing-with-fitnesse-database-fixtures-project-overview","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/acceptance-testing-with-fitnesse-database-fixtures-project-overview\/","title":{"rendered":"Acceptance Testing with FitNesse: Database Fixtures, Project Overview"},"content":{"rendered":"<div id=\"pretty\">Contents<\/p>\n<ul>\n<li><a href=\"#Toc363998893\">Database Access<\/a><\/li>\n<li><a href=\"#Toc363998894\">Inserting Records <\/a>\n<ul>\n<li><a href=\"#Toc363998895\">Updating Records <\/a><\/li>\n<li><a href=\"#Toc363998896\">Deleting Records <\/a><\/li>\n<li><a href=\"#Toc363998897\">Connecting to a Database <\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#Toc363998898\">Notes on the Attached Fixture Library <\/a><\/li>\n<li><a href=\"#Toc363998899\">That&#8217;s a Wrap!<\/a><\/li>\n<\/ul>\n<p class=\"MsoNormal\"><b>FitNesse<\/b> is a wiki-based framework for writing <a href=\"http:\/\/en.wikipedia.org\/wiki\/Acceptance_testing\">acceptance tests<\/a> for software systems. If you are not familiar with FitNesse, Part 1 of this series walks through a complete .NET example from writing the test in your browser to writing the C# code-behind. While FitNesse provides a rather nifty and user-friendly way to write acceptance tests in general, in practice there are plenty of quirks and glitches to watch out for. This article provides &#8220;tips from the trenches&#8221;, i.e. an accumulation of tips collected from intensive use of FitNesse on a daily basis to alleviate or avoid many of those pain points.<\/p>\n<p class=\"MsoNormal\">Here is your roadmap to the series, showing where you are right now:<\/p>\n<table class=\"series-articles table--bare\">\n<tbody>\n<tr>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\n<p><a href=\"https:\/\/www.simple-talk.com\/dotnet\/.net-tools\/acceptance-testing-with-fitnesse,-the-overview\/\">Part 1: FitNesse Introduction and Walkthrough<\/a><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\n<p><a href=\"https:\/\/www.simple-talk.com\/dotnet\/.net-tools\/acceptance-testing-with-fitnesse-documentation-and-infrastructure\/\">Part 2: Documentation and Infrastructure<\/a><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\n<p><a href=\"https:\/\/www.simple-talk.com\/dotnet\/.net-tools\/acceptance-testing-with-fitnesse--naming-and-layout\/\">Part 3: Naming and Layout<\/a><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\n<p><a href=\"https:\/\/www.simple-talk.com\/dotnet\/.net-tools\/acceptance-testing-with-fitnesse-debugging,-control-flow,-and-tracing\/\">Part 4: Debugging, Control Flow, and Tracing<\/a><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\n<p><a href=\"https:\/\/www.simple-talk.com\/dotnet\/.net-tools\/acceptance-testing-with-fitnesse-symbols,-variables-and-code-behind-styles\/\">Part 5: Symbols, Variables, and Code-Behind Style<\/a><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><a href=\"https:\/\/www.simple-talk.com\/dotnet\/.net-tools\/acceptance-testing-with-fitnesse-multiplicities-and-comparisons\/\">Part 6: Multiplicities and Comparisons<\/a><\/p>\n<\/td>\n<\/tr>\n<tr class=\"series-articles--active\">\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">Part 7: Database Fixtures, Project Overview<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"note\">\n<p class=\"note\"><b><\/b><i>Most sections in this article have references with actual hyperlinks to the FitNesse, fitSharp, or DbFit reference material. Some also have references to the sample test suite accompanying this series of articles, e.g. <b> CleanCode.ConceptNotes.LayoutShowingEmbeddedNewlines<\/b>. That path refers to a page on your FitNesse server. Thus if you are running on port 8080 on your local machine, the full URL to visit that page would be http:\/\/localhost:8080\/CleanCode.ConceptNotes.LayoutShowingEmbeddedNewlines<\/i><\/p>\n<\/div>\n<h2 id=\"Toc363998893\">Database Access<\/h2>\n<h3 id=\"Toc363998894\">Inserting Records<\/h3>\n<p class=\"MsoNormal\">Dbfit includes a general purpose <code> Execute<\/code> fixture that lets you execute most any SQL statement you like. However, you should use that only when there is not a more specific fixture available for the task at hand. Thus, to insert data into a table you can pass a SQL insert statement to the <code> Execute<\/code> fixture (left) but it is better to use the <code>Insert <\/code>fixture (right).<\/p>\n<table class=\"MsoTableLightGridAccent3 process-table\">\n<tbody>\n<tr>\n<td>&nbsp;<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> Using General Purpose &#8220;Execute&#8221;<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> Using Specialized &#8220;Insert&#8221;<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p class=\"MsoNormal\"><b> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2061-template.png\" alt=\"2061-template.png\" \/><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><code> !|Execute|insert into <i>tablename<\/i> values (...)|<\/code><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><code> !|Insert|<i>tablename<\/i>|<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"MsoNormal\">Here is a complete test table using the <code> Insert <\/code>fixture. A powerful feature of this fixture is that in an atomic operation you can insert records into the table and retrieve IDs for those inserted records! Here you can see that the first three IDs are returned and also stored into symbols.<\/p>\n<table class=\"MsoTableLightGridAccent3 process-table\">\n<tbody>\n<tr>\n<td>\n<p class=\"MsoNormal\"><b> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1936-imgF5.jpg\" alt=\"1936-imgF5.jpg\" \/><\/b><\/p>\n<\/td>\n<td valign=\"top\"><code><br \/>\n                    !|Insert |dbo.${MyTable}                   |<br \/>\n                    |WidgetID|Price|DateValidFrom    |RecordId?|<br \/>\n                    |25      |1010 |1\/1\/2013 10:21:03|&gt;&gt;record1|<br \/>\n                    |12      |1011 |5\/1\/2013 8:15    |&gt;&gt;record2|<br \/>\n                    |212343  |200  |2\/2\/2014         |&gt;&gt;record3|<br \/>\n                    |1234123 |500  |4\/4\/2012 20:00   |         |<br \/>\n                    <\/code><\/td>\n<\/tr>\n<tr>\n<td>\n<p class=\"MsoNormal\"><b> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1936-imgF8.jpg\" alt=\"1936-imgF8.jpg\" \/><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">Insert<\/p>\n<\/td>\n<td colspan=\"3\" valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">dbo.MyWidgets_tmp<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">WidgetID<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">Price<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">DateValidFrom<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">RecordId?<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">25<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">1010<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">1\/1\/2013 10:21:03<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">&gt;&gt;record1<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">12<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">1011<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">5\/1\/2013 8:15<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">&gt;&gt;record2<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">212343<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">200<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">2\/2\/2014<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">&gt;&gt;record3<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">1234123<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">500<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">4\/4\/2012 20:00<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p class=\"MsoNormal\"><b> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1936-imgFC.gif\" alt=\"1936-imgFC.gif\" \/><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\"><i> Insert<\/i><\/p>\n<\/td>\n<td colspan=\"3\" valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">dbo.MyWidgets_tmp<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">WidgetID<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">Price<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">DateValidFrom<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">RecordId?<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">25<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">1010<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">1\/1\/2013 10:21:03<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">&gt;&gt;record1 <b> <\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">12<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">1011<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">5\/1\/2013 8:15<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">&gt;&gt;record2 <b> <\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">212343<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">200<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">2\/2\/2014<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">&gt;&gt;record3 <b> <\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">1234123<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">500<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">4\/4\/2012 20:00<\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">\u00a0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"MsoNormal\">\u00a0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"caption\">Reference: CleanCode.DataBaseNotes.CrudOperations<\/p>\n<h3 id=\"Toc363998895\">Updating Records<\/h3>\n<p class=\"MsoNormal\">For updating records Dbfit also provides a more specific fixture available for the task at hand, the <code>Update<\/code> fixture. Thus, to update data in a table you can pass a SQL update statement to the <code> Execute<\/code> fixture (left) but it is better to use the <code>Update <\/code>fixture (right) <i>when conditions allow<\/i> (explained below).<\/p>\n<table class=\"MsoTableLightGridAccent3 process-table\">\n<tbody>\n<tr>\n<td>&nbsp;<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> Using General Purpose &#8220;Execute&#8221;<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> Using Specialized &#8220;Update&#8221;<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p class=\"MsoNormal\"><b> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2061-template.png\" alt=\"2061-template.png\" \/><\/b><\/p>\n<\/td>\n<td valign=\"top\"><code><code><\/code><\/code><\/p>\n<p class=\"MsoNormal\">!|Execute|update <i>tablename<\/i> set&#8230; where&#8230;|<\/p>\n<\/td>\n<td valign=\"top\"><code><br \/>\n                    !|Update|<i>tablename<\/i>               |<br \/>\n                    |<i>field_to_change=<\/i>|<i>field_to_select<\/i>|<br \/>\n                    |<i>new         value<\/i>       |<i>matching          value<\/i> |<br \/>\n                    <\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"MsoNormal\">With the <code>Update<\/code> fixture you can convert a standard SQL (left) into the test table (right):<\/p>\n<table class=\"MsoTableLightGridAccent3 process-table\">\n<tbody>\n<tr>\n<td>&nbsp;<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> SQL statement<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> Using Specialized &#8220;Update&#8221;<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p class=\"MsoNormal\"><b> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2061-example.png\" alt=\"2061-example.png\" \/><\/b><\/p>\n<\/td>\n<td valign=\"top\"><code><br \/>\n                    UPDATE dbo.MyWidgets<br \/>\n                    SET Active=1<br \/>\n                    WHERE RecordId in (@record1, @record3) AND Enabled=True<br \/>\n                    <\/code><\/td>\n<td valign=\"top\"><code><br \/>\n                     !|Update|dbo.MyWidgets2   |<br \/>\n                     |Active=|RecordId |Enabled|<br \/>\n                     |1      |&lt;&lt;record1|true   |<br \/>\n                     |1      |&lt;&lt;record3|true   |<br \/>\n                     <\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"MsoNormal\">You may include multiple columns in the selector; the example selects on both the <code>RecordId<\/code> field and the <code>Enabled <\/code>field. You may also include multiple fields to update though only one is shown in the example (<code>Active<\/code>).<\/p>\n<p class=\"MsoNormal\">The main <i>advantage<\/i> of the <code>Update<\/code> fixture over a standard SQL update statement is that you can use different selection criteria and different update values for every record. The example shows the same values because it is purposely matching the SQL statement. But clearly you are free to use different values in each cell in the test table.<\/p>\n<p class=\"MsoNormal\">The main <i>disadvantage<\/i> of the <code>Update<\/code> fixture is that you <i>cannot<\/i> provide an expression or even a simple field name to SQL-you may use only variables or constants known in the scope of the FitNesse test page. That is, within a SQL update statement you could use an expression (e.g. <code>SET Price=Price+1<\/code>) or a field name (e.g. <code>SET BillDate=InvoiceDate<\/code>). Those cannot be used with the <code>Update<\/code> fixture.<\/p>\n<p class=\"caption\">Reference: CleanCode.DataBaseNotes.CrudOperations<\/p>\n<h3 id=\"Toc363998896\">Deleting Records<\/h3>\n<p class=\"MsoNormal\">For deleting records Dbfit provides a more specific fixture available-sort of(!). The <code>Clean<\/code> fixture is available but is undocumented. So just as SQL Server has well-known yet officially unsupported stored procs and such that you should not depend on, nor should you rely on undocumented features in DbFit. But that is not a great loss; I found DbFit&#8217;s Clean fixture unsatisfactory anyway. Rather, I have included a DbClean fixture in my project library that provides more capability.<\/p>\n<table class=\"MsoTableLightGridAccent3 process-table\">\n<tbody>\n<tr>\n<td>&nbsp;<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> Using General Purpose &#8220;Execute&#8221; or &#8220;Clean&#8221;<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b> Using Specialized &#8220;DbClean&#8221;<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p class=\"MsoNormal\"><b> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2061-template.png\" alt=\"2061-template.png\" \/><\/b><\/p>\n<\/td>\n<td valign=\"top\"><code>!|Execute|delete from tablename where...|<\/p>\n<p>                    -- or --<\/p>\n<p>                    |Clean                  |<br \/>\n                    |Table  |Where  |Clean  |<\/code><\/td>\n<td valign=\"top\"><code>!|DbClean                              |<br \/>\n                    |Table |Where    |Clean?|ResultDetails?|<br \/>\n                    |<i>name<\/i>  |<i>condition<\/i>|<i>rows          <\/i>|null          |<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"MsoNormal\">There are several disadvantages of the generic <code> Execute<\/code> fixture and the DbFit <code> Clean<\/code> fixture.<\/p>\n<p class=\"MsoNormal\">First, neither the generic <code> Execute<\/code> fixture nor the <code>Clean<\/code> fixture supports FitNesse symbols. (Recall that you could, for example, define a FitNesse symbol <code>TargetPrice<\/code>, then use that in the <code>Query<\/code> fixture as in: <code>SELECT a,b,c WHERE Price &gt; @TargetPrice<\/code>.) Second, the <code>Clean<\/code> output field is a Boolean, reporting success or failure but not how many records were processed. Finally, I have not been able to characterize what causes it, but both of these fixtures seem to sporadically fail.<\/p>\n<p class=\"MsoNormal\"><code>DbClean<\/code> overcomes all of those disadvantages. The <code>Clean<\/code> output field indicates how many records matched the WHERE clause and were deleted. The <code> ResultDetails<\/code> output field will be <code> null<\/code> for a successful operation; if a problem occurred, it will report an error message. <code>DbClean<\/code> also supports symbols just like Dbfit&#8217;s <code>Query<\/code> fixture so you can build a parameterized WHERE clause when necessary. My recommendation is to use DbClean twice in any database test, once at the <i>start<\/i> of the test where you do a sanity check to confirm that there are <i>no<\/i> matching records (thus checking for 0 returned in the <code>Clean?<\/code> Column)&#8230;<\/p>\n<pre>!|DbClean                                       |\r\n|Table |Where             |Clean?|ResultDetails?|\r\n|names |username like 'M%'|0     |null          |\r\n|prices|Price &gt; @Target   |0     |null          | \r\n\r\n<\/pre>\n<p class=\"MsoNormal\">&#8230; and also at the <i>end<\/i> of the test where you check for the proper record counts inserted during this test and delete them&#8230;<\/p>\n<pre>!|DbClean                                       |\r\n|Table |Where             |Clean?|ResultDetails?|\r\n|names |username like 'M%'|4     |null          |\r\n|prices|Price &gt; @Target   |2     |null          |\r\n\r\n<\/pre>\n<p class=\"MsoNormal\">Any errors at the start of the test indicate some test (possibly the same test) left detritus behind. Errors at the end of the test indicate the current test likely has an error (that may or may not be revealed in other test tables on the page).<\/p>\n<p class=\"MsoNormal\">Alas, DbClean has one important caveat. Because it is external to DbFit, you have to explicitly perform a commit on your DbFit operations before you use DbClean. If you do not, the transaction is left open, blocking DbClean from completing and it will eventually time out and fail. See the reference page to see an example.<\/p>\n<p class=\"caption\">Reference: CleanCode.DataBaseNotes.CrudOperations<\/p>\n<h3 id=\"Toc363998897\">Connecting to a Database<\/h3>\n<p class=\"MsoNormal\">To connect to a database with DbFit, you must choose either <i>flow<\/i> mode or <i>standalone<\/i> mode. The DbFit manual recommends flow mode over standalone mode, in that flow mode provides automatic transaction management &#8220;&#8230; and some other shortcuts&#8221; though it is not clear what that entails.<\/p>\n<p class=\"MsoNormal\">You invoke flow mode with the appropriate database fixture-this one is for SQL Server:<\/p>\n<pre>!|dbfit.SqlServerTest |\r\n|Connect|${ConnString}|\r\n<\/pre>\n<p class=\"MsoNormal\">But flow mode has one serious restriction; the above connection table must be the <i>very first table on the page<\/i>-that includes any tables in <code>SetUp<\/code> or <code> SuiteSetUp<\/code> pages! And that also <i>precludes<\/i> having an <code> import<\/code> fixture to define your namespaces, hence the use of the fully qualified name <code>dbfit.SqlServerTest<\/code> above. One common scenario that causes mysterious problems is if you choose to use flow mode, putting the above connection table on your test page, while at the same time having an inherited <code>SetUp<\/code> page that brings in some other test tables before it, violating the absolutely-first-table rule. You must override the inherited <code>SetUp<\/code> page in such a situation.<\/p>\n<p class=\"MsoNormal\">If you need to interact in one test with more than one database you <i>must<\/i> use standalone mode because of the above rule. In standalone mode use the <code>DatabaseEnvironment<\/code> fixture; you can sprinkle multiple <code>DatabaseEnvironment<\/code>s in one test, though only one database is active at a time.<\/p>\n<pre>!|import      |\r\n|dbfit.fixture|\r\n\r\n!|DatabaseEnvironment|SQLSERVER    |\r\n|Connect             |${ConnString}|\r\n<\/pre>\n<p class=\"MsoNormal\">Particularly when using multiple databases I recommend doing a sanity check immediately after connecting to each database, like this one for SQL Server:<\/p>\n<pre>!|DatabaseEnvironment|SQLSERVER    |\r\n|Connect             |${ConnString}|\r\n\r\n!|query|select @@SERVERNAME [server]|\r\n|server                             |\r\n|${MyDbServer}                      |\r\n\r\n<\/pre>\n<p class=\"MsoNormal\">As the first assertion in a test this lets you know immediately whether you are have connected to the database you think you did and whether you can in fact converse with it.<\/p>\n<p class=\"caption\">Reference: <a href=\"https:\/\/fitnesse.s3.amazonaws.com\/tdd_net_with_fitnesse.pdf\">Flow vs Standalone<\/a><\/p>\n<p> &#8211; see Chapter 12<\/p>\n<h2 id=\"Toc363998898\">Notes on the Attached Fixture Library<\/h2>\n<p class=\"MsoNormal\">Included with this series of articles is an archive containing both a library of custom FitNesse fixtures (CleanCodeFixtures.dll) and a FitNesse suite of tests that illustrate the fixture library as well as document many of the issues discussed in this series.<\/p>\n<p class=\"MsoNormal\">The test suite consists of these subsuites:<\/p>\n<table class=\"MsoTableLightShadingAccent5 process-table\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b>SubSuite<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b>Description<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b>Concept Notes<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">Discusses a variety of issues documented in this series<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b>Control Flow Notes<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">Illustrates debugging, aborting, pausing, and tracing tests.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b>Data Base Notes<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">Examines standard CRUD operations in a FitNesse context.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"MsoNormal\"><b>General Utility Fixture Notes<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"MsoNormal\">Shows example usages of the fixtures in CleanCodeFixtures.dll.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"MsoNormal\">\u00a0<\/p>\n<p class=\"MsoNormal\">Refer to the <code>GeneralUtilityFixtureNotes<\/code> for documentation and examples on how to use the fixtures. (You could also review the source code, of course; that is included as well.)<\/p>\n<p class=\"MsoNormal\">Here is a snapshot of the execution of the entire suite. FitNesse results begin with a one-line summary at the top, which expands into a one-line-per-test summary. Both of those are shown here. (Note that there are deliberately 6 errors injected into this suite for reasons explained on those particular test pages.) Below that is a complete transcript of the entire test run (not shown:-). You will see three of the four sub-suites mentioned above in the figure; the fourth one, Control Flow Notes, could not be included in an automated test run <i>because<\/i> it fiddles with the control flow.<\/p>\n<p class=\"MsoNormal\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2061-imgF9.jpg\" alt=\"2061-imgF9.jpg\" \/><\/p>\n<p class=\"MsoNormal\">If you want to execute any of the control flow tests (or any others not marked as executable by default) you can either edit the properties on the particular page, or just add this suffix &#8220;<code>?test<\/code>&#8221; to the URL and reload the particular page. Example (put this all on one line of course):<\/p>\n<pre>http:\/\/yourserver:yourport\/\r\nCleanCode.ControlFlowNotes.PausingTestsToExamineExternalResources?test\r\n<\/pre>\n<p class=\"caption\">Reference: CleanCode (Remember, non-hyperlinked references point to http:\/\/<i>yourserver<\/i>:<i>yourport<\/i>\/<b><i>reference<\/i><\/b>.)<\/p>\n<h2 id=\"Toc363998899\">That&#8217;s a Wrap!<\/h2>\n<p class=\"MsoNormal\">I did not expect this series to turn into seven parts when I began writing but I suppose, in retrospect, having some 35 pages of notes should have given me a clue! But there you have it. Colleagues in my day job have often said they were glad I took copious notes on issues I encountered when I would venture into exploring a new technology that we were going to be adopting. It saved them a lot of time and effort not having to resolve many of the same issues. I hope that you can similarly use these &#8220;tips from the trenches&#8221; and save some effort so you have more time to get on with your main focus. Let me know!<\/p>\n<p class=\"MsoNormal\">\u00a0<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>There aren&#8217;t many frameworks for writing acceptance tests for databases, including SQL Server. FitNesse is an obvious choice since it is designed with a Wiki-style interface that, once it is configured and set up correctly, makes it very easy for a non-specialist  to set up individual tests. &hellip;<\/p>\n","protected":false},"author":221868,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143538],"tags":[4143,4204,4168,4150,4151],"coauthors":[6802],"class_list":["post-1876","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","tag-net","tag-net-tools","tag-database","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1876","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221868"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1876"}],"version-history":[{"count":16,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1876\/revisions"}],"predecessor-version":[{"id":72853,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1876\/revisions\/72853"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1876"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1876"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1876"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1876"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}