How to: Debug a Multi-tier Database Application

Article
11/16/2012

This topic applies to:

Edition	Visual Basic	C#	C++	Web Developer
Express
Standard
Pro and Team

Table legend:

	Applies
	Does not apply
	Command or commands hidden by default.

This topic contains sample code that demonstrates debugging a multi-tiered application and describes the steps needed to debug from application code residing in a client or mid-tier application into code for a database object that runs inside the SQL Server 2005.

Transitions between the application tier and the database tier require a breakpoint in the target tier; otherwise, the code will just execute without stopping when you try to step into the tier. But transitions between T-SQL and SQL CLR code inside the database tier do not require breakpoints to enable stepping between them.

The following example uses the AdventureWorks database, and steps back and forth between the different tiers and languages. The intent of the example is to illustrate these transitions, and is clearly not a real business scenario.

Three stored procedures are called:

DeleteCurrency is a SQL CLR stored procedure that deletes a currency with a given currency code.
DeleteCurrency_T_SQL does the same thing, but is written in T-SQL.
DeleteCurrencyDriver calls the preceding two stored procedures with an input parameter that specifies the currency code to delete.

The application code calls all three stored procedures, passing in a currency code parameter. The two "non-driver" stored procedures are called from two different contexts, from DeleteCurrencyDriver, and directly from the application. In the first context, you will be able to step from DeleteCurrencyDriver into the other two stored procedures. When they are called from the application, you will be unable to step directly into them, and will have to set breakpoints inside the stored procedures.

Debug a database application

In a new SQL Server Project, establish a connection to the AdventureWorks database. For more information, see How to: Connect to a Database.
Create a T-SQL stored procedure using the code from the first example section below, and name it DeleteCurrency_T_SQL.For more information on this, or any of the steps in this procedure, see How to: Develop with the SQL Server Project Type.
Create a SQL CLR stored procedure using the code from the second example section below, and name it DeleteCurrency.cs.
Create a SQL CLR stored procedure using the code from the third example section below, and name it DeleteCurrencyDriver.
On the Debug menu, click Start to compile and deploy these changes to the AdventureWorks database.
Set at least one breakpoint in each stored procedure. Stepping from native or managed code into a stored procedure will not work.
Create a new console project in Visual Studio.
Paste the code from the fourth example into the text editor.
Place breakpoints before and after each call to a stored procedure.
Run the application by pressing F5.
Step through the different modules.
Try removing some of the breakpoints to see the effect of trying to step between different tiers and languages.
To finish debugging, clear all breakpoints from the Visual Studio Debug menu, and press F5.

Example

This section contains the code for the T-SQL stored procedure.

CREATE PROCEDURE dbo.DeleteCurrency_T_SQL
    (
        @CurrencyCode nvarchar(3)
    )
AS
    SET NOCOUNT ON
    DELETE Sales.Currency 
    WHERE CurrencyCode = @currencyCode 
    RETURN

This code contains the code for the SQL CLR stored procedure called from a driver stored procedure.

using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
 
public partial class StoredProcedures
{
    [SqlProcedure]
    public static void DeleteCurrency(SqlString currencyCode)
    {
        string sCmd = "DELETE Sales.Currency WHERE CurrencyCode = '" + currencyCode.Value + "'";
        SqlConnection conn = new SqlConnection("Context Connection=True");
        conn.Open();
        SqlCommand  DeleteCurrencyCommand = new  SqlCommand( sCmd , conn);
        DeleteCurrencyCommand.ExecuteNonQuery();
    }
}

This code contains the code for the SQL CLR driver procedure that calls other procedures. This stored procedure is called from the application tier.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class StoredProcedures
{
    [SqlProcedure]
    public static void DeleteCurrencyDriver(SqlString CurrencyCode)
    {
        string sCommand = "DELETE Sales.Currency WHERE CurrencyCode = '" + CurrencyCode.Value + "'";
        SqlConnection conn = new SqlConnection("Context Connection=True");
        conn.Open();
        SqlCommand DeleteCurrencyCommand = new SqlCommand(sCommand, conn);
        DeleteCurrencyCommand.ExecuteNonQuery();
 
        // Now execute a T-SQL stored procedure.
        DeleteCurrencyCommand.CommandType = CommandType.StoredProcedure;
        DeleteCurrencyCommand.CommandText = "DeleteCurrency_T_SQL";
        // Fill the parameters collection based upon stored procedure.
        SqlParameter workParam = null;
        workParam = DeleteCurrencyCommand.Parameters.Add("@CurrencyCode", SqlDbType.NChar, 3);
        DeleteCurrencyCommand.Parameters["@CurrencyCode"].Value = "ESC";
        try { DeleteCurrencyCommand.ExecuteNonQuery(); }
        catch { }
 
        // Now execute a CLR stored procedure.
        DeleteCurrencyCommand.CommandText = "DeleteCurrency";
        try { DeleteCurrencyCommand.ExecuteNonQuery(); }
        catch { }
    }
};

This code contains the application code that will call the driver stored procedure, as well as directly calling the T-SQL and SQL CLR stored procedures.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
 
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = <server>;
            builder.IntegratedSecurity = true;
            builder.InitialCatalog = <database>;
 
            SqlConnection SqlConnection1 = new SqlConnection(builder.ConnectionString);
            SqlConnection1.Open();
 
            SqlCommand procCommand = new SqlCommand();
 
            procCommand.CommandText = "DeleteCurrencyDriver";
            procCommand.CommandType = CommandType.StoredProcedure;
            procCommand.Connection = SqlConnection1;
            // Fill parameters collection for the stored procedure.
            SqlParameter workParam = null;
            workParam = procCommand.Parameters.Add("@CurrencyCode", SqlDbType.NChar, 3);
            procCommand.Parameters["@CurrencyCode"].Value = "ESC";
            
            try { procCommand.ExecuteNonQuery(); }
            catch (SqlException e) { DumpException(e); }
 
            procCommand.CommandText = "DeleteCurrency";
            try { procCommand.ExecuteNonQuery(); }
            catch (SqlException e) { DumpException(e); }
 
            procCommand.CommandText = "DeleteCurrency_T_SQL";
            try { procCommand.ExecuteNonQuery(); }
            catch (SqlException e) { DumpException(e); }
 
            SqlConnection1.Close();
        }
        static void DumpException(SqlException e)
       {
            string errorMessages = "";
            for (int i = 0; i < e.Errors.Count; i+)
           {
                errorMessages += "Index #" + i + "\n" +
                       "Message: " + e.Errors[i].Message + "\n" +
                       "LineNumber: " + e.Errors[i].LineNumber + "\n" +
                       "Source: " + e.Errors[i].Source + "\n" +
                       "Procedure: " + e.Errors[i].Procedure + "\n";
            }
            System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();
            log.Source = "My Application";
            log.WriteEntry(errorMessages);
            Console.WriteLine("An exception occurred. Please contact your system administrator.");
        }
    }
}

How to: Debug a Multi-tier Database Application

Debug a database application

Example

See Also

Tasks

Additional resources