HOW TO: Use the Enhanced Debugger in Visual Studio .NET (SQL Debugging)

SUMMARY

This step-by-step article shows how to set up and use SQL Debugging in a small Windows-based program.


back to the top

Requirements

The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs that you will need:
  • Microsoft Windows 2000 Professional (or Server), or Windows XP Professional (or Server) with the Microsoft .NET Framework installed.
  • Microsoft SQL Server 7.0, or later, with the Northwind database.
  • Visual Studio .NET Professional Edition, Enterprise Architect Edition, or Enterprise Developer Edition
Applies To:
  • Visual Studio .NET
  • Microsoft Visual Basic .NET
  • C#
  • SQL Server
back to the top

Use the Enhanced Debugger in Visual Studio .NET (SQL Debugging)

The following steps explain how to create a Windows Application project in Visual Studio .NET, configure your computer for SQL debugging, create a Button Click event handler that executes a stored procedure in the Northwind database, and then set a breakpoint in the stored procedure for debugging purposes:
  1. On the Start menu, point to Programs, point to Microsoft Visual Studio .NET, and then click Microsoft Visual Studio .NET.
  2. Click the New Project button. In Project Types, click Visual Basic Projects. In Templates, click Windows Application. In the Name text box, type HowToSQLDebug, and then click OK.
  3. Press CTRL+ALT+S to open Server Explorer. Right-click Data Connections, and then click Add Connection.
  4. In Data Link Properties, type localhost in the 1. Select or enter a server name dropdown list box.
  5. Under 2. Enter information to log on to the server, in the User name text box, type sa, and then click to select Blank Password (or type the appropriate credentials to connect to your SQL Server installation).
  6. In 3. Select the database on the server, click Northwind, and then click OK.
  7. You must set up the MSSQLServer service to run under an account that has Administrative rights. To do this, follow these steps:
    1. On the Start menu, click Control Panel, double-click Administrative Tools, and then double-click Services.
    2. In Services, locate the MSSQLSERVER service. Right-click the MSSQLSERVER service, and then click Properties.
    3. Click the Log On tab.
    4. Type the appropriate credentials for an account that has Administrator rights on the local computer, and then click OK. (If you receive a dialog box to notify you that the account you have added has been given the Log on as a Service right, click OK.).
    5. Right-click the MSSQLSERVER service, and then click Restart. The service restarts, and then logs on by using the service account that you specified in step d.
  8. Set up the permissions for DCOM. The steps for this vary, depending on your platform, so it is best to visit the following Microsoft Web site, and then follow the steps for your system:
    For example, the steps for a computer running Windows XP are the following:
    1. On the Start menu, click Control Panel, double-click Administrative Tools, and then open the Component Services snap-in.
    2. Expand Component Services , expand Computers, and then expand My Computer.
    3. Right-click My Computer, and then click Properties.
    4. Click the Default COM Security tab.
    5. Under Access Permissions, click Edit Default.
    6. Add your account to the list of users.
  9. Turn on SQL Debugging at the Project level. To do this, follow these steps:
    1. In Solution Explorer, right-click the HowToSqlDebug project, and then click Properties.
    2. In the left pane, expand Configuration Properties, and then click Debugging.
    3. In the lower-right corner, click to select the check box for the SQL Server debugging option, and then click OK.
  10. Press CTRL+ALT+X to open the ToolBox. From the Windows Forms group, drag a Button to Form1.vb [Design].
  11. Double-click Button1 to create a Click event handler. At the top, add the following namespace declarations so that you can access related classes by using shorthand notation:
    Imports System.DataImports System.Data.SqlClient
  12. In the event handler, type the following sample code, which calls the stored procedure that you will debug:
    Dim cn As New SqlConnection("server=localhost;database=northwind;trusted_connection=true")Dim cmd As New SqlCommand("CustOrdersDetail", cn)cmd.CommandType = CommandType.StoredProcedurecmd.Parameters.Add(New SqlParameter("@OrderID", SqlDbType.Int)).Value = 10256cn.Open()cmd.ExecuteReader(CommandBehavior.CloseConnection)MessageBox.Show("SPROC Successfully Executed")
  13. Set a breakpoint in the stored procedure. To do this, follow these steps:
    1. Press CTRL+ALT+S to open Server Explorer.
    2. Expand Data Connections, expand Your SQLServerInstance for Northwind, and then expand Stored Procedures.
    3. Double-click CustOrdersDetail. Position the insertion point anywhere in the WHERE clause, and then press F9. A burgundy-colored dot appears to the left, which indicates that a breakpoint is set.
back to the top

Complete Code Listing (Form1.vb)

Imports System.DataImports System.Data.SqlClientPublic Class Form1    Inherits System.Windows.Forms.Form#Region " Windows Form Designer generated code "    Public Sub New()        MyBase.New()        'Windows Form Designer requires this call.        InitializeComponent()        'Add any initialization after the InitializeComponent() call.    End Sub    'Form overrides dispose to clean up the component list.    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)        If disposing Then            If Not (components Is Nothing) Then                components.Dispose()            End If        End If        MyBase.Dispose(disposing)    End Sub    'Required by Windows Form Designer.    Private components As System.ComponentModel.IContainer    'NOTE: Windows Form Designer requires the following procedure.     'You can modified the procedure by using the Windows Form Designer.    'Do not modify the procedure by using the code editor.    Friend WithEvents Button1 As System.Windows.Forms.Button    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()        Me.Button1 = New System.Windows.Forms.Button()        Me.SuspendLayout()        '        'Button1        '        Me.Button1.Location = New System.Drawing.Point(128, 40)        Me.Button1.Name = "Button1"        Me.Button1.TabIndex = 0        Me.Button1.Text = "Button1"        '        'Form1        '        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)        Me.ClientSize = New System.Drawing.Size(292, 266)        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Button1})        Me.Name = "Form1"        Me.Text = "Form1"        Me.ResumeLayout(False)    End Sub#End Region    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click        Dim cn As New SqlConnection("server=localhost;database=northwind;trusted_connection=true")        Dim cmd As New SqlCommand("CustOrdersDetail", cn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add(New SqlParameter("@OrderID", SqlDbType.Int)).Value = 10256        cn.Open()        cmd.ExecuteReader(CommandBehavior.CloseConnection)        MessageBox.Show("SPROC Successfully Executed")    End SubEnd Class
back to the top

Verify That It Works

  1. Press F5 to run the program in debug mode.
  2. When the form loads, click Button1.
  3. The program stops on the SELECT statement (note that although you set the breakpoint in the WHERE clause, SQL treats all of the code in the SQL block as one instruction).
  4. Press F11 to step into the stored procedure.
  5. The SQL statement runs, and then the message box appears.
back to the top

Troubleshooting

You must use additional steps to debug stored procedures that are found on a Desktop Engine installation. To view these steps, visit the following Microsoft Web site:
back to the top

REFERENCES

For more information about SQL Debugging, visit the following Microsoft Web site:
back to the top
Proprietăți

ID articol: 318144 - Ultima examinare: 14 feb. 2017 - Revizie: 1

Microsoft Visual Studio .NET 2002 Professional Edition, Microsoft Visual Studio .NET 2002 Enterprise Developer, Microsoft Visual Studio .NET 2002 Enterprise Developer, Microsoft Visual Studio .NET 2002 Enterprise Developer, Microsoft Visual Studio .NET 2002 Enterprise Developer, Microsoft Visual Studio .NET 2002 Academic Edition

Feedback