I am working through some client data access layer code in a legacy application that only uses stored procedures to access a SQL Server Database.
Although you could write your own custom code which is exactly what this legacy application does, you might want to conside the Enterprise Library Data Access Application Block which has some pretty cool productivity saving and easy to use methods to handle stored procedures.
For example, if I have a simple Customers Table and want to insert a customer via an InsertCustomer StoredProcedure, I could do it very quickly in an untyped manner via:
var db = new SqlDatabase("[connectionstring]");
db.ExecuteNonQuery("InsertCustomer", "David", "Hayden", "test@test.com");
Now granted I would normally use an O/R Mapper for such a thing, but the point is the ease of using the Data Access Application Block with Stored Procedures. We simply just create a new Database Instance and then call the ExecuteNonQuery Method which will accept the name of a Stored Procedure and any parameters for the stored procedure. The beauty here is that the DAAB will automatically fetch the parameters from the Stored Procedure, cache them, and assign the values appropriately based on what we passed into the method. You will notice I am bypassing all the configuration stuff above and just creating an instance of SqlDatabase directly for use with SQL Server. If you want a bit more abstraction, you can call DatabaseFactory to make the code a bit more database agnostic:
var db = DatabaseFactory.CreateDatabase();
db.ExecuteNonQuery("InsertCustomer", "David", "Hayden", "test@test.com");
Either way, the code to execute a stored procedure is simple. Certainly you could write your own custom code to execute a stored procedure, but I would avoid it if possible and use Enterprise Library or some other well-known and well-supported global solution. Certainly most O/R Mappers will support Stored Procedures, such as SubSonic, LLBLGen Pro, LINQ To SQL, NHibernate, etc.
I really don't want to see too much custom data access code anymore if we can help it. I guess older legacy applications are what they are, but it makes maintainability as well as the learning curve a bit more troublesome.