title: Trying to find a way to implement a nested transaction for my project
body: <p>I recently created a light weight ORM tool in C# and posted it on  <a href="https://www.github.com/RiceRiceBaby/ADOCRUD" rel="nofollow">Github</a>. One of the things my ORM does is manage the opening and closing of connections for you along with opening and disposing of a sql transaction. The way I do this is I open the connection and begin the sql transaction in the constructor of my context class and close the connection and dispose the transaction on the dispose method of my context class. The code below are some methods from my tool:</p>
  
  <pre><code>public class ADOCRUDContext : IDisposable
  {
      internal IDbConnection sqlConnection;
      internal IDbTransaction sqlTransaction;
  
      internal string suffix = "_";
  
      public ADOCRUDContext(string connectionString)
      {            
          sqlConnection = new SqlConnection(connectionString);
          sqlConnection.Open();
          sqlTransaction = sqlConnection.BeginTransaction();
      }
  
      public void Commit()
      {
          sqlTransaction.Commit();
      }
  
      /// &lt;summary&gt;
      /// Disposes transaction and closes sql connection
      /// &lt;/summary&gt;
      public void Dispose()
      {
          sqlTransaction.Dispose();
          sqlConnection.Close();
      }
  
      public void Insert&lt;T&gt;(T item)
      {
          string tableName = ObjectTypeHelper.GetTableName&lt;T&gt;(item);
  
          // Properties of object that are not primary keys
          PropertyInfo[] modelProperties = ObjectTypeHelper.GetModelProperties&lt;T&gt;(item, false);
  
          // Properties of object that are also primary keys
          PropertyInfo[] primaryKeyProperties = ObjectTypeHelper.GetPrimaryKeyProperties&lt;T&gt;(item);
  
          if (modelProperties == null || modelProperties.Count() == 0)
          {
              // Moves composite keys to model properties if they are the only properties of the table/object
              if (primaryKeyProperties.Count() &gt; 0)
                  modelProperties = primaryKeyProperties;
              else
                  throw new Exception("Class has no properties or are missing Member attribute");
          }
  
          // Generates insert statement
          StringBuilder query = new StringBuilder();
          query.Append("insert into " + tableName + "(" + String.Join(",", modelProperties.Select(x =&gt; x.Name)) + ") ");
          query.Append("values (" + String.Join(", ", modelProperties.Select(x =&gt; "@" + x.Name+suffix)) + ") ");
          query.Append("select @primaryKey = @@identity");
  
          try
          { 
              // Generates and executes sql command 
              SqlCommand cmd = GenerateSqlCommand&lt;T&gt;(item, query.ToString(), modelProperties, null, ADOCRUDEnums.Action.Insert, suffix);
              cmd.ExecuteNonQuery();
  
              // Loads db generated identity value into property with primary key attribute
              // if object only has 1 primary key
              // Multiple primary keys = crosswalk table which means property already contains the values
              if (primaryKeyProperties != null &amp;&amp; primaryKeyProperties.Count() == 1)
                  primaryKeyProperties[0].SetValue(item, cmd.Parameters["primaryKey"].Value as object);
          }
          catch (Exception ex)
          {
              sqlTransaction.Rollback();
              throw new Exception(ex.Message);
          }
      }
  </code></pre>
  
  <p>Below is an example of how you use my tool:</p>
  
  <pre><code>public void AddProduct(Product p)
  {
    using (ADOCRUDContext context = new ADOCRUDContext(connectionString))
    {
      context.Insert&lt;Product&gt;(p);
      context.Commit();
    }
  }
  
  public void UpdateProduct(int productId)
  {
    using (ADOCRUDContext context = new ADOCRUDContext(connectionString))
    {
      Product p = this.GetProductById(productId);
      p.Name = "Basketball";
      context.Update&lt;Product&gt;(p);
      context.Commit();
    }
  }
  </code></pre>
  
  <p>Currently if I do something like:</p>
  
  <pre><code>public void DoSomething(Product p)
  {
      using (ADOCRUDContext context = new ADOCRUDContext(connectionString))
      {
          this.AddProduct(p);
          p.Name = "Test";
          throw new Exception("Blah");
      }
  }
  </code></pre>
  
  <p>Even though I threw an exception on DoSomething, the product still got added to the database since the inner transaction was committed. Is there a way to get the inner transaction (AddProduct) to fail if the outer transaction fails?</p>