Return 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 to do it?

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

Now I get the following error:

Exception Details: System.NullReferenceException: object reference not installed on the object instance.

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(); } 

Answer 1

The problem is that leaving the function (via the return statement) throws you out of the using blocks, and therefore the SqlDataReader and SqlConnections that you use are used. To work around this problem, try changing the function signature as follows:

 public static IEnumerable<IDataRecord> GetGeneralInformation ( int RecID ) 

and then update the middle of the function as follows:

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

For the finale, "How do I read?" It might look like this:

 string result = reader.First()["Status"].ToString(); 

Answer 2

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

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

// Function to return the results of SqlDataReader

  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 find https://stackoverflow.com/a/4148648/ A very simple and easy to use solution.

Answer 4

The problem is that you are creating a database connection inside your method.

If you intend to use resources to share databases between many methods, move the SqlConnection outside this area.

Thus, you can return the Reader from this function, and it will be saved.

Also, you do not need .Read () inside this function, just return the object.

Answer 5

By definition, a using statement must Dispose an object after it is called.

Therefore, after you return your data reader, it will be deleted.