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:
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
No comments:
Post a Comment