Stored Procedure Testing using Nunit

In this post we will look at stored procedure testing using Nunit. We will be

• Reusing stored procedure created for SSRS report link
• Create Table Earnings and procedure SaleCity
• Earlier we looked into TST codeplex tool

This example is In continuation with previous Nunit post. Stored Proc test approach I learnt from book Expert SQL Server 2008 Development.

Step 1 - Created VS 2008 Class Library

Step 2 - Added Reference to  DLL nunit.framework.dll in the project

Step 3 - Copy below code in the Class1.cs file

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using NUnit.Framework;

namespace ClassLibrary1

{

    [TestFixture]

    public class Class1

    {

        [Test]

        public void TestSaleCity()

        {

            // Set up a command object

            SqlCommand comm = new SqlCommand();

            // Set up the connection

            comm.Connection = new SqlConnection(

            @"server=.\SQLExpress; trusted_connection=true; database=AppDB");

            // Specify Procedure Name

            comm.CommandText = "dbo.SaleCity";

            comm.CommandType = CommandType.StoredProcedure;

            comm.Parameters.AddWithValue("@City", "Chennai");

            // Create a DataSet for storing the results

            DataSet ds = new DataSet();

            // Define a DataAdapter to fill a DataSet

            SqlDataAdapter adapter = new SqlDataAdapter();

            adapter.SelectCommand = comm;

            try

            {

                // Fill the dataset

                adapter.Fill(ds);

            }

            catch

            {

                Assert.Fail("Exception occurred!");

            }

            // Validate Actual Results

            Assert.IsTrue(ds.Tables.Count == 1,

            "Result set count != 1");

            DataTable dt = ds.Tables[0];

            // There must be exactly four columns returned

            Assert.IsTrue(

            dt.Columns.Count == 4,

            "Column count != 4");

            // There must be columns City, SaleValue, SaleDate and Name

            Assert.IsTrue(

            dt.Columns.IndexOf("City") > -1,

            "Column City does not exist");

            Assert.IsTrue(

            dt.Columns.IndexOf("SaleValue") > -1,

            "Column SaleValue does not exist");

           

            Assert.IsTrue(

            dt.Columns.IndexOf("SaleDate") > -1,

            "Column SaleDate does not exist");

           

            Assert.IsTrue(

            dt.Columns.IndexOf("Name") > -1,

            "Column Name does not exist");

            // There must be more than one row returned

            Assert.IsTrue(

            dt.Rows.Count >= 1,

            "Result rows returned");

        }

    }

}

Step 4 - Build the Project

Step 5 - Using Nunit Open the Debug folder, You will see ClassLibrary1 DLL. Below is the snapshot of results

More Reads

Writing Test Cases for Database Queries and Stored Procedure using VS 2010Testing Times Ahead: Extending NUnit

Using SQL Data Generator with your Unit Tests

Testing framework (Nunit/Xunit/MbUnit/MsTest) attributes comparision

Setting up Continuous Integration with Cruise Control.Net
Running NUnit Tests in TFS 2010 Continuous Build Environment

Guide to building .NET projects using Hudson

CruiseControl + MsBuild + NUnit

Test Driven Design and levels of testing
Testing database and Test database

NUnitit - a Visual Studio Addin for NUnit

TDD : Introduction to Rhino Mocks 
TDD: Test-Driven Development with Visual Studio 2008 Unit Tests
TDD Tests are not Unit Tests

Happy Reading!!