Asp.net Forum

Ask Question   UnAnswered
Home » Forum » Asp.net       RSS Feeds

dataset vs. datareader

  Asked By: Clifford    Date: Aug 27    Category: Asp.net    Views: 1477

I'm building an application where the data layer is abstracted into a
couple of .cs classes. I was building a couple of funtions in these
classes that returned datasets so that I could bind the data to
controls. However, I know that datareaders use up less system resources
and may improve performace so I was going to switch my dataset functions
to datareader functions but once you close the connection within the
function, you loose the datareader. So I'm wondering if it's best to
keep my dataset functions... or is there a proper way to return a
datareader from a class method and make sure the connection object
within the method is safely closed and disposed of.



4 Answers Found

Answer #1    Answered By: Devlan Jones     Answered On: Aug 27

Don't close  the DataReader until you have
bound the controls:

private void BindListBox(DropDownList listControl, string Sql, string dataText,
string dataValue, string listText)

OleDbDataReader dr;

dr = DBUtility.GetDataReader(Sql);

if(dr != null)


if(dr.FieldCount > 0)


listControl.DataSource = dr;

listControl.DataTextField = dataText;

listControl.DataValueField = dataValue;


listControl.Items.Insert(0, listText);

listControl.SelectedIndex = 0;





To get DataReader:

public static OleDbDataReader GetDataReader(string SQLString)


OleDbConnection cn = new OleDbConnection(DBConnString);

OleDbDataReader dr;




OleDbCommand cmd = cn.CreateCommand();

cmd.CommandText = SQLString;

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);


catch (Exception e)


throw e;


return dr;


Answer #2    Answered By: Heru Chalthoum     Answered On: Aug 27

Also when you open the datareader, set the param to
CommandBehavior.CloseConnection eg:

(VB code):

myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

This will close  the connection  when you close the datareader.

Answer #3    Answered By: Murad Bashara     Answered On: Aug 27

public void RunProc(string procName, out SqlDataReader dataReader)
SqlCommand cmd = CreateCommand(procName, null);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

public SqlDataReader GetList(string prodid)
SqlDataReader dataReader = null;
// create data  object and params
Database data = new Database();
SqlParameter[] prams = { data.MakeInParam("@prodid", SqlDbType.VarChar, 30,
prodid) };

// run the stored procedure
data.RunProc("Get_List_sp", prams, out dataReader);
catch (Exception ex)

return dataReader;

SqlDataReader reader = null;
string prodid = Request.QueryString["product_id"];
reader = GetList(prodid);
..... bind controls

Answer #4    Answered By: Juan Reynolds     Answered On: Aug 27

there's bits missing from that, make a global var private
SqlConnection con; to the class

private void Open()
con = new SqlConnection("server=" + server + ";database=" + db + ";User Id=" +
user + ";password=" + pass);
//..........different ways to do this ia web.config, global.asax etc

private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
SqlCommand cmd = null;
// make sure connection  is open

cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure;

// add proc parameters
if (prams != null)
foreach (SqlParameter parameter in prams)

// return  param
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));

return cmd;

/// <summary>
/// close  the connection.
/// </summary>
public void Close()
if (con != null)

/// <summary>
/// Release resources.
/// </summary>
public void Dispose()
// make sure connection is closed
if (con != null)
con = null;

//Note execute nonquery requires you to close()

public int RunProc(string procName)
SqlCommand cmd = CreateCommand(procName, null);
return (int)cmd.Parameters["ReturnValue"].Value;

Its a bit of a mess and probs not worth it - but its all there now if its any
good to anyone. It's all from PetShop app anyway (altered a bit)
Probs shouldn't have sent it.

Didn't find what you were looking for? Find more on dataset vs. datareader Or get search suggestion and latest updates.