Id's say make an object to centralize data access (as such can more easily be tested and mocked out in unit tests) and in that one have your average parameter checking, escape sequence checking and then pass as parameter and you should bee sort of belt and suspenders and can optimize your data connections usage etc.
using (var cn = GetConnection())
{
using(var cmd = new SqlCommand("spMyProcedure", cn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", invoiceNumber);
cmd.Parameters.AddWithValue("@comment", FlareOnBadChars(comment));
cmd.Parameters.AddWithValue("@note", FlareOnBadChars(note));
var affect = (int)cmd.ExecuteScalar();
if (affect != 1)
throw new DataConcernsException(string.Format("Expected to insert data but failed with data: '{0}','{1}','{2}'", invoiceNumber, comment, note));
}
}
a nice addon is to check your string type parameters for escape sequence like the simplified one below ... you don't really want odd exceptions from parameter binding or sql ... much better to know exactly what's wrong if this thing occurs
private string FlareOnBadChars(string source)
{
if (!Regex.IsMatch(source, @"\A[^\\]+\Z"))
throw new DataConcernsException("That's not a nice string");
return source;
}
As an alternative consider using a repository framework like entity framework, this will take care of parameter handling for you and you cannot avoid doing it right so to say ;)