I'm one of those ummm ... purists who reckons you shouldn't put anything (like business logic) in procs that needs unit testing -- *GK*
On 26 April 2017 at 15:36, Greg Low (罗格雷格博士) <[email protected]> wrote: > 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://greglow.me > > > > *From:* [email protected] [mailto:ozdotnet-bounces@ > ozdotnet.com] *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]> > 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 <+61%20419%20201%20410> > mobile│ +61 3 8676 4913 <+61%203%208676%204913> fax > > SQL Down Under | Web: www.sqldownunder.com |http://greglow.me > > > > *From:* [email protected] [mailto:ozdotnet-bounces@ > ozdotnet.com] *On Behalf Of *Tony Wright > *Sent:* Wednesday, 26 April 2017 11:53 AM > *To:* ozDotNet <[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 > > >
