chevron-thin-right chevron-thin-left brand cancel-circle search youtube-icon google-plus-icon linkedin-icon facebook-icon twitter-icon toolbox download check linkedin phone twitter-old google-plus facebook profile-male chat calendar profile-male
Welcome to Typemock Answers. Here you can ask and receive answers from other community members. And if you liked or disliked an answer or thread: react with an up- or downvote Enjoy!
0 votes
The following is the simplified version of the production code I have:

public static int LongProcExecute(string storedProcedure, SqlParameter[] sqlParams)
{

using (SqlConnection conn = new SqlConnection(GetDBConnection()))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = storedProcedure;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300000;

for (int i = 0; i < sqlParams.Length; i++) cmd.Parameters.Add(sqlParams[i]);

object result; conn.Open();
return cmd.ExecuteNonQuery();
}
}

I know that a unit test should not cover the interaction with database (integration test), so is there anyway to write unit test for this method using TypeMock?

Many thanks.
asked by (3k points)

5 Answers

0 votes
I am not sure whether this is a correct way but I wrote the following and it works:

[TestMethod()]
[Isolated]
public void LongProcExecuteTest_NoResultSet()
{
#region Setup
string storedProcedure = "my sp";
string instance = "my instance";
SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("param1", "value1"), new SqlParameter("param2", "value2") };
int expected = 2;

// mock GetConnString() ...

// mock SqlConnection
SqlConnection fakedSqlConnection = Isolate.Fake.Instance<SqlConnection>();
Isolate.SwapNextInstance<SqlConnection>().With(fakedSqlConnection);

// mock Open()
Action openAction = delegate() { fakedSqlConnection.Open(); };
Isolate.WhenCalled(openAction).IgnoreCall();

// mock CreateCommand()
SqlCommand fakedSqlCommand = Isolate.Fake.Instance<SqlCommand>(Members.ReturnRecursiveFakes);
Func<SqlCommand> createCommandFunc = delegate() {return fakedSqlConnection.CreateCommand();};
Isolate.WhenCalled<SqlCommand>(createCommandFunc).WillReturn(fakedSqlCommand);

// mock ExecuteNonQuery()
Func<int> executeNonQueryFunc = delegate() { return fakedSqlCommand.ExecuteNonQuery(); };
Isolate.WhenCalled<int>(executeNonQueryFunc).WillReturn(expected);
#endregion

// exercise
int actual = (int) Utils.LongProcExecute(storedProcedure, sqlParams);

// verify
Assert.AreEqual(expected, actual);
Isolate.Verify.WasCalledWithExactArguments(connectionFunc);
Isolate.Verify.WasCalledWithExactArguments(openAction);
Isolate.Verify.WasCalledWithExactArguments(executeNonQueryFunc);

}

What I am not testing are:
Whether the stored procedure name is available in DB
Whether the Sql parameters are valid for the stored procedure.

Hmm :?:
answered by (3k points)
0 votes
I would actually not recommend that you write unit tests against a data access layer, because you will not be testing the database related logic (triggers, security and related tables etc...)

I would recommend to do integration testing instead, without faking anything the data talks to.
An oldie but a goodie that I wrote I feel still applies:
http://msdn.microsoft.com/en-us/magazine/cc163772.aspx

I would recommend however, to write unit tests for the layers above the DAL.
answered by (2k points)
0 votes
Thank you Royo, good article.

Integration testing is what I was thinking about but not sure how to write it properly.

Do you know where I can find some source code examples?

Thanks.
answered by (3k points)
0 votes
if you use MbUnit they have a [Rollback] attribute that creates rolls back a transaction around your test.
if not, you can simply initizlie a new TransactionScope in your setup and dispose it in your teardown and any data you put in the DB is rolled back.

these are simple tests:
- call some DAL method
- check that some row is now in the DB (or not there anymore)

http://mnour.blogspot.com/2008/06/mbuni ... -test.html
answered by (2k points)
0 votes
If you have Team System, or can afford the add-on, the Microsoft Database Testing tools allow you to test your stored procedures/constraints/keys/etc without having to test it through your DAL, thus allowing you to only have to do functional tests on your DAL (which I do my faking the connection and command and returning fake readers).
answered by (21.8k points)
...