title: How to implement the Unit of Work(UoW) pattern with ADO.NET and use it in ASP.NET MVC
body: <p>I know this question has been asked before, but in those questions there aren't enough details about the actual implementation, so I decided to search for some information about how can this be achieve and this is what I've got so far:</p>
  
  <p><strong>The IUnitOfWork Interface</strong>:</p>
  
  <pre><code>public interface IUnitOfWork : IDisposable
  {
      IDomainTableRepository DomainTables { get; }        
      IVariableRepository Variables { get; }
      IModelRepository Models { get; }     
      IStructureRepository Structures { get; }
      ISentenceRepository Sentences { get; }
      IExpressionRepository Expressions { get; }
      IReturnRepository Returns { get; }
  
      void Commit();
  }
  </code></pre>
  
  <p><strong>The implementation for IUnitOfWork:</strong></p>
  
  <pre><code>public class SqlUnitOfWork : IUnitOfWork
      {
          const string ConnectionStringName = "DefaultConnection";    
          private AdoNetContext _context;
          private DomainTableRepository _domainTables;
          private VariableRepository _variables;   
          private ModelRepository _models;      
          private StructureRepository _structures; 
          private SentenceRepository _sentences;
          private ExpressionRepository _expressions;            
          private ReturnRepository _returns; 
  
          public SqlUnitOfWork()
          {
              var connectionString =
                   ConfigurationManager
                   .ConnectionStrings[ConnectionStringName]
                   .ConnectionString;
  
              _context = new AdoNetContext(connectionString, true); 
          }       
          public IDomainTableRepository DomainTables
          {
              get
              {
                  if (_domainTables == null)
                  {
                      _domainTables = new DomainTableRepository(_context);
                  }
                  return _domainTables;
              }
          }      
  
          //...getters for the remaining repositories    
  
          public void Commit()
          {
              _context.SaveChanges();
          }
  
          #region IDisposable Support
          private bool disposedValue = false; 
  
          protected virtual void Dispose(bool disposing)
          {
              if (!disposedValue)
              {
                  if (disposing)
                  {
                      _context.Dispose();
                  } 
                  disposedValue = true;
              }
          } 
          public void Dispose()
          {            
              Dispose(true);            
          }
          #endregion
  
  
      }
  </code></pre>
  
  <p><strong>The AdoNetContext class</strong> </p>
  
  <p>All the credit for the implementation of this class goes to @jgauffin.</p>
  
  <p><a href="http://blog.gauffin.org/2014/01/unit-of-work-sample-implementation-for-ado-net/" rel="nofollow">His original blog post can be found here.</a>  </p>
  
  <pre><code>public class AdoNetContext : IDisposable
      {
          private IDbConnection _connection;
          private bool _ownsConnection;
          private IDbTransaction _transaction;
  
          public AdoNetContext(string connectionString, bool ownsConnection)
          {
              _connection = new SqlConnection(connectionString);
              _connection.Open();
              _ownsConnection = ownsConnection;
              _transaction = _connection.BeginTransaction();
          }    
          public IDbCommand CreateCommand()
          {                
              var command = _connection.CreateCommand();
              command.Transaction = _transaction;
              return command;
          }    
          public void SaveChanges()
          {
              if (_transaction == null)
              {
                  throw new InvalidOperationException("Transaction have already been already been commited. Check your transaction handling.");
              }
              _transaction.Commit();
              _transaction = null;
          }    
          public void Dispose()
          {
              if (_transaction != null)
              {
                  _transaction.Rollback();
                  _transaction = null;
              }    
              if (_connection != null &amp;&amp; _ownsConnection)
              {
                  _connection.Close();
                  _connection = null;
              }
          }
      }
  </code></pre>
  
  <p><strong>The implementation of a repository(DomainTableRepository) that uses AdoNetContext:</strong></p>
  
  <pre><code>public class DomainTableRepository : IDomainTableRepository
      {
          private AdoNetContext _context;    
          public DomainTableRepository(AdoNetContext context)
          {
              _context = context;
          }    
          public DomainTable Get(int id)
          {
              DomainTable table;
              using (var commandTable = _context.CreateCommand())
              {
                  commandTable.CommandType = CommandType.StoredProcedure;
                  commandTable.CommandText = "up_DomainTable_GetById";
  
                  commandTable.Parameters.Add(commandTable.CreateParameter("@pId", id));
  
                  table = ToList(commandTable).FirstOrDefault();
              }
              return table;
          }    
          public IEnumerable&lt;DomainTable&gt; GetAll(int pageIndex = 1, int pageSize = 10)
          {    
              using (var command = _context.CreateCommand())
              {
                  command.CommandType = CommandType.StoredProcedure;
                  command.CommandText = "up_DomainTable_GetAll";
                  command.Parameters.Add(command.CreateParameter("@pPageIndex", pageIndex));
                  command.Parameters.Add(command.CreateParameter("@pPageSize", pageSize));    
                  return ToList(command).ToList();
              } 
          }    
          public int Remove(DomainTable entity)
          {
              using (var command = _context.CreateCommand())
              {
                  command.CommandType = CommandType.StoredProcedure;
                  command.CommandText = "up_DomainTable_Delete";
                  command.Parameters.Add(command.CreateParameter("@pId", entity.Id));
                      return command.ExecuteNonQuery();
              }
          }       
      }
  </code></pre>
  
  <p>I apologize for posting so much code,but I want to make sure you understand everything about what I want to ask.</p>
  
  <p>First, I created an interface for the UoW(IUnitOfWork), because in the future I might want to change to an ORM like EF, but since I'm just taking my first step into the the world of back-end programming I wanted to start with ADO.NET and stored procedures.</p>
  
  <p>So here are my questions regarding the implementation of the UoW pattern with ADO.NET and its usage in ASP.NET MVC:</p>
  
  <p>1)Given the code for the <strong>AdoNetContext</strong>(the one that manages the db connection and transactions), <strong>is there any performance penalty for always creating a transaction?</strong> </p>
  
  <p>This is what I read in a SQL book :<em>"Executing a SELECT
  statement within a transaction can create locks on the referenced tables, which can in turn block other users or sessions from performing work or reading data"</em>  Do the transactions created in C# behave exactly as their counterparts in SQL?</p>
  
  <p>2)As you can see in IUnitOfWork and its implementation (UnitOfWork), I have readonly properties for every repository I have in my application. This a common "pattern" I've seen in lots of tutorials on EF and I found it very convenient since every time I new up a instance of IUnitOfWork I already have access to all its repositories and I don't have to clutter my code with the instantiation of the repositories I need in a specific <code>Action</code> (from a <code>Controller</code>). </p>
  
  <p><strong>Do you think it would too much overhead to instantiate all the repositories (as SQLUnitOfWork does) every time I create/inject a new instance of IUnitOfWork?</strong> There are some scenarios in which I might work with 4 or 5 repositories at the same time, but in most cases I'll be using just 1 or 2 in the same <code>action</code>.</p>
  
  <p>3) I would like to use DI (maybe Ninject) to inject an instance of IUnitOfWork into my <code>Controller</code>s. This way later when I use another persistence framework, I will only have to change this line <code>kernel.Bind&lt;IUnitOfWork&gt;().To&lt;SqlUnitOfWork&gt;();</code> From <code>SqlUnitOfWork</code>, to let's say <code>EFUnitOfWork</code>.</p>
  
  <p>The problem I've encountered here is that the <code>Dispose</code> methods in <code>SqlUnitOfWork</code> and <code>AdoNetContext</code> are never called and as much as I want to use DI I'd rather wrap the instantiation of <code>SqlUnitOfWork</code> in an <code>using</code> statement in every <code>Action</code> instead of be leaking memory just so that I can use DI.</p>
  
  <p>So this is what I wanted to ask you guys.</p>
  
  <p>I'm trying to learn so please before clicking the close link or marking the question as a duplicate take some time to read my question.</p>
  
  <p>Thanks.</p>