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:

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

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

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

// mock ExecuteNonQuery()
Func<int> executeNonQueryFunc = delegate() { return fakedSqlCommand.ExecuteNonQuery(); };

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

// verify
Assert.AreEqual(expected, actual);


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:

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?

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) ... -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)