Returning a SqlDataReader

Question

I have this code:

public static SqlDataReader GetGeneralInformation ( int RecID )
{
 using ( var conn = new SqlConnection( GetConnectionString() ) )
 using ( var cmd = conn.CreateCommand() )
 {
 conn.Open();
 cmd.CommandText =
 @"SELECT cs.Status, cs.Completed
 FROM NC_Steps s
 INNER JOIN NC_ClientSteps cs
 ON cs.RecID = s.RecID
 WHERE cs.ClientID = 162
 AND s.RecID = @value";
 cmd.Parameters.AddWithValue( "@value", RecID );
 using ( var reader = cmd.ExecuteReader() )
 {
 if ( reader.Read() )
 {
 return reader;
 }
 return null;
 }
 }
}

How do I reference this?

I tried this but it does not work.

SqlDataReader reader = GeneralFunctions.GetGeneralInformation();

Also how would I read from the reader?

Reader.GetString( reader.GetOrdinal( "Status" ) )

Edit:

I am now getting the following error:

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Here is the updated code:

public static IEnumerable<IDataRecord> GetGeneralInformation ( int ClientID )
{
 using ( var conn = new SqlConnection( GetConnectionString() ) )
 using ( var cmd = conn.CreateCommand() )
 {
 conn.Open();
 cmd.CommandText =
 @"SELECT i.GoLiveDate, i.FirstBonusRun, i.TechFName, i.TechLName, i.TechEmail, i.TechPhone, i.WebISPFName, i.WebISPLName, 
 i.WebISPEmail, i.WebISPPhone, i.FullFillFName, i.FullFillLName, i.FullFillEmail, i.FullFillPhone, d.FName,
 d.LName, d.HomePhone, d.Email
 FROM NC_Information i
 INNER JOIN Distributor d
 ON d.DistID = i.ClientID
 WHERE clientID = @value";
 cmd.Parameters.AddWithValue( "@value", ClientID );
 using ( var reader = cmd.ExecuteReader() )
 {
 while ( reader.Read() )
 {
 yield return reader;
 }
 yield return null;
 }
 }
}
protected void Page_Load(object sender, EventArgs e)
{
 IEnumerable<IDataRecord> reader = GeneralFunctions.GetGeneralInformation( (int)Session[ "DistID" ] );
 //string result = GeneralFunctions.GetGeneralInformation( Globals.GeneralInformation ).First()[ "Status" ].ToString();
}

• John Saunders: Why can't you just read it like any other SqlDataReader? Does it fail? If so, that's because of your using statements. You're closing the connection before you get to use the reader.
• John Saunders: You could return an object populated from the reader. And you still haven't said what didn't work.
• BrokenGlass: @JamesWilson: I would refactor this - it's not a good design to "leak" a reader to another method (even if you can make it work), just return the results.
• John Saunders: I wouldn't use ADO.NET directly at all. I'd use Entity Framework or some other abstraction layer. I'd also do it in a separate data access layer class library. You shouldn't be doing direct data access in web pages (though I sometimes make exceptions for Data Source controls).
• Joel Coehoorn: @BrokenGlass - not a fan of using List's for holding data from a database... they miss the whole point of using a datareader vs datatable/dataset, which is to only keep one record in RAM at a time.

Answer 1

The problem is that leaving the function (via the return statement) kicks you out of the using blocks, and so the SqlDataReader and SqlConnections you are using are disposed. To get around the problem, try changing the function signature like this:

public static IEnumerable<IDataRecord> GetGeneralInformation ( int RecID )

and then update the middle of the function like this:

using ( var reader = cmd.ExecuteReader() )
{
 while ( reader.Read() )
 {
 yield return reader;
 }
}

For the final "How do I read from it?" part, it might look like this:

int RecID = 12345;
string result = GetGeneralInformation(RecID).First()["Status"].ToString();

---

Note:

Coming back many years later, and I recognize this pattern has an issue, in that it yields the same object over and over, and relies on mutation of that object. This works in many cases, but can also have unexpected behavior in certain circumstances... calling .ToList() on the result, for example. Today, I tend to also have these methods ask for a transformation delegate to construct a real strongly-typed object for each row. That is, if I write the method at all. In many cases, I find Dapper good enough.

• James Wilson: Could you help me understand what IEnumerable<IDataRecord> and yield do? If not I can look them up, I appreciate the answer either way.
• Joel Coehoorn: @BrokenGlass - try it, it works great. The code is "lifted" into a separate class behind the scenes, and the dispose isn't reached until after enumeration.
• Joel Coehoorn: @JamesWilson The yield keyword creates what is called an "Iterator". It's an object that knows how to loop over a collection of some kind. IEnumerable<IDataRecord> is another way to describe the important parts of an SqlDataReader. You can use it in a foreach(;;) loop.
• Joel Coehoorn: Then you'll need a way to account for that... but now you're on a whole new question.
• Tim Schmelter: @James: That's what i was afraid of. The reader is closed when returning from the using-statement. Initialize a custom object as shown in this answer: stackoverflow.com/a/4912837/284240

Answer 2

Add your connection string to AppSettings section in app.config or web.config.

 public string GetSqlConnection()
 {
 return System.Configuration.ConfigurationManager.AppSettings["SqlConnectionString"];
 }

//Function to return SqlDataReader results

 public SqlDataReader executeReader(string sql, SqlParameter[] parameters=null)
 {
 SqlConnection conn = new SqlConnection();
 conn.ConnectionString = GetSqlConnection();
 conn.Open();
 SqlCommand cmd = new SqlCommand();
 cmd.Connection = conn;
 cmd.CommandText = sql;
 if (parameters != null)
 {
 cmd.CommandType = CommandType.StoredProcedure;
 foreach (SqlParameter p in parameters)
 {
 cmd.Parameters.Add(p);
 }
 }
 else
 {
 cmd.CommandType = CommandType.Text;
 }
 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 return reader;
 }

To use the function:

 string query = @"SELECT cs.Status, cs.Completed
 FROM NC_Steps s
 INNER JOIN NC_ClientSteps cs
 ON cs.RecID = s.RecID
 WHERE cs.ClientID = 162
 AND s.RecID = @value";
 //you can add more parameters by adding commas
 var parameters = new SqlParameter[] {
 new SqlParameter("@value", RecID )
 };
 SqlDataReader dr = executeReader(query, parameters);
 while (dr.Read())
 {
 //fill your controls with data 
 }
 dr.Close();

Answer 3

I believe this StackOverflow answer deserves mentioning. A very simple and pleasant-to-use solution.

Answer 4

The problem is you are creating the database connection within your method.

If you are going to share database resources between many methods, move SqlConnection outside this scope.

This way you can return the Reader from this function and it will persist.

Also, don't .Read() within this function, just return the object.