How to - retrieve records with 1 parameter - passed multiple times - looking for alternative solution

Currently, i have the following in my code, and works ok but takes longer to finish.
public datareader getempinfo(string param_emp_id)
{
...
SQL_COMMAND  =  " SELECT empID,name, address, telno
         FROM Employees
 WHERE emp_id=@emp_id "
...
parms[0].Value = param_emp_id;
return ExecuteREADER(
                    connection,
                    CommandType.Text,
                    SQL_COMMAND,
                    parms
                    );
...

the problem with this function is that it retrieves 1 record at a time.

the problem i am having is that:
there are about a million records in employee table
and i need to retrieve a thousand records at a time (typically around 1000-3000records)
i need to specify which employee info i need to retrieve

Doing this approach, its take a long time to finish.
during the looping
for each (empid_that_i_need)
{
FoundEMPData.add(getempinfo(empid));
}
takes roughly 3 records per second.

I need an alternative approach.
open a connection once
retrieve all the nessessary records
close the connection

any suggestions?

can this be done using dataadapter?
i mean, i have a list of emp_id in a datatable, then retrieve the related records from this table?

suggestions are appreciated. thanx

- storedprocedure wont work  - cause it has a maximum of varchar(8000)
- not sure about query builder where i generate a large query to be executed like the following. im not sure if there's a limit
SELECT empID,name, address, telno
         FROM Employees
 WHERE emp_id in ('empid1','empid2','empid3','empid4')"
Start Free Trial