This month, I will show you a little trick that you might not need very often but which will be invaluable the day you will need it.
If you are like me, you are using a lot of stored procedures from Microsoft SQL Server from within your applications. Sometimes, some of these stored procedures are not working as expected. Even if it is easier than before, debugging them might still be a pain for you if you don’t have all the permissions required.
The trick I will show you here is to add Print statements in your SQL stored procedures (which I hope is not new to you) and to retrieve these Print statements from your .Net applications.
When your stored procedure has a complex path (multiple If statement for example), it might be difficult to follow.
The demo application
This month, the demo application is available in both VB and C#. It was saved using Visual Studio 2010 but the code could be reused in older version of .Net. Notice that you will also need Microsoft SQL Server in order to test the code.
Figure 1: The demo application in action
Creating the test database
My database will be very simple. This little database contains a single stored procedure.
For my demo application, I have created a brand new database using this script:
CREATE DATABASE [TestPrint] GO USE [TestPrint] GO
In this database, we can now create a simple stored procedure only for the purpose of returning Print statements for our demo. This is my demo stored procedure:
CREATE PROCEDURE [dbo].[TestPrint] @P1 VARCHAR(50) AS BEGIN SET NOCOUNT ON; PRINT 'The SP TestPrint execution starts at ' + CONVERT(VARCHAR(20), GETDATE(), 120) PRINT 'The original parameter : ' + @P1 SET @P1 = UPPER(@P1) PRINT 'The transformed value is : ' + @P1 PRINT 'The lenght is : ' + CAST(LEN(@P1) AS VARCHAR) SELECT @P1 PRINT 'The SP TestPrint execution ends at ' + CONVERT(VARCHAR(20), GETDATE(), 120) END
You should at least test it from within SSMS (SQL Server Management Studio) to ensure your stored procedure is working properly:
exec dbo.TestPrint 'This is a test'
After executing this stored procedure, the results tab should show you the result of the Select statement and, what will be more interesting for us, the Messages tab should show you these lines:
The SP TestPrint execution starts at 2011-10-11 20:06:55 The original parameter : This is a test The transformed value is : THIS IS A TEST The lenght is : 14 The SP TestPrint execution ends at 2011-10-11 20:06:55
Creating the UI
As you can see in figure 1, the user interface to test this feature is really simple. A Label and a Textbox (txtParameter) to let you enter a value we will send to the stored procedure, a Button (btnGo) to execute the stored procedure, a DataGridView (grdResults) to display the result set returned by the stored procedure and a ListBox (lstPrint) to display the results of the Print statements.
The .Net code to execute the Stored Procedure
Now that we have a stored procedure against which we can test, we need some .Net code to verify that we can really get the Print statements back.
Here the first snippet of code that we need. This snippet simply is self-explanatory. Here are the steps:
Here is the code for the Click event of the button:
'Clears the listbox lstPrint.Items.Clear() 'create a new connection object Using cn As New SqlConnection("Data Source=(local); Initial Catalog=TestPrint; Integrated Security=SSPI;") 'Open the connection cn.Open() 'create a command object to call the stored procedure Dim cmd As New SqlCommand("TestPrint", cn) cmd.CommandType = CommandType.StoredProcedure 'add and fill the parameter cmd.Parameters.Add(New SqlParameter("@P1", txtParameter.Text)) 'create an adapter and fill a dataset with it Dim da As New SqlDataAdapter(cmd) Dim ds As New DataSet da.Fill(ds) 'set the grid content to the result returned by the stored procedure grdResults.DataSource = ds.Tables(0) End Using
If you execute the code at this point, you will get your DataGridView filled with the result set returned by the stored procedure but the ListBox will remain empty as we haven’t done anything yet to process the Print statements.
The .Net code to retrieve the Print statements
Would you believe me if I tell you that only 2 lines of code (comments are not counted here) are required in order to retrieve the Print statements?
To be able to catch these Print statements, you will need to handle the InfoMessage event from your SQLConnection object. This event can also be used to retrieve warnings and informational messages triggered by your connection.
That means that the first line of code required is simply to be able to catch the InfoMessage event. Just after the opening of the SQLConnection in the previous snippet (cn.Open), add this line:
'add a event handler to the InfoMessage event AddHandler cn.InfoMessage, AddressOf ConnectionInfoMessage
The second line of code is the actual handling of the event. The code is the following:
Private Sub ConnectionInfoMessage(sender As Object, e As System.Data.SqlClient.SqlInfoMessageEventArgs) 'add the message returned by the print statements to the ListBox lstPrint.Items.Add(e.Message) End Sub
This event will simply add any message received to the ListBox control.
That’s it. You now have everything in place to be able to run your stored procedure and to retrieve the Print statements.
Conclusion
I am pretty sure you won’t need this trick today but you might find it useful in the near future! Just don’t lose the reference to it!