Pass a list/array of values to database and retrieve related record set

I am working开发者_如何学运维 on a C# application that communicates with an Oracle database.

In my application, user selects multiple items from a list and request a report/summary of the data related to these selected items.

I am trying to pass a list/array of these items IDs to the database to filter the result set based on them. I already have the option of building my query at run time but I would prefer to use a better solution if existed.

What is the best way to do this regarding the performance and mentability??

Appreciate it.

You can use Linq To Sql to do this but note that you'll be limited to an array size of 2100.

If you have access to linq I believe this will help you. http://www.albahari.com/nutshell/predicatebuilder.aspx

I assume you need assistance creating a procedure that takes a list (correct me if I'm wrong). From the Oracle side, you might do something like:

create or replace package my_package as
  ...
  type t_id_tab is table of my_table.id%type index by pls_integer;
  ...
  procedure do_work(i_ids in t_id_tab);
  ...
end my_package;

create or replace package body my_package as
...
procedure do_work(i_ids in t_id_tab, o_affected_cnt out number) is
 begin
  forall i in i_ids.first..i_ids.last

    -- do something useful here, for example

    insert into some_table(col1, col2, col3)
    select col1, col2, col3 from some_other_table
    where id = i_ids(i);

    o_affected_cnt := SQL%ROWCOUNT;

    commit;
 end;
end my_package;