OleDbCommand doesn’t support named parameters

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…

Advertisements
This entry was posted in .NET, SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s