Integrate SQL unit test case with Bamboo CI.

Problem Statement

Data testing of Report extractor are currently manual,we have generate the reports from RE and validate manually no other ways to do that testing fast with accuracy..

Overview

Create NUnit test project and attach into the build process of bamboo build server.Each time when any code is commit into SVN ,the continuous integration server will responsible to execute the test case.

Prerequisites for the NUnit test project:

Before executing the NUnit test project on the server, ensure that following components are exist:

• NUnit test framework
• NUnit test adapter dll (to view test results on MS Visual studio GUI)
• All the csv file of data should have in their relevant folder defined in config file.
• Access rights of folder(where build server is copied test data csv file) to insert the data into database, which is created on the fly.
• Access rights on tempdb database for create/select data comparison table since every new startup of SQL server or machine restart tempdb is losses the previously configured user permission.For that we can created SQL proc and run on SQL startup.
  

  CREATE PROC AddTempDBOwner AS DECLARE @sql varchar(max) SELECT @sql = N'USE tempdb EXEC sp_addrolemember ''db_owner'', ''NADR\dev''' EXEC (@sql) GO EXEC sp_procoption 'AddTempDBOwner', 'startup', 'on' GO

  
  

Process of SQL testing tool integration with Bamboo

We can implement SQL testing tool with build by following steps:

NUnit test project can be created to perform testing on database from baseline data on the following are the component of the project:

Component Name	Description
Config	Config.xml can be created with the information about the SQL connection setting,Input test cases,NAtrader db data,baseline data for sqltest db based on scenario like TradeCorrect/Liquidity Report. Each scenario has own configuration and based on the config information passed into setup section to create db on target server.
Setup (TestCaseSource)	This section will responsible for creating SQLtest db,natrader db,nacommon db based on information provided in config.xml. Insert input test cases ,baseline data into testing tool db. Insert natrader data into corresponding db. Insert nacommon db data into corresponding db.
Test method	Execute the "RunTests" on all SQLtest db defined in config.xml and setup is created successfully.
Datalogger	Dump all the test results into disk for future use.
Log manager	Log manager will be responsible for logging information in every steps for better troubleshooting if user wants.
TestCleanup(tear down)	This section will responsible for dropping all the created database from the target server

Sample config File :

<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <section name="SQLTests" type="Na.SQL.NUnitTests.SQLTestConfiguration.SQLTestConfigurationSection, Na.SQL.NUnitTests" allowLocation="true" allowDefinition="Everywhere"/> </configSections> <SQLTests> <ConfigSetting> <!--The reports name which needs to be changed in to natrader db created on the fly.--> <ReportsName value="Na_Price_report,Na_Liquidity_report,Na_Liquidity_report_option"/> <MasterConnectionSetting connectionString="server=127.0.0.1;integrated security=SSPI;database=master;Connection Timeout=500;" /> <Setups> <!--Create db for check Liquidityreport/execta daily scenario--> <Setup name="LIQ_TradeBust" outputFilePath="..\..\..\..\..\src\SQL\BuildResult"> <!--Description--> <!--instances defines the number of SQL test db to be created on the fly for multiple scenario(its improvement of the EXEC-8104):--> <ServerSetting> <ConnectionSetting component="SQLTestDB" connectionString="server=127.0.0.1;integrated security=SSPI;database=~;Connection Timeout=500;" /> <ConnectionSetting component="NaTrader" connectionString="server=127.0.0.1;integrated security=SSPI;database=~;Connection Timeout=500;" /> <ConnectionSetting component="NaCommonDB" connectionString="server=127.0.0.1;integrated security=SSPI;database=~;Connection Timeout=500;" /> </ServerSetting> <DataBaseCollection> <!--Srno must be define uniquely for every DB block--> <DB component="Natrader"> <Cab> <File path="Output\NaTrader.cab"/> <File path="Output\NaTrader-RG.cab"/> </Cab> <Data> <object tablename="order_msgs" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaTraderDB\order_msgs.csv" /> <object tablename="order_msgs_incoming" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaTraderDB\order_msgs_incoming.csv" /> <object tablename="order_msgs1" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaTraderDB\order_msgs1.csv" /> <object tablename="order_msgs_incoming1" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaTraderDB\order_msgs_incoming1.csv" /> <object tablename="order_msgs2" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaTraderDB\order_msgs2.csv" /> <object tablename="order_msgs_incoming2" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaTraderDB\order_msgs_incoming2.csv" /> <object tablename="order_msgs3" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaTraderDB\order_msgs3.csv" /> <object tablename="order_msgs_incoming3" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaTraderDB\order_msgs_incoming3.csv" /> </Data> </DB> <DB component="NaCommonDB"> <Cab> <File path="Output\NaCommonDB.cab"/> </Cab> <Data> <object tablename="Common_PossibleValue_Master" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaCommonDB\Common_PossibleValue_Master.csv" /> <object tablename="Common_RG_LastMkt_Mapping" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaCommonDB\Common_RG_LastMkt_Mapping.csv" /> <object tablename="Common_RG_SymbolMapping" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaCommonDB\Common_RG_SymbolMapping.csv" /> <object tablename="Common_RG_Destinations" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaCommonDB\Common_RG_Destinations.csv" /> <object tablename="Common_RG_TierMapping" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaCommonDB\Common_RG_TierMapping.csv" /> <object tablename="Common_RG_Fee_Schedules" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\NaCommonDB\Common_RG_Fee_Schedules.csv" /> </Data> </DB> <!--Scenario must be define for each SQLTestDB component to create multiple databases Sql test db for execute Runtest.--> <DB component="SQLTestDB" scenario="LIQ"> <Cab> <File path="Output\RGTestingUtility.cab"/> </Cab> <Data> <object tablename="InputTestData" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\TestCasesRG\InputTestData.csv" /> <object tablename="BaselineData" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\TestCasesRG\BaselineData.csv" /> </Data> </DB> <DB component="SQLTestDB" scenario="TradeBust"> <Cab> <File path="Output\RGTestingUtility.cab"/> </Cab> <Data> <object tablename="InputTestData" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\TestCasesRG\InputTestData.csv" /> <object tablename="BaselineData" file="..\..\..\..\src\SQL\Na.SQL.NUnitTests.TestScripts\TestCasesRG\BaselineData.csv" /> </Data> </DB> </DataBaseCollection> </Setup> </Setups> </ConfigSetting> </SQLTests> </configuration>

