Content of cheat sheet
Sandbox test (aka. AutoRollBack)If one test is in sandbox, all actions on each DB will be auto-rollback after the test. Otherwise, all actions will commit. Sandbox modal is only applicable for those DBs transaction supported such as Oracle and SQL server and so on, other than Excel.You need only add an extra testInSandbox attribute in test case section. And set the testInSandbox attribute as true. This feature is applicable to both performance test and unit test. By default, the attribute value is false.
<
unitTestCase
testCaseName
="UT_Test_UpdateCountryRegion"
assertion ="RecordCountEqual" testInSandbox ="true" > < setup > < statement statementAlias ="proc_UpdateCountryRegion" > < inputArguments > < argument name ="@CountryRegionCode" value ="ABC" /> < argument name ="@Name" value ="new country" /> </ inputArguments > </ statement > </ setup > < targetResultset statementAlias ="sql_GetCountryRegion" outputArgument ="@Return_Table" > < inputArguments > < argument name ="@CountryCode" value ="ABC" /> </ inputArguments > </ targetResultset > < referenceResultset statementAlias ="sql_get_const_number" outputArgument ="@result" > < inputArguments > < argument name ="@constant" value ="1" /> </ inputArguments > </ referenceResultset > </ unitTestCase > |
Use shared DB Connection fileWe often need to create many test case files to test our database. It will be a bore thing if we declare the same DB connection string in every case file.If we want to share some case files that they need Excel spreadsheet as data source to other persons, the recipients will have to change Excel connection string in every case file possibly, because the connection string for Excel spreadsheet contains Excel file full path. AnyDbTest will provide us with an approach to handle this kind of problem. We can define an XML configuration file as dedicated DB connection file. All of your test case files can import the DB configuration file. If you want to change DB connection, you need only change this DB connection file other than all case files. Let¡¯s take an example. The following dbCfg.XML is just a DB connection file.
<?
xml version="1.0" encoding="utf-8"
?>
< dbConnections xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation ="file:/C:/DbConnectionSchema2.xsd" > < dbConnection connectionName ="Excel_pump" connectionString ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Pump_Source.xls; Extended Properties='Excel 8.0;HDR=YES'" providerName ="Microsoft.Jet.OLEDB.4.0" /> < dbConnection connectionName ="Excel_refer" connectionString ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\External_Refer.xls; Extended Properties='Excel 8.0;HDR=YES'" providerName ="Microsoft.Jet.OLEDB.4.0" /> </ dbConnections > The following XML is one part of a case file. In its globalSetting we use dbConnectionFile attribute to reference one DB connection configuration file. We can also define normal DB connections in this section.
<
globalSetting
dbConnectionFile
="dbCfg.xml"
>
< dbConnection connectionName ="db1" connectionString ="Data Source=localhost\SQLEXPRESS; Initial Catalog=AdventureWorks;Integrated Security=True;" providerName ="System.Data.SqlClient" /> </ globalSetting > |
Export data set into XMLYou can export data into XML file by AnyDbTest GUI Runner. For performance testing, you can export all output-type arguments of the query statement, including record sets and scalar values. For unit testing, AnyDbTest will export the argument result specified by outputArgument attribute, because in most cases, we only care those arguments other than all output-type arguments.You can set two attributes, one is exportRecordCount and another is wrapStringByCDATA. Separately they are to control exported record count and determine whether wrap all string-type record value in CDATA element. By default, the AnyDbTest will only export 200 records for performance reason. exportRecordCount must be in -1, 0, other integer value greater than 0. If exportRecordCount =-1, it will output all records if exportRecordCount =0, it will not output any record if exportRecordCount =Value, it will output the given number of records There are two places to customize export setting. They are in globalSetting and outputSetting section of test case. Obviously, the former setting is global level; it will be the default setting in the test case file. If you set these attributes in outputSetting section of one test case. AnyDbTest will use the setting to replace the global setting for this test case. Global level setting,
<
globalSetting
exportRecordCount
="1000"
wrapStringByCDATA
="false"
>
</ globalSetting > Testing case level setting,
<
perfTestCase
testCaseName
="PT_Fetch_USA_States"
statementAlias ="proc_Get_state_in_country" > < inputArguments > < argument name ="@CountryCode" value ="US" /> </ inputArguments > < testCaseRemark > test how long it takes to execute usp_GetStatesInCountry </ testCaseRemark > < outputSetting exportRecordCount ="20" wrapStringByCDATA ="false" > < field name ="StateProvinceID" /> < field name ="StateProvinceCode" /> < field name ="CountryRegionCode" /> < field name ="StateProvinceName" outputName ="StateProvinceName" /> </ outputSetting > </ perfTestCase > |
Convert XML-fomat data file into Excel formatYour test often needs some data to work with. In most situations you will work with Excel spreadsheet. But we know it is bore and error-prone to prepare the data Excel file, especially when you need a lot of data. You can manually create an Excel data file from scratch or create one by some data from your database.AnyDbTest provides us with an easy approach rather than from scratch to create Excel data file. You can export data into XML file by AnyDbTest GUI Runner, and then convert XML data file into Excel file by using a utility namely Xml2Excel.exe. We must be aware that Excel file has some constraints when it will be used as data source.
After conversion, please remove the first row manually in order to use the excel file for future data comparison. |
How to use Excel as reference test dataIn AnyDbTest, Excel data file is treated as a database just like Oracle or SQL Server. Important note - it must be an Excel 2003 format - not the 2007 format. When we use Excel as test data source, the typical steps are,
Your Excel data may be contained in your workbook in one of the following ways:
Specify a WorksheetThe file contains the following, note you must include a header (otherwise your first test case is ignored):To specify a worksheet as your data source, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example:
SELECT
*
FROM
[
Sheet1$
]
Or,
SELECT
CountryCode, Name
FROM
[
Sheet1$
]
Microsoft prefers the square brackets, which are the standing convention for problematic database object names. If you omit both the dollar sign and the brackets, or just the dollar sign, you receive the following error message: ... the Jet database engine could not find the specified object If you use the dollar sign but omit the brackets, you will see the following error message: Syntax error in FROM clause. If you try to use ordinary single quotes, you receive the following error message: Syntax error in query. Incomplete query clause. Specify a Named RangeTo set the data as a named range, right the block of data (including header), right click and select Name a range.If you want to edit or delete an existing name, then you need to use the Name Manager under the Formulas tab. To specify a named range of cells as your data source, simply use the defined name. For example:
SELECT
*
FROM
TestData
Specify an Unnamed RangeTo specify an unnamed range of cells as your record source, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:
SELECT
*
FROM
[
Sheet1$A1:B10
]
A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheet title above and to the left of the data in cell A1. A caution about specifying ranges: When you specify a worksheet as your record source, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range. With MDAC versions prior to 2.5, when you specify a named range, you cannot add new records beyond the defined limits of the range, or you receive the following error message: Cannot expand named range. |
Cross database testHow to perform unit testing cross multiple databases? It is a common issue that we often face. AnyDbTest can help us to handle these problems in two ways.
|
Cross database test-Compare target result set with reference DBWe can use AnyDbTest to compare results from two databases. In this chapter, we will demonstrate to compare the result set of SQL Server query statement with external excel file.The following sqlDeclaration will fetch all CountryRegion from AdventureWorks database.
<
sqlDeclaration
alias
="sql_GetAllCountryRegion"
dbConnection ="db1" > < remark > to get country region info </ remark > < sql > <![CDATA[ select * from [Person].[CountryRegion] ]]> </ sql > < arguments > < argument name ="@Return_Table" direction ="Return" type ="MSSQL_RECORDSET" /> </ arguments > </ sqlDeclaration > The following sqlDeclaration will retrieve CountryRegion from the excel file.
<
sqlDeclaration
alias
="Sql_GetCountryFromReferExcel"
dbConnection ="excel_refer" > < sql > <![CDATA[ Select CountryCode,Name from [Sheet1$] ]]> </ sql > < arguments > < argument name ="RETURN_Table" direction ="Return" type ="XLS_RECORDSET" /> </ arguments > < remark > Get one recordset from excel </ remark > </ sqlDeclaration > The following unit testing will compute whether recordset of AdventureWorks is superset of recordset of Excel.
<
unitTestCase
testCaseName
="UT_DemoReferExternalData"
assertion ="IsSupersetOf" testInSandbox ="true" > < targetResultset statementAlias ="sql_GetAllCountryRegion" outputArgument ="@Return_Table" > < outputSetting > < field name ="CountryRegionCode" outputName ="CountryCode" /> < field name ="Name" /> </ outputSetting > </ targetResultset > < referenceResultset statementAlias ="Sql_GetCountryFromReferExcel" outputArgument ="RETURN_Table" > < outputSetting > < field name ="CountryCode" outputName ="CountryCode" /> < field name ="Name" /> </ outputSetting > </ referenceResultset > </ unitTestCase > |
Cross database test-Pump data from reference DBWe can use a pump action to initialize test data before performing test case. If we set the test in sandbox, after test, the table of will restore to intact state.In the following example, we will use one excel file as pump data source. It contains column header and 3 rows of data. The following sqlDeclaration will retrieve CountryRegion from the excel file.
<
sqlDeclaration
alias
="Sql_GetCountryFromPumpExcel"
dbConnection ="excel_pump" > < sql > <![CDATA[ Select CountryCode,Name from [Sheet1$] ]]> </ sql > < arguments > < argument name ="RETURN_Table" direction ="Return" type ="XLS_RECORDSET" /> </ arguments > < remark > Get one recordset from excel </ remark > </ sqlDeclaration > The following sqlDeclaration will insert one CountryRegion record into AdventureWorkds DB.
<
sqlDeclaration
alias
="sql_InsertCountryRegion"
dbConnection
="db1"
>
< sql > <![CDATA[ insert Person.CountryRegion (CountryRegionCode,Name) values (@CountryRegionCode,@Name) ]]> </ sql > < arguments > < argument name ="@CountryRegionCode" direction ="Input" type ="MSSQL_NCHAR" /> < argument name ="@Name" direction ="Input" type ="MSSQL_NCHAR" /> </ arguments > </ sqlDeclaration > The following sqlDeclaration will fetch all CountryRegion from AdventureWorks database.
<
sqlDeclaration
alias
="sql_GetAllCountryRegion"
dbConnection ="db1" > < remark > to get country region info </ remark > < sql > <![CDATA[ select * from [Person].[CountryRegion] ]]> </ sql > < arguments > < argument name ="@Return_Table" direction ="Return" type ="MSSQL_RECORDSET" /> </ arguments > </ sqlDeclaration > The following unit testing will perform data pump action at first, then determine whether record count of CountryRegion table in AdventureWorks is equal with 238 or not. At last all inserted records will disappear because the test is in sandbox.
<
unitTestCase
testCaseName
="UT_DemoPump"
assertion
="RecordCountEqual"
testInSandbox ="true" > < setup > < dataPumpAction > < pumpSourceStatement statementAlias ="Sql_GetCountryFromPumpExcel" outputArgument ="RETURN_Table" > </ pumpSourceStatement > < pumpDestinationStatement statementAlias ="sql_InsertCountryRegion" > < inputArguments > < argument name ="@CountryRegionCode" value ="CountryCode" valueIsReferFieldName ="true" /> < argument name ="@Name" value ="Name" valueIsReferFieldName ="true" /> </ inputArguments > </ pumpDestinationStatement > </ dataPumpAction > </ setup > < targetResultset statementAlias ="sql_GetAllCountryRegion" outputArgument ="@Return_Table" > </ targetResultset > < referenceResultset statementAlias ="sql_get_const_number" outputArgument ="@result" > < inputArguments > < argument name ="@constant" value ="238" /> </ inputArguments > </ referenceResultset > </ unitTestCase > |
Validate replication, ETL, DTS and SSIS tasksWe often need to validate the replication, ETL, DTS & SSIS result, because the databases are our most critical knowledge and wealth. It is always necessary to verify the replication/ETL process is successful. AnyDbTest is exactly fit this task because it's very reliable and also very fast and very flexible.The instructions are just like performing a simple unit testing.
|