Manipulating data 72 in asp.net 2.0: Debugging Stored Procedures _ self-study process

Introduction:

Visual Studio has a number of debugging features. We just need a little bit of a mouse, knocking on the keyboard to break a program's execution with a breakpoint (breakpoints) and view its status. In addition to debugging code, Visual Studio supports SQL The stored procedures inside the server are debugged. Just as you can set breakpoints in the ASP.net page's background code class or the business Logic Layer class class, we can also set breakpoints in the stored procedure.

In this article we will examine how to enter a stored procedure in Visual Studio's Server Explorer and set breakpoints. This breakpoint is encountered when the ASP.NET program invokes the stored procedure.

　　Note: Unfortunately, you can only set breakpoints on a stored procedure in Visual Studio's professional and team systems versions. If you are using a standard version of Visual Web Developer or Visual Studio, You can only see how the tutorial is done step-by-step, but not on your own machine.

SQL Server Debugging Topics

Microsoft SQL Server 2005 is a collection of the common language runtime (Common Language Runtime (CLR)) that can be owned by all. NET module. Therefore, SQL Server 2005 supports the management of database objects.

We can create database objects, such as stored procedures, user-defined functions (UDFs), as defined in a C # class. You can use these stored procedures and custom functions from the. NET framework or your own defined classes. Of course, SQL Server 2005 also supports T-SQL database objects. SQL Server 2005 supports debugging of T-SQL database objects and Managing database objects (Managed DB objects). But only in Visual Studio 2005 Professional Edition or Team Systems version. In this article, we will examine the debugging of T-SQL databases. Later tutorials we will examine debugging of managing database objects. Article "Overview of T-SQL and CLR debugging in SQL Server 2005" (http ://blogs.msdn.com/sqlclr/archive/2006/06/29/651644.aspx) summarizes 3 ways to debug SQL Server 2005 objects in Visual Studio:

. Direct Database Debugging (DDD)-Enter a T-SQL database object in Server Explorer, such as a stored procedure or user-defined function UDFs. We will examine the pattern in the first step.

. Application debugging-We can set breakpoints in a database object, And then return to a asp.net application. When these database objects are executed, the you will encounter a breakpoint and go to the debugger. Note that in application debugging mode, we cannot break into a database object in the application code, so we have to set breakpoints directly in these stored procedures or user-defined functions. We will examine the pattern in the second step .

. Debug with a SQL Server project

The Visual Studio Professional and team systems editions contain a SQL Server project type that is typically used to create management database objects (managed DB objects), which we will examine in a later tutorial on SQL Server Projects and how to debug its contents. Visual Studio can debug a local or external instance of SQL Server. If you are using a SQL Server database that is not local, then naturally, it is an external instance. In this article we will use a local SQL Server instance to debug the stored procedures of an external SQL Server instance more than the local one.

If you are using a local SQL Server instance, you can see the end from the first step. If you are using an external SQL Server instance, you first need to make sure that you are logged on to the computer with a Windows user account and that you have a connection to an external SQL The login interface for the server instance. In addition, all should be logged on as the system Administrator role. You can see the "Debugging T-SQL Database Objects on Remote Instances" section at the end of this article to see How to configure Visual Studio and SQL Server to debug external instances.

Finally, we should understand that features that support the debugging of T-SQL database objects are not rich in features that support. NET application debugging. For example, breakpoint conditional filtering (breakpoint conditions and filters) is not supported, and only a few debugging windows are used ; You can't use edit and continue and so on. For more information, please refer to the article "Limitations on Debugger Commands and Features" (http://msdn2.microsoft.com/en-us/library/ms165035 (vs.80). aspx

Step one: Go directly to the storage process

With Visual Studio, we can easily go directly to a database object. Let's look at how to use the Direct database Debugging (DDD) feature to access the PRODUCTS_ in the Northwind database Selectbycategoryid stored procedures. As its name suggests, the stored procedure returns product information for a specific category. We created the stored procedure in chapter 68th, expanded the Northwind database node in Server Explorer, then went to the stored Procedures folder, right-click on the Products_selectbycategoryid stored procedure, and select Step Into Stored Procedure, which will open the debugger. Because the stored procedure accepts a @categoryid input parameter, we enter 1, which returns information for the "Beverage" class product.

Figure 1: Using the @categoryid input parameter with a value of "1"

