title: How to mock SqlParameterCollection using Moq
body: <p>I am trying to mock database operations. I have problem in mocking <code>SqlParameterCollection</code>. I tried to create <code>virtual</code> method that will return <code>DbParameterCollection</code> but then i am loosing all the functionality that <code>SqlParameterCollection</code> gives like <code>AddWithValue</code> etc. Is there a way i can mock <code>SqlParameterCollection</code>? Is there any other approach to unit test DAL? I am using Moq. </p>
  
  <p>Code goes like this:</p>
  
  <p>in DAL:</p>
  
  <pre><code>protected virtual IDbConnection GetConnection(string connectionString)
  {
      return new SqlConnection(connectionString);
  }
  
  protected virtual IDbCommand GetCommand(IDbConnection cn)
  {
      return cn.CreateCommand();
  }
  
  protected virtual IDbTransaction GetTransaction(IDbConnection cn)
  {
      return cn.BeginTransaction(IsolationLevel.Serializable);
  }
  
  Public Bool InsertInDatabase(DataTable dt)
  {
     using (IDbConnection cn = GetConnection(cnstr))
        {
           cn.Open();
  
              using (IDbTransaction tran = GetTransaction(cn))
              {
                  IDbCommand cmd = GetCommand(cn);
                  cmd.Transaction = tran;
                  cmd.Connection = cn;
                  cmd.CommandType = CommandType.StoredProcedure;
  
                  cmd.CommandText = "sp_InsertInDatabase";
                  SqlParameterCollection cmdParams = cmd.Parameters as SqlParameterCollection;
                  cmdParams.AddWithValue("@param1", dt);
                  cmd.ExecuteNonQuery();
             }
       }
  }
  </code></pre>
  
  <p>In Unit test project:</p>
  
  <pre><code>    protected override IDbConnection GetConnection(string connectionString)
      {
          return Mock.Of&lt;IDbConnection&gt;();
      }
  
      protected override IDbCommand GetCommand(IDbConnection cn)
      {
          return Mock.Of&lt;IDbCommand&gt;();
      }
  
      protected override IDbTransaction GetTransaction(IDbConnection cn)
      {
          return Mock.Of&lt;IDbTransaction&gt;();
      }
  
      public void TestInsertInDatabase()
      {
          base.InsertInDatabase(new DataTable());
      }
  </code></pre>
  
  <p>--Solution--</p>
  
  <p>Created an extension method to add parameter with value. Thank you Marc Gravell for pointing me to that direction.</p>
  
  <pre><code>    public static IDbDataParameter AddParameterWithValue(this IDbCommand cmd, string paramName, object paramValue)
      {
  
          var dbParam = cmd.CreateParameter();
          if (dbParam != null)
          {
              dbParam.ParameterName = paramName;
              dbParam.Value = paramValue;
          }
          return dbParam;
  
      }
  </code></pre>