Programming with Stored Procedures in Visual Basic .NET (Part 1)
Stored procedures are an excellent way to optimize complex business solutions for database applications. In this article I will demonstrate how you can edit and run stored procedures in Visual Studio .NET and how to incorporate them into your Visual Basic .NET applications. ADO.Net has made this aspect of database programming accessible to all.
In the couple of pages that follow I have picked stored procedures that already exist from a couple of different databases that you have ready access to. I will use the MS SQL Server version of the Northwind database, which you can run on your desktop with a copy of MSDE (or you can substitute the MS Access version NWIND.mdb, which ships with Visual Studio 6.) I will also use the IBUYSPY portal's MS SQL Server database Portal, which can be downloaded for free from Microsoft. (The IBUYSPY portal was designed as a company portal application for a fictional company IBUYSPY, which sells spy goods. You can download and use the entire application royalty-free as a basis for building your Web applications or as a learning tool.) The Portal database will be used to demonstrate how to use output stored procedure parameters in the section "Passing and Retrieving Output Parameters".
I assume you have some basic familiarity with ADO.NET and subsequently will not be spending a lot of time on the basics, like connecting to the database, although this material is presented. For comprehensive coverage of all things ADO.NET pick up a copy of my "Visual Basic .NET Unleashed" from Sams Publishing.
Note: This article is presented in two parts this month. This article demonstrates editing and running stored procedures in Visual Studio .NET and the second half of the article demonstrates incorporating stored procedures into your Visual Basic .NET code.
Editing and Running Stored Procedures in Visual Studio .NET
You can edit, run, and even debug stored procedures in Visual Studio .NET. Let's take a quick minute to review editing and running stored procedures; unfortunately debugging seems limited to MS SQL Server and requires more configuration and setup than we have time for here.
Visual Studio .NET has a window called the Server Explorer. In addition, to providing you with access to other servers like the Event Log, Performance Counters, and other Windows Services, there is a list of Data Connections and SQL Server instances (see figure 1). Pick any single database in the SQL Servers list and the context menu—accessed by right-clicking—has options for editing, running, and debugging stored procedures. Select one of the menu items to run the indicated operation. For example, to run the Northwind stored procedure CustOrderHist, right-click that stored procedure and select Run Stored procedure. A dialog box will be displayed allowing you to enter parameters for the stored procedure (see figure 2).
Note: Debugging a stored procedure-started by clicking the Step Into Stored Procedure context menu item requires some setup and configuration. Refer to the help files for information on setting up SQL debugging.
Figure 1: The Server Explorer view in Visual Studio .NET with the context menu for the stored procedures shown.
Figure 2: The Run stored procedure dialog permits you to enter input parameters for stored procedures that you elect to run from the Server Explorer.
From the Run stored procedure dialog (shown in figure 2) you see that the IDE provides you with an opportunity to add input parameters. The dialog tells you the data type of the parameter (nvarchar), the direction (in), the name of the parameter (@CustomerID), and an input field for the value.
Hint: To open a table view in Visual Studio .NET, double-click on the table in the Server Explorer. This will open a window containing the contents of the table.
If we didn't author the stored procedure then we can open it in Visual Studio with the Edit Stored Procedure context menu and figure out where the data will be coming from. This stored procedure reads from the Customers, Orders, and Products tables, and a test CustomerID can be read from the Customers table. We can test this stored procedure with the Customer ID for Alfreds Futterkiste, ALFKI.
To test the stored procedure from the Server Explorer enter ALFKI in the Value column of the Run stored procedure dialog (see figure 2) and click OK. The results of the stored procedure will be displayed in the Output window as shown in figure 3.
Figure 3: Output from a stored procedure will be sent to the Output window in Visual Studio .NET.
To incorporate stored procedures into our Visual Basic .NET code we need to perform analogous steps in our Visual Basic .NET code.
Part 1 of our two part series demonstrates how to edit and test stored procedures in Visual Studio .NET. The Server Explorer is a useful addition to .NET that provides access to server applications, Performance Counter, and database connections. The database connections have their own context menus that allow you to manage the database from within Visual Studio .NET.
In Part 2 of our series I will review connecting to a database and how to externalize the connection string, use an adapter and command object to incorporate stored procedures into your Visual Basic .NET code. Stay tuned.
About the Author
Paul Kimmel is a freelance writer for Developer.com and CodeGuru.com. Look for his recent book "Advanced C# Programming" from McGraw-Hill/Osborne on Amazon.com. Paul Kimmel is available to help design and build your .NET solutions and can be contacted at email@example.com.
# # #