Stored Procedures: CommandType.StoredProcedure, Insert/Update/Rollback/Select.
Here is a great example of working with stored procedures in your asp.net code.
In this article by Scott Mitchell, you will find an example of how to create a stored procedure using SQLServer. This stored procedure, sproc_UpdateUsersCurrentActivity, also checks if a row exists, and if so updates, otherwise inserts. It also handles rollback for errors.
Scott also demonstrates how to use the CommandType.StoredProcedure and how to add parameters.
Following the first example, there is yet another stored procedure example that performs a select statement: sproc_GetUsersCurrentActivity. This demonstrates how to pass in 3 input parameters. Scott then demonstrates how to attach the results of the stored procedure to a Gridview.
The article is titled Examining ASP.NET's Membership, Roles, and Profile - Part 10, but provides examples for working with stored procedures that are invaluable for the new developer.