Using Standard Stored Procedures
Visual Studio makes it easy to create standard stored procedures without leaving the Visual Studio integrated development environment (IDE). Simply open Server Explorer, as shown in Figure 3. Right-click Stored Procedures and choose Add New Stored Procedure from the context menu. You’ll get a new editor window with the syntax required to create a new stored procedure already in place.
Figure 3 Use Server Explorer to access the stored procedures in a database or create new ones.
This article uses the same stored procedure for both the standard and managed stored procedure examples so you can compare the two techniques with greater ease. In this case, the stored procedure performs the simple task of retrieving all of the people from the Customers table who have a certain ContactTitle value. Listing 1 shows the code for the standard stored procedure.
Listing 1 Obtaining a list of customers based on title.
CREATE PROCEDURE dbo.SelectContacts ( @Title nvarchar(30) = ’Owner’, ) AS SELECT * FROM Customers WHERE ContactTitle = @Title
After you type the code, save it by clicking Save. The new stored procedure is added to the list in Server Explorer. (If you don’t see the new addition, right-click Stored Procedures and choose Refresh from the list.) Now, drag-and-drop this new stored procedure onto the DataSet Designer shown earlier (refer to Figure 2). The new entry is shown in Figure 4. Notice that Visual Studio automatically obtains the parameters for the stored procedure and the fields that the query returns. You don’t have to do any work to obtain this information.
Figure 4 Placing the new stored procedure in the DataSet Designer adds all of the object requirements automatically.