Testing your databases via C# tests

Question

I've been assigned the task of testing our database. It's a test database and we can do anything we want to it and easily roll it back. I've been given this task because we're still in design phase'ish (meaning any changes at any point in time of the project can happen... renaming the Person.FirstName column to [First_Name] and later renaming it to [First Name]. My goals is to establish a rough estimate for what kind of pain we're walking in to when we make changes so we can plan for this ahead of time. We can also expect these kinds of changes during production too.

The items I have on my list are and have written tests for:

• Send in the word null (not a literal null but "null") because using dynamic SQL it can flip out thinking you really mean null. We found this out because someone with the last name "null" caused an exception to be thrown.

• Using single quotes because dynamic SQL no likely single quotes. Again, someone with one in their name caused a crash.

Having never done this before, that's about all I know that can crash. Any other ideas out there? We're trying to emulate data a user may enter.

edit 1: Our problem is we have a search screen with roughly 25 fields they can search by. Some of these search fields are simple (e.g. first name) some are less simple (category 1 with a date less than 2 but also has category 2 with a date greater than 2 OR has category 4 at any period of time). The search screen allows for a user to select different oeprators and predicates with each of these 25 fields. Is there a better way to handle this than dynamic SQL? I'm in a position and a point in time where we can change to something different if it's better.

edit 2: I don't know if it's worth mentioning, but we use LINQ to access the stored procedures. My research has shown that dynamic LINQ won't do what we need it to do like a dynamic SQL query will. May be wrong though.

Answer 1

Does "'; Drop Table Person; --" cause a crash too?

You should really consider moving your strategy away from dynamic SQL to parameterized queries to avoid SQL injection techniques.

As for the C# testing of your database, you can use transactionized queries and nUnit to do unit testing - of a fashion. Strictly speaking, unit testing is supposed to separate your application from the data store so that the component parts can be tested without the performance penalties of accessing and modifying the data store. However, you can use very similar techniques to test your data store if that's what you decide. Create the transaction in the TestFixtureSetup and roll it back in the TestFixtureTearDown that way your database will be back to the original state when your testing is complete.

You should certainly be aware that there is a performance penalty when testing data stores in this manner though. Your unit tests won't perform like the rest of your application - assuming the rest of your application is performance tuned.