Description about the Setup : 

We can create multiple test scenario for execute test cases into single setup, like above config file have "Setup" block named LIQ which is parent container of SQLtest,but we can put multiple scenario for executing the test cases like config file have two scenario (LIQ and TradeBust) for both two separate SQLtest db installed and test against same Natrader db i.e(LIQ_TradeBustNaTrader2012).

We can also define multiple "Setup" blocks containing multiple scenarios block under the "Setups" block in config, the only difference between these two blocks are test cases and test data path are different from each other.

Database Creation logic :

For creating unique database name of NaTrader and SQLtest DB we can take the setup attribute, then under the setup>server setting and get the component name and add the 4 digit random number by cs code, so the final data base name of each component will be look like this

Logic	DB Name
LIQ+NaTrader+1879	LIQNaTrader1879
LIQ+SQLTestDB+1879	LIQSQLTestDB1879
LIQ+NaCommonDB+1879	LIQNaCommonDB1879

Since we are using Nacommon db name in few reports like(daily,earn report) in Natrader.We don't worry about the name of NacommonDB,the test project is responsible to update dynamic NaCommondDB like "LIQNaCommonDB1879" name is concerned Natrader DB reports and execute the tests based on it.

Structure of Unit test case

[TestFixture] public class ThingTest { // Setup // Exercise // Verify // Teardown [Test] public void TestSomething() { } }

Steps of integration with Bamboo

1. Go to Bamboo and Modify plan.
2. Click on Default job and select "Task" tab.
3. Add the Script task to execute nunit-console.exe by batch file.
4. Provide the script file name and parameters to passed into the bat file.
5. Add NUnit Test parser to parse the test result which is generated by nunit-console.exe.
6. Provide the file name which needs to be parse result and display on Bamboo GUI.

High level diagram of process

Process flow of NUnit test Execution

Structure of NUnit Test project 

Sample test result in Bamboo GUI:

Resharper on the fly code analysis from Jetbrains

What is Resharper?

ReSharper is a popular developer productivity extension for Microsoft Visual Studio. It automates most of what can be automated in your coding routines. It finds compiler errors, runtime errors, redundancies, code smells, and possible improvements right as you type, suggesting intelligent corrections for them. ReSharper helps explore code by visualizing the structure of files, type and style hierarchies, call and value chains, project dependencies. It allows instantly traversing your entire solution and jumping right to the exact file and line that we are looking for, decompiling library code if necessary. Dozens of solution-wide refactoring’s are available to help safely change code base. Code formatting and cleanup features allow to get rid of unused code and help your entire team to ensure compliance to coding standards.

Why Resharper?

With unparalleled support for C#, VB.NET, XAML, JavaScript, XML, HTML, CSS, ASP.NET, ASP.NET MVC, NAnt and MSBuild scripts including comprehensive cross-language functionality, ReSharper will help any Visual Studio user write better code, easily examine and refactor existing code bases.We can spend less time on routine, repetitive manual work and instead focus on the task at hand. A robust set of features for automatic error-checking and code correction cuts development time and increases your efficiency. We'll find that ReSharper quickly pays back its cost in increased developer productivity and improved code quality. With ReSharper, .NET developers can truly experience what we mean when we say "Develop with pleasure!"

Resharper Advantages

1. No need to In-depth Analysis of C# Code.
2. Advanced Coding Assistance.
3. Numerous Code Refactoring’s.
4. Navigation and Search.
5. Code Cleanup.
6. Cross-Language Functionality.
7. ASP.NET page intelligence Support.
8. XML Support.
9. XAML Support.
10. Integrated Unit Testing.
11. NAnt and MS Build Scripts Editing.
12. Find unused members of a class like Methods, Properties and Variables.
13. Suggestions like converting a LINQ expression into a Lambda expression.
14. While using String.Format method, Resharper can validate that the placeholders match the supplied values.
15. While using logical constructs like If Else it can suggest better options.
16. When using multiple if statements it can refactor into a Switch case loop.
17. With methods which return values, and if we are using If Else construct to return two different values based on certain conditions then Resharper can suggest changes to redundant else conditions.
18. Based on the usage of methods, Reshaper can suggest changing the methods to static methods or even the variables to read-only variable.

Comparison among Resharper, FxCop and Stylecop

S. No.	Feature	Resharper	FxCop	StyleCop
1	Analyzes managed code assemblies	No	Yes	No
2	Localization	Yes	Yes	Yes
3	Detecting performance issues	Yes	Yes	Yes
4	Security improvements	Yes	No	No
5	Design rules set	Yes	Yes	Yes
4	Design Guidelines for Class Library	Yes	Yes	Yes
5	Execute on	Windows and command line both	Windows and command line both	Windows and command line both
6	Work with	C#/VB.Net/Linq/Asp.net/ EJB/ JSP/ JSF/CSS/JavaScript	Complied DLL	Only C# File
7	Works With Assemblies	No	Yes	No
8	Handel Exception	Yes	Yes	No
9	Unit Testing	Yes	No	No
10	Checks Code on fly	Yes	No	No
11	Create Class File	Yes	No	No
12	Licensee	Required	Free	Free
14	Code Analysis format	XML	XML	XML
15	Locating the “Dead” Code	Yes	No	No
16	Analyzing Code Dependencies	Yes	No	No
17	Suppressing inspections for the code	Yes	No	No
18	Value Tracking	Yes	No	No
19	Call Tracking	Yes	No	No
20	Navigating Between Highlighted Code	Yes	No	No
21	Quick-Fixes errors	Yes	No	No
22	Code Refactoring	Yes	No	No
23	MVC Support	Yes	No	No
24	Dynamic Language support	Yes	No	No
25	Code Cleanup	Yes	No	No
26	Cross-language functionality	Yes	No	No

FxCop:

FxCop is an application that analyzes managed code assemblies (code that targets the .NET Framework common language runtime) and reports information about the assemblies, such as possible design, localization, performance, and security improvements. Many of the issues concern violations of the programming and design rules set forth in the Design Guidelines for Class Library Developers, which are the Microsoft guidelines for writing robust and easily maintainable code by using the .NET Framework.
FxCop is very similar to Visual Studio Code Analysis. FxCop can be run as either a Windows application or at the command line.

StyleCop:

StyleCop is similar to FxCop, but it provides a different function and is, in fact, complementary to either FxCop or Code Analysis. (Note that it only works on C# source files.) It is mostly concerned with coding style and formatting. As such, it is run against source files, not assemblies like the other two analysis tools.

Conclusion:

ReSharper provides continuous code quality analysis in C#, VB.NET, XAML, XML, ASP.NET, ASP.NET MVC, JavaScript, HTML, and CSS, detecting errors and problems before you even compile. 

I’ve been using Resharper since last 3 years in .NET/C#. It’s a tool with a lot of features which aids you in the average day development and after comparing all others code analysis tool recommended to use Resharper,since it gives you on the fly code analysis and instant code refactoring.