Displaying SQL Server Messages using ASP.NET and ADO.NET

Posted by: Malcolm Sheridan , on 6/30/2009, in Category ASP.NET
Views: 49049
Abstract: The following article demonstrates how to use display SQL Server messages using ADO.NET and ASP.NET
Displaying SQL Server Messages using ASP.NET and ADO.NET
 
I’ll start off this article by saying that I am a big stored procedure believer. I believe in them for three reasons. These are security, execution plans and decoupling. A downfall of them however is trying to debug them. This can be made tougher by the sheer size of some stored procedures I have seen in production systems. A technique I have found that comes in handy is having T-SQL PRINT statements in the stored procedure. Note: There is a very handy SQL debugger tool in SQL Server 2008 Management Studio.
These print statements can help determine the execution path of the stored procedure. The print statements can be returned to your website via the SqlConnection.InfoMessage event. This event occurs when SQL Servers returns a warning or informational message.
For this example I’ll be connecting to the Northwind database. If you don’t have a copy of it, you can go here to download it.
Okay open up the Northwind database through SQL Management Studio. The stored procedure I will use in this example is CustOrderHist. Open the stored procedure and update it to the following code:
ALTER PROCEDURE [dbo].[CustOrderHist] @CustomerID nchar(5)
AS
 
PRINT 'The date is ' + CONVERT(VARCHAR,GETDATE())
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
 
In the code above I have one PRINT statement returning the current date. This is the value will be passed back to the website through the InfoMessage event. Save the stored procedure and close Management Studio.
 The next task is to create a website to display the print messages. Open Visual Studio 2008 and choose File > New > Web > ASP.NET Web Application. Open The Default.aspx page and add a TextBox control to the page:
<asp:TextBox ID="txtMessages" runat="server" Width="323px"></asp:TextBox>
The TextBox will display the message sent back from the database. The trick to getting this code to work is by creating an InfoMessage event handler. I thought it would be cool to use an anonymous method for this. Open the Default.aspx.cs file and add the following code:
 
C#
 
private void GetCustomers()
{
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
      {
            cn.Open();
            cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
            {                                    
                  txtMessages.Text += "\n" + e.Message;                                   
};
 
            SqlCommand cmd = new SqlCommand("CustOrderHist", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@CustomerID", "ALFKI"));
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                  while (dr.Read())
                  {
string productName = dr.GetString(dr.GetOrdinal("ProductName"));       
                  }
}
}
}
 
VB.NET
 
Private Sub GetCustomers()
Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
             cn.Open()
                  AddHandler cn.InfoMessage, Function(sender, e) AnonymousMethod1(sender, e)
 
                  Dim cmd As New SqlCommand("CustOrderHist", cn)
                  cmd.CommandType = CommandType.StoredProcedure
                  cmd.Parameters.Add(New SqlParameter("@CustomerID", "ALFKI"))
                  Using dr As SqlDataReader = cmd.ExecuteReader()
                        Do While dr.Read()
Dim productName As String = dr.GetString(dr.GetOrdinal("ProductName"))
                        Loop
                  End Using
End Using
End Sub
 
Private Function AnonymousMethod1(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs) As Boolean
      txtMessages.Text += Constants.vbLf + e.Message
      Return True
End Function
 
In the code above I am using the using keyword to ensure my SqlConnection and SqlDataReader connections are closed automatically when the code has finished running. I have created an anonymous method to execute the InfoMessage event whenever a message is received from SQL Server:
 
C#
 
cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
{                                   
txtMessages.Text += "\n" + e.Message;                                   
};
 
VB.NET
 
AddHandler cn.InfoMessage, Function(sender, e) AnonymousMethod1(sender, e)
 
Private Function AnonymousMethod1(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs) As Boolean
txtMessages.Text += Constants.vbLf + e.Message
      Return True
End Function
 
Anonymous methods are good for this example because it keeps the code in one place. The rest of the code is normal ADO.NET code. Add the following code to the Page Load event:
 
C#
 
protected void Page_Load(object sender, EventArgs e)
{
GetCustomers();
}
 
