 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / C# / May 2008Best Way to Test Against a Database
Hello: What is the best way to test my methods that hit a database? I believe tests should be as automated as possible. However, I'm not sure how I can test against a database since changes will modify the (development) database. I really don't care whether my SQL does what it's supposed to do, just that I have my CommandText, CommandType and Parameters set up correctly. For instance, I am currently testing my SELECT statements by passing a bogus primary key. I am thinking I can test my INSERTs and DELETEs together. Then I can test my update by passing a bogus primary key again. However, this seems dirty. I just wanted to see how others have handled this in past. Any suggestions would be appreciated. Thanks, Travis P.S. - Not that it should make a difference, but I'm using Team Tester. > I really don't care whether my SQL does what it's supposed to do, just > that I have my CommandText, CommandType and Parameters set up > correctly. If your database client library (and the server) supports the concept of "prepared" statements, this would be a very easy way to verify the correctness of the SQL statements without actually executing the command. "Preparing" a statement will send the SQL to the server and the server can use the SQL to build a query plan. If the SQL is invalid, the server will complain (by raising an exception). Usually I test all statements using a list (array) of SQL strings which will be iterated by the unit test. In a perfect test environment, every SQL would be wrapped in one test case. The test will then run over all SQL statements even if some of them fail. This is my recommended reading for testing: xUnit Test Patterns: Refactoring Test Code (Addison Wesley Signature Series) by Gerard Meszaros Hope this helps(tm)  SignatureMichael Justin SCJP, SCJA betasoft - Software for Delphi™ and for the Java™ platform http://www.mikejustin.com - http://www.betabeans.de > jehugalea...@gmail.com wrote: > > I really don't care whether my SQL does what it's supposed to do, just [quoted text clipped - 23 lines] > SCJP, SCJA > betasoft - Software for Delphi™ and for the Java™ platformhttp://www.mikejustin.com-http://www.betabeans.de How well does this verify the parameters that I set? > How well does this verify the parameters that I set? As I wrote, this approch will verify the correctness of the SQL statement. To verify the correctness of the results, the query has to be executed. As Andy wrote, resetting the database is critical. A clean test environment has to be in place for every test, to make them repeatable. The test steps should also be independent, so that it is not neccessary to run all tests always together in a specific order. This setup makes database tests very slow. Every developer will also need his/her own test database, to avoid 'test run wars'. (If there is a way to test the business logic without accessing the database, this would be much better. However this does not mean that database tests are not neccessary in this case.) Michael Hi, We use transactions and the [Setup] and [TearDown] methods in NUnit. You can begin the transaction in the Setup method, test whatever you like to test in your test and rollback the transaction in your TearDown method. If anything goes wrong the db will complain. If we want to test the result of a query, we do the same, but in our test method we commit, assert the result, restore the database by another commit and then begin a new transaction, in order to have this empty transaction rolled back in the teardown method and avoid an exception in the TearDown method. If you have many testfixtures, you can derive all of them from a base fixture, which for example begins and rolls back the transaction in Setup and TearDown, so you don't need to code this for each testfixture. Joachim Hi, I typically let the statements execute on the server. I have a stored procedure that populates the database with test data, and another that puts it back to it's empty, "clean" state. Of course this does require that this setup script be maintained properly so that as the schema changes the script will still put the expected data in. If you try to code so that you stop just sort of letting the command happen, you'll probably only be able to do that by creating some kind of branch in your code, so you're not testing the code as it will run. Just my thoughts. Andy On May 13, 2:56 pm, "jehugalea...@gmail.com" <jehugalea...@gmail.com> wrote: > Hello: > [quoted text clipped - 20 lines] > > P.S. - Not that it should make a difference, but I'm using Team Tester. I have run into the same issue and came up with a way to deal with it that works but is certainly not ideal. I have a setup script that first goes out and copies a database file (generally yesterday's backup so I don't run into access problems with a live database) to a new location. Then I restore that file to a testing database and run my tests. This works most of the time, but it is slow and I frequently run into file in use type errors. The thing I like about it is that it does not touch my live or even my development database, so I don't have to worry about the testing leaving messy footprints all over my data. Ethan Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.