After you specify the value of the @categoryid parameter, you can execute the stored procedure. However, the debugger aborts after the first statement is executed, rather than running the stored procedure completely ... Note the yellow arrow on the border, which indicates the current position of the statement in the stored procedure. You can view or edit the parameter values in the Watch window, or overwrite the name of the parameter used by the stored procedure.

Figure 2: Debugger stops after debugging the first statement

If you want to debug only one statement statement at a time, click the Step Over button on the toolbar or press the F10 key. Because the Products_selectbycategoryid stored procedure contains only a single SELECT statement, pressing the F10 key crosses the statement and completes the execution of the stored procedure. After execution, the results will be displayed in the Output window, and the governor will be terminated.

NOTE: t-SQL debugging occurs at the statement level but is not valid for SELECT statement

Step two: Set up application debugging for website

While debugging stored procedures directly in Server Explorer requires manual setup, most of the time we are interested in debugging a stored procedure when it is invoked in a asp.net application. This breakpoint is encountered during execution as a stored procedure that sets a breakpoint is invoked by the application. We can view and change the parameter values of the stored procedure, as we did in the first step.

But before we do that, we need to associate the ASP.net Web application with the SQL Server debugger. In Solution Explorer, right-click on the website name (ASPNET_DATA_TUTORIAL_74_CS) and select the property Pages item, and then select Start options on the left, selecting the SQL Server selection box for the debuggers area, as shown in Figure 3:

Figure 3: Selecting the SQL Server box in the application's property page

In addition, we will update the database connection string to disable connection pooling. When a connection to a database is closed, a corresponding SqlConnection object is stored in the connection pool. When a database connection is established, You can retrieve the SqlConnection object again without creating a new connection. Connection pooling improves performance and, by default, is active. However, when debugging, we will close the connection pool because it is not possible to properly set up the corresponding debug infrastructure when processing connections retrieved from the connection pool ( Debugging infrastructure).

To disable connection pooling, update the northwndconnectionstring of the Web.config file, including a "pooling=false" setting.

<connectionStrings>
 <add name= "northwndconnectionstring" connectionstring=
 "Data source=./ SQLEXPRESS; attachdbfilename=| Datadirectory|/northwnd. MDF;
  Integrated security=true; User instance=true; Pooling=false "
 providername=" System.Data.SqlClient "/>
</connectionStrings>

　　Note: Once you have finished debugging SQL Server from the asp.net application, be sure to restore the connection pool, remove the pooling setting from the connection string, or set it to "Pooling=true".

Once the setup is complete, we also need to add a breakpoint to the stored procedure and start debugging

Step three: Add Breakpoints and Debug

Open the Products_selectbycategoryid stored procedure and set the breakpoint at the beginning of the SELECT statement. Method Click on the right border or place the cursor at the beginning of the SELECT statement and press F9. As shown in Figure 9, the breakpoint is rendered as a red dot on the border.

Figure 4: Setting breakpoints in Products_selectbycategoryid stored procedures

In order to debug a SQL database object in the client program, it is necessary to set up the database to support application debugging (application debugging), point to Northwnd.mdf node in Server Explorer, right-click, and select Application debugging ".

Figure 5: Make sure the application debugging item is selected

When the breakpoint is set and the "Application debugging" item is activated, we can invoke the stored procedure from the ASP.net application for debugging. To debug, select "Start Debugging" in the "Debug" menu, or click the green icon in the F5 or dot toolbar. This will open the debugger.

The Products_selectbycategoryid stored procedure is created in chapter 68th, and its corresponding page (~/advanceddal/existingsprocs.aspx) contains a GridView control, To display the results returned by the stored procedure. Log on to the page in the browser, and the page will return to visual Studio when the execution process encounters a set breakpoint, as seen in the first step, we can go to the statements of the stored procedure and view and modify the parameter values.

Figure 6:existingsprocs.aspx Page is the first to show the products of the beverage category

Figure 7: Breakpoint execution to stored procedure settings

In the Watch window shown in Figure 7, the value of the parameter @categoryid is 1, because the Existingsprocs.aspx page initially displays information about the beverage product, and the beverage class has a CategoryID value of 1. Select a different value in the Drop-down list. This will cause the page to return and execute the Products_selectbycategoryid stored procedure again. When you encounter a breakpoint again, the value of the @CategoryID parameter is the CategoryID value you selected in the Drop-down list box.