VB.NET
 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
GetCustomers()
End Sub
 
You’re ready to run the project now. Run the project and the TextBox will be populated by the PRINT statement from the stored procedure:
 
SQL Msg
 
I think this is a nice feature of ADO.NET that isn’t used enough. Hopefully you can start using this to help troubleshoot your stored procedures. 

The entire source code of this article can be downloaded from here

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Malcolm Sheridan is a Microsoft awarded MVP in ASP.NET, a Telerik Insider and a regular presenter at conferences and user groups throughout Australia and New Zealand. Being an ASP.NET guy, his focus is on web technologies and has been for the past 10 years. He loves working with ASP.NET MVC these days and also loves getting his hands dirty with jQuery and JavaScript. He also writes technical articles on ASP.NET for SitePoint and other various websites. Follow him on twitter @malcolmsheridan


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by Pradeep Gaur on Thursday, July 2, 2009 2:31 AM
very useful article .
Comment posted by Samir on Thursday, December 3, 2009 12:36 AM
got error
CS0136: A local variable named 'sender' cannot be declared in this scope because it would give a different meaning to 'sender', which is already used in a 'parent or current' scope to denote something else


plz help me
Comment posted by Malcolm Sheridan on Friday, December 4, 2009 3:43 PM
@Samir
Are you using the downloaded code? I just compiled it and it worked for me. Let me know if you're still having issues.
Comment posted by Kiran on Tuesday, March 9, 2010 5:45 PM
I have a question......
I have a page async task(running in the code behind on a button click present in the update panel) which executes a stored proc. this stored proc has got print msgs which i want to be displayed on the webpage instantly as and when the print statements get executed in the stored proc.

wat shud i do?
Comment posted by Kiran on Tuesday, March 9, 2010 6:00 PM
I have a question......
I have a page async task(running in the code behind on a button click present in the update panel) which executes a stored proc. this stored proc has got print msgs which i want to be displayed on the webpage instantly as and when the print statements get executed in the stored proc.

wat shud i do?
Comment posted by Chris on Tuesday, May 18, 2010 12:55 AM
Good article, useful concept for writing reports in SQL where PRINT can be very user.  I had to change Function(sender, e) to Function(sender, f) to get it to work, and upgrade my web.config to use the .NET 3.x compiler for it to understand the anonymous function call (been meaning to do that for a while anyway) but otherwise fine.
Comment posted by Gaurang on Tuesday, February 22, 2011 12:50 AM
Very nice. Made things easy for me. Thank you sir.
Comment posted by bp on Wednesday, August 31, 2011 6:51 PM
Very good article. You save me a lot of time
Comment posted by Jeff on Thursday, January 19, 2012 4:17 PM
Great article, thank you. Can I use this technique for Windows application?
Comment posted by Jeff on Thursday, January 19, 2012 4:22 PM
Great article, thank you. Can I use this technique for Windows application?
Comment posted by siddharth borania on Monday, May 21, 2012 6:44 AM
You can see here for similar kind of explanation.
Comment posted by Zayati on Friday, June 29, 2012 7:46 AM
how to write code of printing message
Comment posted by Alex on Monday, August 6, 2012 11:02 AM
Hi, its what I was looking for but was wondering how to use that in a while loop so i can use it with Update progrees displaying messages as soon as I get result back from SQL? Thanks
Comment posted by Mohan Udupa on Saturday, February 16, 2013 3:26 AM
Hi Malcolm Sheridan, thank you very much.

I was struggling to display the error message, your article helped me a lot.

Please keep sharing your knowledge, thanks again.

Mohan Udupa
Comment posted by aa on Wednesday, August 21, 2013 4:26 AM
qwqwqqw
Comment posted by Jaka Bolcina on Thursday, January 9, 2014 2:33 PM
Hi,
This is a great article, but I have a problem when having multiple PRINT statements in the same SP. I have a problem where the InfoMessage event fires only once providing all the print messages encountered at once.  Is there a workaround so that InfoMessage fires each time it encounters PRINT?
Thanks

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel