Return datareader from method

Question

I have the following method

public static SqlDataReader MenuDataReader(string url) { using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("spR_GetChildMenus", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@PageUrl", url); cmd.Parameters.AddWithValue("@MenuId", ParameterDirection.Output); cmd.Parameters.AddWithValue("@ParentId", ParameterDirection.Output); cmd.Parameters.AddWithValue("@TitleText", ParameterDirection.Output); cmd.Parameters.AddWithValue("@ExternalUrl", ParameterDirection.Output); cmd.Parameters.AddWithValue("@FullUrl", ParameterDirection.Output); cmd.Parameters.AddWithValue("@ChildCount", ParameterDirection.Output); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { //return reader; while (reader.Read()) { return reader; } } } } return null; } 

which he calls like

  SqlDataReader reader = MenuDataReader(url); if (reader.HasRows) { while (reader.Read()) { }} 

however im getting an error

Wrong attempt to call HasRows when closing the reader.

can someone help me

thanks

Answer 1

Do you really need a reader, or just need a way to iterate over the lines inside it? I suggest an iterator block. You can iterate over the rows in the source method and yield each row in the queue to the caller.

There is a trick with this method: because you give the same object with each iteration, there are times when this can cause a problem, and therefore you should ask the delegate to copy the contents somewhere nearby. I would also like to abstract this into a general method that can be used for any request, and use the same delegate method to process the parameter data, for example:

 private IEnumerable<T> GetRows<T>(string sql, Action<SqlParameterCollection> addParameters, Func<IDataRecord, T> copyRow) { using (var cn = new SqlConnection("Connection string here")) using (var cmd = new SqlCommand(sql, cn) { cmd.CommandType = CommandType.StoredProcedure; addParameters(cmd.Parameters); cn.Open(); using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { yield return copyRow(rdr); } rdr.Close(); } } } public IEnumerable<MenuItem> GetChildMenus(string url) { return GetRows<MenuItem>("spR_GetChildMenus", p => { //these lines are copied from your question, but they're almost certainly wrong p.AddWithValue("@PageUrl", url); p.AddWithValue("@MenuId", ParameterDirection.Output); p.AddWithValue("@ParentId", ParameterDirection.Output); p.AddWithValue("@TitleText", ParameterDirection.Output); p.AddWithValue("@ExternalUrl", ParameterDirection.Output); p.AddWithValue("@FullUrl", ParameterDirection.Output); p.AddWithValue("@ChildCount", ParameterDirection.Output); }, r => { return new MenuItem( ... ); } } 

Answer 2

As can be seen from https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand(v=vs.110).aspx :

  public static SqlDataReader ExecuteReader(String connectionString, String commandText, CommandType commandType, params SqlParameter[] parameters) { SqlConnection conn = new SqlConnection(connectionString); using (SqlCommand cmd = new SqlCommand(commandText, conn)) { cmd.CommandType = commandType; cmd.Parameters.AddRange(parameters); conn.Open(); // When using CommandBehavior.CloseConnection, the connection will be closed when the // IDataReader is closed. SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } } 

Answer 3

I would not return the reader - Dispose your connection and the teams close the connection. Instead, I would return a representative model of your data.

Answer 4

When you return inside the using statement, the code calls Dispose on the SqlConnection . This closes the DataReader , causing an error.

Answer 5

If you want to work this way, I suggest you do the following:

 public static SqlDataReader MenuDataReader(string url) { SqlDataReader reader; using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("spR_GetChildMenus", con)) { cmd.CommandType = CommandType.StoredProcedure; //Parameters stuff con.Open(); reader = cmd.ExecuteReader(); } } return reader; }