Figure 8: Select a different class in the Drop-down list

Figure 9: The parameter @categoryid reflects the class selected on the Web page

　　Note: If you are logged on to the Existingsprocs.aspx page and have not encountered a breakpoint set in the Products_selectbycategoryid stored procedure, be sure to asp.net the application's "property page" The SQL Server entry for the debuggers zone is selected, the connection pool is closed, and the application debugging entry for the database is activated. If you still have a problem, restart Visual Studio and try again.

Debugging a remote instance of a T-SQL database object

Debugging with Visual Studio on the local computer is straightforward, but if SQL Server and Visual Studio are not installed on the same machine, we need to do some setup to make everything work, so we have to do 2 things:

. Make sure that you connect to the database through ado.net with the role of system administrator (sysadmin)

Ensure that the Windows user account used by Visual Studio on the development environment is a valid SQL Server login account that belongs to the system Administrator role (sysadmin roles).

The first is relatively simple. First determine the user account to connect to the database from the ASP.net application, and then add the account to the sysadmin role through SQL Server Management Studio.

Second, the Windows user account you use to debug the program must be a valid account to connect to the remote database, and the problem is that the Windows account you log on to at the local workstation is not necessarily a valid account to log on to SQL Server. Instead of adding your specific login account to SQL Server, a better option is to assign some Windows user accounts as SQL Server debug accounts (SQL Server Debugging Account). Then, to debug a database object for a remote SQL Server instance, You can use these Windows logon account authentication (credentials) to run visual Studio.

Use an example to illustrate. Suppose you have a Windows account named SQLDebug. The account should be added to a remote SQL Server instance to log on legally with the system Administrator role. Then, to debug a remote SQL Server instance from Visual Studio, We should run visual Studio as a SQLDebug account. So we can do this: quit our workstation, log in as a SQLDebug account, and then run visual Studio. There is, however, a simpler way to run visual on behalf of SQLDebug by using Runas.exe instead of quitting the workstation. Studio. This runas.exe allows an application to execute under the guise of another account. To run Visual Studio on behalf of SQLDebug, you can type the following statement at the command line:

Runas.exe/user:sqldebug "%programfiles%/microsoft Visual Studio 8/common7/ide/devenv.exe"

For a more detailed description of this process, see the article How To:set SQL Server Permissions for Debugging (HTTP://MSDN2.MICROSOFT.COM/EN-US/LIBRARY/W1BHYBWZ) ( vs.80). aspx)

　　Note: If your developer is a Windows XP Service Pack version 2, you need to configure the Internet Connection Firewall to allow remote debugging. article "The How To:enable SQL Server debugging" (http://msdn2.microsoft.com/en-us/library/s0fk6z6e (vs.80). aspx) Describes this, which includes 2 steps: (a) in the host where Visual Studio resides, you must add Devenv.exe to the Exceptions list and open TCP 135 ports; (b) on the remote (SQL) machine, you must open the TCP 135 port, and add Dsqlservr.exe to the Exceptions list. If your domain policy (domains policy) requires IPSec for network communication (network communication), you must turn on UDP 4500 and UDP 500 port.

Conclusion:

In addition to supporting the pair. NET program code, Visual Studio also provides a variety of options for debugging SQL Server 2005. In this article, we examined 2 of these choices: direct Database Debugging (Debugging) and application debugging. To debug a T-SQL database object directly, locate the object in Server Explorer, and then right-click to select "Step Into ...". This opens the debugger, and the first statement that runs to the object terminates. At this point, you can enter the object's statements to view and modify the parameter values. In the first step, we use the method to Products_ Selectbycategoryid stored procedures are set up.

Application debugging allows you to set breakpoints directly in a database object. When a client program (such as a asp.net Web program) invokes a database object that contains breakpoints, the program terminates and switches to the debugger interface. Application debugging is useful because it shows more clearly what program behavior is calling a specific database object. However, this method has more configuration and steps than direct database debugging.

Database objects can also be debugged through SQL Server Projects, and in the next chapter we examine using SQL Server Projects and how to use it to create and debug Management database objects (managed DB objects)

I wish you a happy programming!

Introduction of the author

Scott Mitchell, author of this series of tutorials, has six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. You can click to see all Tutorials "[translation]scott Mitchell asp.net 2.0 data tutorial," I hope to learn asp.net help.