How to mock SqlParameterCollection using Moq

Question

I am trying to mock database operations. I have problem in mocking SqlParameterCollection. I tried to create virtual method that will return DbParameterCollection but then i am loosing all the functionality that SqlParameterCollection gives like AddWithValue etc. Is there a way i can mock SqlParameterCollection? Is there any other approach to unit test DAL? I am using Moq.

Code goes like this:

in DAL:

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();
           }
     }
}

In Unit test project:

    protected override IDbConnection GetConnection(string connectionString)
    {
        return Mock.Of<IDbConnection>();
    }

    protected override IDbCommand GetCommand(IDbConnection cn)
    {
        return Mock.Of<IDbCommand>();
    }

    protected override IDbTransaction GetTransaction(IDbConnection cn)
    {
        return Mock.Of<IDbTransaction>();
    }

    public void TestInsertInDatabase()
    {
        base.InsertInDatabase(new DataTable());
    }

--Solution--

Created an extension method to add parameter with value. Thank you Marc Gravell for pointing me to that direction.

    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;

    }

Answer 1

Personally, I approach this problem by writing an AddParameterWithValue extension method to DbCommand (or IDbCommand). It has to be on the command so that you have access to CreateParameter, and then call .Parameters.Add.

This allows easy usage against any ADO.NET stack, including abstractions like logging decorators.