When calling a parameterized stored procedure, if you pass parameters to a SqlCommand object
(i.e.
cmd.Parameters.AddWithValue("@productID", strProductID);
cmd.Parameters.AddWithValue("@productName", strName);
...
)
SqlCommand will correctly map the value with the parameter regardless of the order these AddWithValue methods are called. However, OleDbCommand doesn't support named parameters (MS confirmed it: http://support.microsoft.com/kb/316744). You have to make sure the order you call the AddWithValue method matches the order of your SP parameters. Obviously the SqlCommand is much more convinient because you can simply skip some parameters that have default values defined in SP. In my database utility class for SqlClient, I can pack the parameters in a HashTable and pass them in:
public DataSet ExecuteSPQuery(string spName, Hashtable param)
{
SqlConnection myConnection = null;
DataSet dataSet = new DataSet();
try
{
myConnection = ConnectToDB();
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
// a different way of looping a hashtable
if (param != null)
{
foreach (DictionaryEntry de in param)
{
cmd.Parameters.AddWithValue("@" + de.Key.ToString(), de.Value.ToString());
}
}
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.SelectCommand = cmd;
myAdapter.Fill(dataSet);
}
finally
{
if (myConnection != null)
myConnection.Close();
}
if (dataSet != null && dataSet.Tables.Count == 0)
return null;
else
return dataSet;
}
However for OldDb client, I have to use a queue to do that, and anytime there is a change to the SP, I have to change everywhere that queue is populated, or to define a method for each SP I call. How nice...
