I use SPs and this isn't an SP vs code-behind "Build your SQL command" question. I'm looking for a high-throughput method for a backend app that handles many small transactions. I use SQLDataReader for most of the returns since forward only works in most cases for me.
I've seen it done many ways, and used most of them myself.
Methods that define and accept the stored procedure parameters as parameters themselves and build using cmd.Parameters.Add (with or without specifying the DB value type and/or length)
Assembling your SP params and their values into an array or hashtable, then passing to a more abstract method that parses the collection and then runs cmd.Parameters.Add
Classes that represent tables, initializing the class upon need, setting the public properties that represent the table fields, and calling methods like Save, Load, etc
I'm sure there are others I've seen but can't think of at the moment as well. I'm open to all suggestions.
