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;