How to: Debug a Multi-tier Database Application

This topic applies to:

Visual Studio Ultimate	Visual Studio Premium	Visual Studio Professional	Visual Studio Express

This topic contains sample code that demonstrates debugging a multi-tier database application and describes the steps needed to debug from application code residing in a client or mid-tier application into Transact-SQL code for a database object that runs inside an instance of SQL Server, beginning with 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 Transact-SQL and SQL CLR code inside the database tier do not require breakpoints to enable stepping between them.

The following example uses a C# console application, a SQL Server database table named Currency, and two stored procedures to step back and forth between C# and Transact-SQL. The intent of the example is to illustrate these transitions, and is clearly not a real business scenario.

Two stored procedures are called:

• DeleteCurrency is a Transact-SQL stored procedure that deletes a currency with a given currency code.

• DeleteCurrencyDriver is also a Transact-SQL stored procedure. It calls the preceding stored procedure with an input parameter that specifies the currency code to delete.

The application code calls both stored procedures, passing in a currency code parameter. The "non-driver" stored procedure is 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 stored procedures by clicking the Step Into button. When they are called from the application, you will not be able to step directly into them, and will have to set breakpoints inside the stored procedures.

Create the Transact-SQL database objects for this demonstration

1. In a SQL Server 2005 or later database of your choice, use the code from the first example to create the Currency table and the two stored procedures.

   1. Open the Transact-SQL editor in Visual Studio and connect to the database you can use for this demonstration. For more information, see How to: Start the Transact-SQL Editor and How to: Connect to a Database in the Transact-SQL Editor.

   2. Copy the Transact-SQL code from the first example and paste it into the Transact-SQL editor.

   3. Replace <database> with the appropriate database name, and then click Execute SQL in the Transact-SQL Editor toolbar.

2. If you opened the Transact-SQL editor in the context of a SQL Server Database or Server project, close the project before continuing. Otherwise, you can leave the Transact-SQL editor open and continue.

Prepare Server Explorer for multi-tier debugging

1. Ensure that Transact-SQL debugging has been enabled for your computer. For more information, see How to: Enable Transact-SQL Debugging.

2. Open Server Explorer by opening the View menu and clicking Server Explorer.

3. In Server Explorer, create a new data connection by right-clicking DataConnections and selecting Add Connection. This will launch the Add Connection dialog box.

4. Create a connection to the database where you created the Currency table. It is important that the login used by the connection is a member of the sysadmin fixed server role. For more information, see How to: Connect to a Database with Server Explorer.

5. After you have created the new data connection, locate it under the Data Connections node.

6. Right-click your new data connection and click Application Debugging. This enables multi-tier debugging on the database specified in the connection.

Prepare your C# console application for multi-tier debugging

1. In the same Visual Studio instance that you prepared Server Explorer, prepare a C# console application that will execute the DeleteCurrencyDriver and DeleteCurrency stored procedures.

   1. Open a new C# console application by opening the File menu and clicking New Project.

   2. Copy the C# code from the second example and use it to replace all of the code in the Program.cs file that was created by the project.

2. In the console application properties, on the Debug tab, check Enable SQL Server debugging. This enables multi-tier debugging for the console application. For more information, see How to: Enable Transact-SQL Debugging For a C++, Visual Basic, or C# Project.

Debug your multi-tier console application

1. In your C# code (the Program.cs file), place breakpoints before and after each call to a stored procedure.

2. Place breakpoints in your stored procedures. Note: You cannot step into Transact-SQL code from C# code, but you can step between SQL Server database objects.

   1. In Server Explorer, locate the new stored procedures under the new data connection.

   2. Right-click the DeleteCurrency stored procedure and click Open. This will launch a Transact-SQL editor window displaying the DeleteCurrency stored procedure. Set a breakpoint on the SET NOCOUNT ON line by clicking in the grey bar on the left side of the editor.

   3. Right-click the DeleteCurrencyDriver stored procedure and click Open. This will launch a Transact-SQL editor window displaying the DeleteCurrencyDriver stored procedure. Set a breakpoint on the SET NOCOUNT ON line by clicking in the grey bar on the left side of the editor.

3. Run the application by pressing F5.

4. Step through the different modules.

5. Try removing some of the breakpoints to see the effect of trying to step between different tiers and languages.

6. To finish debugging, clear all breakpoints from the Visual Studio Debug menu and press F5.

Example

This section contains the Transact-SQL code that creates the Currency table and the two stored procedures: DeleteCurrency and DeleteCurrencyDriver. Replace <database> with the name of the appropriate database.

USE <database>
GO
CREATE TABLE Currency
(CurrencyCode nvarchar(1))

INSERT Currency 
VALUES (N'A'),(N'B'),(N'C'),(N'D')

SELECT * FROM Currency 
GO

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

CREATE PROCEDURE dbo.DeleteCurrencyDriver
    (
        @CurrencyCode nvarchar(3)
    )
AS
    SET NOCOUNT ON
    EXECUTE DeleteCurrency @CurrencyCode 
    RETURN
GO

This section contains the C# code of the console application that calls the DeleteCurrencyDriver and DeleteCurrency stored procedures. Replace <server> and <database> with the instance and database name where the Currency table resides, respectively.

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, 1);
            procCommand.Parameters["@CurrencyCode"].Value = "B";
            
            try { procCommand.ExecuteNonQuery(); }
            catch (SqlException e) { Console.WriteLine(e.Message); }
 
            //Execute DeleteCurrency stored procedure directly
            procCommand.CommandText = "DeleteCurrency";
            try { procCommand.ExecuteNonQuery(); }
            catch (SqlException e) { Console.WriteLine(e.Message); }
 
            SqlConnection1.Close();
            Console.WriteLine("Press any key to close...");
            Console.Read();
        }
    }
}

See Also

Tasks

How to: Enable Transact-SQL Debugging For a C++, Visual Basic, or C# Project

How to: Enable Multi-tier Debugging

How to: Enable Transact-SQL Debugging