clrstoredprocedure - CLR Stored Procedure -

i wish use cryptography api in clr stored procedure.

i have created clr stored procedure , written select statement

sqlconnection conn = new sqlconnection(); conn.connectionstring = "context connection=true"; sqlcommand cmd = new sqlcommand(); cmd.connection = conn; cmd.commandtext = @"select * employee";  sqldatareader rdr = cmd.executereader(); 

now wish filter results using employee number stored in encrypted form in database going use cryptography methods.

now stuck how go filtering records sqldatareader.

i want return format sqldatareader, return multiple records clr stored procedure there 1 method sqlcontext.pipe.send() , method accepts sqldatareader objects.

please guide me.

i'm looking @ similar problem want manipulation before returning results. way can see @ moment use:

// note need pass array of sqlmetadata objects represent columns // constructor of sqldatarecord  sqldatarecord record = new sqldatarecord();  // use set methods on record supply values first row of data sqlcontext.pipe.sendresultsstart(record);  // each record want return // use set methods on record populate row of data  sqlcontext.pipe.sendresultsrow(record); 

then call sqlcontext.pipe.sendresultsend when you're done.

if theres better way i'd know too!