You already need parameters independent of whether you're implementing stored procedures.
Right now, your code could be called with a query like SELECT * FROM Table WHERE ID = @ID, in which case, you already need to pass a Dictionary<string,object> params. Have your code fill in the Parameters collection of the command you already have, and test that, before worrying about stored procedures.
Once that works, you should simply create an overload that accepts a bool that says this is a stored procedure, then use it to set the CommandType property of the command.
Edit: Here's How I Would Refactor It
Step 1: Generalize Update
There's nothing special about the Update method that prevents it being used for other non-query operations, aside from the name. So:
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql)
{
return NonQuery(sql);
}
public static int NonQuery(string sql)
{
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
connection.Open();
return command.ExecuteNonQuery();
}
}
}
Step 2: What About Parameters?
Your current code couldn't even handle UPDATE queries that used parameters, so let's start fixing that. First, make sure it still works if no parameters are specified:
public static int NonQuery(string sql)
{
Dictionary<string, object> parameters = null;
if (parameters == null)
{
parameters = new Dictionary<string, object>();
}
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
foreach (KeyValuePair<string, object> p in parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = p.Key;
parameter.Value = p.Value;
command.Parameters.Add(parameter);
}
connection.Open();
return command.ExecuteNonQuery();
}
}
}
Once that works, promote parameters to be a parameter. This doesn't affect any existing callers of Update:
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql)
{
return NonQuery(sql, null);
}
public static int NonQuery(string sql, Dictionary<string, object> parameters)
At this point, test NonQuery with a parameterized query. Once that works, create an overload of Update that accepts the parameters:
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql)
{
return NonQuery(sql, null);
}
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <param name="parameters">Name/value dictionary of parameters</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql, Dictionary<string, object> parameters)
{
return NonQuery(sql, parameters);
}
Step 3: Take Stored Procedures Into Account
There's little difference in terms of how stored procedures would be handled. What you've already got is implicitly as follows:
using (DbCommand command = factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
command.CommandType = CommandType.Text;
So take the CommandType.Text and promote it to be a parameter in an overload:
public static int NonQuery(string sql, Dictionary<string, object> parameters)
{
return NonQuery(sql, CommandType.Text, parameters);
}
public static int NonQuery(string sql, CommandType commandType, Dictionary<string, object> parameters)
Finally, if you like, update Update:
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <param name="parameters">Name/value dictionary of parameters</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql, Dictionary<string, object> parameters)
{
return Update(sql, CommandType.Text, parameters);
}
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <param name="commandType">CommandType.Text or CommandType.StoredProcedure</param>
/// <param name="parameters">Name/value dictionary of parameters</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql, CommandType commandType, Dictionary<string, object> parameters)
{
return NonQuery(sql, parameters);
}
Of course, as a final exercise for the reader, you might replace all your Update calls with calls to NonQuery and get rid of Update entirely.
Of course, this simple technique doesn't handle output parameters, or situations where it's necessary to specify the DbType of the parameter. For that, you'd need to accept a ParameterCollection of some kind.