5

The current system we are adopting at work is to write some extremely complex queries which perform multiple calculations and have multiple joins / sub-queries. I don't think I am experienced enough to say if this is correct or not so I am agreeing and attempting to function with this system as it has clear benefits.

The problem we are having at the moment is that the person writing the queries makes a lot of mistakes and assumes everything is correct. We have now assigned a tester to analyse all of the queries but this still proves extremely time consuming and stressful.

I would like to know how we could create an automated procedure (without specifically writing it with code if possible as I can work out how to do that the long way) to verify a set of 10+ different inputs, verify the output data and say if the calculations are correct.

I know I could write a script using specific data in the database and create a script using c# (the db is SQL Server) and verify all the values coming out but I would like to know what the official "standard" is as my experience is lacking in this area and I would like to improve.

I am happy to add more information if required, add a comment if necessary. Thank you.

Edit: I am using c#

Dave Schweisguth
  • 36,475
  • 10
  • 98
  • 121
James
  • 2,013
  • 3
  • 18
  • 31
  • Are the queries you want to test run from a C# program or are they run by hand or something else? – Dave Schweisguth May 08 '14 at 20:36
  • @DaveSchweisguth They are run within a C# web application. So the final output at the moment is an html table from the original query. – James May 09 '14 at 06:29
  • Just to clarify... are you trying to test the correctness of an application that generates/uses queries? And basically test if those queries correctly reflect some externally-specified business rules? – NicholasM Feb 01 '20 at 21:17

2 Answers2

4

The standard approach to testing code that runs SQL queries is to unit-test it. (There are higher-level kinds of testing than unit testing, but it sounds like your problem is with a small, specific part of your application so don't worry about higher-level testing yet.) Don't try to test the queries directly, but test the result of the queries. That is, write unit tests for each of the C# methods that runs a query. Each unit test should insert known data into the database, call the method, and assert that it returns the expected result.

The two most common approaches to unit testing in C# are to use the Visual Studio unit test tools or NUnit. How to write unit tests is a big topic. Roy Osherove's "Art of Unit Testing" should be a good place to get started.

Dave Schweisguth
  • 36,475
  • 10
  • 98
  • 121
  • Thanks for your answer. I updated my question to state I am using c#. We have no current system for automated tests on a c# level either so I need all advice in this area as well. – James May 08 '14 at 20:22
  • I will mark the question as correct as you have given me a direction to go into and no-one else seems to have a response which is better. Thanks – James May 13 '14 at 14:46
1

The other answer to this question, while generally correct for testing code, does not address the issue of testing your database at all.

It sounds like you're after database unit tests. The idea is that you create a temporary, isolated database environment with your desired schema and test data, then you validate that your queries are returning appropriate data.

Daniel Mann
  • 57,011
  • 13
  • 100
  • 120
  • 1
    archive link: https://web.archive.org/web/20140331215306/http://blogs.msdn.com/b/ssdt/archive/2012/12/07/getting-started-with-sql-server-database-unit-testing-in-ssdt.aspx – Joshua Goldberg Apr 21 '21 at 20:39