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

Question

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

Samuel David 21

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

P a u l 10,761 Reputation points

2022-12-11T14:16:19.487+00:00

Are you testing your DAL methods in isolation or are you testing them through another layer?
Samuel David 21 Reputation points

2022-12-11T15:23:56.247+00:00

I have another layer. But not sure how to code cover this DAL methods
P a u l 10,761 Reputation points

2022-12-11T17:17:14.427+00:00

It depends what you're trying to test really. If you're trying to test some business logic in your business layer & the business layer interacts with DA objects, then you'd normally switch out any concrete types for your DA objects for interfaces.

You would then just need to register your concrete DAL types as implementations of your interfaces when defining your dependency injection services in your app startup. Inside your test project you'd register these same interfaces, except you'd use your mock types instead. The implementation of these mocks could be as simple as hard-coded values or in-memory List<>s of your entities in order to test the full round trip of write & read operations.

Alternatively you could spin up a new database (in-memory or otherwise) to run your tests against and forget mocking your DA all together, then after your tests have finished you rip it down. The downside of this is that there's an overhead (likely in both time & memory), but the benefit is that you're not testing a mock that won't see production & you don't have an explosion of mock classes as your project grows.

1 answer

Your answer

P a u l 10,761 Reputation points

2022-12-11T14:16:19.487+00:00

Are you testing your DAL methods in isolation or are you testing them through another layer?
Samuel David 21 Reputation points

2022-12-11T15:23:56.247+00:00

I have another layer. But not sure how to code cover this DAL methods
P a u l 10,761 Reputation points

2022-12-11T17:17:14.427+00:00

It depends what you're trying to test really. If you're trying to test some business logic in your business layer & the business layer interacts with DA objects, then you'd normally switch out any concrete types for your DA objects for interfaces.

You would then just need to register your concrete DAL types as implementations of your interfaces when defining your dependency injection services in your app startup. Inside your test project you'd register these same interfaces, except you'd use your mock types instead. The implementation of these mocks could be as simple as hard-coded values or in-memory List<>s of your entities in order to test the full round trip of write & read operations.

Alternatively you could spin up a new database (in-memory or otherwise) to run your tests against and forget mocking your DA all together, then after your tests have finished you rip it down. The downside of this is that there's an overhead (likely in both time & memory), but the benefit is that you're not testing a mock that won't see production & you don't have an explosion of mock classes as your project grows.

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.

Share via

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

1 answer

Your answer