locked
VisualStudio Unit test framework for SQL RRS feed

  • Question

  • Hello,

    I'm new to visual studio unit test framework for SQL  and I would like to write unit test cases for my SQL stored procedure.

    I know its easy to write unit test in VS for SQL procedure and validate the output but my question is how can I test the inside logic (IF's / where clauses, inner query results , temp tables etc..) of my SQL procedure. Is there any way to do this?


    Venkat Kundavaram

    • Moved by Just Karl Thursday, September 7, 2017 1:38 PM Looking for the correct forum
    Tuesday, August 29, 2017 5:05 PM

All replies

  • Hello,

    The 'Academic Initiatives - Technical Queries' forum is for posts Related to technical / coding / programming related issues as related to Microsoft's Academic Initiatives.

    As it's off-topic here, I am moving the question to the Visual Studio Unit Testing forum on MSDN.

    Karl

    Thursday, September 7, 2017 1:36 PM
  • Hi Venkat,

    "Here is a simple example, suppose you have a Product table and you want to test whether total number of product in your database is less than 10 products. You need a repository class that supply you the number of product in the database in a form of integer, you don't care about how the method gets the number as long as you get the number (think of abstraction). Take a look at the following code snippet:

    public interface IRepository
    {
        int CountProduct();
    }
    
    // your implementation of repository against real database
    public class DBLinqToSQLRepository : IRepository
    {
        // some constructor here
    
        public int CountProduct()
        {
            // your code here to return the actual number of product from db.
        }
    }
    
    // your implementation of fake repository that will provide fake data for testing
    public class FakeRepository : IRepository
    {
        private List<Product> products = new List<Product>();
    
        // some initialization here
    
        public int CountProduct()
        {
            return products.Count;
        }
    }

    Then in your test environment you can use this FakeRepository instead of the actual database repository to test your business logic."

    Hope above helps.

     

    Best regards,

    Fletcher


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Fletch Zhou Friday, September 8, 2017 5:43 AM
    Friday, September 8, 2017 5:42 AM
  • Hi Venkat, 

    The best to do is : 

    Create Testdatabase that looks almost like the production database, with enough test data it will replicate a real life production database. 

    Add a new connection string to the your unit test project. 

    Next, you probably have some kind of data acces layer where there are sql queries (select with joins , update statements etc or even getting a stored procedure). You can create a new instance of your data acces layer class and use it your unit test method. 

    This is how I do it 

    I  wouldn't recommend Fletchers idea because , you're going to mock objects and not test your queries in your data acess layer. 

    Kind regards


    Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by laurens vdb Friday, September 8, 2017 6:20 AM
    • Proposed as answer by Fletch Zhou Tuesday, September 19, 2017 6:22 AM
    Friday, September 8, 2017 6:19 AM