need suggestion on unit testing template in Visual Studio 2010

I got four queries, which are

1) Every table should have a clustered index

2) Every table should have a primary key

3) No tables should exist on the primary filegroup

4) Primary Keys should follow the naming standard, eg “pk_ ”

I already got the queries for these test conditions. Now i need to build a Visual Studio Database Project for unit testing in Visual Studio 2010 Ultimate. I get new databases coming in every now and then in my Sql Server Mgmt Studio 2008.

I need a suggestion on the best plan to implement so that I can create a unit testing template to test the above 4 test conditions for each new databases coming in. Is it a good idea to create a separate unit testing project for each test condition or should i create one unit testing project for all the conditions. Also Suggest me the best way to do it in Visual Studio 2010 Ultimate.


Rgds, sqlhungry
more ▼

asked Mar 06, 2011 at 09:02 PM in Default

sqlhungry gravatar image

76 10 10 12

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Asking for "the best way" may lead to some discord, but here's a way which works, at least...

First, create a test project. In that test project, add a Database Unit Test.

Creating a Database Unit Test

After that, you will be able to add database tests. I would create four tests, one for each of your queries. These are simple enough that the Database Unit Test in VS2010 can work. More complex tests would probably require using something a bit stronger, like NUnit, but when I wrote the queries in my answer to your last question, I actually had this in mind.

A basic database unit test

You don't need to have different projects for each, nor do you even need a different test file. After you create them, the drop-down list shows each of your tests.

All of the structural compliance tests together

One advantage to doing it this way is that, in your Test View window, you can group by the class name and get all of your structural compliance tests together. Then, you could create, say, read-only tests, write-based tests, authorization/authentication tests, etc.

![Tests grouped by class name][4]

Once you get those in, you could copy the .cs and .resx files into other projects and they'll work there as well, so you get the benefits of a template. For that to work, you would need a DatabaseSetup.cs and an app.config, both of which are created automatically when a Database Unit Test is added to a test project. So you would be able to start working on your project-specific tests and drop these files in.

[4]: /upfiles/GroupedTests.png
more ▼

answered Mar 07, 2011 at 04:22 AM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

Nice answer and congratulations on getting your first thousand points. It's great to have you contributing to this forum, thanks.
Mar 07, 2011 at 04:27 AM Fatherjack ♦♦

Awesome answer (+1).

I also agree with @Fatherjack - great work so far, thanks for contributing.
Mar 07, 2011 at 04:33 AM WilliamD
Thank you both. It's been very interesting so far, and everything I've read about becoming active in trying to answer questions is exactly right: this is a great way to learn and focus your mind. Plus, when you're done, you get to see somebody come up with an even better idea and mercilessly borrow it later. :-)
Mar 07, 2011 at 04:46 AM Kevin Feasel
Kevin - do you know of anything that will do this when VS edition doesnt support Database Test objects? Alternative products, 3rd party objects?
Mar 07, 2011 at 08:40 AM Fatherjack ♦♦

Thanks Kevin for the solution. I tried it just now and it is helpful indeed.

Regards, sqlhungry
Mar 07, 2011 at 08:44 AM sqlhungry
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 06, 2011 at 09:02 PM

Seen: 2069 times

Last Updated: Mar 06, 2011 at 09:02 PM