OleDbCommand with Parameters

Hi,

I have application connected to MS Access DB using OleDB. When creating commands (Insert/Update/Select) I use OleDbParamater class to insert data into command. Examples :

Select ::

OleDbCommand select_cmd = new OleDbCommand("SELECT * FROM " + ObjectTable.TableName + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Update ::

OleDbCommand update_cmd = new OleDbCommand("Update " + ObjectTable.TableName + " SET " +
ObjectTable.idParent + "=@." + ObjectTable.idParent + " , " +
ObjectTable.idParentUnder + "=@." + ObjectTable.idParentUnder + " , " +
ObjectTable.License + "=@." + ObjectTable.License + " , " +
ObjectTable.Type + "=@." + ObjectTable.Type + " ," +
ObjectTable.Language + "=@." + ObjectTable.Language + " , " +
ObjectTable.Name + "=@." + ObjectTable.Name + " , " +
ObjectTable.Checksum + "=@." + ObjectTable.Checksum + " , " +
ObjectTable.VText + "=@." + ObjectTable.VText + " , " +
ObjectTable.VInt + "=@." + ObjectTable.VInt + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Parametes:: (Adding in separate method -> AddParameters(OleDbCommand command); )

command.Parameters.Add("@." + ObjectTable.idObject, OleDbType.BigInt).Value = this.IDUpper;
command.Parameters.Add("@." + ObjectTable.idObjectUnder, OleDbType.BigInt).Value = this.IDUnder;
command.Parameters.Add("@." + ObjectTable.Name, OleDbType.VarChar).Value = this.Name;
command.Parameters.Add("@." + ObjectTable.idParent, OleDbType.BigInt).Value = GetUpper(this.IDParent);
command.Parameters.Add("@." + ObjectTable.idParentUnder, OleDbType.BigInt).Value = GetUnder(this.IDParent);
command.Parameters.Add("@." + ObjectTable.License, OleDbType.BigInt).Value = this.License;
command.Parameters.Add("@." + ObjectTable.Language, OleDbType.BigInt).Value = this.Language;
command.Parameters.Add("@." + ObjectTable.Type, OleDbType.BigInt).Value = (int)this.Type;

command.Parameters.Add("@." + ObjectTable.VText, OleDbType.VarChar).Value = String.IsNullOrEmpty(this.VText) ? null : this.VText;
command.Parameters.Add("@." + ObjectTable.VInt, OleDbType.BigInt).Value = this.VInt;
command.Parameters.Add("@." + ObjectTable.Checksum, OleDbType.BigInt).Value = this.Checksum;

Question: Does the order of adding parameters to command matter? Because allways when the order of parameters added is diffrent from order in command text, I get weird Exceptions . I thought that the name matters, not the order, but it seems that system doesn't care about the parameter's name, it just picks next parameter in command.Parameters when putting values. How is it?Do you mean that if it could matter during the addition of the parameters ? It does not, as the .add method only puts the parameter in the collection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||You know what? It does. And it is the only thing that matters (talking about OleDb) -> ORDER. Despite of using names (I could questionmarks instead of parameter's names). I've done this using Oracle DB and (of cource) namespace and everything worked fine, but OleDb looks to handle thing in it's own weird way. Example :

OleDbCommand selectCmd = new OleDbCommand( "Select * from Customers where CustomerName=@.name AND CustomerAge=@.age);

selectCmd.Parameters.Add("@.age", OleDbType.Integer).Value = 20;

selectCmd.Parameters.Add("@.name", OleDbType.VarChar).Value = "Michael";

this is NOT going to work !!! If the order of parameters added to command's parameters collection is diffrent from order of parameters used in command itself, it won't work.

OLEDBcommand is too slow

Hello,

I'm using an OLEDB Command in a DataFlow which performs a parametric query to update thousands of rowsets but it is very slow.

Is there an alternative ?

it's not the component's fault it is slow. SQL faster at set based operations.

You can insert all the rows into a temporary table and then using a SQL task, run the update by joining the two tables together.|||

Thanks!

I've tested this solution, but it seems to persisted a certains slowness.

My dataflow uses these components:

OLEDB Source --> Lookup --

|||As with anything, you have to find out where the bottleneck is. Is it the source? is it the lookup? is it the dest?

Start simple. How quickly does the source get the records? Dump everything into Trash Destination. Then add the lookup? is the lookup taking a while to cache the rows? Are you selecting the whole table or just the keys that you need? etc etc.

Finally, having a poor query in SQL source will result in a slow data flow. Are the tables correctly indexed in the source query. The dest? Two many indexes? Lookup? Indexes etc etc.

Is the final SQL update correctly indexed?

Listing two components in your data flow and saying they slow is the vaguest statement you could say.

Many reasons, more possible solutions.sql