Replace OracleCommand by SqlCommand [C#/ASP.NET]

Question

I have to replace an OracleCommand by a SqlCommand in my code in the context of a database migration. In my code, I have the use of myOracleCommand.BindByName = true;. The property BindByName doesn't exist in the class SqlCommand so I was wondering if the SqlCommand is 'bindbyname by default' when it comes to parameters and I could thus just delete this line.

Parameters are bound by name in Sql Server. You will have to change all the parameter names in both the query and possibly the SqlParameter instances as well. — Igor
– Igor, Commented Apr 26, 2017 at 11:57
Yes I agree with the necessity to change the query and the instances of SqlParameter. But when it comes to the line of code I mentionned in my post, you agree that I can just delete it? — Neo
– Neo, Commented Apr 26, 2017 at 11:58
Please, check names within queries: Oracle uses : for parameters, when MS SQL requires @, e.g. select * from MyTable where id = @prm_id -> select * from MyTable where id = :prm_id — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Apr 26, 2017 at 12:05
@Neo - correct, you can delete that line. I also added an answer with more detail, binding by position is not possible in Sql Server which is why the SqlCommand does not have such a property. — Igor
– Igor, Commented Apr 26, 2017 at 12:12

Igor · Accepted Answer · 2017-04-26 12:18:41Z

1

You can ignore/not transfer that line (BindByName = true)

Parameters can only be bound by name in SqlCommand and never by position (which is what happens when you set BindByName = true in the OracleCommand).

You will have to change all the parameter names in both the query and possibly the SqlParameter instances as well.

In Oracle its :paramName, in sql server its @paramName. The parameter name in the SqlParameter instance can start with @ or not, if not it will be added by the command when the execution occurs.

edited Apr 26, 2017 at 12:18

answered Apr 26, 2017 at 11:58

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Guzman Over a year ago

I just want to add that one can bind parameters by position with a SQL Server backend using System.Data,Odbc or System.Data,OleDb. It is only System.Data,SqlClient that binds by name, which is the preferred API to access SQL Server from managed code.

Igor Over a year ago

@DanGuzman - good point, I updated my answer to specify SqlCommand instead of Sql Server.

Collectives™ on Stack Overflow

Replace OracleCommand by SqlCommand [C#/ASP.NET]

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related