Unit testing a sql string

Let me try to understand your problem.

You have got an winform application and you are writing unit tests for this. But if you run the the test, you afraid it will going to hit the DB and spoil the data. So you want some mechanism which allows to run your unit tests but will not hit the actual database. Correct?

If I got your problem right, I suggest to separate out your db interaction logic and make it interface driven. Then you can create mock objects, wherein you define the expectation of your db interaction interfaces. So for example, if some GetSales() method is called, what should be returned from this method? and so on. Sharing some links on details about unit testing and mocking.

https://msdn.microsoft.com/en-us/library/ff650441.aspx

https://github.com/Moq/moq4

http://www.developerhandbook.com/unit-testing/writing-unit-tests-with-nunit-and-moq/

Testing a MVC Controller fails with NULL reference exception

A SQLite database is designed for exactly this kind of requirement.

The database is a simple file. The database driver reads from and writes to this file. You can run all the SQL queries and so on that you are used to against the database. (However, you might have problems if your SQL uses language or syntax specific to SQL Server.)

It is true that you have to manage a 'database' full of data. But you should be able to:

• Write a script which quickly sets up all the tables. You might already have a piece of code which create a database with the same schema, or you might be able to dump one automatically.
• Keep some test data around in CSV's, SQL files, or similar. This isn't easy, but it is very very useful. You should add the minimum and only build it up as the testing demands.
• Check the whole SQLite database file into source control if you like.

Thanks for the question - I have been thinking a lot about testable database code recently, and I hadn't figured out a solution to this type of problem until your question made me realize I already knew it.

In my opinion, there are three approaches to make this kind of testing as painless as possible in the long-run:

1. Use an ORM or another wrapper layer, such as Entity Framework as you mentioned. This means that when testing you don't need a 'real database' at all - just a test double of your wrapper.
2. Only use standard portable SQL such as JOIN, SELECT, etc, with nothing which can't be run on a SQLite database. This can be very restrictive, as types vary so much between DBMS.
3. Use SPs exclusively as an interface to your database. This means that your test double only has to recognize which SP is being called, and respond correctly to that. I personally don't like this approach as I think lots of untested, unversioned business logic ends up in the SPs.

Here is an option that we use for our unit testing with MS test

Use the TransactionScope from the System.Transactions namespace in your TestInitialize method Create an instance of the transaction and in the TestCleanup method dispose of it. You can do the insert into the db in the test initialize method or in the individual test methods

[TestCleanup]  
public void testClean()  
{  
    _Trans.Dispose();

} 


[TestInitialize]  
public void testInit()  
{  
    _Trans = new TransactionScope(); 
}   

[TestMethod]
public void TestQuery()
{
    // arrange
    //' insert data
    // act
    Obj Target = Obj.New();
    // Assert
    Assert.AreEqual("someValue",Obj.SomeProperty);
}

Okay I took the old adage of if you cant change your employer, change your employer.

I think tsqlt http://tsqlt.org/ would of been the best fit for this exact problem.

As Entity framework would not be allowed and they had so many dependency tables. Which would let me mock tables move all logic to stored procedures and mock the tables. Kinda mix of Yogi and JWG answer.