Debug Stored Procedures (C#) (2023)

pass throughScott Mitchell

Download PDF

Visual Studio Professional and Team System editions allow you to set breakpoints and single-step stored procedures in SQL Server, making debugging stored procedures as easy as debugging application code. This tutorial introduces immediate database troubleshooting and application debugging of stored procedures.

show

Visual Studio provides a rich debugging experience. With a few keystrokes or mouse clicks, you can use breakpoints to stop the execution of a program and inspect its state and control its flow. In addition to debugging application code, Visual Studio also supports debugging stored procedures in SQL Server. Just as you can set breakpoints in the code of ASP.NET code-behind classes or business logic classes, they can also be placed in stored procedures.

In this tutorial, we'll see how to trace a stored procedure from Server Explorer in Visual Studio and how to set breakpoints that are encountered when calling a stored procedure from a running ASP.NET application.

SQL Server Debugging Concepts

Microsoft SQL Server 2005 is designed to provide the sameCommon Language Runtime (CLR), which is the runtime used by all .NET assemblies. Therefore, SQL Server 2005 supports managed database objects. That is, you can create database objects such as stored procedures and user-defined functions (UDFs) as methods in C# classes. This allows these stored procedures and UDFs to take advantage of the functionality of the .NET Framework as well as the functionality in your own custom classes. Of course, SQL Server 2005 also supports T-SQL database objects.

SQL Server 2005 provides debugging support for T-SQL and managed database objects. However, these objects can only be debugged with Visual Studio 2005 Professional and Team Systems editions. In this exercise, we will look at debugging T-SQL database objects. Subsequent tutorials cover troubleshooting managed database objects.

ThisOverview overview of T-SQL and CLR debugging in SQL Server 2005Blog post bySQL Server 2005 CLR Integration TeamIt highlights three methods for debugging SQL Server 2005 objects from Visual Studio:

• Direct Database Debugging (DDD)- From Server Explorer we can import all T-SQL database objects such as stored procedures and UDFs. We'll look at DDD in Step 1.
• debugging applications- We can set a breakpoint on the database object and then run the ASP.NET application. When the database object is executed, the breakpoint is hit and control is transferred to the debugger. Note that with application debugging, we cannot access database objects from application code. We need to set explicit breakpoints in stored procedures or UDFs where we want the debugger to stop. Check the app debug from step 2.
• Debugging from a SQL Server project- Visual Studio Professional and Team Systems editions include the SQL Server project type that is commonly used to create managed database objects. We will explore how to use the SQL Server project and debug its contents in the next tutorial.

Visual Studio can debug stored procedures on local and remote instances of SQL Server. A local instance of SQL Server is one that is installed on the same computer as Visual Studio. If you are using a SQL Server database that is not located on your development computer, it is considered a remote instance. In these tutorials, we used a local instance of SQL Server. Debugging stored procedures on a remote instance of SQL Server requires more configuration steps than debugging stored procedures on a local instance.

If you are using a local instance of SQL Server, you can start from step 1 and proceed through this guide. However, if you are using a remote instance of SQL Server, first ensure that you are logged on to the development computer during debugging with a Windows user account that has a SQL Server connection to the remote instance. Additionally, both this database connection and the database connection used to connect to the database by the running ASP.NET application must beSystem administratorrole See Debugging T-SQL Database Objects on a Remote Instance at the end of this tutorial for more information about configuring Visual Studio and SQL Server to debug a remote instance.

Finally, understand that debugging support for T-SQL database objects is not as feature-rich as debugging support for .NET applications. For example, breakpoint conditions and filters are not supported, only a subset of the debug window is available, Edit and Continue cannot be used, the Instant window cannot be used, etc. seeLimitations of Debugger Commands and Functionsin see simple information.

Step 1: Go directly to the stored procedure

Visual Studio makes it easy to debug database objects directly. Let's see how to use the Direct Database Debugging (DDD) feature to go through the debugging processProduct_Select by Category IDStored procedures in the Northwind database. As the name suggests,Product_Select by Category IDReturns product information for a specific category. it's insideExisting stored procedures that use a typed TableAdapter datasetcoaching school. First, go to Server Explorer and expand the Northwind database node. Then go to the stored procedures folder, right clickProduct_Select by Category IDStored Procedure, and then select the Enter Stored Procedure option from the context menu. This will start the debugger.

FromProduct_Select by Category IDA stored procedure requires a@categori_idInput parameter, we are prompted to input this value. Enter 1 which will return information about the drink.

@CategoryID parameter "/>

Figure 1: use value 1@categori_idfield of application

After you specify the value@categori_idparameter to execute the stored procedure. But instead of running to completion, the debugger stops executing the first statement. Note the yellow arrow in the margin that indicates the current position in the stored procedure. You can view and edit parameter values ​​through the watch window or by hovering over the parameter name in the stored procedure.

Figure 2: The debugger has stopped at the first statement of the stored procedure (Click to view full size image)

To move to one sentence at a time, click the Enter button on the toolbar or press F10. ThisProduct_Select by Category IDThe stored procedure contains achoosestatement, so pressing F10 will skip the single statement and exit the stored procedure. When the stored procedure completes, its output is displayed in the Output window and the debugger exits.

Step 2: Configure the website for application debugging

Although it is convenient to debug stored procedures directly from Server Explorer, in many cases we are more interested in debugging stored procedures when they are called from an ASP.NET application. We can add a breakpoint to the stored procedure from Visual Studio and start debugging the ASP.NET application. When a stored procedure with a breakpoint is called by the application, execution stops at the breakpoint, and we can see and change the stored procedure's parameter values ​​and step through its declarations, as we did in step 1.

Before we can start debugging stored procedures called by our application, we need to instruct our ASP.NET web application to integrate with SQL Server debugging. Start by right-clicking the site name in Solution Explorer (ASPNET_Data_Tutorial_74_CS). Select "Properties Pages" from the context menu, select "Startup Options" on the left, and select the SQL Server check box under "Debug" (see Figure 3).

picture 3: Select the SQL Server check box in the application properties page (Click to view full size image)

Additionally, we need to update the database connection string used by the application so that connection pooling is disabled. When the database connection is closed, it will be done accordinglysql connectionObjects are placed in a pool of available connections. Once a connection to a database is established, an available connection object can be retrieved from that pool without having to create and establish a new connection. This pooling of connection objects is a performance improvement and is enabled by default. However, when debugging, we want to disable the connection pool because the debugging infrastructure cannot be properly restored when a connection obtained from the pool is used.

Update to disable connection poolingConnection series NORTHWNDexistsweb configurationmake it contain the settingpool=false.

At this point, the ASP.NET application is configured to allow Visual Studio to debug SQL Server database objects when launched through the web application. Now all that's left is to add breakpoints to your stored procedure and start debugging!

Step 3: Add breakpoints and debugging

OpenProduct_Select by Category IDstored procedure and set a breakpoint at the beginningchooseBy clicking in the appropriate place in the margin or by placing the cursor at the beginning of the sentencechooseDeclare and press F9. As shown in Figure 4, breakpoints appear as red circles around the edges.

Figure 4: Set a breakpointProduct_Select by Category IDstored procedure (Click to view full size image)

To debug SQL database objects from a client application, the database must be configured to support application debugging. This setting should be enabled automatically when you first set a breakpoint, but it's wise to double check. Right click北WND.MDFNode in Server Explorer. The context menu should contain a highlighted menu item called "Debug Application".

Figure 5: Make sure the app debugging option is enabled

After setting breakpoints and enabling Application Debugging, we can debug the stored procedure when called by the ASP.NET application. Start the debugger by going to the Debug menu and selecting Start Debug, pressing F5, or clicking the green play icon on the toolbar. This will launch the debugger and launch the website.

ThisProduct_Select by Category IDThe stored procedure is created in theExisting stored procedures that use a typed TableAdapter datasetcoaching school. the corresponding side of (〜/ AdvancedDAL / ExistingSprocs.aspx) contains a GridView that displays the results returned by this stored procedure. Visit this page through your browser. When you reach that side, breaking pointProduct_Select by Category IDThe stored procedure will be hit and control will be returned to Visual Studio. Like step 1, you can read from the stored procedure declarations and view and change parameter values.

Figure 6: ThisExisting Sprocs.aspxThe page shows the drink first (Click to view full size image)

Figure 7: A breakpoint has been reached in the stored procedure (Click to view full size image)

As shown in the Watch window in Figure 7,@categori_idThe parameter is 1. This is becauseExisting Sprocs.aspxThe page initially displays products from the Drinks category, which haveCategory identifierThe value is 1. Select another category from the drop-down list. Doing so will result in a reset and replayProduct_Select by Category IDstored procedure. Hit the breakpoint again, but this time@categori_idThe value of the s parameter reflects the selected items in the dropdown listCategory identifier.

Figure 8: Select another category from the drop-down list (Click to view full size image)

The @CategoryID parameter reflects the category selected from the page" />

Figure 9: This@categori_idparameter reflects the category selected from the page (Click to view full size image)

Debugging T-SQL database objects on remote instances

Debugging database objects through Visual Studio is fairly straightforward when the SQL Server database instance is on the same computer as Visual Studio. However, if SQL Server and Visual Studio are on different computers, careful configuration is required to get everything working. There are two main tasks we face:

• Ensure that the connection used to connect to the database through ADO.NET is ownedSystem administratorRolle.
• Ensure that the Windows user account used by Visual Studio on the development computer is a valid SQL Server login belonging to that computerSystem administratorRolle.

The first step is relatively simple. First, identify the user account that was used to connect to the database from the ASP.NET application, and then add that connection toSystem administratorRolle.

The second task requires that the Windows user account used to debug the application is a valid login account for the remote database. However, it is possible that the Windows account with which you are logged on to your workstation is not a valid connection to SQL Server. A better option is to set certain Windows user accounts as SQL Server debug accounts instead of adding specific login accounts to SQL Server. Then, to debug database objects on a remote instance of SQL Server, you can run Visual Studio using the credentials of that Windows login account.

An example should help clarify things. Suppose there is a nameSQL debuggingWithin a Windows domain. The account must be added as a valid login and subscription to the remote SQL Server instance.System administratorrole Next, to debug a remote instance of SQL Server from Visual Studio, we need to run Visual StudioSQL debugginguser. This can be done by logging out of our workstation and then logging back inSQL debuggingand then launch Visual Studio, but it's easier to log into our workstation with our credentials and then useVoice.exeStart Visual Studio asSQL debugginguser.Voice.exeAllows specific applications to run on behalf of different user accounts. Start Visual Studio asSQL debugging, you can enter the following statement in the command line:

runas.exe /user:SQLDbug“%PROGRAMFILES%\Microsoft Visual Studio 8\Common7\IDE\devenv.exe”

For a more detailed description of this process, seeWilliam R. VaughnofThe Hitchhiker's Guide to Visual Studio and SQL Server, Seventh Edition.

sum up

In addition to providing debugging support for .NET application code, Visual Studio also provides several debugging options for SQL Server 2005. In this exercise, we look at two of those options: direct database debugging and application debugging. To debug a T-SQL database object directly, locate the object through Server Explorer, right-click it, and select Step In. This starts the debugger and stops at the first statement on the database object, after which you can be informed by the object's declarations and view and change parameter values. In step 1 we used this method to enterProduct_Select by Category IDstored procedure.

Application debugging allows breakpoints to be set directly on database objects. When you call a database object with a breakpoint from a client application (such as an ASP.NET web application), the application stops when the debugger takes over. Application debugging is useful because it can show more clearly which application actions lead to calls to specific database objects. However, it requires more configuration and installation than straightforward database troubleshooting.

Database objects can also be debugged through SQL Server projects. In the next tutorial, we'll see how to use SQL Server projects and how to use them to create and debug managed database objects.

Happy planning!

About the Author

Scott Mitchell, author and founder of seven ASP/ASP.NET books4GuysFromRolla.com, has been working in Microsoft web technologies since 1998. Scott is an independent consultant, trainer and author. his last book isSams taught himself ASP.NET 2.0 in 24 hours. It can be accessed in the following ways:mitchell@4GuysFromRolla.com.or through his blog which can be found athttp://ScottOnWriting.NET.