title: c# unit testing, mocking stored procedure
body: <p>In the past when I have been implementing unit test I have struggled to set up 'decent' unit tests for data access layers because they often have a database as an external dependency.  In an ideal world I would mock the stored procedure calls so that the external dependency is removed.  </p>
  
  <p>However I have been unable to work out how to do this with the MOQ mocking framework, or find any other framework which supports this.  Instead I have reverted to creating a scripted test database with known data in (so that I can always get outputs I expect) but this is slightly different from Mocking that layer.</p>
  
  <p>Can anyone suggest how to Mock this part of the data access layer [I know for Entity Framework that <a href="https://effort.codeplex.com/" rel="nofollow">https://effort.codeplex.com/</a> exists]?</p>
  
  <p><hr>
  <b>Detail</b>
  For instance if I have the following method</p>
  
  <pre><code>public object RunStoredProc()
  {
      //Some Setup
  
      using (SqlConnection conn = new SqlConnection(CONNNECTION_STRING))
      {
          using (SqlCommand comm = new SqlCommand("storedProcName", conn))
          {
              conn.Open();
              comm.CommandType = CommandType.StoredProcedure;
              using (SqlDataReader reader = comm.ExecuteReader())
              {
                  while (reader.Read())
                  {
                      //Logic
                  }
              }
          }
      }
  
      //Return object based on logic
  }
  </code></pre>
  
  <p>then how do I mock the stored procedure output so that <code>SQLDataReader</code> contains specified data.  At a higher level I could mock the <code>RunStoredProc()</code> method - but that won't help me test whether the logic in that method is correct.  Alternatively I could strip the <code>SQLReader</code> out into another method</p>
  
  <pre><code>public object RunStoredProc()
  {
      //Some Setup
  
      List&lt;object&gt; data = GetData();
      //Logic
  
      //Return object based on logic
  }
  
  private List&lt;object&gt; GetData()
  {
      using (SqlConnection conn = new SqlConnection(CONNNECTION_STRING))
      {
          using (SqlCommand comm = new SqlCommand("storedProcName", conn))
          {
              conn.Open();
              comm.CommandType = CommandType.StoredProcedure;
              using (SqlDataReader reader = comm.ExecuteReader())
              {
                  while (reader.Read())
                  {
                      //place into return object
                  }
              }
          }
      }
  }
  </code></pre>
  
  <p>but as the 'GetData' method should be private (not part of the published interface) I then wouldn't be able to mock that and so the issue remains.</p>