I was having problems with querying null parameters, mainly because of the syntax. For instance, I have a query:
select * from MyTable where NullParameter = @Param and OtherParameter = @OtherParam
But I didn't get any results. When I attempted this:
select * from MyTable where isnull(NullParameter, '') = isnull(@Param, '') and OtherParameter = @OtherParam
it worked. Essentially, I brought the parameters to the same level when they were null. I suspect it's because you have to use the is keyword whenever you reference null. So, doing this works. Any more knowledgable SQL developers want to provide a better solution, please do so in the comments.
As I've been working iteratively during the testing process, I've come across several problems that I've experienced, mostly that deals with how I work. The primary issue you have to be concerned with, if you follow my "using a SQL 2005 Express database for testing", is making changes to it. You actually have to maintain two separate databases, unless you use a process to compare the two sources. Because I want to run the application, I didn't do that; plus, the original is currently SQL Server 2000 for the time being, and I didn't think scripting changes could go back to 2000 with full compatibility. So, there are some considerations to keep in mind when doing this.
Recently I came across the issue of testing against the database. The database provides its own unique challenges, as you need to ensure a standardized set of data exists within the database, so you can test certain conditions in your code. If you don't, you will find you will have very generalized checks, such as:
- Checking to see if more than zero records returned
- Checking to see if fields are not null
- Checking the record counts returned
These tests aren't bad; however, they are very limited still. It would be nice to ensure that if I have a standardized set of data, which covers a wide array of situations also, already known in the database, I can ensure my tests occur very specifically.
For instance, I can update and check the values for a specific record in the database, because I knew what the original and what the new values are. Also, since data in the database changes, I have to worry about a potential future conflict of data with my values. I also have to write my tests that I add new values at the beginning, and clear out those values at the end, and if there is an error in my test, then the rest of my tests are going to fail on that dependency. To a point, you will always have a dependency; however, this is a very high dependency, as compared to the alternative.
What is the alternative? I had a friend who ran scripts to recreate the local or development database; however, I found what I believe to be a better alternative, SQL Server 2005. The reason is that SQL Server 2005 can work with a detached database, which means I can have a copy of the database in my Visual Studio 2005 NUnit test project. I set the "Copy to Output Directory" setting to Copy Always, so I always have the same data set every time I test. This ensure that I won't have any future data conflicts because certain information is or isn't there.
And, the structure can be copied from a 2000 or 2005 existing database, and this database works independently of that. The one drawback can be that you have to do your maintenance twice; one to the development database on the server and one to the detached database in your Visual Studio .NET project. You can always recreate the database by copying scripts over again, but the problem lies in that the standarized data set has already been created, and you can't lose that.
An alternative could be that you copy the database to the SQL Server data folder, attach it to SQL Server 2005, copy over the changes through DTS, and detach and move it back to the VS.NET test project. However, if they are minor changes, then copy/paste might work the best.
This also works seamlessly if you store your connection string in the configuration file; however, if you don't, depending on where you store it, you may experience some problems.