How to use bind variable in asp.net (behind of button)?

Question

I used once, bind variable in pl/sql with stored procedure. (To speed up my query result).

For example in stored procedure I use it like so:

create or replace procedure dsal(p_empno in number)
as
  begin
    execute immediate
     'update emp set
     sal = sal*2 where empno = :x' using p_empno;
  commit;
end;

Now, My query is in front-end with (asp.net(vb) and pl/sql) not in a stored procedure. I want to use bind variable with a string query.

How can I use this structure in front-end (behind of button)?

Answer 1

xRather than using a stored procedure with dynamic SQL...

Use ADO.NET instead of dynamic SQL in a stored procedure. You can use bind variables like this:

OracleCommand command;
...
command.CommandType = CommandType.Text;
command.CommandText = "UPDATE emp SET sal = sal*2 WHERE empno = :x";
command.Parameters.Add("x", someValue);
...
command.ExecuteNonQuery();
...

Or, use a stored procedure but don't use dnynamic SQL like this:

create or replace procedure dsal(p_empno in number) as
begin
     UPDATE emp SET sal = sal*2 WHERE empno = p_empno;
end;