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
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 Ben (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 Ben (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 royo (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 Ben (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 royo (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 boo (21.8k points)
...