0

I use C# and I instantiate a DbCommand in order to execute an Oracle stored procedure.

My question is: why does the procedure receive the value through a different named parameter than the on in db?

When I add a parameter to the dbCommand:

...
string value = "Whatever"

db.AddInParameter(dbCommand,"WrongParamName",DbType.String);
db.SetParameterValue(dbCommand, "WrongParamName", value);

and I execute:

dataSet = db.ExecuteDataSet(dbCommand);

It will pass the dbCommand parameter to the stored procedure parameter correctly.

  • Why is that?

  • Does it set the value to the first parameter without a value or is it based on position?

  • If it's based on position why do we need the name for?

  • Is the name only to help the dev understand the code?

Zirbo Filip
  • 300
  • 1
  • 13
  • Hi! If you want to give you an answer as complete and correct as possible, you should provide the real query of the command, the real parameter name you use and the header of the stored procedure (the name, the name of parameters and there data types). – Nițu Alexandru Dec 21 '16 at 17:46
  • It's not rocket science, let's say 2 parameters and in code you change the naming, but they are sent to the stored procedure by order. 1st goes to 1st and 2nd to 2nd, even if the names are switched. – Zirbo Filip Dec 22 '16 at 07:29
  • C#: param1: "B" param2: "A" DB Stored Procedure: param1: "a" param2: "b" Final: "a <- B" and "b <- A". – Zirbo Filip Dec 22 '16 at 07:33
  • Note that caps lock is used only to differentiate. let's say "A" is the same name as "a" and "B" is the same as "b" – Zirbo Filip Dec 22 '16 at 07:34
  • I've asked for the sql command as string because I need to know if you execute the stored procedure like "EXEC sp_StoredProcedure '@'Param1 = '@'Param1, '@'Param2 = '@'Param2" or "EXEC sp_StoredProcedure '@'Param1, '@'Param2". In the first case, if you change the order of parameters in the sp, the call won't be affected. In the second case on the other hand, it does, because first parameter from the command fulfill the first parameter from the sp, the second to second and so on. (Ignore the ' before and after the @) – Nițu Alexandru Dec 22 '16 at 07:39
  • I do not have SQLscript to execute the procedure, I only have code in C# to execute it. – Zirbo Filip Dec 22 '16 at 07:43
  • You don't have the dbCommand.CommandText (assuming that dbCommand is SqlCommand data type)? – Nițu Alexandru Dec 22 '16 at 07:46
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/131222/discussion-between-zirbo-filip-and-alex). – Zirbo Filip Dec 22 '16 at 07:47

2 Answers2

1

I need to know if you execute the stored procedure like "EXEC sp_StoredProcedure @Param1 = @Param1, @Param2 = @Param2" or "EXEC sp_StoredProcedure @Param1, @Param2". In the first case, if you change the order of parameters in the sp, the call won't be affected. In the second case on the other hand, it does, because first parameter from the command fulfill the first parameter from the sp, the second to second and so on. If you cannot obtain a list of parameter names you can at least ask the other developer to not change the order and add parameters only to the end of the parameter list in stored procedure. If you cannot do this either, you have nothing else to do then to pray not to change them. With some scripts, you can determine the list of parameters though (see this How to get stored procedure parameters details?). You can execute this command like an ordinary select statement.

Community
  • 1
  • 1
Nițu Alexandru
  • 714
  • 1
  • 11
  • 33
0

After further investigation it seems that dbCommand passes parameters by order, not by name because this is how dbCommand is supposed to behave.

I did not find the purpose of the name, other than it only helps the developer to know which parameter is which.

I also did not find any property in dbCommand to set BindByName (a property in OracleCommand).

Zirbo Filip
  • 300
  • 1
  • 13