VS.NET can use languages such as C # and VB.NET to develop .NET architectures, can also be used to write CSS (laminated style sheets), HTML, XML, and SQL. Among them, the functionality of SQL is often neglected by us. Here I explain in detail how vs.net works with SQL.
????
???? Database Connectivity
???? VS.NET's Server Explorer can be very convenient to implement connection with the database server. Although this connection is not limited to SQL Server. Figure A shows the Server Explorer panel on the right side of the VS.NET workspace. You can find the object via the View drop-down menu, or click the tab to display the Server Explorer on the right side of the workspace. Right-click Data Connections in Server Explorer, which will appear options to connect to the new database. Figure B is a connection dialog. Where the Test Connection button can easily test the connectionability and connection correctness of the database.
????
????
????
???? Figure A: Visual Studio .NET Server Explorer
????
????
????
???? Figure B: Add a new database connection to Server Explorer
???? Once a connection is added in Server Explorer, it will appear in the server node of Server Explorer. At the same time, the server name and related options will appear below. The SQL Servers node can provide programs to access SQL Server's capabilities of data elements. Now, you can already start debugging SQL code, but some settings are required before this.
????
??? SQL Server settings
???? SQL Server contains an extended stored procedure that can be used to control the accessibility of the calling process. This stored procedure is called sp_sdidebug. The following T-SQL code can be used to allow a user to debug the stored procedure:
????
???? USE MASTER
???? Grant Execute
???? N sp_sdidebug
???? to username
????
???? In addition, the user must have permission to access the database and stored procedures. Once you do a good job, the user can start the real debugging process. In fact, the debugging process is very simple, just right-click the stored procedure in VS.NET Server Explorer, and select Step Into Stored Procedure. Figure C is an example of a stored procedure commissioning using the Northwind database. In this example, the stored procedure returns the highest price products from the database.
????
????
????
???? Figure C: Debug stored procedure via Server Explorer
???? When the debugging process begins, there is no difference with the daily debug C # or VB.NET code. Figure D is the view of the stored procedure in debug mode. The corresponding option can be implemented by the debug drop-down menu or shortcut. Here are a few shortcuts related to debug:
????
????
???? f5: Continue
???? shift f5: stop debugging
???? f11: Single step entry
???? f10: Single step skip
???? Shift F11: Single step jump out
????
????
????
???? Figure D: Storage procedure in debug mode
???? In addition, you can add breakpoints and view points to monitor debugging procedures. At the bottom of the VS.NET workspace, you can see the output result of the debugging process. Figure E shows the VS.NET workspace after debugging the stored procedure. From the graph, we can see debugging information: The return value of the stored procedure run is 0, which means that the run is successful.
????
????
????
????? Figure E: Debugging information after running the stored procedure
???? We can also display the Database Output from the drop-down menu on the debug window to display the output results from the drop-down menu on the debug window. The actual output and Figure F are similar.
????
????
????
???? Figure f: View the output result of the stored procedure
???? parameter
???? The stored procedure in reality is more complicated than our example. Most of the actual stored processes can be accepted or returned to one or more parameters. The debugger with vs.net is also very simple, you can see the code running after adding different parameters. When debugging a stored procedure that needs to be input, there is a window that pops up to accept parameters.
????
????? Figure G is a dialog box for receiving parameters. In this example, I chose to enter the starting and abort time stored procedure (annual sales), which will return sales in this period of time. In addition, in the input parameters, NULL or DEFAULT can also be selected, which can check if the stored procedure will have problems in the case of null values and default values.
????
????
????
????? Figure G: debugging process with parameters
???? to sum up
???? VS.NET's ability to develop, maintain, and debug a stored procedure, so that it fully replaces the work of the Query Analyzer such as SQL Server.
