How to write unit test for DAL using TypeMock?

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 :?:

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.

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.

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

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