Yep, that’s how we do it. Some people use transactions to do a similar thing but you can’t test transactional code by doing that.
Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> |http://greglow.me<http://greglow.me/> From: [email protected] [mailto:[email protected]] On Behalf Of Tony Wright Sent: Wednesday, 26 April 2017 3:08 PM To: ozDotNet <[email protected]> Subject: Re: Unit testing question and stored procedures So let me understand this. I believe what you are doing is having a database snapshot (or testing database) that you can continuously revert to its initial state, then you run the stored proc via nunit, then in the init for the next test, revert back to the initial state and run that test, etc. I would have thought that it would take a lot of extra processing time to run tests that way, especially if a restore is needed? I've used in memory databases (via the database first philosophy of EF entity creation) but they don't handle stored procs. TSQLUnit looks...interesting. Must investigate. On Wed, Apr 26, 2017 at 12:48 PM, Greg Low (罗格雷格博士) <[email protected]<mailto:[email protected]>> wrote: I should have added that the dac framework stuff had testing but has now removed it. Some use TSQLUnit but I’ve not found it any more useful and NUnit fits well with other testing. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410<tel:+61%20419%20201%20410> mobile│ +61 3 8676 4913<tel:+61%203%208676%204913> fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> |http://greglow.me<http://greglow.me/> From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Tony Wright Sent: Wednesday, 26 April 2017 11:53 AM To: ozDotNet <[email protected]<mailto:[email protected]>> Subject: Unit testing question and stored procedures Hi all, A while ago, we were discussing avoiding using LINQ to query sql server. The preferred method of querying discussed was either to use direct SQL calls or stored procs to perform data manipulation. This was because the overhead of starting up Entity Framework is significant and the underlying queries produced by LINQ can be quite convoluted and inefficient. Lazy loading is also something to be avoided (at the very least you should be using Eager loading – which forced you to be explicit about what related data is being included/loaded. As an aside, I’ve also seen a massive performance drop when using mappers to covert database objects in EF to POCO objects using tools such as AutoMapper.) Add to this, that putting some business logic in stored procs is about the most efficient way to perform data manipulation in a SQL Server database. It is unbelievably fast and efficient compared to passing all the data over the wire to your middle tier to perform any updates and then passing it back to commit the data to the database. In fact, I would argue that the very fact that current “best practice” is to inefficiently pass all your data to the middle-tier to be modified, only to be returned to the database for the update, is a failure in modern development, but of course, there is not really an alternative if your intent is to performing proper unit testing. It is a very sad thing that modern enterprise development has not worked out how to utilise the full power of SQL Server other than to say "only use stored procs in special cases." So the question I have is, if it was decided to put business logic in stored procedures (and some of you have, I know, even though a few of you with the purist hat would deny it!), how do people currently unit test their stored procs? Kind regards, Tony
