Geeks With Blogs
Mayank Sharma

.NET's SqlDataReader class is based on an active connection to the database. This means that while the SqlDataReader is in use, the SqlConnection that is serving the SqlDataReader is open and cannot be used anywhere else.

That is why, returning an SqlDataReader from a method is not stright forward like returning a DataTable or a DataSet. To read more about SqlDataReader, please see SqlDataReader class.

Though it is not straight forward, it is very simple. Here's a piece of code that would let you return a SqlDataReader from a method:

public SqlDataReader GetData(string item1, string item2)
{
    SqlDataReader reader = null;
    SqlConnection connection = new SqlConnection(ConnectionString);
    try
    {
        SqlCommand command = new SqlCommand();
        command.CommandType = CommandType.StoredProcedure;
       command.CommandText = "YOUR_SPROC_NAME";
       command.Parameters.Add("@Item1", SqlDbType.VarChar, 255).Value = item1;
       command.Parameters.Add("@Item2", SqlDbType.VarChar, 255).Value = item2;
        command.Connection = connection;
        connection.Open();
        reader = command.ExecuteReader(CommandBehavior.CloseConnection);
    }
    catch (Exception ex)
    {
        connection.Close();
       Response.Write(ex.Message);
    }
    return reader;

}

Posted on Tuesday, May 26, 2009 1:58 AM C# , ASP.NET | Back to top


Comments on this post: Return SqlDataReader from a method

# re: Return SqlDataReader from a method
Requesting Gravatar...
hi, i tried it and did not get any return value , view below:

MySqlDataReader dr = null;

using (MySqlConnection conn = new MySqlConnection(Utils.GetConn()))
{
conn.Open();

string sql = "proc_generaloptions_Countries_Get";

try
{
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

}
catch (Exception ex)
{
throw new Exception(ex.Message);
}

}
HttpContext.Current.Response.Write(dr.HasRows);
return dr;
Left by Michael Adom on Oct 19, 2011 5:48 AM

Your comment:
 (will show your gravatar)


Copyright © bullpit | Powered by: GeeksWithBlogs.net