How to write Unit test for DAL which call stored procedure and direct sql commands

Question

Hello Everyone,

I am writing unit test cases(using xunit) for DAL methods(which are calling stored procedures and some direct sql commands and not returning methods). How can I mock the results coming from db in unit tests.

public class Notification : Base, INotificationBal  
{  
public void InsertNotification(NotificationType notif)  
{  
using(var con=GetConnction())  
{  
using(var command = CreatSqlCommand(con, "sp_InsertNotifDetails"))  
{  
//notif.NotificationType=1, notif.NotificationName="Mobile"  
command.SetParamsValues(  
("@NotificationType", notif.NotificationType),  
("@NotificationName", notif.NotificationName)  
));  
command.ExecuteNonQuery());  
}  
}  
}  
}  

Please let me know sample references or suggestions.

Thanks,
Sam

Answer 1

It usually not recommended to do unit test for a database. These should be integration tests. You dal should be easily mockable to implement unit test for business logic rather than database access.

If you decide to unit test data access, then you need a way to setup the test database before the test, and cleanup after the test so it is repeatable.

You may find it better to create a sql database project and add unit tests to the database project rather than the dal, which should be a simple mapping layer.