c# - Returning the SqlDataReader -

i trying either pass reader reference or have 1 returned. , having issues on return.

public static sqldatareader getsql(string businessunit, string taskid) {             const string connstring = "connection string";             sqlconnection conn = new sqlconnection(connstring);             sqlcommand command = new sqlcommand();             sqldatareader reader;             try             {                 conn.open();                 command.connection = conn;                 command.commandtype = commandtype.text;                 command.commandtext =                     "select * audits businessunit = @bu , taskid = @tid";                 command.parameters.addwithvalue("@bu", businessunit);                 command.parameters.addwithvalue("@tid", taskid);                 return reader = command.executereader(commandbehavior.closeconnection);             }             catch (exception ex)             {                 return null;             }                         {                 conn.close();             } }   sqldatareader reader = qaqcsqllib.getsql("job", "task1");  if (reader.hasrows) {    while (reader.read())       messagebox.show(reader[0].tostring()); } 

but following error

invalid attempt call hasrows when reader closed.

any ideas?

this problem:

finally {     conn.close(); } 

you're closing connection before method returns. reader isn't going able function without open connection.

(it's not clear why you've got reader variable @ all, given use when returning.)

returning sqldatareader in method opens connection tricky - because means don't have nice way of closing connection. better let caller pass in connection, @ point can have:

using (var connection = new sqlconnection(...)) {     using (var reader = qaqcsqllib.getsql(connection, "job", "task1"))     {         // use reader here     } } 

edit: noted scott, use of commandbehavior.closeconnection would allow closing reader close connection. however, makes other things trickier. example, you'd have dispose of connection if exception occurs (because caller won't have chance), not otherwise - still prefer approach.