{"id":147,"date":"2018-08-09T11:07:52","date_gmt":"2018-08-08T22:07:52","guid":{"rendered":"http:\/\/blog.wiseowls.co.nz\/?p=147"},"modified":"2026-03-08T00:41:02","modified_gmt":"2026-03-07T11:41:02","slug":"transaction-control-linq-to-sql-repositories","status":"publish","type":"post","link":"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/","title":{"rendered":"Seamless transaction control for MSTest code"},"content":{"rendered":"\n<p>Covering a legacy system with tests is always an interesting task:<\/p>\n\n\n\n<p><b>10<\/b> You want as much of your codebase covered before you are confident enough to start changing the system itself,<br><b>20<\/b> but you can not mock out external dependencies such as databases without touching too much code&#8230;<br><b>30<\/b> GOTO <b>10<\/b><\/p>\n\n\n\n<!--more-->\n\n\n\n<h3 class=\"wp-block-heading\">Okay, how do we break out of this loop?<\/h3>\n\n\n\n<p>One way to do so would be to not mock the database out: just have your tests <a href=\"https:\/\/stackoverflow.com\/questions\/3537972\/doing-a-rollback-repository-integration-tests\">clean up after themselves<\/a>. Easy, right?<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"csharp\" class=\"language-csharp\">[TestClass]\r\npublic class Testing {\r\n  [TestInitialise]\r\n  public void Init() {\r\n      var tran = new ScopedTransaction();\r\n    }\r\n    [TestMethod]\r\n  public void Test() {\r\n    using(var db = new DbContext()) {\r\n      db.ExecuteCommand(\"DELETE FROM tblEmployee;\");\r\n    }\r\n  }\r\n}<\/code><\/pre>\n\n\n\n<p>Kind of. What if at some point we&#8217;d like to have one test in a class commit something?<br>I guess we could save transaction handle as a field and refer back to it. But I still feel transaction control should not be part of <code>TestInitialise<\/code>  routine &#8211; we&#8217;re not exactly setting up tests here, we&#8217;re setting up the environment itself. And having to define a decorated method everywhere we need this trick seems repetitive. So is there a cleaner way of doing it that does not clutter our Init() method and still does the job? <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">A quick peek into <b>TestMethodAttribute<\/b> reveals &#8211; there actually is<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"csharp\" class=\"language-csharp\">using System;\r\n\r\nnamespace Microsoft.VisualStudio.TestTools.UnitTesting {\r\n  \/\/\/\r\n\r\n  The test method attribute.\r\n\r\n  [AttributeUsage(AttributeTargets.Method, AllowMultiple = false)]\r\n  public class TestMethodAttribute: Attribute {\r\n    \/\/\/\r\n\r\n    Executes a test method.\r\n\r\n    \/\/\/The test method to execute. \/\/\/ An array of TestResult objects that represent the outcome(s) of the test.\r\n    \/\/\/ Extensions can override this method to customize running a TestMethod.\r\n    public virtual TestResult[] Execute(ITestMethod testMethod) {\r\n      return new TestResult[1] {\r\n        testMethod.Invoke((object[]) null)\r\n      };\r\n    }\r\n  }\r\n}<\/code><\/pre>\n\n\n\n<p>Turns out Microsoft have thoughtfully made <code>TestMethodAttribute.Execute<\/code>  virtual meaning we can override it should we be inclined to do so: <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"csharp\" class=\"language-csharp\">[AttributeUsage(AttributeTargets.Method)]\r\npublic class TransactionTestMethodAttribute: TestMethodAttribute {\r\n  \/\/\/\r\n\r\n  Executes a test method.Wrapped in Transaction Scope.In the end the thansaction gets discarded effectively rolling eveything back\r\n\r\n  \/\/\/The test method to execute. \/\/\/ An array of TestResult objects that represent the outcome(s) of the test.\r\n  public override TestResult[] Execute(ITestMethod testMethod) {\r\n    using(new TransactionScope())\r\n    return base.Execute(testMethod);\r\n  }\r\n}<\/code><\/pre>\n\n\n\n<p>With above test attribute our tests look a bit better now:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"csharp\" class=\"language-csharp\">[TestClass]\npublic class Testing {\n  [TransactionTestMethodAttribute]\n  public void Test() {\n    using(var db = new DbContext()) {\n      db.ExecuteCommand(\"DELETE FROM tblEmployee;\"); \/\/that's it folks, everyone is fired\n    }\n  }\n}<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">We can take it a step further<\/h3>\n\n\n\n<p>and have other actions wrap around our tests. For example, we opted to temporarily disable all triggers in the database, well, because we could:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"csharp\" class=\"language-csharp\">[AttributeUsage(AttributeTargets.Method)]\npublic class NoTriggersTransactionTestMethodAttribute: TransactionTestMethodAttribute {\n    \/\/\/\n    \/\/\/\n\n    Executes a test method.Against a database with disabled triggers\n\n    \/\/\/The test method to execute. \/\/\/ An array of TestResult objects that represent the outcome(s) of the test.\n    public override TestResult[] Execute(ITestMethod testMethod) {\n      var db = new DbContext(); \/\/no using(dbcontext) here in favor of try..finally\n      try {\n        db.ExecuteCommand(\"sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'\");\n        return base.Execute(testMethod);\n      } finally {\n        db.ExecuteCommand(\"sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'\");\n        db.Dispose(); \/\/note we need to call this, as we opted to not go for c# using(dbcontext) syntax\n      }\n    }<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Covering a legacy system with tests is always an interesting task: 10 You want as much of your codebase covered before you are confident enough to start changing the system itself,20 but you can not mock out external dependencies such as databases without touching too much code&#8230;30 GOTO 10<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[11],"tags":[9,10,59],"class_list":["post-147","post","type-post","status-publish","format-standard","hentry","category-dev","tag-linq-to-sql","tag-mstest","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Seamless transaction control for MSTest code - Timur and associates<\/title>\n<meta name=\"description\" content=\"Wrapping MSTest methods in TransactionScope for automatic rollback. A clean alternative to cluttering TestInitialise.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Seamless transaction control for MSTest code - Timur and associates\" \/>\n<meta property=\"og:description\" content=\"Wrapping MSTest methods in TransactionScope for automatic rollback. A clean alternative to cluttering TestInitialise.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/\" \/>\n<meta property=\"og:site_name\" content=\"Timur and associates\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-08T22:07:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-03-07T11:41:02+00:00\" \/>\n<meta name=\"author\" content=\"timur\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@TimurKh\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2018\\\/08\\\/09\\\/transaction-control-linq-to-sql-repositories\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2018\\\/08\\\/09\\\/transaction-control-linq-to-sql-repositories\\\/\"},\"author\":{\"name\":\"timur\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/#\\\/schema\\\/person\\\/34d0ed30d573b5bc317ea990bd2e0c59\"},\"headline\":\"Seamless transaction control for MSTest code\",\"datePublished\":\"2018-08-08T22:07:52+00:00\",\"dateModified\":\"2026-03-07T11:41:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2018\\\/08\\\/09\\\/transaction-control-linq-to-sql-repositories\\\/\"},\"wordCount\":250,\"keywords\":[\"linq to sql\",\"mstest\",\"sql\"],\"articleSection\":[\"Development\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2018\\\/08\\\/09\\\/transaction-control-linq-to-sql-repositories\\\/\",\"url\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2018\\\/08\\\/09\\\/transaction-control-linq-to-sql-repositories\\\/\",\"name\":\"Seamless transaction control for MSTest code - Timur and associates\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/#website\"},\"datePublished\":\"2018-08-08T22:07:52+00:00\",\"dateModified\":\"2026-03-07T11:41:02+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/#\\\/schema\\\/person\\\/34d0ed30d573b5bc317ea990bd2e0c59\"},\"description\":\"Wrapping MSTest methods in TransactionScope for automatic rollback. A clean alternative to cluttering TestInitialise.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2018\\\/08\\\/09\\\/transaction-control-linq-to-sql-repositories\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2018\\\/08\\\/09\\\/transaction-control-linq-to-sql-repositories\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2018\\\/08\\\/09\\\/transaction-control-linq-to-sql-repositories\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Seamless transaction control for MSTest code\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/#website\",\"url\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/\",\"name\":\"Timur and associates\",\"description\":\"Notes of an IT contractor\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/#\\\/schema\\\/person\\\/34d0ed30d573b5bc317ea990bd2e0c59\",\"name\":\"timur\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg\",\"caption\":\"timur\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/TimurKh\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Seamless transaction control for MSTest code - Timur and associates","description":"Wrapping MSTest methods in TransactionScope for automatic rollback. A clean alternative to cluttering TestInitialise.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/","og_locale":"en_US","og_type":"article","og_title":"Seamless transaction control for MSTest code - Timur and associates","og_description":"Wrapping MSTest methods in TransactionScope for automatic rollback. A clean alternative to cluttering TestInitialise.","og_url":"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/","og_site_name":"Timur and associates","article_published_time":"2018-08-08T22:07:52+00:00","article_modified_time":"2026-03-07T11:41:02+00:00","author":"timur","twitter_card":"summary_large_image","twitter_creator":"@TimurKh","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/#article","isPartOf":{"@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/"},"author":{"name":"timur","@id":"https:\/\/blog.wiseowls.co.nz\/#\/schema\/person\/34d0ed30d573b5bc317ea990bd2e0c59"},"headline":"Seamless transaction control for MSTest code","datePublished":"2018-08-08T22:07:52+00:00","dateModified":"2026-03-07T11:41:02+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/"},"wordCount":250,"keywords":["linq to sql","mstest","sql"],"articleSection":["Development"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/","url":"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/","name":"Seamless transaction control for MSTest code - Timur and associates","isPartOf":{"@id":"https:\/\/blog.wiseowls.co.nz\/#website"},"datePublished":"2018-08-08T22:07:52+00:00","dateModified":"2026-03-07T11:41:02+00:00","author":{"@id":"https:\/\/blog.wiseowls.co.nz\/#\/schema\/person\/34d0ed30d573b5bc317ea990bd2e0c59"},"description":"Wrapping MSTest methods in TransactionScope for automatic rollback. A clean alternative to cluttering TestInitialise.","breadcrumb":{"@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2018\/08\/09\/transaction-control-linq-to-sql-repositories\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/blog.wiseowls.co.nz\/"},{"@type":"ListItem","position":2,"name":"Seamless transaction control for MSTest code"}]},{"@type":"WebSite","@id":"https:\/\/blog.wiseowls.co.nz\/#website","url":"https:\/\/blog.wiseowls.co.nz\/","name":"Timur and associates","description":"Notes of an IT contractor","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.wiseowls.co.nz\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/blog.wiseowls.co.nz\/#\/schema\/person\/34d0ed30d573b5bc317ea990bd2e0c59","name":"timur","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg","caption":"timur"},"sameAs":["https:\/\/x.com\/TimurKh"]}]}},"_links":{"self":[{"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/posts\/147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/comments?post=147"}],"version-history":[{"count":20,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/posts\/147\/revisions"}],"predecessor-version":[{"id":913,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/posts\/147\/revisions\/913"}],"wp:attachment":[{"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/media?parent=147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/categories?post=147"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/tags?post=147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}