CODEWITHSUNDEEP
c#.netdatabaseunit-testing
JoshSub
JoshSub

Reputation: 509

How to 'Unit' Test a database access layer

I have written a .Net application which has many components, some of those components are database access layers which abstract from the rest of the components where the data comes from.

I have unit tested the rest of the components by mocking the database access layer. One way I have of testing the database access layers is to use create new empty databases on the test servers. This can be slow, and most would argue that it is not a unit tests as I depend on the database server.

What I think I want, is a mocked database which I can use to test my database access layer. The mocked database can be given a schema and process SQL commands as if it were a remote database, but in fact it is all in-memory. Does this exists? Or how else can I test my SQL and database <-> data model code.

To solve my problem you may want to know I am using SQL Server, versions 2008 and later, and my code is written in C#, running with .Net 4.5 and using Visual Studio 2013

Note: I do not want to use Linq2SQL/EntityFramework to replace my database access layer, as in my experience it results difficult to debug issues and performance problems.

I tried to phrase my question carefully to avoid people lecturing me on their beliefs in what should be tested and how, but perhaps to be a little more blunt: I want to unit test my SQL, small changes to that have a big impact on the outcome of the program. I do have integration tests, but it takes much longer to create a release for the test environment than it does to tweak code and run the unit tests. I appreciate people taking the time to read my question and respond anyhow.

Upvotes: 0

Views: 1770

Answers (1)

blas3nik
blas3nik

Reputation: 1381

I don't know if it's going to be the best answer, but. The way we're doing is that we're using SQLite, which is an in-memory database. There are a number of different ways to set it up, we use NHibernate as an ORM, and for that it is fairly easy to set up using FluentNHibernate, but I don't think it's much harder using any other framework either:

Fluently.Configure()
            .Database(SQLiteConfiguration.Standard.InMemory())
            .Mappings(m => ... )
            .BuildConfiguration()
            .BuildSessionFactory();

I think you can run queries against a SQLite database without any ORMs as well, e.g. using the base SqlConnection class.

This database can accept migrations, schemas, etc. It behaves very close to a proper MsSql database, we can run any DDL an DML statements against it, and it works fine. Since it's all in-memory, it's also pretty fast.

Upvotes: 1

Related Questions