Creating an Application by Using a Stored Procedure
At this point, you should have a nifty new managed stored procedure installed on SQL Server. However, as far as any application you build is concerned, stored procedures are all the same; it doesn’t matter when they’re managed or rely on standard T-SQL to get the job done. Even when you view the stored procedure in SSMS, the most you’ll see is a slight difference in the icon. The managed stored procedures appear with all of the other stored procedures on your system.
Unfortunately, using a managed stored procedure in Visual Studio requires a bit more work. Don’t select your managed stored procedure while you create your dataset using the wizard (refer to Figure 1), or the resulting object will be unusable for drag-and-drop purposes. In addition, you won’t begin by dragging-and-dropping an object from Server Explorer onto the DataSet Designer. Instead, right-click the DataSet Designer and choose Add > Table Adapter from the context menu. In the Table Adapter Configuration Wizard, you’ll add the managed stored procedure you created. Be sure to choose Use Existing Stored Procedures when you get to the Choose a Command Type page of the Table Adapter Configuration Wizard. Selecting this option lets you choose your managed stored procedure on the next page.
After you finish creating the table adapter, notice that it lacks column entries like the ones shown earlier in Figure 4. That’s because Visual Studio can’t detect the columns; you have to add them manually. This is a major drawback of using the managed stored procedure approach, but it’s not difficult to correct. Simply right-click the upper area and choose Add > Column from the context menu. You can configure each of the columns by using the Properties window.
Once you’ve configure the columns, using a managed stored procedure is precisely the same as using a standard stored procedure. You make any required display configuration changes in the Data Sources window and then drag-and-drop the objects onto your form. Figure 8 shows the output from this portion of the example. This time I decided to use a DataGrid for output.
Figure 8 A DataGrid view of the customer list.