User Guide

 

This reference contains an explanation of each of the public tables, views, stored procedures and functions provided by tSQLt.

 Posted by at 12:20 am
19 comments
Ganesh
Ganesh

Hi, Can we get documentation of TSQL framework the in PDF format? Ganesh

Jeff Schanhals
Jeff Schanhals

Can an individual test take a parameter? For example, we have login/logout functionality where we store the user in a table. I write a unit test to verify this. Then, I need to go to another unit - which also requires that the user is logged in. Rather than rewriting (or copying/pasting, faking data), I would like to call the previous unit test to login the user(variable), then perform the rest of the tests. Is this possible? There also may be different levels of accessiblity depending on the user name, but really only want to write a single unit test (again to avoid copy/pasting) that can verify this based on the user name passed.

Wondering
Wondering

Is there any way to change the format that tSQLt displays after completing a test. For example, making each failure message to have a blank line beneath it?

G. Vlad
G. Vlad

I recently started using tSQLt and I really like it. I have a little problem when I am trying to test a stored procedure containing "ALTER TABLE tablename ADD CONSTAINT ...". I got the error: [testclass].[testcase] failed: (Error) Could not create constraint. See previous errors.{[name of the stored proc called in the testcase],304} If I removed this line of code from the stored proc, the test case is running sucessfully, Is it supposed to be supported by the framework? Is there any workaround? Thanks for your help.

Hamidreza
Hamidreza

Hi. How can i integrating tsqlt with Mstest runner on Visual studio 2012 ?

Alin Hanghiuc
Alin Hanghiuc

I think ResultSetFilter should be included on this page, maybe under "Test Creation and Execution"? Thanks.

Mikah
Mikah

I'm looking for a way to run tests and persist results in my own custom fashion. tSQLt smartly rolls back every transaction (being a test suite, this makes sense). I've tried BEGIN TRAN/COMMIT TRAN inside my procedures for commands I don't want rolled back - like additional logging, but I realize those transactions begin as a child of the tSQLt parent transaction. Any suggestions?

Guru
Guru

Method "AssertResultSetsHaveSameMetaData" Doesnt seem to work on SQL 2008 R2 / .NET framework 4. I get below error: Msg 6522, Level 16, State 1, Procedure AssertResultSetsHaveSameMetaData, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "AssertResultSetsHaveSameMetaData": System.Data.SqlClient.SqlException: tSQLt.Failure System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at tSQLtCLR.TestDatabaseFacade.assertEquals(String expectedString, String actualString) at tSQLtCLR.MetaDataEqualityAsserter.AssertResultSetsHaveSameMetaData(SqlString expectedCommand, SqlString actualCommand) at tSQLtCLR.StoredProcedures.AssertResultSetsHaveSameMetaData(SqlString expectedCommand, SqlString actualCommand) .

Sebastian
Sebastian

Hi Ganesh. We are currently working on a book about tSQLt. Stay tuned.

Sebastian
Sebastian

Currently tSQLt does not support parameterized tests. (It is on the backlog however.) Additionally, you do want all your tests to be independent of each other. That leads to only one (or very few) tests failing when a new defect is introduced. That in turn makes it easy to figure out what caused the problem. If you have dependent tests, one failure could lead to many failing tests of which the majority likely does not even touch the broken piece of code. That makes troubleshooting unnecessarily hard.

Sebastian
Sebastian

You are right. There are a few other ones missing too. I'll probably add a tools section for those.

Sebastian
Sebastian

You could use tSQLt.NewConnection to insert data into a table outside the current transaction. Be very careful however to not block yourself, as that will cause an undetectable deadlock. Here is a demo: CREATE TABLE dbo.tst(i INT) BEGIN TRAN EXEC tSQLt.NewConnection 'INSERT dbo.tst(i) VALUES(13);'; ROLLBACK SELECT * FROM dbo.tst; DROP TABLE dbo.tst;

Mark Wojciechowicz
Mark Wojciechowicz

Guru, I have not used this testing framework before, but I can validate that I am getting the same error when the metadata is not the same. This is executing on sql 2008 r2 as well. When the metadata is the same, no error is thrown. The guide does say that an error will be raised when the schema does not match, but I would expect the message to have information about what the differences were. I would be happy to contribute to this project to get it working. Mark

Wondering
Wondering

Then does anyone know what the procedure tSQLt.SetTestResultFormatter is used for, or more importantly how to use it correctly?

Wondering
Wondering

How do you make sure you don't block yourself?

Sebastian
Sebastian

This is an extension point that allows the output to be formatted in different ways. There is for example an XML formatter that you can use to generate JUnit compatible output instead of human readable. We have not stabilized that piece yet. that is, why it is not documented yet. The goal is to allow external formatters to be installed through this as extension to the existing ones. However, you can get the XML output through other means. See for example http://tsqlt.org/177/integrating-tsqlt-with-cruise-control/

Wondering
Wondering

I looked through the tSQLt code and saw some things that might interest some people. If you want to add a line in-between Error messages tSQLt sends as out put go to tSQLt.Private_RunTest.sql Line 77 and add either a CHAR(13) or CHAR(10) to @Msg2. And to change the Test Summary output go into tSQLt.DefaultResultFormatter.sql.

Sebastian
Sebastian

The best way to avoid self blocking is to not access the same tables within NewConnection and outside of it.